Create Excel Charts and Dashboards with Color Alerts for Actual and Target Values

consulting frameworks and tools
Excel Chart with Color Alerts for Actual vs Target Values

As a consultant you want your client to see when they have exceeded or fallen short of metrics and KPIs you have decided are critical to success. With this technique you can create charts that notify clients with colors.

Your Excel charts should alert you with color differences between actual and target values. Here’s a quick way to create an Excel chart that shows the color coded difference.

In this example the Delta, or difference, is a column containing the calculation,

Delta = Actual – Target

The chart is created from the month labels and the Delta column. To create a chart using these separate columns, you will select the month column and the Delta column. First, select the blank cell above “Jan” and all the month cells below. Select the separate Delta column while preserving the months date selection by holding down Ctrl, clicking on “Delta” and then dragging downward. You should now have both Months and Delta columns selected.

Now insert a 2D column chart based on this selection. Clean up the chart junk by clicking on one of the internal horizontal grid lines and pressing the Delete key. Since there is only one data set there is no need for a legend. Click the legend to select it and press the Delete key. Make the title more descriptive by selecting it and typing a better title. Your chart should now look similar to this,

Excel column chart showing all differences with the same color

You can make your differences chart more visually striking by changing the negative values in the chart to a different color from the positive numbers.

Right-click on a column in the chart and select Format Data Series. In the Format Data Series dialog box select Fill and Solid Fill. Check the Invert if Negative check box. Instead of one fill color box you will now see two fill color boxes as shown in this figure.

You can make your differences chart more visually striking by changing the negative values in the chart to a different color from the positive numbers. Right-click on a column in the chart and select Format Data Series. In the Format Data Series dialog box select Fill and Solid Fill. Check the Invert if Negative check box. Instead of one fill color box you will now see two fill color boxes as shown in this figure.

Use Format Data Series and Fill to invert colors of negative values on a chart.

In the left color drop down select the color you want for positive value columns. In the right color drop down select the color you want for the negative value columns.

Make the difference between actual and budget (target) pop out with a different color.

Make the difference between actual and budget (target) pop out with a different color.

For a finished chart all you have left to do is adjust the vertical scale, then start monitoring results and taking actions to improve performance.

Strategies, Tactics, and Tips for
Consultants and Independent Professionals

Stay up to date with the latest opportunities, strategies, tactics, and tips for
independent consultants and professionals.

We hate SPAM. We will never sell your information, for any reason.