Track your growth with a simple, one-cell progress bar

 

by Kara Hiltz

Application:

Microsoft Excel 2007/2010/2013

Operating System:

Microsoft Windows

Most of the data you keep in Excel revolves around goals: meeting goals, developing goals, and exceeding goals. There are so many ways to track your progress, such as charts and formulas. But sometimes you just want a quick visual marker to see where you stand. Excel offers data bars as part of its conditional formatting so you can create a simple, one-cell progress bar in your worksheet.

To add a progress bar to our spreadsheet, we’ll:

  • Format the cells in which we want to create progress bars.
  • Enter a formula that calculates the percentages that we want our progress bars to display.
  • Set up conditional formatting options for the progress bars.
  • Look at alternative ways to format the progress bars.

 

Although in-cell bars with conditional formatting originated in Excel 2007, that version offered only gradient bars. Excel 2010 has added a slightly new feature— solid bars. These solid bars look a bit more like traditional bar charts and you can use one color instead of choosing between a two-color or three-color scale. One-color solid bars look more clear-cut, as shown in Figure A.

Setting up a spreadsheet with one-cell progress bars is just a matter of preparing the cells so that they include the data that the progress bars will be based on and then applying conditional formatting. We’ll take you through the process and offer a few different formatting options.

A:

ime17c02A

Solid color data bars are new in Excel 2010.

Prep your worksheet

In our example, we’ll create progress bars to demonstrate how well our business has met its monthly sales goals for 2011. For each month, we included the actual sales versus the sales target, as shown in Figure B.

B:

ime17c02B

This worksheet will benefit from a quick visual indicator of how each month’s sales measured up to the sales target.

 

To prepare the worksheet for progress bars:

  1. Select the range D2:D13 and change the fill color to black by clicking on the Fill Color button fill color found in the Home tab’s Font group and selecting black from the dropdown palette. This creates a border between our progress bars and the worksheet data.
  2. Click and drag column E’s right border to extend the column. Make this column as wide as you want your progress bars.
  3. Select the range E2:E13 and use the Fill Color icon again to create a background color for your progress bars. We chose a light gray color.

 

Your worksheet should now look similar to ours, as shown in Figure C.

C:

ime17c02C

A background color for your progress bars makes them more pronounced, but leave the cells without any fill if you prefer.

 

Add progress bar values

Depending on what kind of data you’re tracking in your progress bar, you’ll need to add values to the progress bar cells so that Excel knows how much of the cell each progress bar should fill. Most likely, you’ll use percentages, just like we will in our sales example.

We want to find out what percentage of the monthly sales target represents our actual sales. For instance, if our sales target is $50,000 but the actual sales are only $25,000, the progress percentage would be 50%. We’ll use a percentage formula to calculate these percentages, and then format the cells as percentages.

 

To calculate and display our percentages:

  1. Enter =B2/C2 in cell E2. Press [Enter] to accept the fo[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage