Registration is now open for ACCELERATE 2014 in Atlanta, Georgia on September 18th. Reserve your spot today at Eventbrite — tickets are only $99 USD!



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.

 


Recent Blog Posts

Hello. I’m a Radical Analytics Pragmatist
Tim Wilson, Partner

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.

Except I didn’t let it go.

Continue reading this article ... ... more from Tim Wilson

Competitor Pricing Analysis
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

How to Deliver Better Recommendations: Forecast the Impact!
Michele Kiss, Partner

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 ..."

Continue reading this article ... ... more from Michele Kiss

ACCELERATE 2014 “Advanced Analytics Education” Classes Posted
Eric T. Peterson, Senior Partner

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 Gallaria in Atlanta, Georgia.

Continue reading this article ... ... more from Eric T. Peterson

Product Cart Addition Sequence
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

7 Tips For Delivering Better Analytics Recommendations
Michele Kiss, Partner

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?

Continue reading this article ... ... more from Michele Kiss

Overcoming The Analyst Curse: DON’T Show Your Math!
Michele Kiss, Partner

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.

Continue reading this article ... ... more from Michele Kiss

Making Tables of Numbers Comprehensible
Tim Wilson, Partner

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.

Continue reading this article ... ... more from Tim Wilson

Automating the Cleanup of Facebook Insights Exports
Tim Wilson, Partner

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.

Continue reading this article ... ... more from Tim Wilson

The Recent Forrester Wave on Web Analytics ... is Wrong
Eric T. Peterson, Senior Partner

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.

Continue reading this article ... ... more from Eric T. Peterson

Funnel Visualizations That Make Sense
Tim Wilson, Partner

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).

Continue reading this article ... ... more from Tim Wilson

Reenergizing Your Web Analytics Program & Implementation
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

Registration for ACCELERATE 2014 is now open
Eric T. Peterson, Senior Partner

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.

Continue reading this article ... ... more from Eric T. Peterson

Current Order Value
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

A Guide to Segment Sharing in Adobe Analytics
Tim Wilson, Partner

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.

Continue reading this article ... ... more from Tim Wilson

Currencies & Exchange Rates
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

Linking Authenticated Visitors Across Devices
Adam Greco, Senior Partner

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.

Continue reading this article ... ... more from Adam Greco

The 80/20 Rule for Analytics Teams
Eric T. Peterson, Senior Partner

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.

Continue reading this article ... ... more from Eric T. Peterson

Ten Things You Should ALWAYS Do (or Not Do) in Excel
Tim Wilson, Partner

Last week I was surprised by the Twitter conversation a fairly innocuous vent-via-Twitter tweet started, with several people noting that they had no idea you could simple turn off the gridlines.

Continue reading this article ... ... more from Tim Wilson

Omni Man (and Team Demystified) Needs You!
Adam Greco, Senior Partner

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 ...

Continue reading this article ... ... more from Adam Greco

A Useful Framework for Social Media "Engagements"
Tim Wilson, Partner

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?" ...

Continue reading this article ... ... more from Tim Wilson

It’s not about "Big Data", it’s about the "RIGHT data"
Michele Kiss, Partner

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."

Continue reading this article ... ... more from Michele Kiss

Eric T.
Peterson

John
Lovett

Adam
Greco

Brian
Hawkins

Kevin
Willeitner

Michele
Kiss

Josh
West

Tim
Wilson

Contact Us

You can contact Web Analytics Demystified day or night via email or by reaching out to one of our Partners directly.

» Contact Information

Web Analytics Demystified, Inc.
P.O. Box 13303
Portland, OR 97213
(503) 282-2601


Useful Links