For business users of Microsoft Excel Free guides and templates

Analytical Charting

3 Simple Tricks to Improve Analytical Charting in Excel

If you're charting only your raw business data, you're missing many opportunities to give your readers useful business insight. Here are three simple tricks that will help you to turn your ordinary charts into analytical ones with much greater insight. 

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

For years, my Excel charts of trends used a simple idea: To gain insights about periodic data, just chart it.

What else was there to do?

As it turns out, there were a lot of tricks I could have performed with my data before charting it. Those tricks bring many more insights than simply charting the raw data would bring.

To show you what I mean, I'm going to show you a series of charts that that illustrate a combination of six tricks that you can use with your own data to find more insight in it. I explain three of the tricks below, and three more in 3 More Simple Tricks to Improve Excel Charts for Business.

To do so, I'm going to show you charts of US economic data, for at least two reasons. First, seeing real data is much more interesting than seeing artificial data.

Second, many reports and analyses of internal data could be improved by using external data to add context to your internal numbers. For example, if external data shows that our economy is in a slowdown—a fact that's seldom reported—including slowdown indicators in your reports could help you to show your company's performance in context.

Trick 1: Shift Your Time Periods

I'm writing this on February 5, 2018. The Dow Jones Industrial Average has dropped 1804 points since January 31. That seems like a big deal; but is it?

Several weeks ago, I saw an interview with Art Laffer, who reminded us that after the Reagan tax cuts, the DJIA increased 12-fold over the next 17 years. So I created a chart that shows the performance of the Dow since the end of the last recession, placed on top of the progress of the Dow for the 17 years after the Reagan tax cut.

Here it is:

An Excel chart that compares the DJIA after the Reagn tax cut to the Dow after the Great Recession

The red line in this chart ends with February's nearly 2,000-point drop in the Dow through in the first third of February, 2018. As you can see, the drop is hardly visible.

From an Excel perspective, I've used two tricks here. First, I've shifted the time periods so that I'm not showing the performance by date. Instead, the horizontal axis shows the number of years after two specified starting dates.

When I was the CFO of a technology company, I used the same trick to compare our sales after the launch of each new product with sales after the launch of other products in the past.

The preceding chart also shows a second trick. Rather than comparing the values of the DJIA at two separate periods, I compared the growth of the two series over time. That is, I divided each value in the red and blue lines by a base period for each line, which typically is the beginning value in the chart; I subtracted one; and then I multiplied by 100. This caused each line to start at zero.

Trick 2: Index Your Data

I recently was talking with a production manager who complained that his Excel users had no idea about how to normalize their data when they chart two values with significantly different magnitudes.

Because he works in Massachusetts, I created this chart to illustrate the problem and two solutions.

An example of two data series that need normalization before they can be charted in Excel.

As the caption explains, because the Massachusetts employment levels are relatively insignificant when compared to the entire US, their employment levels appear as a flat line at the bottom of the chart.

One way to deal with this problem is to take a similar approach as in Trick 1. But rather than displaying the growth rate, we display the index of both lines, like this:

The indexes of US employment levels and Massachusetts employment levels in one chart.

This is the same approach that many economic data series use. For example, the chart of the current S&P/Case-Shiller U.S. National Home Price Index uses this approach. However, that chart's vertical axis label tells us that Jan-2000 equals 100, even though the chart data begins in 1987.

That approach works fine. The chart begins in 1987 with a value of about 65, grows to 100 in Jan-2000 and continues to grow from there.

Trick 3: Chart the Rate of Change

One of my favorite techniques is to chart the data's Rate of Change (ROC), using rolling quarters. To do so, you divide the most-recent three months of performance by the same three months one year earlier, and then subtract one.

Rather than using three-month periods in your calculation, you could divide only the current month by the same month one year earlier, and then subtract one. But doing so adds noise to your chart—it makes the lines in your chart more jaggy—while adding very little useful information.

So instead, I recommend that you calculate the ROC using rolling quarters.

Here's the ROC version of the first chart for Trick 2:

A chart of the Rates of Change (ROC) of the employment levels for the US and Massachusetts.

Typically, as in this chart, it's best to use non-seasonally adjusted data. By doing so, you use actual data, not pre-processed data. And also, as you can see from this chart, you deseasonalize the data.

Also notice that I've added a recession indicator to the chart, to give the performance additional context. With those two shaded areas in place, it's easy to understand why employment fell so drastically in 2001 and 2008.

In 3 More Simple Tricks to Improve Excel Charts for Business., I show you three more tricks you can use to bring greater insight to your data.


3 More Simple Tricks to Improve Excel Charts for Business

Introducing the Power of Year-Over-Year Performance Charts in Excel

Beveridge-Chart Trend Analysis with Excel

Two Business Uses for Excel's New Chart Feature

How to Show Recessions in Excel Charts

Chart Your Rate of Change to Reveal Hidden Business Performance

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.