Excel Tips

Excel Dynamic Named Ranges Redux — Multiple Series in One Chart

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!

 

 

Presentation

How Succinctly Can I Explain Why Pie Charts Are Evil?

I’m right at three months into my new gig, and, around the office, probably the most commonly known fact is, “He hates pie charts.” It’s not that I’ve exactly been standing at the elevator handing out leaflets explaining why pie charts are evil, but I have, apparently, chosen a couple of particularly public venues to make a mild statement or two. And, the quasi-preplanned visceral groan when some co-workers put up a pie chart might’ve contributed just a teensy bit.

I’ve been put on the spot since then a couple of times to do one of two things:

  • Explain why pie charts are evil, or
  • Agree that one or another particular usage of a pie chart is appropriate

After catching up on some blog reading yesterday morning and seeing an excellent example of pie chart alternatives from Jon Peltier, and then watching seven presentations yesterday, six of which used the same basic presentation template, and five of which stuck with a pie chart for the sole non-text slide in the presentation, how could I not write another post?! Let’s see how succinct I can make it (don’t hold your breath that you could read the whole thing before exhaling!).

Yes, There is ONE Thing That a Pie Chart Does Well

This kills me, because there’s one way, in a a very narrow set of circumstances, that pie charts do marginally better than alternatives. All THREE of the following criteria have to be met for this to be the case:

  • Exactly 2 or 3 categories that make up the “whole”
  • A fairly significant difference in % makeup for each of the categories
  • Plenty of space available to present the information

99 times out of 100 when pie charts get used, all of these criteria are not met. But, there, I’ve admitted that there is a situation where pie charts are appropriate.

Of course, mullets are an appropriate hairstyle if you are prone to both warm ears and spontaneous hair donations…but that doesn’t mean I’m going to sport one!

Of Course, We Must Start with a Before/After Example

With only the category names changed, below is one of the pie charts I saw yesterday:

Pie Chart Example

In my experience, a simple horizontal bar chart is a better option (among a variety of better options):

Bar Chart Example

Why is this a better option? Oh, let me count the ways…

1. Rainbows Are Good in Princess Tales — Not in Data Visualization

When it comes to data visualization, a chart that doesn’t rely on multiple colors always trumps a chart that does. Four reasons:

  • If you use subtle/muted colors, you can’t get past 4 or 5 categories before you are asking the person reading the chart to work hard to distinguish between subtle shading differences
  • If you use bright/high-contrast colors, you’re asking your user to put on sunglasses to keep from wincing at the visual overkill
  • Roughly 10% of men suffer from some form of color-blindness — it’s darn tricky to nail a palette with more than a small handful of colors that works across the various types of the condition (of course, if you’ve got a secret agenda to have women take over the world, this is one way to contribute, as color blindness is exceedingly rare in women)
  • Maybe you’re presenting your chart in glorious, projected color…but are you sure no one is going to try to print it in black-and-white?

These are all issues with any pie chart that has more than 3 categories. None of these are an issue with a horizontal bar chart.

2. Labels, Labels, Labels

If you’ve every constructed a pie chart in Excel, you’ve run into the challenge of trying to get all of the wedges labeled right there on the chart. Excel continues to make odd choices as to where to wrap text in pie charts, and the circular nature of the whole layout means some wedges have plenty of horizontal labeling room, while others have almost none. You’ve tried some (or all) of the following:

  • Using leader lines for some of the wedges so you can label the most troubling wedges somewhere more spacious
  • Abbreviating the category names
  • Strategically rotating the chart so that the labeling all happens to work (it never does)
  • Rearranging the underlying data so that the pie wedges occur in a different order (which also never works)

After fiddling with the above, you finally break down and yank the labels from the chart and just use a legend. This is bad, bad, BAD! Scroll back up to the pie chart example above and pretend you’re actually trying to interpret the data, but pay attention to how many times you look back and forth between the legend and the pie. This is putting a totally unnecessary strain on your brain! Take a look at the horizontal bar chart — no jumping back and forth needed!

With a horizontal bar chart, the label sits right next to the data, and it doesn’t need to be abbreviated to do so (this is one reason that I find horizontal bar charts to be better than vertical column charts in many cases — with a horizontal orientation, the labels have more width with which to work).

3. Those Pesky Near-Zero Values

Pie charts suck at the small percentages. Small percentage categories wreak havoc on the labeling issue, for sure, but they’re also nearly impossible to compare to each other. In the example above, the smallest percentage is 3%, and that’s almost manageable. But, heaven forbid you have a couple of pesky sub-one-percent categories, and you’re looking at wedges that look suspiciously like the lines between wedges.

4. Seeing Small Differences

Fundoogles & Flibbers came in at 3%, while Dracula’s Mickety Micks came in at 5%. Do the wedge sizes really look different? That’s a fundamental challenge with pie charts — we don’t do a very good job of comparing the areas of these odd sorta-triangular-but-with-one-curved-side shapes. In the case of the bar chart, all you have to compare is lengths — much easier.

5. Economy (of Space) Is a Virtue

Check out the overall size of the charts. While they have the same font size, the same text displayed, and the same width, the bar chart is 20% shorter…and it could have been shorter still! Bar charts are more efficient space-wise. With pie charts, and largely because of the other issues listed above, it’s often necessary to make the chart larger and larger to make it readable.

Of Course, This Exampel Was At Least Flat

This post would be twice as long if I went into the additional issues of using the “3D effect” version of the pie chart.

[Update] Always Room for Improvement

Of course, the danger of posting a “here’s a better way” is that you leave yourself open for suggestions as to how the better way can be improved! See Naomi’s comment below. She raises a good point — basically, that I didn’t do a great job of heeding the data-pixel ratio with my bar chart! So, below is a revised version.

bar chart exampleIn a subsequent email exchange, Naomi made the case for keeping the x-axis and the numbers, but simply removing the “%” signs entirely and putting the word “Percent” in the axis label:

Bar Chart Example

Her main point is that numbers can be read more easily if they are not cluttered with symbols like dollar signs and percent signs. And, her case for keeping the gridlines and labeled axis is that it helps show that the bars are drawn to scale — there hasn’t been any incorrect or misleading scaling (intentional or not — in the same spate of presentations that spurred this post, there was a bar chart with an accompanying table of data…and one of the bars was clearly not accurate).

I’m partial to the version with all of the lines removed, but, at this point, the debate is at a much healthier level than “pie vs. bar,” so I’m happy!