How to make visual comparisons of actual sales to target goals in an Excel chart

Charts are impressive tools when they tell a story, and they do so by highlighting details that might otherwise get lost in the chart’s noise. The quieter a chart is, in my opinion, the more effective it is. This is easily represented by clarifying details in a chart that compares actual sales by personnel to each person’s target goal. A good chart will tell the data’s story with little to no effort on the viewer’s part to find the hidden clues—details that matter. In this article, I’ll show you two charts that represent sales and target goals in Excel. We’ll start with the default charts and change formatting to achieve a clean chart that exposes details management will certainly want to know.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. Excel Online will display the charts, but you can’t create them online. For your convenience, you can download the .xlsx and .xls demonstration files.

Prepare your data

To get the expected results, the way you structure your data matters. Sometimes, you can change axis and series values once the chart exists, but for the most part, you should start with a stable structure design, and that’s not as hard as it sounds. In addition, remember that you don’t have to chart the entire data set; you can chart the columns and rows you want, even if they’re not contiguous. We won’t be dealing with that sort of chart this time around, but it’s good to know.

SEE: How to use XLOOKUP() to find commission benchmarks in Excel (TechRepublic)

For our purposes, we’ll track actual sales amounts and each person’s quota or target goal. Many people in sales work with specific expectations. Formulas that determine commissions and bonuses usually evaluate the differences between the actual sales and the targets; commission and bonus amount probably use a higher percentage when targets are routinely met or exceeded.

Figure A shows a simple data set with no real structural issues. The chart to the right is a 2D Column chart that groups each person’s actual sales and target amount. (I entered a new chart title, but you don’t need to do so.) Notice that the selected data includes the header text and doesn’t include the Commission column.

Figure A

As is, the chart is OK, but it’s a bit difficult to pick out who’s meeting their target and who isn’t. You have to look at each set of bars to discern anything meaningful, and that’s not how charts are supposed to work!

If you don’t know how to insert a chart, do the following:

1. Select B2:D8—the data you want in the chart. Include the header text so Excel can generate a legend automatically.
2. Click Insert and then click Insert Column Or Bar Chart in the Charts group.
3. Choose the first option in the first line. That’s it!

Creating this chart was easy, but I think we can all agree that it isn’t as effective as it could be. The values, both target and actual, are too close to one another to really highlight what’s going on (or isn’t). For instance, you might totally miss that James didn’t meet his target and that Luke, the new guy is already surpassing his initial goal. James needs a pep talk; Luke has earned a pat on the back.

How to overlap two series in Excel

Overlapping the two series is one way to clear things up a bit. In other words, let’s move the target series so that it’s on top of the actual value series. The difference between each person’s target and actual sales will be more apparent—or at least, we think they should be. Sometimes it works; sometimes it doesn’t.

To generate the overlapping series, do the following:

1. Click any of the bars in the actual series (the blue bars) to select the entire series.
2. Right-click the section and choose Format Data Series from the resulting submenu, which will open the Format Data Series pane (to the right).
3. Change the Plot Series On option to Secondary Axis, as shown in Figure B. Doing so adds a new axis to the right side. It’s easy to see that James hasn’t met his target but now June and Luke are a bit of a mystery. They’ve exceeded their targets, but by how much?
4. Return to the chart and select the target series (the orange columns).
5. Change the Gap Width to 100. As you can see in Figure C, everything is more apparent, but we have that secondary (vertical) axis to deal with. The secondary axis allowed us to format the two series differently, but we no longer need it, and frankly, it adds confusion.
6. Select the secondary axis to the right and press Delete. Figure D shows the resulting overlapping series, and it makes sense!

Figure B

Figure C

Figure D

I don’t know about you, but I find the default colors a bit too garish. It isn’t necessary for the technique, but you might want to change the color of one or both of the series. You can choose any colors you like, but I’m going to change the target columns, the orange color to a lighter blue by choosing a different theme. To do so, click the Format icon in the top-right corner of the chart—it’s the paintbrush. In the resulting pane, click Color (at the top). Choose a more pleasing theme. I chose the next-to-the-last one, which is shades of blue. Feel free to choose any theme you want. Alternatively, you could right-click the series, choose Format Data Series and click the Fill option to change only the one series.

SEE: Office 365: A guide for tech and business leaders (free PDF) (TechRepublic)

The overlapping columns do a better job of highlighting details than the default chart, but it isn’t the only way to represent these values. In fact, because the values are all so close, James’ failure to meet the target is still easy to miss—not as easy, but I think we can still do better.

How to make a combo chart in Excel

The problem that we’ve dealt with from the beginning is that all of the values are similar so there’s not a lot of contrast—contrast that would draw our attention and make some details more obvious. This time we’ll represent the target values as a line instead of a column by using a combo chart.

To get started, select the same data, B2:D8 and then choose Insert Combo Chart in the Charts group on the Insert tab. Choose a Clustered Column Line chart. Figure E shows the new chart (using the same default blue and orange). Without doing a thing, it’s clear where everybody stands. However, you might change the color theme as we did to the first chart. As before, it isn’t necessary to meet the technical requirements; it’s just easier on the eyes.

Figure E

At this point, you could stop, but I’ll show you a change you might like. Specifically, we’ll remove the connecting lines and reformat the markers. The result is a cleaner chart, but you do lose the flow of the connecting lines that can emphasize the differences between personnel. But in this case, comparing the personnel may not be of interest.

Our line doesn’t have markers, so that’s the first step. If you already have markers, you can skip this next section. To display markers, select the line and then click the contextual Chart Design tab. In the Type group click Change Chart Type. In the resulting dialog, change the Target setting from Line to Stacked Line with Markers, as shown in Figure F, then click OK.

Figure F

To remove the connecting lines, double-click it to open the Format Data Series pane, click the Fill & Line option, and then click Line at the top (if necessary). If you don’t like the round markers, change them as follows:

1. Click Marker (at the top).
2. In the Marker Options section, click Built-in.
3. Choose the horizontal line from the Type dropdown and increase its size; I changed it to 15.

As you can see in Figure F, removing the connecting lines produces a cleaner chart, but the truth is, this change is preferential. A quick glance is all you need to see that James is the only person not meeting the target. A second quick observation is that none of the staff are producing well above their target. In addition, it might be time to increase Luke’s target, which will most likely mean also increasing his commission percentage. He’ll be happy to hear that.

Figure F

There are other ways to achieve visual results that highlight the data’s details. You could use sparklines or even conditional formatting if you don’t want to create a traditional chart. You can use either of these techniques for any kind of individual comparison where the relationship between the charted entities isn’t as important as the individuals. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see