Excel: Charting Averages without Adding Columns

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:

1. Make the core data that is being plotted a named range (I was doing this already)
2. 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
3. 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!