In-Cell Progress Bar for Each Record

In the meantime, here is a status bar you can drop into any [knock wood] base as needed.

statusBar
As configured, it requires you to add three Formula fields, {BarGoal}, {BarCurrent}, and {StatusBar}.

  1. {BarGoal} should be set to equal the ABS() value of whichever Airtable field contains your target goal.

  2. Assuming {Current} is the field containing your current measurement, {BarCurrent} should be set to

    IF({Current},ABS({Current}),0)
    
  3. {StatusBar} should be configured for the following formula:

IF(
    BarGoal,
    REPT(
        '█',
        INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*18
            )
        )&
        IF(
            ROUND(
                MOD(
                    (IF(
                        BarCurrent<=BarGoal,
                        BarCurrent,
                        BarGoal
                        )/BarGoal)*18,
                    1
                    ),  
                0
                )=1,
            '▌'
            )
    )

As provided, the status bar is 18 characters wide at 100%; this width can be varied by replacing the value ‘18’ with your preferred width. The status bar maxes out at 100%, and blank values of {BarCurrent} are treated as zero (0). The formulas for {BarGoal} and {BarCurrent} take the absolute values of your goal and current metrics to avoid generating errors, but negative target or current values probably won’t work the way you expect.

Optionally, the status bar can be preceded with the current percent of goal reached by using the following formula:

IF(
    BarGoal,
    REPT(
        ' ',
        3-LEN(
            INT(
                (IF(
                    BarCurrent<=BarGoal,
                    BarCurrent,
                    BarGoal
                    )/BarGoal)*100
                )&''
            )
        )&
        INT(
           (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )&
        '% '&
        REPT(
            '█',
            INT(
                (IF(
                    BarCurrent<=BarGoal,
                    BarCurrent,
                    BarGoal
                    )/BarGoal)*18
                )
            )&
            IF(
                ROUND(
                    MOD(
                        (IF(
                            BarCurrent<=BarGoal,
                            BarCurrent,
                            BarGoal
                            )/BarGoal)*18,
                        1
                        ),  
                    0
                    )=1,
                '▌'
                )
    )
14 Likes

Thanks so much! I really appreciate this response.

This is fantastic. Thank you for this great solution, W_Vann_Hall!

I can believe I am not the only person in the world named Max Goldberg but it is blowing my mind that I am not the only Max Goldberg who wanted to know how to make a progress bar in Airtable.

19 Likes

Replace the ’ █ ’ with ’ ░ ’ or ’ ▒ ’ or ’ ▓ ’ for various shades other then black.

1 Like

Edited the (genius) formula from @W_Vann_Hall to show the rest of the status bar shaded in grey.

Looks like this:

IF(
    BarGoal,
    REPT(
        ' ',
        3-LEN(
            INT(
                (IF(
                    BarCurrent<=BarGoal,
                    BarCurrent,
                    BarGoal
                    )/BarGoal)*100
                )&''
            )
        )
        &
        INT(
           (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )
        &
        '% '
        &
        REPT(
            '█',
            ROUND(
                (IF(
                    BarCurrent<=BarGoal,
                    BarCurrent,
                    BarGoal
                    )/BarGoal)*10
                )
            )
            &
        REPT(
            '░',
            ROUND(
                10-(IF(
                   BarCurrent<=BarGoal,
                    BarCurrent,
                    BarGoal
                    )/BarGoal*10),0
                )
            )
    )
10 Likes

Many thanks to @W_Vann_Hall and @Sara_Guirgis for your genius and sharing skills!

Really smart solution! I tried to simplify it a bit further, since there was quite a bit of repetitive code in the progress bar. I decided to make a second column Percentage that calculated the percent value to be used for the view.

I also had to do some tweaks on the characters used since they didn’t have the same width in my chrome browser on Mac and moved the number after the bar since it seems like airtable now trims spaces, so it was hard to get the bars to align correctly with the above code sample.

Also fixed a bug where it would sometimes render 11 boxes total because of where the ROUND method was located on the empty boxes loop.

Here’s my contribution:

IF(
    Percentage >= 0,
    REPT(
        '■',
        ROUND( MIN(Percentage, 1) * 10 )
    )
    &
    REPT(
        '□',
        10 - ROUND( MIN(Percentage, 1) * 10)
    )
    &
    ' '
    &
    INT( MIN(Percentage, 1) * 100 )
    &
    '%'
)
4 Likes

This may be a super silly question… however, where are you getting these rectangle emoji shapes? I only have squares in my default emojis and would love to use a color other than black. :slight_smile:

This progress bar is AMAZING! Thank you

How is that little black/red/grey box symbol created?

I can copy & paste it from this thread, but I’m not sure how to generate it on my own.

I tried going into my emojis and choosing a black/red/grey emoji box, but multiple emoji boxes in a row don’t connect to each other (they leave a little space in between each of them).

However, the black/red/grey boxes in this thread actually DO connect to each other when you have multiple of them in a row!

Anybody know how to recreate those boxes from scratch?

Dude this is awesome, is a lot simpler and worked for me! Is there a way we can change the bar colors?

This is an amazing Thread, and thanks for providing such a great formula. I tweaked it a bit to ensure the spacing stays aligned when the Percentage # in front of the Progress bar has 1,2, or 3 digit pad.

So you have this Screenshot 2021-04-09 at 16.29.31 vs Screenshot 2021-04-09 at 16.31.15

IF(LEN(INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )&'')=1,INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )& "%-- "&
    REPT(
        '⬛️',
        ROUND(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*10
            )
        )
        &
    REPT(
        '🔲',
        ROUND(
            10-(IF(
               BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal*10),0
            )
        ),IF(LEN(INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )&'')=2,INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )& "%- "&
    REPT(
        '⬛️',
        ROUND(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*10
            )
        )
        &
    REPT(
        '🔲',
        ROUND(
            10-(IF(
               BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal*10),0
            )
        ),IF(LEN(INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )&'')=3,INT(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*100
            )& "% "&
    REPT(
        '⬛️',
        ROUND(
            (IF(
                BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal)*10
            )
        )
        &
    REPT(
        '🔲',
        ROUND(
            10-(IF(
               BarCurrent<=BarGoal,
                BarCurrent,
                BarGoal
                )/BarGoal*10),0
            )
        ))))
3 Likes

Hi @Lindsey_Bavaro and @ScottWorld ,

These ‘emojis’ are a little different in that they are like another type of text character. So like ‘a’ ‘b’ and ‘c’ are all text characters, so are ‘§○•◄►♪’. (This is over simplification but I think gets the idea across) The color is then decided by the text color you are using in whatever tool you are typing in: MS Word/Google Docs, for example.

On Windows you can use the ‘alt code’ to type the different characters using the ALT button and the number pad. See https://www.alt-codes.net/ for a list of the codes and how to type them.

Or, alternatively, you can just copy the character you want from the website and paste it wherever you want to put it - I’d recommend doing this in most cases, especially if you are not using a Windows computer :slight_smile: Also, you are able to copy and paste emoji’s directly in a lot of places as well (including Airtable text, see below). I use https://emojipedia.org to search for emojis to copy and paste.

To show that you can use any(or most) of these characters, I edited Martin_Gudmundson 's simplified progress bar code to create a progress bar with stars and circle emojis!

IF(
    Percentage >= 0,
    REPT(
        '⭐',
        ROUND( MIN(Percentage, 1) * 10 )
    )
    &
    REPT(
        '⚪',
        10 - ROUND( MIN(Percentage, 1) * 10)
    )
    &
    ' '
    &
    INT( MIN(Percentage, 1) * 100 )
    &
    '%'
)

This would display this at Percentage value 50:

⭐⭐⭐⭐⭐⚪⚪⚪⚪⚪ 50%

So you can basically substitute the stars and circles for any (or most) characters! Here’s some other examples:

●●●●●○○○○○ 50%

★★★★★⛤⛤⛤⛤⛤ 50%

✿✿✿✿✿❀❀❀❀❀ 50%

♥♥♥♥♥♡♡♡♡♡ 50%

💚💚💚💚💚🤍🤍🤍🤍🤍 50%

🟩🟩🟩🟩🟩⬜⬜⬜⬜⬜ 50%

▓▓▓▓▓░░░░░ 50%
3 Likes

Wouldn’t let me tag @Martin_Gudmundson, but wanted to make sure he got credit, so tagging him here! :slight_smile:

Any suggestions on how to modify the formula so that if the Percentage is between a certain range it’s ‘:yellow_square:’ or ‘:red_square:’?

Thank you everyone for sharing ideas and formulas. I’m agree that this is a missing feature in AirTable over competitors. I hope it will be available in a near future. In the meantime, I use single select field with automation to achieve this. It’s not perfect, but it does the job. :wink:

Happy holidays everyone!

image

4 Likes

Interesting!! Thanks for sharing

Hey @PR1 how did you do the single select field like that?

Thanks!

Hi @Ryan4 .

First: create the single select field
For this first step, it depends of the precision you’re looking for. For exemple, if you would like to have 1% step, you will need to create manually 100x value in a single select field. In my example, I decide to have a step of 5% (0%, 5%, 10%, …) so I created 20x value in a single select field, with the value representing the % and the color associated with. And this is the fun part because you can play around with colors to get a visual result based on your needs.

EDIT: add spaces in the value, before the number and percent sign, until you reach the correct size. For example, no space for 0%, 3 spaces for 5%, 6 spaces for 10% and so on.

Second: create the formula field
Once done, you need to create the formula field that will compute the % based on the columns/values you wish. For example, estimated time and time spent. Here it’s important that the result has the same precision and same value returned as the single select field.

Final step: create an automation rule
In this final step, you need to create an automation rule that will be triggered on formula field updates. When the value of the formula field changes, the automation rule will affect this value to the single select field.

That’s it!

NOTE: if the value returned from the formula field is not part of the single select field list, the automation rule will create a new value.

Hope this help. If you have questions, let me know.

2 Likes

Hi there,

Looking for the copy paste (single) elements so I can add it to my base.

Thanks