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

10 Tips for Building a Dashboard in Excel
Tim Wilson, Partner

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!

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

Exploring Optimal Post Timing ... Redux
Tim Wilson, Partner

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.

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

What I Love: Adobe and Google Analytics*
Tim Wilson, Partner

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.

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

Top 5 Metrics You’re Measuring Incorrectly ... or Not
Eric T. Peterson, Senior Partner

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.

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

Bulletproof Business Requirements
John Lovett, Senior Partner

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.

Continue reading this article ... ... more from John Lovett

Welcome to Team Demystified: Nancy Koons and Elizabeth Eckels!
Eric T. Peterson, Senior Partner

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.

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

When to Use Variables vs SAINT in Adobe Analytics
Adam Greco, Senior Partner

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.

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

5 Tips for #ACCELERATE Exceptionalism
Tim Wilson, Partner

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.

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

I’ve Become Aware that Awareness Is a #measure Bugaboo
Tim Wilson, Partner

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

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

Advanced Conversion Syntax Merchandising
Adam Greco, Senior Partner

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.

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

Team Demystified Update from Wendy Greco
Eric T. Peterson, Senior Partner

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.

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

SiteCatalyst Unannounced Features
Adam Greco, Senior Partner

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.

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

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