Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

In yesterday’s blog entry, I showed an example of using the Wingdings2 font style with Conditional Formatting to show a checkmark in a cell for completed items. Here’s how you can employ a progress bar using a the REPT function with the Wingdings font style.

As shown in the following picture, a company lists its projects and assigns a number in the “Needed Points” column to indicate how many points are required for the project to be completed. As numbers in the Task List section are entered that represent the points for that respective task, the progress bar is updated in the Completion Progress column.



Step 1
Select the cells in the Completion Progress column, type in the formula…
=IF(B5<=SUM(C5:F5),REPT("n",B5)&REPT("n",SUM(C5:F5)-B5), REPT("n",SUM(C5:F5))&REPT("o",B5-SUM(C5:F5)))
...and press Ctrl+Enter.
Note, I purposely broke the formula into two lines here, so it can be viewed in full on all browsers, but you'd enter the formula as usual on just one single line in the cell.



Step 2
Format the formula range as font style Wingdings. For added emphasis I bolded the range and set the font color to red.



And that's it. Just fill in the Task List numbers. Recommended: format the formula range for Locked and Hidden, and protect the worksheet.