How-To: Automatically Update Graphs in Excel
Published: July 17, 2014
Author: Oliver Eldredge
Imagine if you could save yourself an hour a week (or maybe more) by automating the process of updating charts and graphs. Well…yup, you know where I’m going. There’s an Excel technique for that, so put your geeking cap on, and let’s get to it.
The process that we are going to cover involves the application of several Excel formulas and functions. We have an appendix file with instructions on how they work at the end of this post, and they are:
- Count
- If
- Named ranges
- Defining series values in charts and graphs
- Sumifs
- Offset
If you’d like to follow along with the process, here’s a supplemental excel document.
Old vs. new process
Let’s see if this sounds familiar. The old, manual process usually looks something like this:
- Access data from a tool
- Manipulate your data
- Add data to a table manually
- Right-click on your graph
- Redefine the data powering your graph
If you repeat this process for several charts and graphs each week, that time can add up. Before you know it, you’re throwing away an hour here and an hour there – or more, depending on how much of your workload is focused on reporting.
Our approach will eliminate the need to complete any manual steps besides grabbing a report and dumping it into your Excel file. All of the tables, charts and graphs will all keep themselves updated automatically.
- Accessing data from a tool
- Having your graphs update themselves
Step-by-step instructions
We’re going to accomplish this by automating the tables that power our graphs with sumifs formulas nested within if formulas, and then defining our series values within the graphs with named ranges. Those named ranges, in turn, will be defined with offset and count formulas. This means that you can dump data in one location and then have a table update itself automatically, which in turn updates a graph automatically.
Here’s an example of what our formulas look like in this automated table. Our sumifs simply keeps track of the sum of each metric for each month (this process is explained in our appendix for those of you who are new to this formula).
Formula (abridged) from cell B3
SUMIFS(‘Data For Our Graph’!$D:$D,’Data For Our Graph’!$B:$B,’Automated Graph’!B$2)
We nest this in a simple if formula to prevent our sumifs from returning a zero, and replacing it with an empty text string instead.
We then use named ranges that point towards our automatically updated tables. The trick here is that a named range can be defined using a formula. So we’ll take advantage of the offset and count formulas to create a named range that is automatically the same length as our table is complete.
Example from the named range “Chart_Axis”
=OFFSET(‘Automated Graph’!$B$2,0,0,1,COUNT(‘Automated Graph’!$B$3:$M$3))
We will create a named range for our graphs axis and each of its series. In this case that means 3 ranges for the axis, spend, and revenue values.
The final step to automating this process is to define the series and axis in our graph with the named ranges that we just completed. This step has a small curveball included in it. The problem that you can run into is that Excel will redefine the axis values if one of your series has a different shape. This won’t happen if you define the axis value last, but will happen if you define it before completing all the series values.
The result of this setup is a chain reaction where you dump data into a worksheet, your table keeps itself updated with sumifs formulas and your graph automatically keeps itself updated after that. You can experiment with this functionality by clearing the contents of cells in the Data For Our Graph worksheet and replacing it with the data from the Additional Data worksheet.
You can apply the same principle to graphs that represent a rolling data set (last 12 months) by utilizing the second and third parameters in the offset function, or by creating a table that automatically updates the header column to contain the appropriate months.
When applied correctly, this technique can save you and your team every time you end up needing an updated version of preexisting chart or graph.
Good luck! And if you’re drowning in Excel-speak, check out our Appendix below.
Appendix of formula explanations
Count: This formula simply counts the number of cells within a range that contain numbers. There are many variations of this formula in Excel.
If: Simple test of a logical argument that returns one value when the argument is true, and another value when it is false. Also has many useful derivatives such as iferror, ifnumber, etc.
Named Ranges: Found under the Formulas ribbon, this Excel function allows you to create a name for a range of cells that you can then use in other formulas and functions. We defined our named range using a formula, but you can use a static definition as well. We also took advantage of our named range as a definition for a series on a chart, but you can also use named ranges as parameters in formulas.
Defining Series Values in a Charts and Graphs: Pretty straightforward Excel function. Right-clicking on a chart allows you to redefine the data that powers the chart. You can also define individual series, and the axis, by editing them manually after right-clicking on the chart and clicking on Select Data.
Sumifs: The sumifs formula works a little bit like a filter works. With a filter, you choose one column to include based on the value it has. So you could select “Google” from a column that describes engines. Then you can select a different column to add values from, like summating the values in a spend column. The result is that you can extract all of the spend value from a table that meets your criteria for engine, or any other values.
The sumifs formula does exactly that. You define one metric that you want to add (the first parameter) and then define subsequent criteria ranges and values that will define filter ranges and values that you want to include in your summation.
In the worksheet titled Sumifs Example, we tried to demonstrate how this works, one column at a time. In the D and E columns, we have logical arguments that test the values for Engine and Campaign within our table. When these logical arguments are true, we pass 1s, otherwise we pass 0s. We can then take the product of all of those arguments plus our desired metric. The summated result is the sum of our metric from columns that meet our filtering criteria.
Offset: Things might get a little tricky here. The offset function can define an individual cell, or a range of cells. That cell or range can be defined dynamically. We’ll use a fixed starting place, along with the count function to define a range that covers the data that has been included in our table. In our example we’re really only using the first, fourth, and fifth parameters.
The first parameter in this formula defines a starting point to be used. The second and third parameters define how you would like to offset from that starting point in terms of rows and columns, and the fourth and fifth parameters define the shape of the array that you want to create (only if you want to create an array, and not reference a fixed cell).
Our example uses the count formula in the fifth parameter, with the count formula pointed towards the cells in our table that were defined with the sumifs formula.
