In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.

Using conditional formatting is easy to create a horizontal progress bar. This can be useful for things like task project analysis where you are monitoring tasks within a project. You can use the percentage entry and a conditional format to have a visual representation of the percentage within the cell.

Horizontal

The default settings for conditional formatting can be amended. You need to do that if you need to create a single cell progress bar. In the image below I have entered a percentage in a cell.

If I apply a data bar conditional format to that cell Excel will treat it like the maximum value in the range and the data bar will fill the whole cell. I prefer to use the Solid Fill.

To convert this into a progress bar we need to manipulate the settings for the data bar. To amend a conditional format, with the cell selected, click the Conditional Formatting drop down and select Manage Rules.

Double click the rule in question and the interface will display it. Each different type of conditional format has a slightly different interface. The data bar interface is shown below.

We need to change the Minimum and Maximum Types selections to Number via the drop down. Change the values to be 0 for the minimum and 1 for the maximum.

Job done click OK and OK again.

Vertical

A vertical progress bar is achieved by using a Sparkline chart. Sparkline charts were added back in Excel 2010. They are on the Insert ribbon on the right-hand side of the chart section. They allow you to create a small chart in a cell that plots a single data series.

You can hack the Sparkline by only plotting a single data value and then by changing the upper and lower limits of the axis value. The image below has the row height changed to allows a vertical progress bar.

Select the cell and click the Insert ribbon and click the Column Sparkline

In the Location range click the same cell and click OK.

Again the cell will be filled.

we need to change the Axis options. With the cell still selected click the Design tab and on the right-hand side click the Axis drop down and in the Minimum Value Option section choose Custom Value.

The entry should already be zero so just click OK.

Click the Axis drop down again in the Maximum Value Option section choose Custom Value. Enter 1 and click OK.

Job done.

Values above 100% will not extend past the cell in either example.

Download example file