Select Data to Display in an Excel Chart With Option Buttons

The Problem

A member of the MrExcel forum wanted to know about using form controls to select columns from a data table to populate a graph. Essentially he wanted his users to be able to choose between two charts, one comparing their scores to district and regional scores, the other comparing their scores to the top 20%. This is a retelling of my suggested approach to his question.

Here is some dummy data I used for his problem, and a chart showing all of the data.

Data and Preliminary Line Chart

My suggested approach will add some columns (F:I) to the existing data range with formulas that show or don’t show data based on which of two option buttons is selected, and my chart will use all of this data. When the formulas don’t show the values, they will not appear in the chart.

The Data

The extended data range is shown below. At first the formula in cell F2 (and copied into the entire range F2:I13) is simply


To give the user a choice, add two option buttons and label them Chart A and Chart B. Right click on one of them (doesn’t matter which) and select Format Control, and on the Control tab, click in the Cell Link box, and select cell K1. This cell shows which of the buttons is selected, 1 or 2.

Extended Data and Option Buttons

You don’t need to show all of this to the user. You could put the chart and option buttons on the active sheet, and all of the data (and the option button linked cell) can go onto another sheet, and you can hide this other sheet if you want. Or you can place the original data on the same sheet as the chart and option buttons, and the formulas onto another sheet, a hidden sheet if desired. You could replace the two option buttons with one listbox that shows both options; the linked cell works the same way.

The Chart

Set up the chart first, before adjusting the formulas to show the result of the option button selection.

Select A1:A13, then hold Ctrl while selecting F1:I13, and insert a line chart.

Preliminary Line Chart

Delete the legend, because it will always show all series, even if some don’t appear because of the formulas. Instead, label the last point in each series with the series name, and use label text that matches each series color. This is more user-friendly than using a legend, anyway. (I also shortened the axis tick labels so they could all be displayed horizontally.)

Cleaned Up Line Chart

The Formulas

Now adjust the formulas. Keep the formulas in column F the way they are, since this series appears whichever option is selected. The formula in G2 (copied into G2:H13) is


This means if the first option button is selected, show the corresponding value, otherwise show #N/A. #N/A isn’t plotted in a line or scatter plot.

Similarly the formula in I2 (and filled into I2:I13) is


That is, if the second option button is selected, show the value, otherwise show #N/A.

The Finished Product

Here is the data and chart if the Chart A option button is selected:

Data and Chart 'A'

Here is the data and chart if the Chart B option button is selected:

Data and Chart 'B'

You can download a sample workbook by clicking on this link: MrExcelOptionButtonChart.xlsx.

Peltier Tech Chart Utility

New Survey: What Spreadsheet Programs Do You Use?

Please participate an improved, small, non-scientific survey about spreadsheet version usage.

My previous survey allowed only one version of Excel to be selected for work and for home. I knew that this was limiting, but the survey seemed easier to set up this way. But a few comments corrected my thinking, and a few responses helpfully had multiple versions entered as “Other”, so I’m going to set that one aside, and offer the following survey. As it turns out, it was not really any harder to set it up. Live and learn. You can select any and all spreadsheet versions that you use.

WordPress sometimes does funny stuff with embedded content like this survey. If the survey does not appear when the page is first loaded, wait a few seconds, then refresh the page.

[field name=iframe]

Peltier Tech Chart Utility

Survey: What Version of Excel Do You Use?

Update: Survey closed, Results posted

This quick and dirty survey has been closed. Thanks to all who participated. Results will be posted in the near future. In addition, a newer and better version of the survey will be/has been posted at New Survey: What Spreadsheet Programs Do You Use?

The original survey (shown below) allowed one response each for version of Excel used at work and home. This ignores those who use multiple versions, for example, developers who need too make sure their spreadsheets will work on any version of Excel.

Excel Version Usage Survey

My main intention in this survey was to get an idea of how many Excel users have upgraded to “New” versions (2007 and 2010), and how many are still using the “Classic” versions (1997 through 2003). The following chart shows my findings.

Breakdown of Excel versions in use

So about 86% of users have upgraded, while 14% are staying pat. The percentage was almost identical for usage at work and at home. I’m not sure if I’m surprised.

A handful of respondents entered multiple versions into the “Other” boxes in the survey; if these responses included both Classic and New, I didn’t count them. The follow-up survey will try to capture this usage more accurately.

Peltier Tech Chart Utility

Getting Answers For Your Excel Questions

NOTE: Please do not reply to this post with a question. Read this post to see where and how to ask your question.

You’ve exhausted the built-in and online help provided by Microsoft, and don’t have what you need. So where do you go to get help in Excel?

There are a large number of resources available to you. Search engines, online forums, and a number of useful general Excel topic web sites, and a huge selection of Excel books.

What Have You Tried?

In What Have You Tried?, Matt Gemmell bemoans the questions from people who have not done their homework. Here are some preliminary suggestions from Matt to ensure that you are ready to be helped:

  • Have you broken the question or problem down sufficiently to really ask something concrete?
  • Is your problem the sort of standard question for which there’s definitely already some sample code and documentation available? Skim the documentation, or do a quick search.
  • Try searching the web. If you’re having trouble getting a decent result, you need to narrow things down. Someone else has probably asked your question – or maybe a hundred someones.

Direct Inquiries

It’s tempting to send an email directly to me or to another expert whose previous website or forum post has been helpful. I get a dozen or more unsolicited emails a week asking for general Excel help. I welcome questions and clarifications regarding topics posted on my web site, but I don’t often have the time (or motivation) to address emails out of the blue. It’s more effective to post a question on a forum with a broader audience (see below), because many more people will see the question, and several people will respond to a public post before a single busy individual even notices a stray email. I’d rather answer a public question, because it becomes part of the body of public knowledge, more people will see it, and Google will have a chance to pick it up.

Posting a question in a comment to an unrelated blog post is also not very effective. You’ll either have your comment deleted, or if you’re lucky you might get a link to a more relevant post. If you’re on someone’s blog, use their search box to find a more relevant post yourself.

Search Engines

[Read more…]

Peltier Tech Chart Utility

Why Do We Love Pie Charts?

There is quite a discussion ongoing in Business intelligence vs. infotainment on Nathan Yau’s Flowing Data blog. It all started with a marketing guy from Teradata praising the innovation of the data visualization of David McCandless, designer of The Visual Miscellaneum. Stephen Few responded to Teradata’s praise of McCandless’ work in Teradata, David McCandless, and yet another detour for analytics, in which Few called McCandless to task (again) for passing off ineffective data art as cutting-edge information visualization. Nathan called attention to Stephen’s “rant”, and we were off to the races.

I will let the dozens of comments under Nathan’s post speak to the debate about McCandless’ efforts, which I find eye-catching but generally not effective at sharing actual information. However, I did want to share my thoughts on why so many of us continue to use ineffective visualization techniques.

“People like ineffective graphics”

A number of commenters stated that that people like attractive, sexy-looking presentations and fluffy pictures, regardless of their shortcomings. Andrew Vande Moere of Infosthetics suggests that we try to “learn why people actually prefer the less ‘effective’ infographic” (and he specifically mentioned “circular graphs”), so that we can improve the general state of data presentation.

Few replied, “I’m not aware of any research offhand that specifically attempts to explain why some people (certainly not all) prefer forms of display that don’t effectively provide what they need from the information.”

I have a hypothesis about this, pure speculation really, but others may add to it, or at least be amused.

Why do we love pie charts?

We don’t like ineffective graphics, we like familiar graphics.

We learn pie charts from Miss Jones in second grade. Miss Jones teaches many subjects and has to keep a classroom orderly and well-mannered, so she is by no means an expert in data visualization techniques. However, she is a person of authority and we naturally follow her example.

Over the years, we see many pie charts, taught to every second grader by all the well-meaning Miss Joneses out there. The pies are ubiquitous, so we become familiar with them, and we never realize their limitations.

Since all of out software packages, particularly the expensive shiny ones, offer pie charts prominently, we assume they are among the best charting options.

When we see an icon for a 3D pie chart, we click on it, because after all, it’s one whole D better than 2D.

Pies have been ingrained in our consciousness for so long, they become one of the first things we reach for.

Proof That Circular Graphics Are Not Intuitive

Maybe “proof” is too strong a word, but what follows is certainly a telling demonstration.

To anyone who doubts how unintuitive circular charts (pies etc.) truly are, I have a simple question: Have you ever taught a young child to read an analog clock?

Whatever – – Whatever

If children know their numerals, they can read “8:47″ on a digital clock, and at least know it’s between 8 and 9 o’clock. In time they even learn that :47 means closer to 9:00 than to 8:00. On an analog clock, 8:47 is obviously closer to the following hour, but knowing the hour isn’t easy. You either have to estimate the angle of the hour hand, or you have to count and interpolate between ticks or read the numerals. Clocks with a square face have lost some of the supposed effectiveness of circular symmetry. And don’t forget the additional difficulty provided by hours expressed in Roman numerals.

Disclosure: The clock images above are affiliate links to product pages on Amazon, where you can purchase such clocks, and earn me a teeny commission. There is a round Whatever clock hanging in the hall outside my office.

A Defense of Pie Charts

I have read Robert Kosara’s recent In Defense of Pie Charts in his Eager Eyes blog. He points out (as Stephen Few did way back in Save the Pies for Dessert (pdf)) that pies have one advantage over other types of graphs: you can readily compare combinations of adjacent wedges. In a pie with four wedges, for example, you can compare A+B to C+D. You can also compare A+D to B+C. However, unless this is planned it is simply the result of an accidental arrangement of the data, since you may in fact be more interested in comparing A+C to B+D.

I’ve always felt that this argument is an afterthought, a rationalization for having used that pie in the first place. If you knew a priori that the comparison of added data points was important, you could easily enough have prepared a stacked bar chart with A+C vs. B+D, or even with all pairs. This would of course be preceded by a bar chart comparing all of the individual points.

Peltier Tech Chart Utility

Peltier Tech Chart Utility


Create Excel dashboards quickly with Plug-N-Play reports.