In the data below, there are two years of data for two different companies.

Here is all of the data plotted in a single chart.

Comparing data for a given company is most important, so I want to separate the data into separate charts for each company. But I still want to be able to compare the two companies. Here are my two charts.

Since Company 2′s data is higher, the maximum Y axis scale is larger. I could manually set the Y axis maximum for both charts to 120, but if the data changes, I’ll have to reset both charts again.

To determine what values to use, I add a small summary table near the main data table. Cells B10 and B11 compute the minimum and maximum of the data. Cells C10 and C11 show the values I will use. I want the minimum to be zero, so I simply type 0 in C10. I want the maximum to range with the data, so I enter =B11 into C11.

Copy C9:C11 and use Paste Special to add this data to each chart as a new series, with data in columns and series names in the first row (don’t worry about X values). Notice the two charts have the same Y axis maximum, because they have the same maximum value from the added series.

It’s a simple matter to format the added series to use no line, and the charts will magically stay in synch.

Te show the value of this approach instead of the manual approach, let’s add another year of data. Company 2 has really started to take off.

Here is all the data in one messy chart.

Here are the two original charts, with no attempt to synchronize axes.

Here is how the charts would look if the maximum had been manually fixed at 120. The 2012 data for Company 2 is cut off between Q3 and Q4.

However, if we make sure that our minimum and maximum formulas in B10 and B11 include the added data, the hidden series of both charts include the new maximum, so the axis scales are the same.

This is a very easy technique, applicable to line, column, and area charts (in all cases, change the added series to a line chart series). It can also be used for both X and Y scales of an XY chart if we determine minimum and maximum values for X and Y.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

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

The bulk of the graphic is taken up with what remind me of snake skins laying out to dry. Apparently the widths indicate the relative popularity of each candidate along a vertically-oriented time axis.

The unorthodox alignment of the time axis is initially disorienting, but the blobs are big, they must be important. Hmm, are those all months delineated by the horizontal white lines? Apparently so, even though they differ by more than the days in each month would account for. The spacing must be dictated by the occurrence of each successive poll, not by the passage of days.

The snake skins are decorated with parallel arrows, which change direction in mid-January. Is it the dropping out of Perry or of Huntsman that this change indicates? Or nothing; probably that’s just where the fancy fill pattern changes.

Auxiliary Charts

Okay, I’ve feasted my eyes on this lovely rotated timeline blob thing, what is shown in these small charts added as an afterthought to the bottom of our infographic?

The small chart at bottom left shows the popularity of the candidates. Wait. That’s what the big paint drips also showed. The date axis here is horizontal, left to right, but the data is indistinguishable from that in the upper graph. The months have the same irregular spacing.

At first, the small round markers beside each candidate’s name appear to show a significant time point, because Huntsman’s line is labeled at the point he dropped out. But Santorum and Paul and Romney and Gingrich haven’t dropped out, so that’s not the purpose of the circles. On further inspection, they just seem to say, “This label goes with that line”. Some lines are labeled once, some twice, and . . . QUICK! Without referring to the large chart above, which candidate goes with the yellow line?

Poor Herman Cain, couldn’t buy any respect.

I think the only purpose of the line chart is to show what the monster paint blobs showed, but in easy to read fashion (except for the labeling issue). Conversely, the big blobchart seems to be shouting, “Look Maw, I made a info graffick!”

The remaining chart shows basically that Mitt Romney has raised a lot of money, much more than his competition, and he’s also spent more than they have.

Overall, this monstrous graphic would have been more informative with just the bottom two charts, without the distraction of the rattlesnake hides that occupy 85% of the original. Of course, if the intent was to get people to look at your page, the snake hides may have succeeded.

Apparently the National Post is waiting for anybody, even Newt Gingrich, to overtake Mitt Romney’s lead in the polls. But this article, even the updated edition, predates Gingrich’s poor showing in the Florida primary. The question that remains is, Can money buy election happiness?

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

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.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

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.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

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??”

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.

Peltier Technical Services, Inc., Copyright © 2011.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.