Excel Dynamic Named Ranges Redux — Multiple Series in One Chart

Published by Tim Wilson on March 14, 2011 All posts from Tim Wilson

In one of the more consistently popular posts I’ve written, I went into detail about how to set up charts that would update based on a value selected from a couple of dropdown menus – specifically geared towards a dropdown menu that allows the selection of a date such that the chart(s) would update to reflect the data up to that date.

One of the commenters asked how to include multiple data series in a single chart using that same technique. I did a very quick example via email, but I mentally committed to documenting the specifics on the blog, so here we go (file download at the end of this post).

Add Some Data

I could, of course, just use the data I was already working with, but none of that fictitious data made sense as a stacked bar chart. So, the first step is to add a couple of data series that might reasonably belong in a stacked column chart – an easy one is to break out the web traffic into “New Visitors” versus “Returning Visitors.”

Following the same technique as described in the original post, I name the top cells NewVisitors_Current (Column E) and ReturningVisitors_Current (Column F) and copy the formula from the Web Traffic column into those two columns (it’s the same formula in all cells in row 1, and they can be copied without modification due to the use of “COLUMN()” in the formula).

Then, create NewVisitors_Range and ReturningVisitors_Range named ranges by going to Formulas » Name Manager, copying the formula for WebTraffic_Range, and then creating the two new named ranges using the same formula, except swapping out “WebTraffic” in the formula with “NewVisitors” and “ReturningVisitors.”

Note: This may seem like a complicated setup. It’s actually pretty quick and simple, and can even be achieved using a macro if there are a slew of metrics that need to be set up. One tip, though, is to establish a consistent naming convention for the different aspects of each metric.

So, enough with the seup. How do we put multiple series into a stacked bar chart?

Copy One of the Line Charts

The easiest way to get our base chart is to simply hold down <Ctrl>-<Shift> and click and drag one of the existing charts straight down on the worksheet. I’m a fan of copying charts rather than making new charts from scratch for two reasons: 1) It’s easier to keep them aligned and exactly the same size, and 2) It’s easier to keep the formatting the same (the formatting in this example is horrid, but that was for the sake of simplicity in the initial tutorial).

So, now we have two charts (I copied the date and “current total” cells as well, but we’re pretty much done there now – in this case, the current total uses the “Web Traffic” value, and it’s the sum of the New Visitors and Returning Visitors):

Change the Chart Type

Select the chart and then go to Chart Tools » Design » Change Chart Type and select the Stacked Column chart type:

You will now have a chart that looks like this:

But, this is still only one data series, and it’s the overall web traffic – not the breakout of new visitors and returning visitors. So…

Update the Data Series

Click on the columns in the chart, and a formula will appear in the formula bar (it’s not you…it’s a small image; image width constraints I apply to this blog, but you get the idea):

There are other ways to update the data, but this is the fastest when it’s a viable option. Simply change the first “Web Traffic,” which is the name of the data series, to “New Visitors.” Then, change “WebTraffic” later in the formula to “NewVisitors”. What you’re really doing with this second change is changing the data source from “WebTraffic_Range” to “NewVisitors_Range”.

The chart will update and will look like this:

Now, since we’re going to have two series on this chart, let’s go ahead and click on the column title and change it to “Web Traffic” manually (when you changed “Web Traffic” to “New Visitors” in the formula bar, you were changing the series name — Excel just noticed that you had only one series and no legend, so it decided to make that the chart title, too; you’ll still want the series name to be “New Visitors,” though; the reason should become apparent shortly…like…after the next sentence!). And, while we’re at it, let’s add a legend and make the chart a bit taller to make room for it!

Add the Second Series

Now, here’s where the fun happens. Right-click in the chart and select Select Data. Then, select New Visitors and click the Edit button. You’re not actually going to edit that data series, but it’s the fastest way to get the second series set up. In the Edit dialog box, select the entire contents of the Series values field and select <Ctrl>-<C> to copy the formula:

Click Cancel.

Click Add.

For the Series name enter “Returning Visitors” and then paste the formula (<Ctrl>-<V>) you just copied into the Series values field. Then, scroll to the end of that formula and replace “New” with “Returning”:

Click OK and then click OK again on the next screen.

Voila!

Still, as before, you can change the Report Period and the Report Range dropdowns to alter what data gets displayed on the chart.

You can download the spreadsheet with the full example if you want to fiddle around with it without starting from scratch.

Happy charting!

 

 

Categorized under Excel

  • Mark

    Hi Tim — I went through the exercise…ridiculously helpful. Thanks!

    Would you have any tips on modifying the process for non-uniform dates? eg, the charts would have the same x-axis, but would count a certain number of events per month.

    And to apply this to a data set w/o dates, would it work to just change the Report_Period/Range stuff on the dashboard to categories and write-in array formulas into the Index/Match?

  • http://www.gilliganondata.com Tim Wilson

    Mark — that’s a good question. For the “certain number of events per month” situation, it seems like you might want to make the underlying data a table and then put in a formula that looks at each previous row and the current row to see if the current row is the “last” in the month. Put a value in that formula that lets you identify the last value for the month (this is starting to feel like another blog post to fully explain!), and then key off of that column. Then, just use an OFFSET to incorporate the data you want.

    Array formulas might do the trick on the latter, but I haven’t actually worked with array formulas inside of named ranges. It would definitely take some experimentation.

    Good luck! Shoot me some additional details as to the specific scenario and I’ll see what I can do.

  • karce

    Hi Tim,

    interesting or topic in how to make dynamic chart… But could you help to make more complex chart with multi dynamic variables? Let say on your web traffic chart there are 2 visitors (new & Returning) But how if we have 2 group visitor? like group A that has new & returning visitors and group B also same, we want to make thats 2 group in 1 chart? please help really need it!. Thanks a million :)

    • http://www.gilliganondata.com Tim Wilson

      Unfortunately, this is one of those things that Excel really doesn’t make easy. Several of the true Excel gurus have tackled the basics of doing multiple stacked bars — all essentially blowing out what Microsoft recommends here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214119. But, that’s just for a basic static table of data.

      In theory, you could build a “middle step” in the dynamic charting notes in this post — have a table of data that is built by referencing the dynamic named ranges, but that populates a table of data that “skips rows” as needed to make multiple stacked bar charts. But, ugh, it makes my head hurt thinking about pulling that off.

      There’s an interesting approach proposed at this link: http://www.faronics.com/2012/clustered-and-stacked-bar-charts-in-excel-possible/. The idea there is to make two charts — one each with one “set” of stacked bars. Then, make all of the backgrounds/fills transparent in one chart , adjust the offsets, and put the two charts directly on top of each other. That may be worth a shot, as it would be pretty quick to try.

      Good luck!

      I wish I had an easier solution.