A Partner at Web Analytics Demystified, Mr. Wilson has been working in digital analytics for over 12 years in a diverse range of environments and with a wide range of analytics platforms.

Excel Dynamic Named Ranges = Never Manually Updating Your Charts

[This post was written in 2010. I've made a new version of the post that takes advantage of Excel tables, which simplified the process a bit (it's still kinda' complicated). That post is available here.]

[This post is about dynamic named ranges in Excel 2007. I'm seeing a lot of referral traffic to this post searching for Excel 2010. If you're simply looking for where you define or modify named ranges in Excel 2010 (as one commenter indicated in response to an earlier version of this update), it's on the Formulas tab in the Defined Names area -- Name Manager. If you are looking for other Excel 2010-specific information that this post doesn't cover, please leave a quick comment as to what the change/issue is that led you to the search. Thanks.]

I’ve had a pretty good run of theoretical posts about the nature of marketing measurement of late, so it seemed like I was due for a more down-in-the-weeds-Excel-efficiency-tactics write-up. This blog isn’t really focussed on all of the myriad ways that Excel can be contorted to represent data effectively, but I’m a big believer in using tools as effectively as possible to remove as much rote report generation as possible. There are lots of blogs devoted entirely to Excel tips and tricks. My favorite on that front is Jon Peltier’s (if you get intrigued by this post, hop over and peruse a slew of other ways to have charts dynamically update).

This post describes (and includes a downloadable file of the example) a technique that we use extensively to make short work of updating recurring reports. Here are the criteria I was working against:

User-selectable report date

User-selectable range of data to include in the chart

Single date/range selection to update multiple charts at once

No need to touch the chart itself

Reporting of the most recent value (think sparklines, where you want to show the last x data values in a small chart, and then report the last value explicitly as a number)

No use of third-party plug-ins — one of these days, I’ll get around to playing with the various Excel add-ons like those offered by Tableau Software and XLCubed (or even the Peltier Tech add-ins, which are targeted but made by one of the top 3 most authoritative Excel resources on the ‘net), but that adds just the slightest of barriers and, again, isn’t needed for this exercise

No macros used — I don’t have anything against macros, but they introduce privacy concerns, version compatibility, odd little warnings, and, in this case, aren’t needed

The example shown here is pretty basic, but the approach scales really well.

Sound like fun?

Setting Up the Basics

One key here is to separate the presentation layer from the data layer. I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data. (Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone. This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here. See An Excel Dashboard Widget for a look at my thoughts on dashboard visualization.)

Data Tab Setup — Part 1

This is a slightly iterative process that starts with the setup of the Data tab. On that worksheet, we’ll use the first column to list our dates — these could be days, weeks, months, whatever (they can be changed at any time and the whole approach still works). For the purposes of this example, we’ll go with months. Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later. I like to use a simple shading schema to clearly denote which cells will get updated with data and which ones never really need to be touched. And, in this example, let’s say we’ve got three different metrics that we’re updating: Revenue, Orders, and Web Visits. This approach can be scaled to include dozens of metrics, but three should illustrate the point. That leaves us with a Data tab that looks like this:

While we’re on this tab, we should go ahead and defined some named cells and some named ranges. We’ll name the cell in the first row of each metric column as the current value for that metric (the cells don’t have to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows). Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name. It’s important to have consistent naming conventions, so we’ll go with <metric>_Current for this (it works out to have the metric identified first, with the qualifier/type after — just trust me!). The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic — WebTraffic_Current).

And, we’re definitely going to want to have the whole range of data on the tab available to us. Let’s call this MainData and define it by going to Formulas » Name Manager and clicking on New (this is Excel 2007 — it’s somewhere else easier to find in Excel 2003). Define a new range with a Workbook scope that encompasses all the columns and all of the rows of data (starting at row 3):

There are lots of ways to dynamically define MainData. You can just drag a big area if you want, but this is a slightly more elegant approach. I’m not going to go into the nuts and bolts of why this formula works, but you can look up the OFFSET and COUNTA functions and figure it out if you’re so inclined:

=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2,COUNTA(Data!$2:$2))

We’ll also want a named range that just includes the list of months — create that the same way as MainData, but call it DateSelector and use a slightly different formula:

=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2,1)

And, of course, we’ll actually need data — this would come later, but I’ve gone ahead and dropped some fictitious stuff in there:

That’s it for the Data tab for now…but we’ll be back!

Dashboard Tab Setup — Part 1

Now we jump over to the Dashboard worksheet and set up a couple of dropdowns — one is the report period selector, and the other is the report range (how many months to include in the chart) selector. Start by setting up some labels with dropdowns (I normally put these off to the side and outside the print range…but that doesn’t sit nice with the screen resolution I like to work with on this blog):

Then, set up the dropdowns using Excel data validation:

First, the report period. Click in cell C1, select Data » Data Validation, choose List, and then reference the named range of months we set up earlier, DateSelector:

When you click OK, you will have a dropdown in cell C1 that contains all of the available months. This is a critical cell — it’s what we’ll use to select the date we want to key off of for reporting, and it’s what we’ll use to look up the data. So, we need to make it a named cell — ReportPeriod:

Now, let’s do a similar operation for the report range — this tells the spreadsheet how many months to include in each chart. Click in cell C3, select Data » Data Validation, choose List, and then enter the different values you want as options (I’ve used 3, 6, 9, and 12 here, but any list of integers will work):

And, let’s name that cell ReportRange:

Does this seem like a lot of work? It can be a bit of a hassle on the initial setup, but it will pay huge dividends as the report gets updated each day, week, or month. Trust me!

Before we leave this tab, go ahead and select a value in each dropdown — this will make it easier to check the formulas in the next step.

Data Tab Setup — Part 2

Now is where the fun begins. We’re going to go back over to the Data worksheet and start setting up some additional named ranges. We’ve got MainData, which is the full range of data. We want to look at the currently selected Report Period (a named range called ReportPeriod) and find the value for each metric that is in the same row as that report period. That will give us the “Current” value for each metric. All you need to do is put the exact same formula in each of the three “Current” cells:

=VLOOKUP(ReportPeriod,MainData,COLUMN())

In this example, these are the values for each of the three arguments:

ReportPeriod – Jul-09, the value we selected on the Dashboard tab

MainData — this is the full set of data, including the list of months in column A

COLUMN() — this is 2, the column that the current metric is listed in (this function resolves to “3″ for Orders and to “4″ for Web Traffic)

So, the formula simply takes the currently selected month, finds the row with that value in the data array, and then moves over to the column that matches the current column of the formula:

Slick, huh? And, because the ReportPeriod data validation dropdown on the Dashboard worksheet is referencing the first column of the dataon the Data tab, the VLOOKUP will always be able to find a matching value. (Read that last sentence again if it didn’t sink in — it’s a nifty little way of ensuring the robustness of the report)

This little bit of cleverness is really just a setup for the next step, which is setting up the data ranges that we’re going to chart. Conceptually, it’s very similar to what we did to find the current metric value, but we want to select the range of data that ends with that value and goes backwardsby the number of months specified by ReportRange. So, in the values we selected above, Jul-09 and “6,” we basically want to be able to chart the following range of data:

We’ll do this by defining a named range called Revenue_Range (note how this has a similar naming convention to Revenue_Current, the name we gave the cell with the single value — this comes in handy for keeping track of things when setting up the dashboard). We can’t use VLOOKUP, because that function doesn’t really work with arrays and ranges of data. Instead, we’ll use a combination of the MATCH function (which is sort of like VLOOKUP on steroids) and the INDEX function (which is a handy way to grab a range of cells). Pull your hat down and fasten your seatbelt, as this one gets a little scary. Ultimately, the formula looks like this:

Working from the outside in, you’ve got a couple of INDEX() functions. Think of those as being INDEX(First Cell) and INDEX(Last Cell).

The range is defined, in pseudocode, as simply:

=INDEX(First Cell):INDEX(Last Cell)

The Last Cell calculation is slightly simpler to understand. As a matter of fact, this is really just trying to identify the cell location (not the value in the cell) of the current value for revenue — very similar to what we did with the VLOOKUP function earlier. The INDEX function has three arguments: INDEX(array,row_num,column_num). Here’s how those are getting populated:

array — this is simply set to MainData, the full range of data

row_num — this is the row number within the array that we want to use; we’ll come back to that in just a minute

column_num — we use a similar trick that we used on the Revenue_Current function, in that we use the COLUMN() formula; but, since we set up this range simply as a named range (as opposed to being a value in a cell), we can’t leave the value of the function blank; so, we populate the function with the argument of Revenue_Current — we want to grab the column that is the same column as where the current revenue value is populated in the top row.

Now, back to how we determine the row_num value. We do this using the MATCH function, which we need to use on a 1-dimensional array rather than a 2-dimensional array (MainData is a 2-dimensional array). All we want this function to return is the number of the row in the MainData array for the currently selected report period, which, as it turns out, is the same row as the currently selected report period in the DataSelector range. The formula is pretty simple:

MATCH(ReportPeriod,DateSelector)

The formula looks in the DateSelector range for the ReportPeriod value and finds it…in the seventh row of the array. So, row_num is set to 7.

INDEX(First Cell) is almost identical to INDEX(Last Cell), except the row_num value needs to be set to 2 instead of 7 — that will make the full range match the ReportRange value of 6. So, row_num is calculated as:

MATCH(ReportPeriod,DateSelector)-ReportRange+1

(The “+1″ is needed because we want the total number of cells included in the range to be ReportRange inclusive.)

Now, that’s not all that scary, is it? We just need to drop the full formula into a named range called Revenue_Range by selecting Formulas » Name Manager » New, naming the range Revenue_Range, and inserting the formula:

Tip: After creating one of these named ranges, while still in the Name Manager, you can select the range and click into the formula box, and the current range of cells defined by the formula will show up with a blinking dotted line around them.

You’re getting sooooooo close, so hang in there! In order for the chart labels to show up correctly, we need to make one more named range. We’ll call it Date_Range and define it with the following formula (this is just like the earlier _Range formulas, but we know we want to pull the dates from the first column, so, rather than using the COLUMN() formula, we simply use a constant, “1″:

If you want, you can fiddle around with the different settings on the Dashboard tab and watch how both the “Current” values and (if you get into Name Manager) the _Range areas change.

OR…you can move on to the final step, where it all comes together!

Dashboard Tab Setup — Part 2 (the final step)

It’s back over to the Dashboard worksheet to wrap things up.

Insert a 2-D Line chart and resize it to be less than totally obnoxious. It will just be a blank box initially:

Right-click on the chart and select Select Data. Click to Add a new series and enter “Revenue” (without the quotes — Excel will add those for you) as the series name and the following formula for the series values:

=DynamicCharts_Example.xlsx!Revenue_Range

(Change the name of the workbook if that’s not what your workbook is named)

Click to edit the axis labels and enter a similar formula:

=DynamicCharts_Example.xlsx!Date_Range

You will now have an absolutely horrid looking chart (thank you, Excel!):

Tighten it up with some level of formatting (if you just can’t stand to wait, you can go ahead and start flipping the dropdowns to different settings), drop “=ReportPeriod” into cell E6 and “=Revenue_Current” into cell E7, and you will wind up with something that looks like this:

Okay, so that still looks pretty horrid…but this isn’t a post about data visualization, and I’m trying to make the example as illustrative as possible. In practice, we use this technique to populate a slew of sparklines (no x-axis labels) and a couple of bar charts, as well as some additional calculated values for each metric.

To add charts for orders and web traffic is a little easier than creating the initial chart. Just copy the Revenue chart a couple of times (if you hold down <Ctrl>-<Shift> and then click and drag the chart it will make a copy and keep that copy aligned with the original chart).

Then, simply click on the data line in the chart and look up at the formula box. You will see a formula that looks something like this:

Change the bolded text, “Revenue,” to be “Orders” and the chart will update.

Repeat for a Web Traffic chart, and you’ll wind up with something like this:

And…for the magic…

<drum rollllllllllll>

Change the dropdowns and watch the charts update!

So, is it worth it? Not if you’re going to produce one report a couple of times and move on. But, if you’re in a situation where you have a lot of recurring, standardized reports (not as mindless report monkeys — these should be well-structured, well-validated, actionable performance measurement tools), then the payoff will hit pretty quickly. Updating the report is simply a matter of updating the data on the Data tab (some of which could even be done automatically, depending on the data source and the API availability), then the Report Period dropdown on the Dashboard tab can be changed to the new report period, and the charts get automatically updated! You can then spend your time analyzing and interpreting the results. Often, this means going back and digging for more data to supplement the report…but I’m teetering on the verge of much larger topic, so I’ll stop…

As an added bonus, you can hide the Data tab and distribute the spreadsheet itself, enabling your end users to flip back and forth between different date ranges — a poor man’s BI tool, if ever there was one (in practice, there will seldom be any real insight gleaned from this limited number of adjustable dropdowns, and that’s not the reason to set them up in the first place).

I was curious as to what it would take to create this example from scratch and document it as I went. As it’s turned out, this is a lonnnnnnngggg post. But, if you’ve skimmed it, get the gist, and want to start fiddling around with the example used here, feel free to download it!

Very interesting! You came up with a solution to a problem I currently have thanks

Marv

Excellent post. This is exactly what I’ve been looking for.

Thank you

Stacey

You asked for feedback from people who found this page after performing an Excel 2010-specific search. That would be me! I upgraded to Excel 2010 from Excel 2003, and maybe I had just missed it before, but for the life of me, I just couldn’t find where Micro-Switchy-Changey hidden the named ranges interface at all. “Name Manager” (a new name, too, I believe) used to be poorly-placed under the “Insert” menu, but now that all the menus are gone, I would never have thought to look for something called “Name Manager” in the Formulas tab…I was looking for it under the “Insert” tab, and under the “Data” tab. So it was just simple usability. But thanks — I’m glad I stumbled across so much more useful information while I’m here — thanks!

http://www.gilliganondata.com Tim Wilson

@Stacey Thanks for taking the time to let me know what you were looking for! I’m going to throw up a quick post geared just towards that — hopefully shorter path to the information for anyone else who can’t find Name Manager. You’re right, though — it’s a bit of a leap to see how it naturally falls under “Formulas.”

Kellen

Fantastic post – incredibly useful. I’ve done similar projects in the past but have always gotten tripped up on trying to create dynamic charts with more than one data series. If you have any insight into that problem I would greatly appreciate it!

John Inman

First, thanks for doing this. It’s awesome. I get an error when I try to Name the Date_Range with this formula =INDEX(MainData,MATCH(ReportPeriod,DateSelector) – ReportRange+1,1):INDEX(MainData, MATCH(ReportPeriod,DateSelector),1)

Any thoughts on why?

Thanks

http://www.gilliganondata.com Tim Wilson

@John I think my blog editor got a little fancy and changed the minus sign — which should be a run-of-the-mill hyphen — to an en dash.

See if the following formula works with that change:
=INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,1):INDEX(MainData, MATCH(ReportPeriod,DateSelector),1)

I’ve updated the post content, so others shouldn’t run into this issue.

I appreciate the help dusting off the cobwebs on creating dynamic named ranges. Thank you.

I tried downloading the example file, but every folder only contains XML documents. Did I do something wrong?

Again, thanks for the detailed and helpful entry.

X31

Excellent post. This is exactly what I’ve been looking for.
One more thing, how to do the following?
On dashboard worksheet, add one more dorpdown for 3 charts so that we can select which one amoung Revenue, order or webtraffic…

Any thoughts on how…

Laura

I am currently trying to update charts for expenses based on each months data. I am using Excel 2010, and I followed all of the directions except I had data for 14 columns instead of 3.

Everything went smoothly until the Revenue_Range part. I tried to enter the formula into the Name Manager but when I enter it, {…} shows up under the value part. It isn’t giving me any numbers in the chart… Do you have any thoughts on why these formulas aren’t working?

http://sitedeals.nl deals

thanks fot the post this time

Chris

Am I the only one who can’t get an Excel 2010 chart to accept a named range as source data!? I’m an advanced excel user (prior to getting this new and incredibly badly upgraded 2010 version), so I’m pretty sure I’m not doing anything stupid!
Also, has anyone else noticed that there is zero user support from MS for Excel 2010…and that the help function is less than worthless now (when it used to be ok in Excel 2003).
I was frustrated for a long time. Now I’m just angry.

http://www.gilliganondata.com Tim Wilson

(I’ve been going back and forth with Laura via email trying to work through what is going on — I’ll post what we find once we get a resolution)

http://www.gilliganondata.com Tim Wilson

@Chris Getting Excel (2007 or 2010) to play nice with named ranges as source data is always a little tricky — seems to be pretty finicky about the syntax — but we use named ranges as chart sources regularly, including inside 2010. As for documentation, Microsoft seems to have largely deferred to the community on that front. Jon Peltier (http://peltiertech.com/WordPress/) observed the same thing a couple of months ago…and he’s one of the top 2-3 super users of the platform.

Dan Short

@Chris Make sure you are using a fully qualified reference to the named range – it’s the only way to make it work in 2010. You can’t just plug in the named range – you have to include the workbook name and the named range. e.g. OddExcelRequirement.xlsx!NAMED_RANGE

This assumes the named range is qualified for the workbook – else you’ll have to add the sheet name too.

http://www.gilliganondata.com Tim Wilson

@Dan Great points. Thanks!

Graham Ward

Has anything come of Laura’s problem? I have the exact same issue…..

Leslie

Thanks for this blog; it’s great information that amny people can utilize. I am getting the same problem as Chris, excel 2007 not accepting named ranges for the charts. Do you have any updates on that issue?

http://www.gilliganondata.com Tim Wilson

So, clearly this approach is not quite as bulletproof as I thought. We’ve used it with great success at my current company, but there are people reporting issues — sometimes I’m able to replicate, and sometimes I’m not. This is an open offer — if you take this approach and hit a snag, feel free to send me the file (tim at this blog’s domain) and I’ll take a look to see if I can figure out what is going on (obfuscate the data / data labels as appropriate before sending).

Dave

Hi,

This example is almost perfect for a task I’m doing at work, however I want to reverse the date table so that (for example) the date at A3 is 25 Sep, A4 18 Sep, A5 11 Sep and so on. However I can’t seem to manipulate any of the formulas to ‘reverse’ the first / last cell selected. Any suggestions anyone?

thank you however for a great website and assistance with Excel

Dave

http://www.gilliganondata.com Tim Wilson

@Dave This is an interesting question. That makes the most recent date appear at the top of the date selector dropdown, which is definitely a usability improvement.

It’s doable with the following steps:

First, update the formulas in row A of the Data tab from “=VLOOKUP(ReportPeriod,MainData,COLUMN())” to “=VLOOKUP(ReportPeriod,MainData,COLUMN(),FALSE)”. This update could actually be made to the original structure, but, since the rows are no longer in ascending order, the “FALSE” needs to be added to ensure that Excel looks for an exact match.

Second, you need to update the “range” formulas, which is a little trickier (in the example, the four named ranges that end in “_Range”). The two things you need to do to these formulas are:

1) add a “,0″ to the end of the MATCH functions (same reason as the VLOOKUP one — you’re no longer sorting dates in ascending order), and

2) swap the “+” and the “-” to tell Excel to find the current date and go *down* rows to build the range rather than to go *up* rows. So, “=INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,COLUMN(Orders_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector),COLUMN(Orders_Current))” becomes =INDEX(MainData,MATCH(ReportPeriod,DateSelector,0)+ReportRange-1,COLUMN(Orders_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector,0),COLUMN(Orders_Current))

Thank you for such a quick response – and of course the new file – It’s just what I needed

dave

Dave

one more thought. In your example you’re using three data ranges – so generally the maximum result would be three charts.

If one were to have 5 plus data ranges then displaying them all, or trying to would look kinda messy. Is there a way where (and I was looking at your drop down list options) a user can choose the chart from a drop down list – the choices made would reflect into the report range / report period area and those values would create a chart.

dave

http://www.gilliganondata.com Tim Wilson

That would start to turn this into a real exploratory tool. Off the top of my head, I would think adding another “range” of data to the data tab that is the “active” data set — keyed off of a second dropdown. The chart, then, would simply reference *that* range of data. A wrinkle that would be introduced there would be changing the number format on the fly (which would be really cool to play around with!). That, in theory, is doable as well — Excel can be finicky when you try to get it to draw the chart number format from the underlying data number format, but I’ll play around with it!.

Dave

Wish you luck, I’m getting to the stage where after ripping out all my hair I’m now hiding under the desk wishing I’d never used the words: Yep, I can do that boss…lololol

let me know if you get it to work…. every time I attempt to use another range… things just go sideways… (well, I’m sure you get the idea)

http://www.gilliganondata.com Tim Wilson

Dave – It may be worth looking into something like Tableau Software, depending on the requirements. But, now you’ve really got me wanting to take a crack at this!

http://tonykau.com Tony Kau

Phenomenal. I was about to try to use VBA for a similar task. Thank you for this walk-through!

http://www.gilliganondata.com Tim Wilson

Tony – You’re welcome! Let me know if you hit any snags. It’s pushing the non-VBA interactivity capabilities of Excel a bit, I’m finding out — as the comments note, some environments don’t seem to support the approach, and we haven’t been able to get to the bottom of exactly why.

Dave

I wish you luck Tim, in the meantime… I’ll reserve a place under the desk…lolol

Antony

Hi Tim,

I’ve hit your post searching for Excel 2010. Basically my problem is that I used Data Validation Lists to pick a list of data which can change in length according to other information entered. If you specify a source list, you may not know the length of the list (how many rows) and this causes a load of white spaces in the dropdown (for all cells in the range which had no data in them).

In previous Excel versions, there was a neat trick using the OFFSET command in the Data Validation list source criteria to prevent the white space cells at the end of the dropdown list…

Example:

My variable list of values is in Cells D2-D20. In cell D21 I work out how many cells have data in them using the formula ‘=19-COUNTIF(D2:D20,”")’

I can now create a dropdown list which uses the specific amount of data in my list in the Data Validation settings:

Source: =($D$2:(OFFSET($D$2,$D$21-1,0)))

- so in this case, the OFFSET command takes the value of D20 as the offset.

The problem is that now in Excel 2010, using OFFSET (or INDEX for that matter) as a Data Validation List source gives the error:

“You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation Criteria”

Do you know if there is any way around this problem. I am in a real mess now because i’ve started using some other features new to Excel 2010 – so I am stuck with a worksheet which i cannot save in either format!!

“- so in this case, the OFFSET command takes the value of D21 as the offset.”

Lenn Johnson

Data –> Range
How difficult would that be?

Antony

thanks Tim – I actually don’t have a full column because my validation criteria is within hidden rows at the top of the sheet. However, your solution does work for me because I can just move my OFFSET command into the Name Manager and this does the trick as the validation source is now just the name reference – thanks very much for your help!

Derek — there are a number of ways to show the previous period’s value. In this specific example, the easiest way is to just do a VLOOKUP for [Current Selected Month minuse 1]. So, if, for instance, you add a row on the second tab and, at the top of each column in the new row, you want to display the value for the previous month, simply use the following formula:

If you break this down a bit, you’ll see that it is the exact same formula as the “Current” value, but, rather than looking for “ReportPeriod,” you’re looking for ReportPeriod…minus one month. That’s what “DATE(YEAR(ReportPeriod),MONTH(ReportPeriod)-1,1)” does.

You can then make these “Previous” values named cells and display them on the first worksheet.

Derek

That make sense Tim. Thanks!

I ran into another issue…

if i had column A with the same dates for 5 rows, then column B with data:

ColA ColB
Nov 52
Nov 67
Nov 65
Nov 22
Nov 22

can i dynamically plot a line chart using range? It appears the formula only works for unique date set?

thanks

http://www.gilliganondata.com Tim Wilson

Derek — I hadn’t planned for that use case. You want a chart that takes all the “Nov” values and plots those in order? So, the line would plot 52, 67, 65, 22, 22? What would the x-axis labels be (or would it be unlabeled?). Help me understand the use case a little bit better (and I apologize for another slow response!).

You are amazing. I’m sure I’ll eventually understand these (since the instructions are perfectly clear), and I have to since I’m learning (on the job) to create dynamic charts. Thank you for all that you do!

http://www.gilliganondata.com Tim Wilson

I’m glad to hear it!

Katie

Fantastic post! Just what I was looking for. I know it was mentioned earlier in the comments and maybe I missed a response, but in 2010 is there a way to add more series of data from named ranges? If each series has two variables? We’re tracking the relationships between two variables for each date. Thoughts?

http://www.gilliganondata.com Tim Wilson

Katie, I’m not sure I fully understand the question. It’s doable to make the named range include more than one column of data, but it makes my head hurt a little bit to think through how the charting would work. If you’re looking to show two series on the same chart (say, Revenue and Orders in the example in this post), that is totally doable — it’s actually how I use the tool in practice. You simply have to add another series to the same chart.

Now, if you’re looking to what Dave was referring to earlier in the comments — to enable a way to toggle between different views of the data, I actually stumbled across a nifty trick today that Chandoo came up with some time back (I was looking into something entirely different than this post): http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/. That adds another hidden worksheet, but it might do that trick.

If I haven’t understood the question…let me know!

http://avicompanies.com Jon

Hi Tim,
I am using your formulas to draw information from multiple sheets into one concise page. Basically the same as the post but instead of showing info on a chart, displaying (on the upwards of 20 columns) into one table, with the date selection decending and all the information showing exactly as it appears on other pages going across. I haven’t been able to find anyone else doing anything close to this in scope and size. VBA only led me circles. Can you please help? Thank you for your time!

http://www.gilliganondata.com Tim Wilson

Jon and I had an email exchange offline that involved swapping some additional details and, ultimately, a sample spreadsheet. The solution involved the same principles as described in this post, but added on the use of Excel tables, which were something I wasn’t using heavily when I initially wrote this post. If either Jon’s question, or my reference to tables, is something you have questions about, shoot me an email or leave another comment here. There’s another post or two that I need to write coming out of the exchange, but it’s more than will fit reasonably in a comment!

PJ

I’ve seen umpteen tutorials and pages regaarding dynamic charts from Named ranges, but none seem to address the issue I am having.

The problem is rooted in two main areas. One, that the Series function always converts entries to absolute RC:RC references, and the other, that using OFFSET to obtain the relative range by use of another defined cell will only remain valid if the Named ranges are still in the same relative order from the reference used to base the OFFSET.

http://www.gilliganondata.com Tim Wilson

I’m not sure I understand the specifics of either issue. I definitely have used named ranges in the definitions of the series that get charted (download the spreadsheet at the end of this post). And, I’m not sure what you are referencing regarding named ranges being in the “same relative order.” Let me know, and perhaps I can help!

Martina

Good job, Tim!! I have been looking for this kind of solution for a very long time. I was afraid it would be necessary to use Macros, but you have created such a descriptive guideline that I couldn´t screw it up It was really helpful, thank you very much.

http://www.gilliganondata.com Tim Wilson

Great!

Barrington

Many thanks Tim,
There are many versions on the web of “how to set up dynamic charts”, but I have found your instructions and explanations here to be not only easy to follow but delivered great results. Thanks so much, you’ve opened up a whole new aspect of Excel for me, And made it fun!

Gil

I think I owe you my salary! It was thrilling to following your explanations and it open up my eyes for much more advanced possibilities with Excel (and I am a very advanced in Excel)

Cool! Thanx, man! I’m creating something similar to this. I went through and built your example, and now I just need to modify it to fit my scenario.

Catalina Camuyrano

this post is really usefull! I’m also looking for a way to create dynamic mean formulas. Instead of a chart I have a table with the averages of different stuff. I want to be able to select the amount of periods I want the average to compute. So I can have the 5-year average, the 10-year average and so on. I think the match formula is taking me close to doing it, but still haven’t nailed it. If you have any advice as on how to get there I’ll really appreciate. Thanks!

Leon Claassen

I have a similar workbook that does what you are wanting to do. My workbook totals my totals for the number of months I select or days, etc. Basically what I did was created a reference sheet where i have a my list. I then renamed the the cells using the Name Manager.

I then use an IF statement to see that my formula matches the desired criteria. Here is an example of the formula I use:

There is probably a better way of adding each cell, but this is a starting point
All the cell in column C have this formula in them: =IF(D12<=$P$4,$P$4,D12)

And Cell P4 is named Criteria
Column D I just have a list of numbers (in this case indicating the month number)

Let me know if this helps, but like I said, I am also positive that there is a genius out there who can fine tune my formula / way of working this.

http://tim.webanalyticsdemystified.com/ Tim Wilson

Oops! I missed this request. You’re on the right track, I think. A combination of MATCH (to find the starting cell) and then OFFSET (to actually select the range you want to average) should do the trick. If not, shoot me an email — just my first name @ this domain.

Rachel Harris

Hi Tim, thank you for the helpful tutorial!! I am getting an error message in my own Worksheet when I get to creating my dynamic chart (my data set is much larger than your example, fyi). The error message I’m getting is “Reference is not valid. Reference must be to an open worksheet.” Could you provide some feedback on how to address this ? I really appreciate it. Thanks!

Rachel Harris

Nevermind! I was able to figure out the issue from downloading your example worksheet – it was in the way something was named. Thanks!

rashelle

Thank you so much for posting this! I had been trying to find something online for awhile to help me with this exact problem, and through your steps was able to now save myself and my group tons of work. Thank you!

Kannan

Hi, I’m trying to work something similar. My worksheet has a dynamic range and I’m selecting the data I needed using a named range. However, when I use the name in chart formula, it shows an error “A formula in this worksheet contains one or more invalid references. Verify that the formula contains a valid path, workbook, range name, and cell reference. any thoughts why this happens?

http://tim.webanalyticsdemystified.com/ Tim Wilson

The chart formulas are notoriously finicky. The good news is, once you get it working, it will be solid and reliable. The most common issue I see people running into is that the filename has to be used in the chart formula (as shown in the example here). It will update cleanly on its own if you rename the file. It doesn’t make sense that it’s required, but it is. Any chance that is the issue you’re having?

Kannan

Hi Tim, Absolutely!! That was the issue, now its working fine. I’m having a little more complex problem now, consider the data below -
Jack William Amy
Maths 50 77 11
Science 40 88 57
English 76 71 63
I want to create a dynamic chart that gets updated if I add a student next to Amy or a subject after English or both. Do you think that is possible using named ranges rather than using a VBA code?

http://tim.webanalyticsdemystified.com/ Tim Wilson

Great! I’m glad that worked.

For the more complex problem, it should be doable. My initial reaction was to suggest flattening the data structure so that “Student” was a column with the student’s name in it. It would be a much longer table:

Math Jack 50
Math William 77
Math Amy 11
Science Jack 40
:

If the raw table could be maintained that way, then a pivot table / pivot chart would make for dynamic viewing of the data, possibly without any named ranges at all!

But, if the base structure needs to stay as it is, then it’s probably still doable. I’d definitely recommend using Excel tables for this (I wrote an updated version of this post that uses tables here: http://tim.webanalyticsdemystified.com/?p=2176. If you do that, then you can get all the column headings (student names) with something like: =Table1[#Headers] and all of the subjects with something like: =Table1[Subject] (assuming you had a column heading for the first column of “Subject”). See the “Referencing tables and parts of tables” section in this post: http://tim.webanalyticsdemystified.com/?p=1877. So, you could make those named ranges to get “all the possible values” to populate your dropdowns and then use MATCH, INDEX, VLOOKUP, and maybe even HLOOKUP to get the data you need to chart it.

The short answer: it’s doable! But, it will take some tinkering. You’ll definitely be a stronger Excel user once you’ve pulled it off, though!

Kannan

Tim, Thanks for your suggestion. I have been using a macro to transpose the data in the way you’d suggested and created pivot charts to create dynamic charts. But I won’t be able to restructure the data in the current problem in hand. I like the idea of tables, one problem again though – the data gets pulled from a different sheet, hence in case of multiple selections, the subjects would get added (as per my example above) or removed. There are already formulas written in the cells, basically a VLOOKUP function, which shows the values of the subject if selected, otherwise shows a blank cell and blank cells for the students as well.. In this case, if I create a table, the entire range containing the formula gets selected. Hence, the chart would reflect some blank legend entries and axis. Not sure if I’ve explained my problem well and clear, please let me know if not.

http://tim.webanalyticsdemystified.com/ Tim Wilson

Unfortunately, it’s a little tough to follow the description. I’ll email you separately.

Rachel Harris

Tim, can this be modified to just have the most current month that is populated with data always display as my “reportperiod”, instead of being able to choose which reportperiod i want to display?

http://tim.webanalyticsdemystified.com/ Tim Wilson

That should be pretty easy. There are two ways to approach it:

1. As the “maximum date populated in the spreadsheet.” Say that you’ve only updated data through the end of June, so you want the report to show data with the “reportperiod” as June, even if you’re looking at it in September. Then, once you add July and August…you want it to be August. All you need do do there is go into Name Manager (Formulas >> Name Manager), select “ReportPeriod,” click Edit, and change the formula from a cell reference to be: “=MAX(DateSelector”. Basically, that’s just grabbing the maximum (most recent) date in the first column of the Data tab.

2. As “the most recent month” (or week, or yesterday). In this case, you still just want to update ReportPeriod, but you want to make it key off of the current date. So, to do “the previous complete month,” for instance, you could make the formula for ReportPeriod be: “=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1″. Basically, you’re just taking the current day (TODAY()) and going to the first day of the prior month. Excel is crafty in that, even if the current day is 1/2/2015, the formula will still resolve to 12/1/2014 (the YEAR would be 2015, the MONTH would be 1-1=0, and the DAY would be 1; Excel recognizes that a date of 0/1/2015 is actually 12/1/2014).

Does one of those get you what you’re looking for?

Recent Blog Posts

No, I’m not referring to SecondLife (which, BTW, is still around and, apparently, still has life in it). I’m referring to the fact that podcasts just turned ten, and there are a lot of signs that they might be one of the "next big things" in digital. Earlier this year, when I wrote a post announcing the launch of the Digital Analytics Power Hour podcast, I listed three examples as to how it seemed like podcasts were making a comeback ...

Vendors commonly pitch the need for “real-time” data and insights, without due consideration for the process, tools and support needed to act upon it. So when is real-time an advantage for an organization, and when does it serve as a distraction? And how should analysts respond to requests for real-time data and dashboards?

I had this come up a couple of weeks ago with a client, and I realized it was something I’d done dozens of times…but had never written down the “how” on doing. So, here we go. This is a post about one very specific application of Excel, but it is also implicitly a post about how, with an intermediate level of knowledge of Excel, with a little bit of creativity, and a strong aversion to manually parsing/copying/pasting anything, a spreadsheet can accomplish a lot! And very quickly!

One of the benefits of having a number of friends in the analytics industry is the spirited (read: nerdy) debates we get in to. In one such recent discussion, we went back and forth over the merits of "bounce rate." I am (often vehemently) against the use of "bounce rate." However, when I stepped back, I realized you could summarize my argument against bounce rate quite simply ...

Happy belated new year to everyone reading this blog — on behalf of everyone at Web Analytics Demystified and Team Demystified I sincerely hope you had a wonderful and relaxing holiday season and that you’re ready to wade back into the analytical and optimization fray! Since I last wrote a few cool things have happened ...

I’ll admit it: I’m a Nate Silver fanboy. That fandom is rooted in my political junky-ism and dates back to the first iteration of fivethirtyeight.com back in 2008. Since then, Silver joined the New York Times, so fivethirtyeight.com migrated to be part of that media behemoth, and, more recently, Silver left the New York Times for ESPN — another media behemoth.

In digital analytics, "Governance" is a term that is used casually to mean many different things. In our experience at Web Analytics Demystified, every organization inherently recognizes that governance is an important component of their data strategy, yet every company has a different interpretation of what it means to govern their data. In an effort to dispel the misconceptions surrounding what it means to truly steward digital data, Web Analytics Demystified has developed seven data governance principles that all organizations collecting and using digital data should adhere to.

Hiring in the competitive analytics industry is no easy feat. In most organizations, it can be hard enough to get headcount – let alone actually find the right person! These three foundational tips are drawn from successful hiring processes in a variety of verticals and organizations.

Those of you who follow my blog have come to know that when I learn a product (like Adobe SiteCatalyst), I really get to know it and evangelize it. Back in the 90′s I learned the Lotus Notes enterprise collaboration software and soon became one of the most proficient Lotus Notes developers in the world, building most of Arthur Andersen’s global internal Lotus Notes apps. In the 2000′s, I came across Omniture SiteCatalyst, and after a while had published hundreds of blog posts on Omniture’s (Adobe’s) website and my own and eventually a book! One of my favorite pastimes is finding creative ways to apply a technology to solve everyday problems or to make life easier.

One of the things customers ask me about is the ability to profile website visitors. Unfortunately, most visitors to websites are anonymous, so you don't know if they are young, old, rich, poor, etc. If you are lucky enough to have authentication or a login on your website, you may have some of this information, but for most of my clients the "known" percentage is relatively low. In this post, I'll share some things you can do to increase your visitor profiling by using advertising campaigns and other tools.

Some of you may have noticed that I don't blog as much as some of my colleagues (not to mention any names, but this one, this one, or this one). The main reason is that I'm a total nerd (just ask my wife), but in a way that is different from most analytics professionals. I don't spend all day in the data - I spend all data writing code. And it's often hard to translate code into entertaining blog posts, especially for the folks that tend to spend a lot of time reading what my partners have to say.

Do you used in-cell dropdowns in your spreadsheets? I used them all the time. It's both an ease-of-use and a data quality maneuver: clicking a dropdown is faster than typing a value, and it's really hard to mis-type a value when you're not actually typing!

Yesterday, an article in the Harvard Business Review provided food for thought for the analytics industry. In Tesco's Downfall Is a Warning to Data-Driven Retailers, author Michael Schrage ponders how a darling of the "analytics as a competitive advantage" stories, British retailer Tesco, failed so spectacularly - despite a wealth of data and customer insight.

Regardless of what type of website you manage, it is bound to have some sort of conversion funnel. If you are an online retailer, your funnel may consist of people looking at products, selecting products, and then buying products. If you are a B2B company, your funnel may be higher-level like acquisition, research, trial and then form completion.

This post has an unintentionally link bait-y post title, I realize. But, I did a quick thought experiment a few weeks ago after walking a client through the structure of a dashboard I'd built for them to see if I could come up with ten discrete tips that I'd put to use when I built it. Turns out…I can!

Back in 2012, I developed an Excel worksheet that would take post-level data exported from Facebook Insights and do a little pivot tabling on it to generate some simple heat maps that would provide a visual way to explore when, for a given page, the optimal times of day and days of the week are for posting.

While in Atlanta last week for ACCELERATE, I got into the age-old discussion of "Adobe Analytics vs. Google Analytics." I'm up to my elbows in both of them, and they're both gunning for each other, so this list is a lot shorter than it would have been a couple of years ago.

Last night as I was casually perusing the days digital analytics news - yes, yes I really do that - I came across a headline and article that got my attention. While the article's title ("Top 5 Metrics You're Measuring Incorrectly") is the sort I am used to seeing in our Buzzfeed-ified world of pithy "made you click" headlines, it was the article's author that got my attention.

As a digital analytics professional, you've probably been tasked with collecting business requirements for measuring a new website/app/feature/etc. This seems like a task that's easy enough, but all too often people get wrapped around the axle and fail to capture what's truly important from a business users' perspective. The result is typically a great deal of wasted time, frustrated business users, and a deep-seated distrust for analytics data.

I am delighted to announce that our Team Demystified business unit is continuing to expand with the addition of Nancy Koons and Elizabeth "Smalls" Eckels. Our Team Demystified efforts are exceeding all expectation and are allowing Web Analytics Demystified to provide truly world-class services to our Enterprise-class clients at an entirely new scale.

In one of my recent Adobe SiteCatalyst (Analytics) "Top Gun" training classes, a student asked me the following question: When should you use a variable (i.e. eVar or sProp) vs. using SAINT Classifications? This is an interesting question that comes up often, so I thought I would share my thoughts on this and my rules of thumb on the topic.

Next month's ACCELERATE conference in Atlanta on September 18th will be the fifth - FIFTH!!! - one. I wish I could say I'd attended every one, but, sadly, I missed Boston due to a recent job change at the time. I was there in San Francisco in 2010, I made a day trip to Chicago in 2011, and I personally scheduled fantastic weather for Columbus in 2013.

A Big Question that social and digital media marketers grapple with constantly, whether they realize it or not: Is "awareness" a valid objective for marketing activity?

I've gotten into more than a few heated debates that, at their core, center around this question. Some of those debates have been with myself (those are the ones where I most need a skilled moderator!).

As I have mentioned in the past, one of the Adobe SiteCatalyst (Analytics) topics I loathe talking about is Product Merchandising. Product Merchandising is complicated and often leaves people scratching their heads in my "Top Gun" training classes. However, many people have mentioned to me that my previous post on Product Merchandising eVars helped them a lot so I am going to continue sharing information on this topic.

When Eric Peterson asked me to lead Team Demystified a year ago, I couldn't say no! Having seen how hard all of the Web Analytics Demystified partners work and that they are still not able to keep up with the demand of clients for their services, it made sense for Web Analytics Demystified to find another way to scale their services. Since the Demystified team knows all of the best people in our industry and has tons of great clients, it is not surprising that our new Team Demystified venture has taken off as quickly as it has.

Lately, Adobe has been sneaking in some cool new features into the SiteCatalyst product and doing it without much fanfare. While I am sure these are buried somewhere in release notes, I thought I'd call out two of them that I really like, so you know that they are there.

I was reading a post last week by one of the Big Names in web analytics…and it royally pissed me off. I started to comment and then thought, "Why pick a fight?" We've had more than enough of those for our little industry over the past few years. So I let it go.

One of my newest clients is in a highly competitive business in which they sell similar products as other retailers. These days, many online retailers have a hunch that they are being "Amazon-ed," which they define as visitors finding products on their website and then going to see if they can get it cheaper/faster on Amazon.com. This client was attempting to use time spent on page as a way to tell if/when visitors were leaving their site to go price shopping.

One of the most valuable ways to be sure your recommendations are heard is to forecast the impact of your proposal. Consider what is more likely to be heard: "I think we should do X ..." vs "I think we should do X, and with a 2% increase in conversion, that would drive a $1MM increase in revenue ..."

I am delighted to share the news that our 2014 "Advanced Analytics Education" classes have been posted and are available for registration. We expanded our offering this year and will be offering four concurrent analytics and optimization training sessions from all of the Web Analytics Demystified Partners and Senior Partners on September 16th and 17th at the Cobb Galaria in Atlanta, Georgia.

In working with a client recently, an interesting question arose around cart additions. This client wanted to know the order in which visitors were adding products to the shopping cart. Which products tended to be added first, second third, etc.? They also wanted to know which products were added after a specific product was added to the cart (i.e. if a visitor adds product A, what is the next product they tend to add?). Finally, they wondered which cart add product combinations most often lead to orders.

As an analyst, your value is not just in the data you deliver, but in the insight and recommendations you can provide. But what is an analyst to do when those recommendations seem to fall on deaf ears?

If I could give one piece of advice to an aspiring analyst, it would be this: Stop showing your "math". A tendency towards "TMI deliverables" is common, especially in newer analysts. However, while analysts typically do this in an attempt to demonstrate credibility ("See? I used all the right data and methods!") they do so at the expense of actually being heard.

I'm always amazed (read: dismayed) when I see the results of an analysis presented with a key set of the results delivered as a raw table of numbers. It is impossible to instantly comprehend a data table that has more than 3 or 4 rows and 3 or 4 columns. And, "instant comprehension" should be the goal of any presentation of information - it's the hook that gets your audience's brain wrapped around the material and ready to ponder it more deeply.

This post (the download, really - it's not much of a post) is about dealing with exports from Facebook Insights. If that's not something you do, skip it. Go back to Facebook and watch some cat videos. If you are in a situation where you get data about your Facebook page by exporting .csv or .xls files from the Facebook Insights web interface, then you probably sometimes think you need a 52" monitor to manage the horizontal scrolling.

Having worked as an industry analyst back in the day I still find myself interested in what the analyst community has to say about web analytics, especially when it comes to vendor evaluation. The evaluations are interesting because of the sheer amount of work that goes into them in an attempt to distill entire companies down into simple infographics, tables, and single paragraph summaries.

Funnels, as a concept, make some sense (although someone once made a good argument that they make no sense, since, when the concept is applied by marketers, the funnel is really more a "very, very leaky funnel," which would be a worthless funnel - real-world funnels get all of a liquid from a wide opening through a smaller spout; but, let's not quibble).

Those of you who have read my blog posts (and book) over the years, know that I have lots of opinions when it comes to web analytics, web analytics implementations and especially those using Adobe Analytics. Whenever possible, I try to impart lessons I have learned during my web analytics career so you can improve things at your organization.

I am excited to announce that registration for ACCELERATE 2014 on September 18th in Atlanta, Georgia is now open. You can learn more about the event and our unique "Ten Tips in Twenty Minutes" format on our ACCELERATE mini-site, and we plan to have registration open for our Advanced Analytics Education pre-ACCELERATE training sessions in the coming weeks.

I recently had a client pose an interesting question related to their shopping cart. They wanted to know the distribution of money its visitors were bringing with them to each step of the shopping cart funnel.

Over the past year, I've run into situations multiple times where I wanted an Adobe Analytics segment to be available in multiple Adobe Analytics platforms. It turns out…that's not as easy as it sounds. I actually went multiple rounds with Client Care once trying to get it figured out. And, I've found "the answer" on more than one occasion, only to later realize that that answer was a bit misguided.

If your web analytics work covers websites or apps that span different countries, there are some important aspects of Adobe SiteCatalyst (Analytics) that you must know. In this post, I will share some of the things I have learned over the years related to currencies and exchange rates in SiteCatalyst.

In the last few years, people have become accustomed to using multiple digital devices simultaneously. While watching the recent winter Olympics, consumers might be on the Olympics website, while also using native mobile or tablet apps. As a result, some of my clients have asked me whether it is possible to link visits and paths across these devices so they can see cross-device paths and other behaviors.

I had the pleasure last week of visiting with one of Web Analytics Demystified's longest-standing and, at least from a digital analytical perspective, most successful clients. The team has grown tremendously over the years in terms of size and, more importantly, stature within the broader multi-channel business and has become one of the most productive and mature digital analytics groups that I personally am aware of across the industry.

As someone in the web analytics field, you probably hear how lucky you are due to the fact that there are always web analytics jobs available. When the rest of the country is looking for work and you get daily calls from recruiters, it isn't a bad position to be in! At Web Analytics Demystified, we have more than doubled in the past year and still cannot keep up with the demand, so I am reaching out to you ...

Whether you have a single toe dipped in the waters of social media analytics or are fully submerged and drowning, you've almost certainly grappled with "engagement." This post isn't going to answer the question "Is engagement ROI?" ...

Unless you've been living under a rock, you have heard (and perhaps grown tired) of the buzzword "big data." But in attempts to chase the "next shiny thing", companies may focus too much on "big data" rather than the "right data."