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:

1. Access data from a tool
2. Manipulate your data
3. Add data to a table manually
4. Right-click on your graph
5. 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.

1. Accessing data from a tool
2. 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)

chart

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.

name manager

 

data sourceedit series

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.

 

 

 

 

 

4 Comments

  1. Oliver January 23rd, 2015

    Ricky,

    Yes, but it is dependent upon on what kind of data the pie chart represents. This technique would be useful if you were continually adding data that represented new additional date ranges to a pie chart. If you just wanted to update the data (replace the old with the new) then there are easier techniques.

  2. RATNA BHATNAGAR June 23rd, 2015

    Hi, I have to update around 18 excel charts on a daily basis. While this is helpful, I also need to update their data range to the most recent 30 values. I’m sure there is a way to automate that as well. Could you let me know please? Thanks!

  3. Pol April 29th, 2016

    Hi Oliver,

    This is so well explained, thanks a lot. Just applied it to my work, since I am normally the guy that needs to do the work when the team needs “same graphs as last time, updated with latest info” This will save me so much time :)

  4. Lamees August 30th, 2016

    This is helpful and a good start, however, what I am trying to automate is a bit more complicated.

Leave a Comment

Oliver Eldredge
Oliver has been working in digital marketing since 2007, and began working with 3Q Digital in November of 2013. His focus in the industry has been around training, paid search, analytics, and all things Excel. In his spare time you'll probably find him out on his bike or up in the Sierras.