Progress Bar Chart

Would you like to show progress on a KPI by putting a nice progress bar into your report? In this tutorial I’ll show you a very easy way of making a progress bar chart.

This chart too is a version of a thermometer chart with two single value data series. It is basically same chart as self filling chart. Only this is a bar chart instead of a column chart. Idea is basic, while one series is static, other will be dynamic, changing as we input data. By adding a label with percentage, we will have a progress bar chart.

We will need a setting like below for this chart.

We need a total cell that gets the sum of values from a list. And a cell that will contain a target value for comparison. When this part is done we need a simple addition for percentage part.

total% is equal to total/target (formatted as percentage), target% is equal to 1 (formatted as percentage).

Now select total% cells and insert a bar chart. Then select the chart and access “select chart data” from right-click menu. Here add a series (select target% for name and 100% as value). At this point you will have a bar chart with two data series.

Click on the total series and format it:

• fill: solid(blue)
• add white and bold label (inside end)

Click on the target series and format it:

• fill: no fill
• border: thick blue)
• Set series overlap to 100%.

Now you established progress bar chart. Remove any legend, axis, etc. and you are done.

Bonus:

You can make a little more formatting  and get another loading bar chart version from this chart. All you need to do is; make a copy of this chart and

• Set total% series to secondary axis (if you can’t select, set series overlap to 50%, but correct it when you finished).
• Set fill to “no fill”.
• Set border to black thick border
• Set label font color to black.

Set target% series fill color as shown in the picture

Now you have a nice 5th loading bar chart alternative.

If you like, you can download the example workbook and check the charts for practice.