One of my responsibilities at work is to produce a monthly statistics package and top-level commentary. A single spreadsheet serves as a data repository and there are approximately 50 charts based on differing data that we use to visually present our monthly results. All of the charts are designed to be dynamic. By dynamic I mean that they automatically update as statistics are entered into the spreadsheet. The "upgrade" to dynamic charting vs. manual updates has probably saved at least one to two days of work as well as decreased the chance of error.

One of the easiest charts to automate is an actual vs. plan chart. The chart below is an example of a simple actual vs. plan dynamic chart:

The data source for the chart is below:

The monthly data is entered into the yellow shaded range C4:C15.

The chart is composed of three different chart types - a simple Line chart, an Area chart, and an XY Scatter chart. The actual data in the range F4:F15 is the base of the Line chart. The formula in cell F4 is below:

=IF(C4,C4,NA())

This formula is copied into each cell in the range F4:F15. The chart source data - series dialog box for the actual numbers looks like this (note that the example is on Sheet2 of the spreadsheet):

The plan numbers in the range G4:G15 simply refer to the numbers in the range D4:D15. The plan data uses the Area chart type. The chart source data - series dialog box for the plan numbers looks like this:

The next task is to automate the positioning of the data labels. In this case, we want the data labels to reflect the actual and plan numbers for the current month. The XY Scatter chart is used for data labeling positioning. Two XY Scatter points are used to track the actual and plan data points for the current month. The first XY data point reflects the actual month number and is based on the range J5:J6. Formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J5 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J6 (Y Axis point) is:

=OFFSET($C$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the actual XY Scatter point numbers looks like this:

Finally, the second XY data point reflects the plan month number and is based on the range J10:J11. As with the actual data labels, formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J10 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J11 (Y Axis point) is:

=OFFSET($D$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the plan XY Scatter point numbers looks like this:

In another variation of the chart above, the plan is shown going into the future only. The chart is made up of a Line chart with two series and an XY Scatter chart with two data label points. Below is an example:

The data source for the chart is below:

In this example, all formulas and chart sources are the same except for the formulas in range G4:G15. The formula in cell G4 is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

This formula is copied into each cell in the range G4:G15.

What's nice about using dynamic techniques such as these is that so many people don't. If you're lucky enough to work for one of these people, they'll still think that it takes more time than it really does to complete the work. As a result, set your email to send something to him or her every 1/2 hour and take the rest of the afternoon off. Unfortunately, once the secret's out they'll want everything now.