Last week, in You Have 1 New Notification On Klout!, I used social media metrics site Klout to illustrate how choice of axis scales can exaggerate or wash out the variation in a data set. Today I’ll pick on another social media metrics site, Topsy, to show how to pick meaningful axis tick spacing parameters.

A meaningful axis spacing allows a human viewer to make sense of the numbers in your chart.

Original Topsy Charts

Here is a chart of Twitter mentions of my blog over a one week period. Sorry the chart’s too wide, that’s as small as Topsy would make it and still have text large enough to read. You could right click on it and choose your browser’s equivalent of “Open Image In New Tab” to see it in all its glory.

Here’s the Topsy graph for two weeks.

Notice anything wrong with these charts? No, they do have the right number of points. But the vertical gridlines and the horizontal axis labels are not aligned with the points. In the 7-day chart (top), there are 8 labels between the axis min and max values. To accommodate this mismatch, some adjacent pairs of tick marks fall within the same day, so a couple of labels are repeated. In the 14-day chart (above), there are 9 labels between the axis min and max value; some days have no tick marks, so dates are left out, but not in a regular pattern.

This kind of unorthodox labeling causes the humans to have to think too much about the chart. Sometimes the choice of incredible charting options like this leads to lack of credibility of the whole chart.

Human-Friendly Axis Spacing

In a 7-day graph, what would be an intuitive axis tick spacing? Let’s try one day, since one week is too wide and one hour too narrow. In general, numbers that are 1, 2, or 5 times a power of ten make good values for axis tick spacing. 1, 20, 500, 0.01, and 0.5 are reasonable choices. If the scale is days, and a spacing of 1 or 2 days result in crowded labels, 7 days is a reasonable choice.

Here I’ve reconstructed Topsy’s 7-day chart with a 1-day axis tick spacing. It’s very natural, the ticks and gridlines are spaced the same as the data points, one day apart. Nobody has to use any excess gray matter to understand the time scale.

Here is the 14-day Topsy data plotted with a 1-day axis spacing. It is as easy to read as me 7-day chart with 1-day spacing, which is to say, much easier than the Topsy Turvy spacing in the original chart.

This is really more axis labels than are needed, and some of them are forced to wrap so they don’t overlap. We can fix this by using a 2-day axis spacing. Also easy to read. I’ve helped the viewer by placing small minor tick marks at 1-day intervals.

Intermediate gridlines work as well as intermediate tick marks.

Topsy’s Axis Scale Parameters

So what was Topsy thinking? Well, I can’t answer that, but I can estimate the axis tick positioning that they used.

Here is Topsy’s 7-day data. I’ve secretly replaced the regular time scale axis with an XY series that has spacing independent of the actual plotted points. Vertical error bars on the invisible points serve as my gridlines. The X values are based on formulas I can tweak in the worksheet, and I align the custom gridlines to closely resemble the original Topsy alignment. Jan 13 and Jan 15 both appear twice as in the original chart.

To get the spacing right, the first gridline appears at 3:44 pm on January 12, which rounds up to the Jan 13 shown in the label. Each subsequent gridline is 16 hours and 40 minutes after the previous one. I think we can all agree that 16:40:00 is not as intuitive as 24:00:00.

I’ve reproduced the 14-day chart as well. The first gridline appears at 3:20 pm on January 5, which rounds up to Jan 6. Subsequent labels are 33 hours and 20 minutes apart. Again, not so intuitive.

I can’t really say where these strange tick spacing values came from, but I have a suspicion. 16:40 is 1000 minutes, and 33:20 is 2000 minutes. If the time dimension were plotted in minutes, the two charts have ranges of 8640 minutes (7 days) and 18720 minutes (14 days), so in fact 1000 and 2000 are human-friendly numbers. Of course, the data is spaced 1440 minutes apart, so the nice minute-based axis spacing is really irrelevant.

I suspect the charting mechanism has a nice algorithm to calculate the spacing based on the minimum and maximum data values, but it doesn’t consider the actual data spacing, nor does it investigate alternative units. And the algorithm was automated before a human had a chance to look at it and say “Huh??”

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.

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

Go to the source, Google. Search for a few related keywords. If the results aren’t what you need, they may at least give you ideas for better keywords. I even use Google to search Microsoft’s site. Use site:microsoft.com as one of your keywords to focus the search on microsoft.com.

Forums

There are a large number of forums devoted to Excel. For some reason, new forums keep popping up, even though a new forum lacks the core of experts and depth of archived information of an established forum. The established forums include Mr Excel, OzGrid, Excel Help Forum, Experts Exchange. I visit various forums from time to time to see if I can answer a few questions.

Choosing a Forum

Qualities of a good forum include:

• Lots of traffic: dozens or hundreds of new threads each day.
• A long history: archives extending back five years or more.
• Recognized experts: members with designations indicating expertise (but watch out for too much game-like clutter, like badges and medals and point counts).
• Relatively few unanswered threads.

How to Ask a Forum Question

• Take a couple minutes to try Google first. Many questions have been asked and answered a thousand times before.
• Spend a few minutes searching the forum’s archives.
• Use a descriptive subject line. I skip posts with subjects like Help!! or Excel Question.
• Write a concise and clear problem statement. State what you are trying to do, what steps you took, and what happened. (Sometimes framing a question well is enough to clarify the problem in your own mind, and you figure it out yourself.) If you get an error message, include the description in its entirety and not just the error number. Sure it’s an effort to retype it, but the error number may correspond to several different descriptions. Error number 1004 means a procedure halted during execution: very informative.
• Include the Excel version somewhere in your problem statement.
• Write clearly, DON’T TYPE IN ALL CAPITALS, don’t use text message shorthand LOL, proofread your post.
• Don’t look for a button to upload your workbook. If you can’t describe your problem without attachments, most people won’t bother trying to answer. If someone wants more information, they will ask for a workbook.
• Be courteous and patient. If your question isn’t answered within several hours or a day, reread the question.
• Don’t bump your own post to bring it to the top of the list. Not everyone is in your time zone or takes breaks when you do, so let your question mellow until someone finds it. Also, adding a post to the thread, even just yourself bumping it, makes the thread look answered, so someone who is looking for unanswered questions will skip yours.

Why isn’t the Forum Answering My Question?

• Is the subject line vague, as in I need excel help!!!?
• Is it so simple that a Google search would have uncovered the answer in 60 seconds?
• Is it written so poorly that nobody wants to try to decipher it?
• Does it look too much like you want someone to do your homework?
• Does it look like you’re asking for someone to do a whole project for free?
• Is it written discourteously?

A Few Forums

Mr Excel is undoubtedly the best of the forums. Mr Excel receives more traffic than the rest, hosts a huge archive of solved issues, and has a large number of knowledgeable users willing to answer questions.

OzGrid used to be focused more on revenue than on the content of the forum, but this has improved in recent years. OzGrid has decent traffic, a good archive, and a number of experts who respond to posts.

While Mr Excel and OzGrid receive a meaningful amount of traffic, most other forums don’t seem to have critical mass.

StackOverflow and SuperUser, which are respectively developer- and power-user-targeted partner forums, have moderate amounts of Excel traffic, and I like the flow of their layout. They are relatively new, but their Excel traffic and archive are expanding.

Microsoft has a number of relevant forums, such as Microsoft Office Excel, Excel for Developers, and VBA. Microsoft’s used to host very good newsgroups, but they ditched the newsgroups in favor of their own forums. The early versions of the forums were unusable. (MS suffers from an incredible case of Not Invented Here, so they are continuously reinventing the wheel, and as we know, Wheel 1.0 is often square.)

The Microsoft forums have improved, and they get a huge amount of traffic, redirected no doubt from the helpless online help, but they lost most of their knowledgeable members when the newsgroups were liquidated. Instead, numerous helpdesk-style contractors respond semi-intelligibly on Microsoft’s behalf.

Excel Help Forum is somewhat active, but you can scroll through a few days’ new posts in just a couple minutes.

Experts Exchange is a good quality forum, but it’s a paid membership service, and its structure is very constraining. Plus it’s overly concerned about scoring answers, to the extent that once a responder got upset that I added to his answer, because he didn’t want to share points. Sheesh, if I’m trying to answer someone’s question, I care if the answer helped. Why do I need points to prove how smart I am?

VBA Express and XL Guru are of good quality, but unfortunately their traffic is too low. Tek-Tips has Office and VBA forums which are intermediate in quality and traffic.

LinkedIn has recently appeared on the scene with its forums. So far most of these these forums have had no value. Most members have no apparent experience with forums, have not developed forum etiquette, and ask dumb questions with answers found easily elsewhere. Then someone will give a lame answer as if it’s actually helpful, and seventeen others will respond with “Me, too!”

I once suggested that a member of a LinkedIn forum visit Mr Excel, and was told by several forum members that the personal interaction on the LinkedIn forum was a great feature. Uh, what? You get the same interaction on Mr Excel and the other forums, only it’s with smart people who can actually help. I no longer spend any time on the LinkedIn forums.

Specific Web Sites

There are a large number of useful Excel web sites and blogs. You’ll find most of them if you use Google well, and if you take note of who is answering questions on the forums. But here are a few noteworthy sites.

Chip Pearson’s Excel Source has hundreds of pages covering a wide variety of topics using worksheet formulas and VBA.

The Spreadsheet Page is John Walkenbach (Mr Spreadsheet)’s site filled with free tips, downloads, and other stuff, plus information about his books and other Excel products.

ExcelUser is Charley Kyd’s site directed toward business users of Microsoft Excel. ExcelUser has written about Excel dashboards, and offered dashboard-related products for Excel, since authors of other dashboard web sites were still in grade school.

Contextures is Debra Dalgleish’s site of Excel Tips and Tutorials. If you have a question about pivot tables, autofilters, conditional formatting, data validation, you’ll find the answer here.

Chandoo.org has a number of free and paid Excel learning resources by everyone’s favorite, Chandoo.

Excel Books

There are innumerable books on all aspects of working with Excel. I won’t list them here; instead I’ll direct you to my web page that lists the Excel Books that I own and actually use. (I own others that I don’t find useful, and I’ve left them off the list.) I update this book list every so often, when I’ve gotten a couple new books or when a new version of Excel has been released. Disclosure: the book page contains Amazon Affiliate links, as does the sidebar of this page, so if enough of you buy enough books, I’ll be able to start posting from exotic vacation destinations.

Here’s a problem that I’ve heard people ask (and complain) about. They’ll have data in their workbook, neatly sorted from top to bottom. When they make a bar chart, the sorting is reversed, with the Bottom data appearing at the top of the chart. Not only that, but the series are in backwards order too. If they sort the data in reverse order, the chart’s categories look right, but now the worksheet is upside-down, and the series are still in the wrong order.

The reason for this arrangement is logical, once you figure it out. To illustrate, let’s start with this data. Yes, the data in column A is mixed up. Instead of 1-2-3-4, it goes 1-3-2-4, to help illustrate differences between XY and Line charts at the same time.

Here is an XY chart made from this data. Looking at Series 1, we see it starts at X=1, proceeds to X=3, then X=2, and finally X=4. No surprise, since it’s following the data, which as I pointed out above, is in mixed up order.

Big deal, no surprises. But let’s look at the chart axes for a moment, and review 4th grade math.

By default, the two axes will be located along the bottom and left edges of the chart. Where the axes intersect is called the origin, and it is where both axes have their minimum values (we’re dealing with non-negative data). Lower values are plotted closer to the origin, and higher values further away.

Let’s make a Line chart with the same data. It looks different from the XY chart. No, the series are formatted the same, with markers and lines. But check out the horizontal axis. The numbers are not sorted numerically, they are listed in the order they appear in the worksheet, as if they have no numerical value. In fact, in line, column, area, and bar charts, Excel treats X values as non-numeric labels. (Unless the X values are dates, and I’ll cover that another time.)

Note that the axes still cross at the origin in the lower left of the chart. Lower Y values are closer to the origin, and X values encountered earlier in the worksheet are closer to the origin.

Okay, easy enough. In a line chart, Excel lists the X axis labels in the same order as in the worksheet, and the origin is at the bottom left of the chart.

Same with a column chart. Note in the column chart that the order that series are clustered is also the same order as in the worksheet.

Now let’s make a problematic bar chart.  1 is in the top of the worksheet range, but at the bottom of the axis. But remember, the origin is at the bottom left, and the lowest values and first labels are located closest to the origin. So the “1″ label on the vertical axis is closest to the origin, that is, lowest in the chart, even though it was highest in the worksheet. The series are also in the order they are because series 1 is closest to the origin.

If you really need the chart to be arranged the other way, it’s a simple two-step fix. First, format the vertical axis, and check the box for “Categories in reverse order”.

See, now the top label in the worksheet is also the top label in the chart. Reversing the categories has moved the origin to the top left of the chart, so the bottom axis is now at the top of the chart. Note also that the series are now listed in the expected order.

If you want the horizontal axis back at its customary bottom position, format the vertical axis again. (You can do this step at the same time as the earlier Format Axis step.)  Select the option button for “Horizontal axis crosses: At maximum position.”

Since the vertical axis maximum is now at the bottom, that’s where the horizontal axis appears.

Now we know enough to go back and fix the original chart. Format the vertical axis, reverse the category order and make the horizontal axis cross at the maximum.

Top is Top, Bottom is Bottom, series are plotted in 1-2-3 order, and we can finally rest easy.

Every so often I get an email with a subject line that’s something like “Jon, you have 1 new notification on Klout!” Wow, another social network thing, to go with all the other ones. Sure, I follow a bunch of people on Twitter every day, and I have a neglected Facebook account and a LinkedIn account that I don’t know what it’s useful for yet. So I also have a Klout account.

My current Klout ranking?

I dunno, I guess that’s pretty good. Some of my colleagues, and the people I follow on Twitter mostly fall in the range between 25 and 55. So who else has a 43? My fellow Excel chart master, Jorge Camoes, has a 43. An old college buddy, Joel Foner, has a 43. So does fellow Microsoft Excel MVP Ken Puls. Annmariastat has a 43, and she doesn’t even have a Klout account, but she has a Wikipedia entry. So I’m in good company.

Read more »

Someone tweeted a link to a bad pie chart. I forget who it was: if it was you, let me know in the comments and I’ll give you credit here. But it’s not merely a bad pie chart, it’s an awful pie chart. A wonderful, awful pie chart.

And the Grinch got a wonderful, awful idea.

The chart appeared in 2011: The Year in Review on the Kickstarter web site. Kickstarter is a unique program for funding projects, which lines up proposals with backers who pledge support for them. It actually seems like a great program, but they need to work on their graphs.

The cited article describes their funding activities for last year. The pie chart contains much of this data, but not in a form that is remotely useful.

The “Chart”

This is the first view I had of the pie chart. All of the slices are the same size, which is strange, and no information is visible except for the category names, which is awkward.

More ominous is the label above the chart that reads “Mouse over a color to see that category’s stats.” So you have to mouse over the chart to show any of the data.

I first saw this on my mobile, and, well, I couldn’t mouse over anything. Later I loaded the page on my laptop so I could examine it in greater detail. I moused over the pie, expecting to see a popup with some data. Nothing. I spent more than a minute mousing over the picture before I realized the data was displayed in the same rectangle that first directed me to mouse over the chart.

When you mouse over the wedge, you need to note the category name, and try to memorize three numbers, before you move to the next wedge and its three numbers. And the next, and, uh. . .

The Problem

I’ve been calling this illustration a “chart”, but it’s not really a chart. It’s not really a graph. The best I can call it is a picture, or an illustration.

It’s not a graph, because not a single byte of data is displayed graphically in the illustration. You see the data when you mouse over a segment of the pie, then it disappears when you mouse over another segment. If you can’t remember the text, you cannot compare the values from segment to segment. Maybe I was stupid not to notice the data the first time I moused over a slice of pie, but nobody is smart enough to keep all of this text in their memory.

It’s so bad, that I moused over the picture for several minutes, and had no idea which of the categories had more or less support, and whether support measured by amount pledged tracked with number of backers or number of funded projects.

Interactivity is okay when it’s done effectively. The user should not need the mouse to see the overview of the data. The interactivity should be used to allow deeper investigation into subsets of the data and into alternative views.

A Better Chart

This is a rough draft of a different view of the data. I hardly spent any time on the labels, just enough to give viewers a taste.

It’s a simple panel chart, with three panels of bars, one each for amount pledged, number of projects, and number of backers. The categories are sorted by decreasing order of amount pledged, not alphabetically. The sorting is almost preserved in the other panels, which makes sense: more projects and more backers would gather more pledges.

Every bit of data that is hidden within the original pie is clearly displayed in the panel chart. No need to play cat and mouse to see information, no need to rely on a weak short term memory to allow comparisons and to see trends.

It is not difficult to build interactive charts in Excel that allow a user to selectively highlight a subset of the data for detailed examination. Excel’s semi-intuitive worksheet controls make this technique suitable for dynamic dashboard reports.

Background

Last week in Series Lines: Useful or Chart Junk?, I wrote about Excel’s “Series Lines” feature, and how it seems like a good way to clarify the data in a stacked column chart, until you implement it and realize it just adds chart junk to your chart. I proposed a panel chart to show the stacked chart data more clearly.

Gregor Aisch commented that my panel approach did not allow point-to-point comparisons as effectively as a simple line chart (despite the line chart’s clutter). So I put together the following example of an interactive chart that allows a user to compare a couple series in context of the whole data set.

Here is the original stacked column chart, with series lines cluttering up the space between columns.

Here is the panel-style chart I constructed to show the trends more clearly. As Gregor has pointed out, comparing individual point values (for example, 2008 for alpha and for epsilon) is difficult. Also, if there are many categories (alpha through omega, for example), the data will be compressed horizontally and will be impossible to read.

Our interactive chart will look like this. The user can selectively highlight data using the two listboxes next to the chart.

Interactive Chart: Data

We’ll use this data for our exercise. A1:G7 contains the data used in the previous discussions. A9:G11 contains repeated data for two of the series in the upper table. These are selected using Excel’s INDEX() worksheet function and the index values in H10 and H11.

Cell A10 contains the formula

=IF($H10>1,INDEX(A$1:A$6,$H10),"")

which is copied into A11. Cell B10 contains the formula

=IF($H10>1,INDEX(B$1:B$6,$H10),NA())

which is copied into C10:G10 and B11:G11. The way these formulas work is as follows:

If the index in column H is 1, column A looks blank and columns B:G contain #N/A, which is not plotted in a line or XY chart. If the index is greater than 1, the label from column A and the values from columns B:G are displayed in that row. By changing the values in H10:H11, we can change the data in their rows. If this data is plotted, then changing H10:H11 will change the chart.

Worksheet Controls

It’s nice to be able to modify a chart by changing numbers in a couple cells, but this becomes tedious after a while. Fortunately, Excel has a number of helpful controls that we can drop onto the worksheet to do this in an easier and more natural way. For choosing from among a list of items (alpha through epsilon), we can use a Combo Box or a List Box.

In Excel 2003 and earlier, you can access these controls by displaying the Forms toolbar. In Excel 2007 and later, you need to display the Developer tab*, then pick from among the Forms controls under the Insert button. (There are also ActiveX versions of these controls, and they have more extensive formatting capabilities, but ActiveX controls can misbehave.

* To show the Developer tab, you have to open Excel Options, and find the setting for the Developer tab.
In Excel 2007: Click on the big Office button in the top left of the window and select Excel Options; check the box in front of Show the Developer tab in the Ribbon.
In Excel 2010: Click on the File tab and select Options; click the Customize Ribbon button on the left, and in the right list, check the box in front of Developer.

Here is the Combo Box button on the Developer tab.

And here is the List Box button.

Both the Combo Box and List Box display a list of items for the user to select. I prefer the List Box, because you can several items even without clicking on the control. The Combo Box only shows one item at a time, until you click the control. Of course, if there isn’t room to display a List Box, then the Combo Box works fine.

When you click on the control button in the ribbon (or on the Forms toolbar in ancient Excel), the cursor turns into a crosshair, and you can draw your control just like drawing any other shape. In this example I’ve made two identical controls, one for each series we allow the user to highlight. You could display two list boxes side by side, two combo boxes side by side,  two combo boxes one above the other, or any other arrangement that works in the available space on the worksheet.

Even if I’m using multiple controls, I usually make one and format it, then make as many copies as I need, either by using Copy and Paste or by Ctrl-dragging. After copying, I change specific formats (such as the cell link, below).

When you right click on a control and choose Format Control, you are presented with this dialog. In a combo box or list box, the input range contains the list of items displayed in the control. The cell link is the cell that contains the index number of the selected list item, starting with 1 for the first item in the list. This is how the index values in H10:H11 will be changed.

With the controls formatted and the formulas already in place, play with the controls and make sure the data changes as expected. Note that multiple controls can use the same cell link. Changing one control will change the value in the linked cell, and consequently change any other controls using this cell.

Building the Interactive Chart

The data and the controls are all set up, so now we create our charts. Select A1:G6, and insert a line chart. Resize and format the chart as appropriate.

Now format all the series to de-emphasize them. Thin lines, small markers, light gray color.

It’s easier to keep these gray series, and add duplicate series with bolder formatting for the series we want to highlight. The lighter data is hidden behind the bolder series.

To add the highlighted series, copy A9:G11, select the chart, and use Paste Special to add the data as new series, with the First Row and First Column check boxes both checked. Format these series to stand out compared to the light gray background data, and add a series name data label to the last point of each.

Now arrange the controls next to the chart. The list boxes have ugly black borders, but I hid them behind unfilled rectangles with light gray lines, and I colored the chart area border to match.

In fact, you can relate the list boxes to the appropriate highlighted series by coloring the gray rectangles around the list boxes to match the series.

Using the Interactive Chart

The user can now highlight and compare two series, without interference from the rest of the data which is still shown in the chart. If no other series are selected in the list boxes (or combo boxes), the corresponding data values are all #N/A, so the points don’t even appear in the chart.

Any two series can be selected for comparison. Note how easily the opposite trends of series gamma and epsilon can be seen in this chart. In the original line chart it was very difficult to resolve these series, because the other series were not de-emphasized by shading them gray. Yet here we can still see where these two series lie compared to the overall spread of the data.