3 Steps you can take to spend less time updating Excel charts each month (Hint: use a strategic approach to your Excel chart worksheet)

With organizations measuring more metrics than ever before, the time it takes to update all the charts you create for senior managers seems to increase each month. You’ve streamlined the analysis using tools in Excel like Power Query, Pivot tables, and various formulas. Once you’ve identified the insights you need to communicate, it takes a lot of manual effort to update all the charts they expect to see.

You can dramatically reduce the time it takes to update Excel charts if you structure your worksheet and apply some of the same ideas you used to streamline the analysis of the data. Here are three steps you can take.

Step 1: Separate cells for analysis from cells used for charting

In the cells you use for analysis, you take care to separate cells used for different tasks so that the analysis is clear and easy to understand. The same approach should be used to separate the cells for charting from the cells for analysis. When the cells used in the chart are the cells you used in analysis, it can result in a lot of manual work updating the cell references every month. Instead, separate your worksheet into three sections.

• The first section is where you will do the analysis. Here is where you use the tools in Excel to gather, massage, and analyze the data.
• The second section is the Summary of Insights. This is the section where you gather the key information that you want to show in a chart. The cells you reference in this section may change each month depending on the messages you need to communicate to the senior leaders. You can also use formulas in these cells to automatically select the top values to highlight to the executives.
• The third section is the Chart Data Table. These are the only cells used to create each chart. Each chart will have its own set of cells so each cell has only one purpose. The cells in this section only refer to cells in the Summary of Insights, either through a simple direct reference or use in a formula.

By separating the worksheet into these three sections, chart updating becomes automated because when the data in the Summary of Insights changes, the chart data changes automatically and the chart is updated (see the video below to see this in action).

Step 2: Use calculated parameters

In your analysis, you calculate parameters based on that month’s data which then allows you to perform comparisons and other types of analysis. Similarly, charts can use parameters to increase the flexibility of the chart to adapt to different values each month.

Many charts should be created using calculated parameters instead of fixed values. For example, when creating a multiple-width overlapping column graph to compare actual values to budget and previous year values, the actual columns will be placed on a secondary axis. Instead of making the maximum of each axis the same by typing the value into the task pane in Excel, use a cell to calculate the maximum of the values for all data series. Then use that parameter to set the scale of the primary and secondary axes through the use of a single point line chart on each axis. When the values change next month, the parameter updates and the graph remains accurate. Parameters are also very helpful when creating small multiple column or bar charts or positioning explanatory text beside bars.

Step 3: Use multiple data series

In your analysis, each column or row has its own meaning. Similarly for charts, separating the data into multiple series allows each series to have its own meaning.

When each data series has only one meaning, formatting is easier and changes in the data do not require changes in formatting. For example, split components in a waterfall graph into those that show positive movement and those that show negative movement. Then you can format each series with a meaningful color and data labels can include meaningful direction indicators. Use formulas in the chart data table to determine which series a value belongs in based on whether it is positive or negative. Use the NA formula to populate the non-used series so the value does not appear in the graph. Use the IFNA formula to perform mathematical calculations where a cell may contain an #N/A value. By using multiple data series and the NA and IFNA formulas you can create a chart that intelligently decides on what data is shown and in the correct format.

Video: These 3 steps in action

The video below shows how using these three steps makes updating the monthly cost variance chart automatic when the data for the current month is approved.

By applying similar approaches that you use in streamlining the analysis of data, you can structure your chart worksheet and reduce the time it takes each month to update charts for senior managers and leaders.

Note: If you are an association who would like to re-publish this article in your newsletter to members, just contact me and I will be happy to give you permission to do so.