A compelling chart in three minutes…

…for Actual vs Budget, Target, Last Year

Excel Chart - ActvsBudvsTgtvsLY

If

  1. you need to plot chart to show actual sales vs budget, target, and LY sales;
  2. agree that the chart on the right is a better visualization such purpose;
  3. want to know how to create the chart on the right;

Then

  • please continue to read this post.

In business world, we often compare actual sales to various benchmarks such as budget, target, and last year.  In this post, I am going to show you step-by-step how to make a compelling chart for this purpose. This is basically a simplified version of bullet chart, and is super-easy to create. I hope you find it useful and relevant.

Indeed, the make the following chart is easier than you may have thought… well, in less than 3 minutes when you know how.

Excel Chart - ActvsBudvsTgtvsLY 1

 

You may download Sample File to follow along.

1) Prepare the data

This is very straight forward:

Excel Chart - ActvsBudvsTgtvsLY 2

2) Ride on a default Cluster Column Chart

Note: All screenshots on this post are prepared with Excel for Office 365.  You may find the UI a bit different if you are using Excel 2010 or before, but the elements are more or less the same.

  • Select the range of data for plotting chart
  1. Go to Insert tab
  2. Select Column Charts
  3. Select 2-D Cluster Columns

Excel Chart - ActvsBudvsTgtvsLY 3

Now we should have this basic and the most commonly seen chart…  Let’s do some make up to it.

Excel Chart - ActvsBudvsTgtvsLY 4

3) Change Chart Type

  1. Select the chart
  2. Go to Design Tab of Chart Tools
  3. Change Chart Type
  4. Select Combo type when the “Change Chart Type” dialog box opens

Excel Chart - ActvsBudvsTgtvsLY 5

Tip: The above steps can be done by right-clicking on any chart series and select “Change Series Chart Type…”

Excel Chart - ActvsBudvsTgtvsLY 5.1

4) Edit the chart types for the four data series

  1. Select the “Custom Combination” of Combo chart
  2. For data series of Actual, select “Clustered Column”
  3. Make it as “Secondary Axis” (This step is crucial)
  4. For data series of Budget, select “Line with markers”
  5. For data series of Target, select “Line with markers”
  6. For data series of LY select “Clustered Column”
  7. Press OK

Excel Chart - ActvsBudvsTgtvsLY 6

 

Now we should have the chart like this:

Excel Chart - ActvsBudvsTgtvsLY 8

5) Remove the lines and change the markers (for both Budget and Target series)

  1. Right-click the line for “Budget”, select Format Data series
  2. On the Format Data Series pane, select Fill and Line
  3. Select No line (under Line)
  4. Select Marker
  5. Open the Marker Options
  6. Select “Build-in” and select the market type you want. (“-” is use in our example)
  7. Change the colors of Fill and/or Border (if you wish) (Red is used in our example)

Excel Chart - ActvsBudvsTgtvsLY 7

Repeat the steps above for “Target”, but choosing a different marker. (“x” is used in our example)

Now, we should be able to see a chart like this:

Excel Chart - ActvsBudvsTgtvsLY 9

6) Adjust the column width

  1. Select the data series of LY (by clicking on any one of the columns; please be careful not to select only one column of the series, we need the whole series)
  2. Select Series Options
  3. Set the Gap Width to “80%” (or any other value of your preference)

Note: The gap here means the gap between columns, the narrow the gap is, the wider the column is.

Excel Chart - ActvsBudvsTgtvsLY 10

Now we should have this:

Excel Chart - ActvsBudvsTgtvsLY 11

7) Change the Fill color of the columns

Excel Chart - ActvsBudvsTgtvsLY 12

8) Delete the Secondary Y-axis

  • Select the Secondary Y-axis and press DELETE

Did you notice that the visual doesn’t look right? The Actual sales of Dec is 72 while that of LY is 82.  How come it appears on the chart that the Actual is “taller” than LY on Dec?

It is because the Actual series is set as “Secondary Axis”, which has a different scale from the “Primary Axis”.  Please be very careful about this.  Depends on the dataset, both primary and secondary axis may have the same scale when you first set it up.  This may not alert you on this “potential and critical” error.  So please ensure you “DELETE” secondary Y-axis.

Excel Chart - ActvsBudvsTgtvsLY 13

Here we go!

Excel Chart - ActvsBudvsTgtvsLY 14

Easy, right?!

Honestly, once you know the steps, you may transform the chart from the default to this compelling chart in less than three minutes.  So why not?

Please let me know what you think about this by leaving comments.

Bonus topics

Wait…  the above steps haven’ show how to make the marker for “Budget” wider, as shown below:

Excel Chart - ActvsBudvsTgtvsLY 1

You are right.

I leave it in the “Bonus time” on my 2nd video (which is coming soon now available ), in which not only do I show you how to create this chart step-by-step, but I also discuss briefly why this is better than the default cluster columns.  I will embed the video here once it is ready (voice-over, subtitles and editing are in progress).

Please stay tuned. 😉

Well… after one week, the video is now ready for watching.  Hope you enjoy it:

Note: Please turn on CC for English subtitles

A better way is to subscribe to my YouTube Channel.  You support is highly appreciated. 🙂

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Chart and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.