Actual vs Targets Chart in Excel

Bottom Line: Learn how to create a column or bar chart that compares two or more numbers. Actuals versus budget, goal, forecast, target, etc.

Skill Level: Intermediate

Video Tutorial

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

Feel free to download the workbook that I use in the video so you can play around with the charts and reconstruct your own versions.

Actual Vs Targets Chart.xlsx (68.5 KB)

Charting Actuals vs. Targets

When the boss wants to know if we've hit our goals for the month (or quarter, or year, etc.) it's always a good idea to give him or her a visual report in the form of a chart that shows each goal in relation to the actual numbers achieved.

Chart showing actual numbers vs. target numbers
Column Chart that displays Actual versus Budget and Forecast

While you can compare these numbers side by side, it looks a whole lot cleaner to overlay these numbers, especially when there are lots of target goals to fit onto the chart.

In this tutorial, I'll explain how to format a column or bar chart to achieve a cleaner look (and impress the boss). Here's an example:

Excel Bar Chart Actual vs Target Forecast Budget
Bar Chart that displays Actual versus Budget and Forecast

The advantage of this technique is that it only uses the primary axis. There are alternative solutions that use a secondary axis. However, using only the primary axis will prevent errors and reduce maintenance as the data changes in the future.

The chart can also be quickly converted between a column (vertical) or bar (horizontal) chart, which makes it more versatile than some other solutions.

Start with Your Data

For this type of chart, you are going to need at least four columns of data.

Formula to calculate the max
  1. Your actual numbers. These are the gray filled bars that you see in the chart above.
  2. Your target numbers. These are represented by the horizontal lines. You can have more than one set of target numbers. In my example, I include both budget and forecast numbers, so I have a total of five columns in my data set.
  3. A Max Invisible column. In this column, a simple formula using the MAX function returns the largest value in your data range. This is used to cover the vertical lines of the target bars in the column chart. Add a cushion to the formula so that none of your target lines are at the very top of the chart. In my example, I added 50, but if you are working with larger numbers you might need to add a number in the thousands or millions. This ensures that the invisible line won't overlap the largest number in your data set.
  4. A Max Outline column. This is the exact same data as the Max Invisible column, and we use it to create that nice perforated border that you see above. This series is optional.

Create a Chart

Here are the steps to create the chart. I recommend you watch the video as it will likely help you to follow along.

  1. Select all of the data in your table, including the header row and column.
  2. From the Insert tab on the Ribbon, click on the icon that shows a column chart. This opens a drop-down menu, and you can select 2D Clustered Column Chart.
  3. You'll notice that the data points are listed along the X axis, and we want the regions listed there instead. So we can easily flip that. With the chart selected, go to the Chart Design tab on the Ribbon and click the button that says Switch Row/Column.
  4. Format the chart to your preference. I will detail how mine is formatted in the section below, but for now, your chart should be looking something like this:
Column Chart with no overlap

Formatting the Bars in the Chart

  1. Remove the gridlines by clicking on any one of the lines so that they are selected and hit the Delete key.
  2. We will leave the bar that shows the actuals alone, but moving on to the first target bar, right-click on it and then choose Format Data Series. That will bring up the Format Data Series pane.
  3. Select the Fill & Line button, which looks like a tipping paint can.
  4. Choose No fill for the Fill option. Choose Solid line for the Border option. Select a color of your choice. Increase the width to 1.5 pt or more.
  5. If you have more than one target column, follow the same procedure as in Step 4, but use a different color.
  6. Moving on to the Max Invisible column, again choose No fill, but this time, make the border color the same as the chart background. In our case, that color is white. This essentially makes the border invisible. Make the width the same as the widest width you've used so far. In our case, that is 1.5 pt.
  7. For the Max Outline column, choose No fill, Solid line, a light gray color, and select a dash type, if desired.

At this point, your chart should look similar to this:

Actual vs Target Chart with no overlap

Overlaying the Columns

Now that we've formatted the columns to be the way we want them, we want to overlap the columns so that they are essentially combined into one column per data point. To do this,

  1. Click on the Series Option icon on the Format Data Series pane. This is the one that looks like three columns.
  2. Using the slider for the Series Overlap, slide the indicator all the way to the right so that it reaches 100%.

Additional Formatting

To make things just a bit cleaner, you can do a little more formatting. See the video if you don't know how to do these things. These are all optional, according to your taste:

  1. Change the fill color of the Actual column to a light gray.
  2. Remove the horizontal line on the X axis.
  3. In the legend, delete the Max Invisible and Max Outline.
  4. Change the Maximum amount from 500 to 450 on the Y Axis.
  5. If you are using more than one target, and you have some equal amounts, the indicator lines for those amounts might not be distinguishable. You can change the thickness of the lines to make it so that both colors show in your chart.
  6. You can move the legend to the top instead of the bottom.

Again, here's how the end result appears.

Actual vs Target Chart

It's important to note that the order of the columns matters. If your data is in a different order than the one that is presented above, some of your columns may hide others that you want to be visible. You can change the order of the data in your chart by choosing Select Data on the Chart Design tab on the Ribbon.

Converting a Column Chart to a Bar Chart

Changing your chart to to a bar graph is actually really easy.

  1. With the chart selected, go to the Chart Design tab on the Ribbon, and then select Change Chart Type.
  2. Choose a Clustered Bar Chart from your options.
  3. You'll just need to perform the overlap procedure again. (Under Series Options, slide the indicator to the right until it reaches 100%.)

So now we have the exact same information, but the data is represented horizontally in a bar chart:

Actual vs. Target Bar Chart

More Bar Chart Options

I also wanted to show you that you can add data labels to your chart in order to show:

Values

Data labels showing values

Percentage of Target

Data labels showing percentage of target

Learn more about how to go about that from this tutorial.

Variance

Data labels show variance

If you are interested in learning more about variance, I recommend this tutorial: Variance on Clustered Column or Bar Chart.

Conclusion

Charting an actual number in the same bar or column as its target number makes for great visual comparison. If you have questions or comments about creating or using these helpful charts, I hope you will write them in the comments section.

8 comments

Your email address will not be published. Required fields are marked *

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly