A rare manager in his practice is not faced with the need to visualize the results achieved in comparison with those originally planned. In different companies, I have seen many similar charts called “Plan-Fact”, “Actual vs Budget”, etc. Sometimes they are built like this:
The inconvenience of such a diagram is that the viewer has to compare the plan and fact columns in pairs, trying to keep the whole picture in his head, and the histogram here, in my opinion, is not the best option. If we are to build such a visualization, then it is definitely more visual to use graphs for the plan and the fact. But then we are faced with the task of a visual pairwise comparison of points for the same periods and highlighting the difference between them. Let’s try some handy techniques for this.
Method 1. Up-down bands
These are visual rectangles connecting in pairs the points of the plan and fact graphs on our diagram. Moreover, their color depends on whether we completed the plan or not, and the size shows how much:
Such bands are included on the tab Constructor – Add Chart Element – Up/Down Bands (Design — Add Chart Element — Up/Down Bars) in Excel 2013 or on a tab Layout – Advance-Decrement Bars (Layout — Up-Down Bars) in Excel 2007-2010. By default they will be black and white, but you can easily change their color by right-clicking on them and selecting the command Up/Down Bands Format (Format Up/Down Bars). I highly recommend using a translucent fill, because. the solid line closes the original graphs themselves.
Unfortunately, there is no easy built-in way to adjust the width of the stripes – for this you will have to use a little trick.
- Highlight the built diagram
- Press the keyboard shortcut Alt + F11to get into the Visual Basic Editor
- Press keyboard shortcut Ctrl + Gto open the direct command input and debug panel immediate
- Copy and paste the following command there: ActiveChart.ChartGroups(1).GapWidth = 30 and press Enter:
Of course, parameter (30) can be played around to get the width you need experimentally.
Method 2. Chart with zone filling between plan and fact lines
This method involves a visual fill (it is possible with hatching, for example) of the area between the plan and fact graphs:
Quite impressive, isn’t it? Let’s try to implement this.
First, add another column to our table (let’s call it, let’s say, Difference), where we calculate the difference between the fact and the plan as a formula:
Now let’s select the columns with dates, plan and difference at the same time (holding Ctrl) and build a diagram with areas with accumulationusing tab Insert (Insert):
The output should look something like this:
The next step is to select the rows Plan и Fact, copy them (Ctrl + C) and add to our diagram by inserting (Ctrl + V) – in our “sandwich in the section” two new “layers” should appear on top:
Now let’s switch the chart type for these two added layers to a graph. To do this, select each row in turn, right-click on it and select the command Change the chart type for a series (Change Series Chart Type). In older versions of Excel 2007-2010, you can then select the desired chart type (Graph with markers), and in the new Excel 2013 a dialog box will appear with all rows, where the desired type is selected for each row from the drop-down lists:
After clicking on OK we will see a picture already similar to what we need:
It is easy to figure out that it remains only to select the blue area and change its fill color to transparent No fill (No Fill). Well, and bring general shine: add captions, a title, remove unnecessary elements in the legend, etc.
In my opinion, this is much better than the columns, no?
- How to quickly add new data to a chart by copying
- Bullet chart for displaying KPI
- Video tutorial on creating a project Gantt chart in Excel