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

Excel

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.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.

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

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.

The best part is when people ask, “How did you make the columns different colors?” Just send them a copy of this newsletter.

 

Share the power...