Excel Dynamic Named Ranges (w/ Tables) = Chart Automation
The single post on this blog that has, for several years now, consistently driven the most traffic to this site, is this one that I wrote almost three years ago. Apparently, through sheer volume of content on the page and some dumb luck with the post title, I consistently do well for searches for “Excel dynamic named ranges” (long live the long tail of SEO!).
The kicker is that I wrote that post before I’d discovered the awesomeness of Excel tables, and before Excel 2010 had really gone mainstream. I’ve been meaning to redo the original post with an example that uses tables, because it simplifies things a bit.
This is that post — 100% plagiarized from the original when it makes sense to do so. The content was created in Excel 2010 for Windows. However, it should work fine on Excel 2007 for Windows, too. Macs are a bit of a crap shoot, unfortunately (but you can always run Parallels, so I hear, and use Excel for Windows!).
This post describes (and includes a downloadable file of the example) a technique that I’ve used extensively to make short work of updating recurring reports. Here are the criteria I was working against when I initially implemented this approach:
- 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
- No macros — 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.
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:
Now, turn that range of data into a table by selecting the area from A2 to D19 and choosing Insert » Table. Then, click over to the Table Tools / Design group and change the table name from “Table1” to “Main_Data” (this isn’t required, but I always like to give my tables somewhat descriptive names). The sheet should now look like this:
Because this is now a table, as you add data in additional rows, as long as they are on the rows immediately below the table, the table will automatically expand (and that new data will be included in references to Main_Data, which is critical to this whole exercise).
While we’re on this tab, we should go ahead and defined some named cells and some named ranges. We’ll name the cells in the first row of each metric column (the row labeled “Current–>” 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, 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 first column in the Main_Data table (see the “Referencing Tables and Parts of Tables” section in this post for an explanation of the specific syntax used here, including the use of the INDIRECT function):
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 Main_Data, which is the table that includes 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,Main_Data,COLUMN())
In this example, these are the values for each of the three arguments:
- ReportPeriod — Jul-12, the value we selected on the Dashboard tab
- Main_Data — this is the full table of data
- 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 (Note: If you have additional columns in your data sheet, you may have to make this “COLUMN()-<some fixed value>.” If, for instance, you have a blank column A before the table starts to provide some space, you would use “COLUMN()-1.” This applies to other uses of COLUMN() throughout this post.)
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 data table on 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 backwards by 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:
=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Revenue_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Revenue_Current))
It’s really not that bad when you break it down. I promise!
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 Main_Data, the full data table
- 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 (Main_Data is a multi-column table, which makes it a 2-dimensional array). All we want this function to return is the number of the row in the Main_Data table for the currently selected report period, which, as it turns out, is the same row as the currently selected report period in the first column (“Report Period”). The formula is pretty simple:
MATCH(ReportPeriod,Main_Data[Report Period])
The formula looks in the first column of the Main_Data table for the ReportPeriod value and finds it…in the seventh row of the table. 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,Main_Data[Report Period])-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:
=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Revenue_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Revenue_Current))
The whole formula is there, even if you can’t see it!
Repeat this last step to create two more named ranges with slightly different formulas (the differences are in bold):
- Orders_Range: =INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Orders_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Orders_Current))
- WebTraffic_Range: =INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(WebTraffic_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(WebTraffic_Current))
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”:
=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1,1):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]),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:
=DynamicChartsWithTables_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:
=DynamicChartsWithTables_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:
=SERIES(“Revenue“,DynamicChartsWithTables_Example.xlsx!Date_Range, DynamicCharts_ExampleWithTables.xlsx!Revenue_Range,1)
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 can 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!
Happy dynamic charting!
[Update] Troubleshooting Tip
A few people who have left comments have run into a snag where, for one reason or other, one of the named ranges has not been properly created. When that named range gets used in a chart, it either throws an error or doesn’t work. One way to check the named ranges is to open the named range manager, highlight the named range used in the chart, and then click in the formula box at the bottom of the window. A flickering/moving dashed line should then appear around the cells that the named range refers to:
If this highlighting doesn’t occur, then there is something not right with the formula.
[…] [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.] […]
Wow. So frustrating! Seems so easy but I cannot get past the #n/a error on the VLOOKUP. I did quite a bit of searching on this error and verified my TYPE of date is the same and that the results in the Report Period is the same as the data in the DATA table. My data is a big larger than yours but have hit a wall. Your demo sheet works well, just wish I could get my data to do the same.
Ok for anyone else getting stuck on this…my mistake was that I did not realize the date column in the data sheet has to be the left most column for the VLOOKUP to work.
Sorry for the slow response on this — I just missed some comments coming in. I’m glad you figured it out. I absolutely <3 VLOOKUP, but the fact that you can't specify which column is the key — it's always the first — definitely can be frustrating. The workaround is to use MATCH and INDEX, which are equally awesome, but more complicated.
I have spent several hours trying to implement this and am now crying ‘uncle’. I am at the point where I am trying to insert the “Revenue_Range” into the chart, but it refuses to allow me to do so. After I click on the non-helpful error it actually shows me the source data with the pulsing dotted lines surrounding the data cells – and I can tell those cells are correct. But it is giving the the non-helpful message that says “The formula you typed contains an error. Try one of the following:” …. None of which help. If I press “OK” it shows me the source data table with the proper cells highlighted. Feel I am very close, but no idea how to overcome this.
One question, when in Name Manager, if you look at these ranges (Revenue and Date) do you see “{…}” for the values they represent? This is how they appear in my Name Manager. Which would have caused me concern except for the fact that when I try to actually use the named range I am shown the proper values.
Naturally, the moment I post I solve. I did all of this in the same workbook. It never occurred to me that, as I was in the same workbook that I would be required to enter the filename as part of the series name (as you did in your example). So, I just provided the name itself. The moment that I added the filename as you did, it worked.
Sorry for the slow reply! I slipped on comment management.
Yes, “{…}” is what you should see. And, Excel is incredibly annoying when it comes to that error (especially because it won’t let you save it, anyway, so you can come back and fiddle with it later).
Feel free to email me — my first name at this domain — and I’ll see if I can help. I’ve definitely run into that frustration before, and it could be any number of a range of ticky-tack little issues.
Aha! And…as I work through the comments…you already figured it out!
FWIW, this has never made sense to me. Luckily, even if you rename the file, the filename in these references will get updated.
Thank you for this! it works, but I have just one question. When selecting the Report Period from the drop down, I will get an error if the Report Range does not go back as far as the data. For instance, the selected date is 9/1/13,and the lookback is 12 months. Since there no data before 1/1/13, it will create an error. Is there a way to limit the Report Period range on the list or create come kind of error handling for it?
(Sorry for the slow response — poor moderation on my part). The way I typically handle this is pretty crude — I just put additional rows in the spreadsheet with no data so that it doesn’t error out. In theory, changing the dropdown data validation to shorten the list of available options to not allow such an error condition would work, too. But, in my experience, people generally aren’t looking that far back, so it hasn’t been an error I run into very often.
That will work for me. Thanks so much for your help.
Hi, i would like to know whether this works on pivot table?
Thanks!
This post is a-ma-zing! Thank you, your instructions were right on and I’ve created something more than I thought I could with what I’m working with. Thanks!
You’re welcome!
I’m not sure exactly which part you’re asking about. But, when I have data that I’m trying to dynamically get from a pivot table, I typically introduce an intermediate table. That table uses GETPIVOTDATA to pull values from the pivot table, and the chart then gets built off the table.
Tim, as you’ve pointed out in the article, the COLUMN() parameter can be a bit tricky. It’s an absolute reference rather than a
relative reference to the range. I’ve thought up a few enhancements to this great (!!) post of yours.
Using the following formula for the Revenue_Current, Orders_Current, WebTraffic_Current cells means you don’t have to keep re-editing the formula every time you add a new column to the left of the data, or insert new columns within the data …
=VLOOKUP(ReportPeriod,Main_Data,COLUMN()-COLUMN(Main_Data)+1)
That is, I’ve turned your COLUMN() into … COLUMN()-COLUMN(Main_Data)+1 because this creates the relative reference needed, with “COLUMN(Main_Data)” showing the column number where the data range starts.
To make the spreadsheet completely bulletproof, the WebTraffic range formula would become …
=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1,
COLUMN(WebTraffic_Current)-COLUMN(Main_Data)+1):INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period]),COLUMN(WebTraffic_Current)-COLUMN(Main_Data)+1)
… and similarly for Orders_Range and Revenue_Range formulas.
Now you can add as many columns to the left of the table, and insert new columns within the data, without any of the formulas giving an error.
This is adding another level of complexity where you are attempting to explain a fairly complex process in the simplest of terms. Sorry for that!!
When editing the chart series, I kept getting errors when typing in “=DynamicChartsWithTables_Example.xlsx!Revenue_Range”. So I changed the formula to “=Data!Revenue_Range” and it worked! Looking back into Name Manager I saw that it had changed the reference back to what Tim had originally suggested above … =DynamicChartsWithTables_Example.xlsx!Revenue_Range I hope this helps anyone else struggling …
Thanks, Brian! That’s a great tip. It comes at the cost of adding a little more complexity to the formula…but the basic approach already shoved that ship on its way pretty hard! Thanks!
Hi Tim
Thanks for a brilliant post – this has really helped me with something I’ve been working on – thanks.
How easy would it be to use this approach to created a 2D column chart? I’ve been looking at it but as I’m a complete novice regarding Excel I can’t see how to do it.
Hi Alex,
This works for any type of chart. And, Excel makes it pretty easy to change chart types after data is already populated — just right-click on the chart and choose “Change Chart Type.”
Straight after I posted my question, I solved the issue (simply add another series).
Hi Tim
Lovely post. Question: How would I do this if my references where names instead of dates. For example my charts would be based on the performance of employees. So how would i create that drop down to reference say “Sarah” or “David”, so it only digs out data corresponding to those names?
That’s going to depend on what data you’re trying to present and how it’s organized. If it is a series of single data points — where there is one entry in a table for each name, for instance — then you can use the same basic approach for making the dropdown list using in-cell data validation, and then pull the data points you need using VLOOKUP.
If that doesn’t make sense, provide a little more detail of the scenario and I’ll propose some other approaches.
Ok so there are multiple entries for each employee, every week. About 5 employees, but each week, data(KPI related) is entered for each employee. So when I create the drop down for “Sarah”, I would like the chart to show data for “Sarah” for a certain number of weeks. So I will essentially be seeing Sarah’s(or the employee in question) progress.
Gotcha. So, in that case, what you can do is create a second table behind the scenes that is the “selected employee only” data. Use a VLOOKUP in that table to populate the weekly data for “,” and then have the chart plot data from that table.
Another way that has some more flexibility, but is a little more involved, too, is to add a column to your main data table that adds a “Selected Employee” flag. The cells in that column would be an IF statement: =IF(=,”X”,””). Then, you can create a pivot table off of your main table that is filtered by the Selected Employee flag. The wrinkle there is that you’ll need the pivot table to refresh whenever someone changes the dropdown. That’s a simple macro, but it means you’d be introducing macros into the workbook, which you may not want to do.
Hope that helps!
Tim thank you, but the macro syntax goes over my head. For the first suggestion, are you saying that I need to create another worksheet that contains a table with one column containing only the names of the employees? Sorry I’m really confused now. Please bare with me on this one.
Hi there,
Thank you so much for this post. I hardly leave comments, but this warrants high praise! It helped me so much in my work. Great walkthrough! 🙂
Is there a way to show all dates (in my case 9/1 to 11/14) on the x-axis, but only graph through the current date?
In theory, yes. The trick would be to adjust the formula for the Date_Range named range to, essentially, have a constant end point (of 11/14). Go to Formulas >> Name Manager, find Date_Range, and update the formula to be the following:
=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1,1):INDEX(Main_Data, MATCH(DATE(2014,11,14),Main_Data[Report Period]),1)
I just replaced the “end of the range” with a constant date — DATE(2014,11,14) — rather than the currently selected date (ReportPeriod). Give that a shot and let me know if it works.
This could actually be another dropdown, where you select what you want as the “max X-axis” value so you wouldn’t have to periodically go in and update the named range.
OK, here is my situation. Hopefully someone can offer some insight, as the message board world isn’t really getting there:
I am trying to create a vacation tracker for work. I have the following fields:
Hire Date (MM/DD/YYYY)
Total Time with Company in years (=(TODAY()-)/365)
Vacation Hours Allotted
Vacation Hours Remaining (Calculated from taking value of the Allotted Field and subtracting the sum of all of the time sheets within the workbook)
I would like to have the Vacation Hours Alotted value dynamically reset to the appropriate value on the anniversary of the hire date posted, so employees can easily track “at a glance” how much time they have left before their vacation time resets. For example, if I was hired on February 1, I would like to be able to look at my vacation hours remaining field on January 1 and know how much time I have left so I can use it before I lose it.
Is there a way to use an IF Statement to do this? Right now, I have an IF Statement in there that sets the hours on specific anniversary dates, but does not reset the value every year:
=IF(B2=””,””,INDEX({40,40,80,120,160}, MATCH(B2,{0,1,2,5,10})))
I would rather not have to set a massive IF statement to get my desired result, and I am not 100% comfortable with using a macro or VBA to achieve the desired result. Could the information in this article perhaps give me my desired result?
I don’t think there is necessarily anything in this post that will help with this, unfortunately.
Am I reading this correctly that it’s a “use it or lose it” vacation policy? So, based on the tenure with the company, the counter resets to 40, 80, etc. on the employee’s anniversary date? If I’m in my second, year, say, and I was hired on February 1st, do I have 40 hours available as of February 1st, or does it accrue linearly over the course of the year?
And, is the “/365” going to cause any issues when it comes to leap years? I think it’s likely so nominal as to be inconsequential, but thought I’d ask.
I am also having the same problem as Neil. I have been trying for hours to solve the reason why I am continually to get error messages from typing in the series name as well as the series values. I have a different work book name “ExecutiveDashboard.xlsm” and I have replaced that with yours Tim. I have gone through the Name Manager to see if there are any errors and everything seems to work just fine. Do you think you could help me out?
This is definitely the most finicky part of the whole setup. Is it possible for you to delete any sensitive data and send me the file? I’ll see if I can figure out what’s going on. The good news is, once it’s working, it tends to be very robust — I’ve never had this break once it’s charting correctly at first, even as I change the file name and move the file around.
Tim, I figured out my problem this morning. Instead of typing in the name for the formula I clicked on the sheet that I was pulling data from and then typed in the specific range I wanted and it worked perfectly! I appreciate your help and this great post. It is extremely helpful.
That’s great (if frustrating that it’s so touchy on that front). Thanks for updating!
If I have an additional criterion column, how do I incorporate that into the dynamic charts. For instance, using the example in this post, what if there was an additional column called “web addresses” and you wanted to see the revenue, orders, and traffic for each of the specific sites. How would you incorporate that additional detal?
Thanks,
Patrick
Hi Patrick,
It really depends on the specific situation. But if, for instance, your raw data had 3 rows for each month — visits, revenue, and orders for each of three different web sites — then you might want to add an intermediate pivot table where you can isolate each site’s data. Then, do a separate table that uses GETPIVOTDATA to pull the data just for the selected site (or have that table make a set of columns for each of the sites, if you want to show all three). Then, plot off of that table.
It definitely starts to get a little messier, but, with some forethought and some behind-the-scenes layout carefulness, the same concept applies: update the data in one spot, and then give the user control as to which subset of data they want to see and how they want to see it.
HTH,
Tim
Hi Tim,
Thanks for this post it’s really helpful. I’m stuck on the “Dashboard Tab Setup – Part 2 (the final step). When I insert the chart and add the new series with the correct series values =DynamicChartsWithTables_Example.xlsx!Revenue_Range it gives me an error message. All other steps I had no issue so I’m not sure what I’m doing wrong. I wanted to replicate your post so that I can use it for a dynamic dashboard I’m working on after I understand all the steps. Also named the worksheet “DynamicChartsWithTables_Example”. Does anyone have any tips to debug what I’m doing wrong to create the dynamic graph in the end? The error says the formula contains an error.
Nicole
Hi Brian,
I’m having the same issue and it doesn’t work when I change the series name either. Is there any way you could help? I’m basically replicating the steps in Tim’s article but stuck on updating the charts (series).
Thank you,
Nicole
We got the issue figured out offline, so I’m going ahead and commenting here if the problem Nicole had hits someone else reading the post. As it turned out, the issue was not with adding the series to the chart, but, rather, with the “Revenue_Range” named range. And, really, the issue wasn’t with that named range so much as the result of “ReportRange” not being defined. This step — the last part of Step 1 — had inadvertently gotten skipped. So, Revenue_Range was referencing a named cell (ReportRange) that didn’t exist, which meant it could not resolve to an actual set of data. An Excel quirk is that named ranges don’t have great validation, so the error only cropped up when Revenue_Range was attempted to be *used*.
A debugging tip: you can quickly check that named ranges are valid and resolving to ranges as you expect by going into Name Manager, highlighting a named range, and clicking in the formula box at the bottom of the Name Manager window. The range of cells that named range refers to should get highlighted with a dashed line. If that’s not happening, then there is an issue with the named range that needs to be addressed.
Hi Tim, as a little modification, can you get us offset formula for Start date and End date instead of last 3,6,12 months?
Hi Tim,
Thank you so much for your help. I’m able to automate the charts now in my dashboard! The steps were really clear and helpful thank you! I just have one more question. Would this automation work if my data on the ‘data’ tab is coming from Site Catalyst report builder? When I refresh the data using report builder, the tables I created (i.e. main_data) seem to disappear and erases my named ranges for the tables.. Is there a way around this?
Thanks,
Nicole
I’m not quite sure I’m following. But, I use OFFSET() sparingly. It has a tendency to go a little haywire with the first parameter and change it to weird things. Generally, if you make that an absolute reference with “$”s, that’s minimized. But, it’s an odd one.
It’s definitely doable without using tables, and I’ve built plenty with Report Builder data. The initial version of this post was actually doing this without tables: http://tim.webanalyticsdemystified.com/?p=820
Hi Tim,
Thanks for this step by step example, it is super helpful. btw, I am having the same problem as Nicole had and I don’t quite understand the debugging tip. “The range of cells that named range refers to should get highlighted with a dashed line.”
Thanks,
Dee
Hi Dee,
I’ve added a section to the very end of the post as a “troubleshooting tip” that shows how to check that the named ranges are set up and working correctly. See if that does the trick.
Tim
thanks Tim!
Thank you!! I followed the post and now all of the charts are updating with the Report Builder data. I’m wondering for the last step “Revenue Range” and “Date Range” named ranges, do I need to adjust the formula if I want to automate a pie chart instead of a line graph? It looks like all my charts update to the drop down month except for the ones where I chose a pie chart.
Thank you,
Nicole
Hi Nicole,
First, we have to acknowledge that pie charts are inherently evil (http://tim.webanalyticsdemystified.com/?p=615)… 🙂
But, really, there’s no reason this shouldn’t work for pie charts as well. After you switch the dropdown, check what data is being referenced by the named ranges used in the chart (see the tip I recently added to the end of the post). If those are referencing the right data, then the chart should be updating.
Hi, this post was great! However I find that when I update my table with a new row of data, the named ranges in my graphs disappear and are replaced with absolute references. What is going on?
Which version of excel are you using because i cannot get the Named Ranges to highlight and it gives me an error when I am trying to add the chart. I also cannot get the vlookup(ReportPeriod,Main_Data,Column()) formula to work. I removed the column() portion and just put in the column I wanted to reference and it worked perfectly. Also, I do not understand the Offset formula you have listed in the named ranges for MainData? I am using excel 2007 just FYI. Also, if it helps, my data table does not start until O72 which is the row i have column headers.
Hi, have you tried a 3-in-1 chart ie. make data validation drop-down with list 1,2,3; name the range ReportType; define range ReportType_Range =INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1,ReportType+1):INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period]),ReportType+1)
Then use ReportType_Range as your series definition. The chart changes with change of dropdown in ReportType, but the y-axis units don’t, they are delayed by one step ie they show the last units you needed when you changed ReportType. I tried index(match()) after reading this post because I was getting the same units problem with using offset() for variable charts. This has only started happening in Excel2013. The charts were originally developed in Excel2003 and worked ok in Excel2010.
Hi Tim, I have the above working, but would like to add one further selectable parameter aside from the 2 date parameters that you have detailed. Grateful for any tips!
This is a great tutorial. I am looking for something along these lines… but slightly different. I got a bit overwhelmed reading through and can’t tell if I can alter this for my purposes. I’m comfortable with Vlookup but have not used data validation or the index/match combo and got a bit lost at this phase. Especially when creating the name ranges using index/match.
What I’m looking to do, is create a dynamic line graph as in the example here. However, rather than have my reference cells be a date, then a period after (3,6,9 months), I want to use 2 date cells. Thus altering my graph to show all the data between (and including) these 2 dates. So I could have Oct. 1 and Oct 31 in the cells or Jan 1 and Feb 15, or any other combination of dates within the range on my data sheet.
Is this possible? Could you point out the major differences I need to alter to accomplish this??
Thanks for this great tutorial and any help you can give me trying to solve this!
I cant get my horizontal series to match up to my graph? As in to transition with the changing cells. any ideas on where to go to fix?
Hi Taylor. It’s hard to diagnose from your description, but, if you shoot me a sample file, I’ll be happy to give it a look.
Hey Tim, Absolutely amazing. The one thing I am stuck on is the date range when you create a graph. When I enter “=DynamicChartsWithTables_Example.xlsx!Date_Range” it populates with everyday of the week instead of the date range (which is selected in the for series). Any suggestions?
Have you tried the troubleshooting tip at the end of the post to confirm that the “Date_Range” named range is, indeed, highlighting the appropriate subset of cells?
Yes I have. It only has the selected dates I wish to show, not all dates. If I shrink the graph it will reduce to the selected criteria, however I would like to see it a bit larger.
https://uploads.disquscdn.com/images/5420563171ac4f6edf969f11ca9b6e9fa88cb4cdb3636fea2f6be2d3acea9fce.png
Ah! It looks like the axis type for the x-axis is either set to “Date” or it’s set to “Auto” and Excel is treating it as “Date.” Excel can get squirrely with charts and weekly dates (as common as that is). Try right-clicking on the axis and selecting “Format Axis” and then changing it to be “Text.” Does that work, or am I still missing the point?
This is excellent. Is it possible to plot two columns on the same chart using this technique?
Excellent, Excellent Article!!!!!
Thanks for this article. I tried to translate it to work for my data that is organized by columns instead of rows, but was unsuccessful. Is this a possibility? Or am I trying to make something work that can’t work. Thanks
it’s partly personal preference that I tend to want my data to “grow” vertically rather than horizontally. But, I know it’s not uncommon to have data organized where dates extend across columns. Excel tables, I think, are generally more suited to growing vertically than horizontally, but that may just be my limited thinking.
The fundamentals of this technique should still work — it would just require transposing some of the logic when it comes to the INDEX and MATCH columns. And, if you’re doing this without Excel tables — just a plain grid of data — you might want to check the predecessor to this post: https://analyticsdemystified.com/excel-tips/excel-dynamic-named-ranges-never-manually-updating-your-charts-2/. You’ll still have to do the transposing of the formulas, but it removes Excel tables from the mix.
Absolutely! It just means putting multiple dynamically defined series on a single chart. Once you have one series working, go in to “Select Data” and you can add additional series using the same pattern as the original series, but with the appropriate (different) named range for the actual data.
Thanks Tim – I’ve spent a couple hours this morning translating with a little success. I got stuck trying to translate the VLOOKUP formula in “Data Tab Setup – Part 2”. Does this also require translation?
Ah. Yes. The VLOOKUP likely needs to be converted to an HLOOKUP. VLOOKUP looks in the first column of a set of data and then moves over the specified number of columns when it finds a match. HLOOKUP, on the other hand, looks in the first ROW of a set of data and then moves down the specified number of rows when it finds a match.
Ok – I think I have that working now. I am in over my head with the Revenue_Range formula that includes INDEX and MATCH. These are new to me and the fact that they are nested are making it hard for me to translate. I’m going to keep hacking away at it. If you have time to take a look, hre’s how my spreadsheet is set up.
https://uploads.disquscdn.com/images/ad8f306658a14c655e079e02830dffcab262a23cd136927f3f8905efcadfc7ec.png
Any chance you can shoot me the file (with any sensitive info removed) at tim @ this domain? It’ll be easier to get it working that way. But, structurally, it should definitely be doable. It definitely would be confusing to walk through the actual transposing process, though, at the same time you’re trying to figure out how it works.
Hi Tim,
Thank you so much for this – it’s really upping my analysis game!
I’m using this to plot multiple data series in one chart (for the same data point in different geographic areas). Is it possible to use a dropdown or checkbox form to easily toggle the multiple data series “on” or “off”? I’ve seen some tutorials on this but not sure how well those will play with named ranges. I know I can check/uncheck the data series under “Select data” but curious if there was a more user-friendly way.
I’m not sure. I could see having a dropdown (probably) with On/Off and then adding additional logic to the named range definitions that, in the event that it’s “On,” the data range spans the different geographic areas and, if it’s “Off,” then it does not — it selects whatever the “total” range is. So, some formula messiness, in that you’d have two sets of dynamic logic both included in an “IF()” statement.