6 Best charts to show % progress against goal

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

Best charts to show % done against goal - Excel charts

So today, lets talk about best charts to show % progress against a goal.

Please download example file and keep it handy while reading the rest of this tutorial.

Data for these charts

For all these charts, we will use below data:

Data for best charts to show %done against goal values - Excel charts

Chart #1: Conditional Formatting Icons + % values

Traffic light icon-sets chart - show % done against goal

This is my all time favorite. It is very easy to implement and works really well.

All you have to do is,

  1. Select the % completion data
  2. Go to Home > Conditional Formatting > Icon sets
  3. Select 3 traffic lights
  4. Edit the rule as shown below:
    Conditional formatting rules traffic light icon set
  5. Done!

Why you should use this?

  • Very easy to set up.
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.
  • Looks great
Keep in mind that traffic light icons do not work well when printed or shown to color-blind people

Keep in mind:

  • The traffic lights in Excel are not great for color-blind people.
  • The traffic lights do not look good when printed in black-and-white (or gray scale)

Related: Never show simple numbers in your dashboards

Chart #2: Conditional Formatting Data Bars

Databars conditional formatting chart - show % done against goal

Another easy and quick answer.

  1. Select % completion data
  2. Go to Home > Conditional Formatting > Data bars
  3. Select Solid Fill if available.
  4. Done!
  5. Extra step: Adjust maximum bar size to 100% so that you can see relative progress better.
Conditional formatting rules for databar

Why you should use this?

  • Very easy to set up.
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.

Keep in mind:

  • By default the maximum value in your data takes 100% of the cell width. So make sure you set this to 100% for better depiction of progress.

Chart #3: In-cell bar charts

Using REPT formula and in-cell chart - show % done against goal

If for some reason you cannot use databars, then rely on in-cell bar charts. These are simple to setup and works great in many situations where conditional formatting may not be an option.

  1. Assuming your % data is in A1,
  2. In adjacent cell (B1),  write = REPT(“|”, A1*100)
  3. You will get a lot of pipe symbols | in this cell.
  4. Select the cell and change font to Playbill
  5. Adjust font size and color if needed.
  6. Done!

Why you should use this?

  • Very easy to set up.
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.
  • Can be handy when making dashboards or reports (where conditional formatting may have limitations)

Keep in mind:

  • The font & size has impact on how in-cell chart is displayed. Use either Playbill or Script fonts.

Chart #4: Pies

Conditional formatting pie chart icons chart - show % done against goal

Conditional formatting pie charts are a simple alternative to show % progress data.

The process is same as traffic light icons. Make sure you adjust pie icon settings as per your taste.

Why you should use this?

  • Very easy to set up.
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.

Keep in mind:

  • Pie chart icons have only 5 stops. So they are not really pies.
    Pie chart iconset settings - conditional formatting
  • Not everyone likes pie charts. Make sure your boss / customers dig them.

Chart #5: Color scales or heat maps

Color scale or heatmaps to  chart - show % done against goal

When you have a lot of items to track, your focus is really on which items are lagging (or leading). In such cases, a color scale (also known as heatmap) can work very well. It colors cells based on their value. For example, the darker a cell color is, the more that particular project is done and vice-versa.

Why you should use this?

  • Very easy to set up.
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.

Keep in mind:

  • Make sure the color starting & end points are well contrasted. Else the color scale looks bland.
  • By default color scales show the values too. To hide them use ;;; custom cell formatting code (how to).

Chart #6: Thermometer charts

Thermometer chart - show % done against goal

This is my favorite technique. It works very well for data like this.

Tutorial on how to create thermometer charts.

Why you should use this?

  • Easy to understand
  • Scalable. Works the same when you have 20 or 200 or 2000 items to track.

Keep in mind:

  • If any value is more than 100% the chart may not explain it properly.
  • Make sure the axis min & max are set to 0 and 1 respectively.
  • You need a dummy column with 100% in it to show outline of thermometer.

Download Examples

Click here to download example workbook. It contains all these charts.

Special bonus for you:

As a bonus, the download workbook also has 5 step tracker to make you awesome in Excel. Go ahead and download now.

What is your favorite chart to show % progress?

My most favorite chart is thermometer. The next is traffic light icon-set.

What about you? Which of these 6 is your favorite? Please share your chart in the chart. If you use something else altogether, please tell me. I am eager to learn from you.

More on comparison charts

Budget vs. Actual Chart
Budget vs. Actual Chart

Just like my project manager, I am sure your manager too loves tracking & comparison. If so, please go thru below articles to learn few more tricks to impress her.

  • Us vs. Them – compare one value with many using interactive chart
  • Best charts to compare budget with actual values
  • Indicating lower & upper bounds on a chart
  • Customer service dashboard – a case study in comparison
  • Exploring Flu trends in excel chart – a case study in heat maps for comparison

Now if you excuse me, I have to report to my new project manager: my wife. She is asking me about the progress of taking down Christmas lights. And I am still at 9%.

The post 6 Best charts to show % progress against goal appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: http://feedproxy.google.com/~r/PointyHairedDilbert/~3/b3uMpA_AHYg/

Leave a Reply

Close Menu