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.
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,
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.
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.
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.