Excel: Charting Averages without Adding Columns

SEARCH BLOG

I was recently building out a pretty involved dashboard where, ultimately, I had about 50 different metrics that were available through various drilldowns in Excel. Beyond just the number of metrics (from multiple data sources), I wanted users of the dashboard to be able to select the report timeframe, whether to display the data trended weekly or monthly, and how many periods they wanted in the historical trend of the data. So, there was already some pretty serious dynamic named range action going on. But, I realized it would also be useful to include an average line on the metric charts to illustrate the mean (a target line is a related use case for this — that’s equally applicable and addressed at the end of the post). Basically, getting to a chart like this:

Now, the classic way to do this is to add a new column to the underlying data, put a formula in that column to calculate the average and repeat it in every cell. Then, simply add that data to the chart (a clustered column chart), select the average column and change the chart series type to be a line and “Voila!” there is the chart.

But…50 metrics…built on multiple tabs of underlying data from different sources…that were relying on pivot tables and clever formula-age to change the timeframe, data granularity, and trend length… and my head started spinning. That was going to get messy! So, I figured out a way to accomplish the same thing without taking up any additional cells in the spreadsheet.

In a nutshell, there are just three steps to pull this off:

Make the core data that is being plotted a named range (I was doing this already)

Make a new named range that calculates the average of that named range and repeats it a many times as the original named range has it

Add that new named range to the chart as a line

It’s the second step that is either a brilliant piece of baling wire or a shiny piece of duct tape, but no amount of Googling turned up a better approach, so I ran with it. If you know a better way, please comment!

Let’s break it down to a bit more detail.

Make the Data a Named Range

Okay, this is the easy part, and, in this example, it’s just a dumb, static range. But, more often than not, this would be a slicker — at least a column of a table or a dynamic named range of one flavor or another. But, that’s not really the point of this post, so let’s go with a simple named range called WidgetsSold:

Make a New Named Range that Is the Average Line

Now, here’s where the fun happens. I made a second named range called “WidgetsSold_AverageLine” that looks like this:

See what that does? Let’s break it down:

WidgetsSold*0 — since WidgetsSold is a multicell range, it’s, essentially, an array. Multiplying that range by 0 makes an array of the same length with zeros for all of the values (whether it’s really an array in Excel-land, I don’t know — I tried to actually insert array formulas in the definition of the named range with no luck). Think of it as being an array that looks like this: {0,0,0,0,0,0,0,0,0,0,0,0}

+AVERAGE(WidgetsSold) — this actually takes the average of the WidgetsSold range and adds that to each of the zero values, so now we have a list/array/range where each value is the average of the original named range: {15493,15493,15493,15493,15493,15493,15493,15493,15493,15493,15493,15493}

Make sense? Cool, right?

Add that Line to the Chart

Now, it’s just a matter of adding a new data series to the chart referencing that named range. Remember that you have to include the name of your workbook in the Series values box:

And, there you have it!

A Few More Notes about This Approach

This post didn’t cover the step-by-step details on how to actually get the chart to play nice, but there are scads of posts that go into that. Heck, there are scads of posts on Jon Peltier’s site alone (like this one). But, here are a couple of other thoughts on this approach:

Because the average line named range is based solely off of the named range for the chart itself, it’s pretty robust — no matter how complex and dynamic you make the base named range, the formula for the average line named range stays exactly the same.

Having said that, in my dashboard, I actually made the formula a bit more complex, because I didn’t want to include the last period in the charted range in average (e.g., if I was viewing data for October and had data trended from June to October, I only wanted the average to be for June through September). That’s a pretty straightforward adjustment, but this post is already long enough!

This example was for the average, but, what if, instead, you wanted to plot a target line, where the target for the data was a fixed number? The same approach applies, and you’re not stuck duplicating your target data across multiple cells.

What do you think? Do you have a simpler way?

[Update] And…a (Brief) Case Cautioning Against this Approach

Jon Peltier pointed out that, while named ranges, when used to refer to ranges of data, make a lot of sense, named formulas like the one described in this post have some downsides. Compiling the multi-part tweet where he described these:

You can used named formulas (“Names”) in Excel worksheets and charts. Named formulas are clever, dynamic, and flexible. Names are also hidden, “magical.” and hard to create, modify, understand, and maintain. In 6 months, try to recall how your Name works. Or someone else’s. Try to explain Names to the Sarbox auditors. Using worksheet space (“helper” columns) is cheap, fast, visible, traceable, easy to work with. Whenever possible, limit use of Names to those that reference regions of the worksheet.

Excellent points!

6 Comments

Andy BattenNovember 24th, 2013

Great post! Very helpful. I like this solution; another option that I like to use for something similar is to use the OFFSET function to set the range for AVERAGE, and set the “rows” parameter of OFFSET dynamically to expand/shrink the size of the range based on user input (you could even use a named range within the range.)

Andy BattenNovember 25th, 2013

I take it back, not sure my solution would work. So, i’ll be referencing this in the future. Thanks!

Lea Synefakis-PicaNovember 25th, 2013

The only simpler way I can think of is to cheat altogether and use a platform like Tableau Software. It’s where we run the majority of our heavy lifting dashboards. What’s your thought on doing the tried-and-true way through Excel vs. fast-track with Tableau? Am I depriving myself and my team valuable knowledge of the inner workings of Excel?

cleveyoungNovember 25th, 2013

Clever. I wouldn’t have thought of trying to build an “array” that way, or that it would even work like that. Now I have to go in and play around with that concept, to see what other devious ways it could be used.

Tһankѕ ffor one’s maгveslous posting! I definitely enjoyeԁ
reading it, you can bee a great author.I will ensure that I Ƅookmark your blog and wilol come back at some point.I want to encourage one to continue your great ԝriting, have a nice day!

Tim has moved on from Analytics Demystified effective 12/31/2017 but his content lives on. If you have questions for Tim please send them to eric@analyticsdemystified.com directly and they will get routed.