An Enormous Infographic

In Anyone But Romney: The GOP race so far, the National Post shares an enormous infographic with us (click for full size image).

Read more »

According to Do You Know What Planned Parenthood Actually Does?, “the Susan G. Komen Foundation has announced they will stop funding Planned Parenthood for breast cancer exams and other breast-health services.” MoveOn.Org attributes this action on pressure from the Republican Party, because of their stance on abortion and on the abortion practices of Planned Parenthood.

Planned Parenthood gives us this breakdown of services provided by their affiliated health care centers (download pdf):

As pie charts go, it’s not terrible. It demonstrates MoveOn’s point, that abortion accounts for only 3% of Planned Parenthood’s treatments. The remainder goes for contraception and health services, mostly for poor women.

The data is also well-presented using a bar chart. This chart uses larger type and yet requires less space than the pie. The data is sorted in an easier-to-read layout, and it’s easier to highlight one value (abortion) using a darker color, because the chart uses shades of only one color.

The bars show all of the components of the whole block of services provided, as indicated by the horizontal axis label. To show that the bars add to 100%, another data series can be added and connected with a line, to make a Pareto chart. The top four items, all unrelated to abortion, account for 96% of Planned Parenthood services.

Microsoft introduced Sparklines as a native feature of Excel 2010. In a rare guest post, Sparklines For Excel vs. Excel 2010 Sparklines by Alex Kerin of Data Driven Consulting compared this new feature to existing third-party sparkline add-ins for Excel. In Sparklines and Data Bars in Excel 2010, I gave an introduction into how to use the new sparkline feature, demonstrated some of the options available for Excel 2010′s sparklines, and also showed how Excel 2010′s implementation of Data Bars was far superior to the original Data Bars in Excel 2007. I’ve also shown How to Make Horizontal Bullet Graphs that can be fitted into the cells of a dashboard.

While it’s good to see sparklines as a native Excel feature, the Excel 2010 implementation is rudimentary, and third-party sparkline products have more functionality and more features than the native Excel sparklines. Yet it’s not necessary to deal with add-ins in order to realize expanded sparkline capabilities in Excel. Excel’s regular charts can be used to create decent sparklines, and this article will show you how.

Native Excel 2010 Sparklines

It’s easy enough to insert sparklines into an Excel 2010 worksheet. First, make sure you are not in “Compatibility Mode”. Compatibility Mode means the active workbook is as compatible as possible with Excel 2003; the most obvious feature is that the worksheet grid has the Excel 2003 numbers of rows and columns, not the expanded grid introduced in Excel 2007.

Select the data range or the location for the sparklines, and click one of the Sparklines buttons on the Insert tab. In this example, I selected the range where I wanted the sparklines to appear, then clicked the Column Sparkline button. The dialog shows the selected range in the Location Range edit box.

Then I selected the data range, which appears in the Data Range edit box. For some reason, the Location Range edit box is cleared (and each box clears itself when the other box is edited), but Excel remembers the selected range.

Here are the sparklines with the location range still selected.

Finally, here is the table with sparklines, with the active cell out of the way.

Create Sparklines From Regular Charts

The protocol for generating your own sparklines using regular charts is presented below. This protocol works well in Excel 2010 and 2007. In earlier versions of Excel, charts have a border of several pixels around the plot area, so the chart area must be sized larger than the cell you want the sparkline displayed in. In earlier versions, there is also a limit to how much the chart can be shrunk and still show the entire plot area, so you’ll have to shrink the chart only partway, then shrink the plot area to a smaller fraction of the chart area size.

Keep in mind that while you can use regular charts for sparklines, the small size of a sparkline limits the amount of information you should try to cram into one. Leave out labels and limit yourself to about two series maximum in any given sparkline.

Start creating your sparkline by selecting the data for a single series.

Insert a chart of the desired type. This is a typical Excel 2010 column chart.

Now simplify formatting. Below left shows the chart with the chart area border removed and the chart and plot areas made transparent, so borders and cell fill colors show through. Below right, the legend and any axis and chart titles have been removed.

The bars have been widened in the chart below left (by decreasing the gap width to 50%), and the major unit of the Y axis has been set to a small value, below right.

The axes have been hidden by selecting “None” for axis tick marks and axis tick labels and choosing “No line” for the axis line color. Note the size of the plot area within the chart.

For best results, extend the plot area almost to the left and right edges of the chart, and stretch the bottom of the plot area to the bottom of the chart. Leave a large margin between the plot area and the top of the chart. In fact, you may have to increase this top margin in the sparkline, after the chart has been shrunk to fit a cell.

Finally, shrink the chart and position it over the appropriate cell. If you hold down the Alt key while moving or resizing the chart, the chart edges will line up with the cell boundaries. If necessary, shrink the plot area from the top to increase the margin.

When all of the necessary formatting has been applied to the sparkline, and nothing else needs to be done, copy the sparkine and paste it into each of the other cells that need a sparkline. If you hold Ctrl and Alt while dragging the chart, a copy of the chart will be dragged into and aligned with the next cell.

All the charts are formatted identically. They also use the identical data, so let’s fix that.

Select the first sparkline. Notice how the source data is highlighted in the worksheet.

Select the second sparkline. Notice how the highlighted data is from the first row. So is the highlighted data for the third sparkline.

You could change the source data by choosing Select Data from the Chart Tools > Design tab, or from the right-click menu. You could also edit the chart series formula. But the easiest way to adjust the chart data is to drag the range highlight with the mouse. Move the mouse over the highlight until its border thickens, then drag it to the new range. If you’ve selected the plot area or chart area, both the series name and the Y value highlights move together. If you’ve selected the series itself, the series name and Y data must be changed separately.

Correct the third sparkline’s source data in the same way.

Here is the finished table with sparklines. Looks like the built-in sparklines.

You can use any chart type: here’s a line chart with markers.

Why Use Regular Charts?

There are a number of reasons to use regular charts rather than the built-in Excel 2010 sparklines.

Excel Version

One obvious reason is that you might not have upgraded to Excel 2010. If you’re using Excel 2007, the techniques shown here work the same way.

In Excel 2003 and earlier, the chart imposes a thicker border between the plot area and the chart area, so you have to oversize the chart to make the plot area fit as intended. Also, in earlier versions, the plot area only shrinks a certain amount within the chart area, so further shrinking of the chart truncates the plot area. The chart area can only be shrunk so far, but the plot area can be reduced further without shrinking the chart. The result is a chart that’s substantially larger than the cell it covers, but making the plot and chart areas transparent makes this no problem.

Mouseover Information

An important feature of a regular chart that is lacking in a sparkline, is the ability to mouse over a point and read relevant information from a popup. This is very useful in an interactive dashboard.

Combination Charts

What if you want to compare a time series to other data, such as a target value.

Native sparklines can only plot one timeline, while a regular chart has more flexibility (as do some of the third-party sparkline programs).

Remember the limited resolution of a cell-sized chart: adding the simple target data to these sparklines is almost not worth the effort.

Other Embellishments

You may wish to reverse the vertical axis. For example, your costs are probably reported as positive numbers, but you want to show them as negatives.

Native sparklines do not support reversing the vertical axis, but regular charts do.

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.