Using chart types appropriate for trending

It would be nice if you could definitively say which chart type you should use when building trending components. But the truth is, no chart type is the silver bullet for all situations. For effective trending, you want to understand which chart types are most effective in different trending scenarios.

Using line charts

Line charts are the kings of trending. In business presentations, a line chart almost always indicates movement across time. Even in areas not related to business, the concept of lines is used to indicate time — consider timelines, family lines, bloodlines, and so on. The benefit of using a line chart for trending is that it’s instantly recognized as a trending component, avoiding any delay in information processing.

Line charts are especially effective in presenting trends with many data points — as the top chart in Figure 7-1 shows. You can also use a line chart to present trends for more than one time period, as shown in the bottom chart in Figure 7-1.

Using area charts

An area chart is essentially a line chart that’s been filled in. So, technically, area charts are appropriate for trending. They’re particularly good at highlighting trends over a large time span. For example, the chart in Figure 7-2 spans 120 days of data.

Using column charts

If you’re trending a single time series, a line chart is absolutely the way to go. However, if you’re comparing two or more periods on the same chart, columns may best bring out the comparisons.

An alternative option is to use a combination chart. A combination of line and column charts is an extremely effective way to show the difference in units sold between two time periods. For instance, Figure 7-3 demonstrates how a combination chart can instantly call attention to the exact months when 2010 sales fell below 2009 sales. (You’ll find out more about combination charts later in this chapter.)

Starting the vertical scale at zero

The vertical axis on a trending chart should almost always start at zero. The reason I say almost is because you may have trending data that contains negative values or fractions. In those situations, it’s generally best to keep Excel’s default scaling. However, if you have only non-negative integers, ensure that the vertical axis starts at zero.

This is because the vertical scale of a chart can have a significant impact on the representation of a trend. For instance, compare the two charts shown in Figure 7-4. Both charts contain the same data. The only difference is that in the top chart, I did nothing to fix the vertical scale assigned by Excel (it starts at 96), but in the bottom chart, I fixed the scale to start at zero.

Now, you may think the top chart is more accurate because it shows the ups and downs of the trend. However, if you look at the numbers closely, you see that the units represented went from 100 to 107 in 12 months. That’s not exactly a material change, and it certainly doesn’t warrant such a dramatic chart. In truth, the trend is relatively flat, yet the top chart makes it look as though the trend is way up.

The bottom chart more accurately reflects the true nature of the trend. I achieved this effect by locking the Minimum value on the vertical axis to zero.

To adjust the scale of the vertical axis, follow these simple steps:

1. Right-click the vertical axis and choose Format Axis from the menu that appears.

   The Format Axis dialog box appears; see Figure 7-5.

2. In the Format Axis dialog box, expand the Axis Options section and set the value in the Minimum box to 0.

3. (Optional) Set the Major bound value to twice the Maximum value in your data.

   Setting this value ensures that the trend line gets placed in the middle of the chart.

4. Click Close to apply your changes.

Many would argue that the bottom chart shown in Figure 7-4 hides the small-scale trending that may be important. That is, a 7-unit difference may be significant in some businesses. Well, if that’s true, why use a chart at all? If each unit has an impact on the analysis, why use a broad-sweep representation like a chart? A table with conditional formatting would do a better job of highlighting small-scale changes than any chart ever could.

Leveraging Excel’s logarithmic scale

In some situations, your trending may start with very small numbers and end with very large numbers. In these cases, you end up with charts that don’t accurately represent the true trend. In Figure 7-6, for instance, you see the unit trending for both 2009 and 2010. As you can see in the source data, 2009 started with a modest 50 units. As the months progressed, the monthly unit count increased to 11,100 units through December 2010. Because the two years are on different scales, it’s difficult to discern a comparative trending for the two years together.

The solution is to use a logarithmic scale instead of a standard linear scale.

Without going into a discussion of high school math, a logarithmic scale allows the axis to jump from 1 to 10; to 100 to 1,000; and so on without changing the spacing between axis points. In other words, the distance between 1 and 10 is the same as the distance between 100 and 1,000.

Figure 7-7 shows the same chart as the one in Figure 7-6, but in a logarithmic scale. Notice that the trending for both years is now clear and accurately represented.

To change the vertical axis of a chart to logarithmic scaling, follow these steps:

1. Right-click the vertical axis and choose Format Axis from the menu that appears.

   The Format Axis dialog box appears.

2. Expand the Axis Options section and select the Logarithmic Scale check box, as shown in Figure 7-8.

Logarithmic scales work only with positive numbers.

Applying creative label management

As trivial as it may sound, labeling can be one of the sticking points to creating effective trending components. Trending charts tend to hold lots of data points, whose category axis labels take up lots of room. Inundating users with a gaggle of data labels can definitely distract from the main message of the chart. In this section, you find a few tips to help manage the labels in your trending components.

Abbreviating instead of changing alignment

Month names look and feel very long when you place them in a chart — especially when that chart has to fit on a dashboard. However, the solution isn’t to change their alignment, as shown in Figure 7-9. Words placed on their sides inherently cause a reader to stop for a moment and read the labels. This isn’t ideal when you want them to think about your data and not spend time reading with their heads tilted.

Although it’s not always possible, the first option is always to keep the labels normally aligned. So rather than jump directly to the alignment option to squeeze them in, try abbreviating the month names. As you can see in Figure 7-9, even using only the first letter of the month name is appropriate.

Implying labels to reduce clutter

When you’re listing the same months over the course of multiple years, you may be able to imply the labels for months instead of labeling each and every one of them.

Take Figure 7-10, for example. The chart in this figure shows trending through two years. It has so many data points that the labels are forced to be vertically aligned. To reduce clutter, as you can see, only certain months are explicitly labeled. The others are implied by a dot. To achieve this effect, you can simply replace the label in the original source data with a dot (or whatever character you like).

Going vertical when you have too many data points for horizontal

Trending data by day is common, but it does prove to be painful if the trending extends to 30 days or more. In these scenarios, it becomes difficult to keep the chart to a reasonable size and even more difficult to effectively label it.

One solution is to show the trending vertically using a bar chart. (See Figure 7-11 for an example.) On a bar chart, you have room to label the data points and keep the chart to a reasonable size. This isn’t something to aspire to, however. Trending vertically isn’t as intuitive and may not convey your information in an easy-to-read form. Nevertheless, this solution can be just the work-around you need when the horizontal view is impractical.

Nesting labels for clarity

Often, the data you’re trying to chart has multiple time dimensions. In these cases, you can call out these dimensions by nesting your labels. Figure 7-12 demonstrates how including a year column next to the month labels clearly partitions each year’s data. You would simply include the year column when identifying the data source for your chart.

Creating side-by-side time comparisons

Figure 7-13 shows a chart that presents a side-by-side comparison of three time periods. With this technique, you can show periods in different colors without breaking the continuity of the overall trending.

Here’s how to create this type of chart:

1. Structure your source data similar to the structure shown in Figure 7-14.

   Note that instead of placing all the data into one column, you’re staggering the data into respective years. This tells the chart to create three separate lines, allowing for the three colors.

2. Select the entire table and create a line chart.

   This step creates the chart shown earlier, in Figure 7-13.

3. If you want to get fancy, click the chart to select it, and then right-click and select Change Chart Type from the contextual menu that opens.

4. When the Change Chart Type dialog box opens, select Stacked Column Chart.

   As you can see in Figure 7-15, the chart now shows the trending for each year in columns.

Would you like a space between the years? Adding a space in the source data (between each 12-month sequence) adds a space in the chart, as shown in Figure 7-16.

Creating stacked time comparisons

The stacked time comparison places two series on top of each other instead of side by side. Although this removes the benefit of having an unbroken overall trending, it replaces it with the benefit of an at-a-glance comparison within a compact space. Figure 7-17 illustrates a common stacked time comparison.

To create a stacked time comparison, follow these steps:

1. Create a new structure and add data to it, like the one shown in Figure 7-18.
2. Highlight the entire structure and create a column chart.

3. Select and right-click any of the bars for the 2010 data series, and then choose Change Series Chart Type from the menu that appears.

   The Change Chart Type dialog box appears.

4. In the Change Chart Type dialog box, select the line type in the Line section.

This technique works well with two time series. You generally want to avoid stacking any more than that. Stacking more than two series often muddies the view and causes users to continually reference the legend to keep track of the series they’re evaluating.

Trending with a secondary axis

In some trending components, you have series that trend two different units of measure. For instance, the table in Figure 7-19 shows a trend for People Count and a trend for % Labor Cost.

These are two different units of measure that, when charted, produce the unimpressive chart you see in Figure 7-20. Because Excel builds the vertical axis to accommodate the largest number, the percentage of labor cost trending gets lost at the bottom of the chart. Even a logarithmic scale doesn’t help in this scenario.

Because the default vertical axis (or primary axis) doesn’t work for both series, the solution is to create another axis to accommodate the series that doesn’t fit into the primary axis. This other axis is the secondary axis.

To place a data series on the secondary axis, follow these steps:

1. Right-click the data series and select Format Data Series from the menu that appears.

   Doing so opens the Format Data Series dialog box.

2. In the Format Data Series dialog box, expand the Series Options section (see Figure 7-21) and then click the Secondary Axis radio button.

   Figure 7-22 illustrates the newly added axis to the right of the chart. Any data series on the secondary axis has its vertical axis labels shown on the right.

Again, changing the chart type of any one of the data series can help in comparing the two trends. In Figure 7-23, the chart type for the People Count trend has been changed to a column. Now you can easily see that although the number of people has gone down in November and December, the percentage of labor cost continues to rise.

Technically, it doesn’t matter which data series you place on the secondary axis. A general rule is to place the problematic data series on the secondary axis. In this scenario, because the data series for percentage of labor cost seems to be the problem, I place that series on the secondary axis.

Formatting specific periods

Imagine that you’ve just created the chart component illustrated in Figure 7-24 and you want to explain the spike in October. You could, of course, use a footnote somewhere, but that would force your audience to look for an explanation elsewhere on your dashboard. Calling attention to an anomaly directly on the chart helps give your audience context without the need to look away from the chart.

A simple solution is to format the data point for October to appear in a different color and then add a simple text box that explains the spike.

To format a single data point:

1. Click the data point once.

   This step places dots on all data points in the series.

2. Click the data point again to ensure that Excel knows you’re formatting only that single data point.

   The dots disappear from all but the target data point.

3. Right-click and select Format Data Point from the menu that appears.

   This step opens the Format Data Point dialog box, shown in Figure 7-25. The idea is to adjust the formatting properties of the data point as you see fit.

   The Format Data Point dialog box is for a column chart. Different chart types have different options in the Format Data Point dialog box. Nevertheless, the idea remains the same in that you can adjust the properties in the Format Data Point dialog box to change the formatting of a single data point.

   After you change the fill color of the October data point and add a text box with some context, the chart nicely explains the spike, as shown in Figure 7-26.

To add a text box to a chart, click the Insert tab on the Ribbon and select the Text Box icon. Then click inside the chart to create an empty text box, which you can fill with your words.

Using dividers to mark significant events

Every now and then a particular event shifts the entire paradigm of your data permanently. A good example is a price increase. The trend shown in Figure 7-27 has been permanently affected by a price increase implemented in October. As you can see, a dividing line (along with some labeling) provides a distinct marker for the price increase, effectively separating the old trend from the new.

Although there are lots of fancy ways to create this effect, you’ll rarely need to get any fancier than manually drawing a line yourself. To draw a dividing line inside a chart, take the following steps:

1. Click the chart to select it.
2. Click the Insert tab on the Ribbon and click the Shapes button.
3. Select the line shape you want, go to your chart, and draw the line where you want it.
4. Right-click your newly drawn line and select Format Shape from the menu that appears.
5. Use the Format Shape dialog box to format your line’s color, thickness, and style.

Representing forecasts in your trending components

It’s common to be asked to show both actual data and forecast data as a single trending component. When you do show the two together, you should ensure that your audience can clearly distinguish where actual data ends and where forecasting begins. Take a look at Figure 7-28.

The best way to achieve this effect is to start with a data structure similar to the one shown in Figure 7-29. As you can see, sales and forecasts are in separate columns so that when charted, you get two distinct data series. Also note the value in cell B14 is actually a formula referencing C14. This value serves to ensure a continuous trend line (with no gaps) when the two data series are charted together.

When you have the appropriately structured dataset, you can create a line chart. At this point, you can apply special formatting to the 2013 Forecast data series. Follow these steps:

1. Click the data series that represents 2013 Forecast.

   This step places dots on all data points in the series.

2. Right-click and select Format Data Series from the menu that appears.

   This step opens the Format Data Series dialog box.

3. In this dialog box, you can adjust the properties to format the series color, thickness, and style.

Avoiding overload with directional trending

Do you work with a manager who is crazy for data? Are you getting headaches from trying to squeeze three years’ worth of monthly data into a single chart? Although it’s understandable to want to see a 3-year trend, placing too much information on a single chart can make for a convoluted trending component that tells you almost nothing.

When you’re faced with the need to display impossible amounts of data, step back and think about the true purpose of the analysis. When your manager asks for a “3-year sales trend by month,” what is he looking for? It could be that he’s asking whether current monthly sales are declining versus history. Do you really need to show each and every month, or can you show the directional trend?

A directional trend is one that uses simple analysis to imply a relative direction of performance. The key attribute of a directional trend is that the data used is often a set of calculated values as opposed to actual data values. For instance, rather than chart each month’s sales for a single year, you could chart the average sales for Q1, Q2, Q3, and Q4. With such a chart, you’d get a directional idea of monthly sales, without the need to look into detailed data.

Take a look at Figure 7-30, which shows two charts. The bottom chart trends each year’s monthly data in a single chart. You can see how difficult it is to discern much from this chart. It looks like monthly sales are dropping in all three years. The top chart shows the same data in a directional trend, showing average sales for key periods. The trend jumps at you, showing that sales have flattened out after healthy growth in 2011 and 2012.

Smoothing data

Certain lines of business lend themselves to wide fluctuations in data from month to month. For instance, a consulting practice may go months without a steady revenue stream before a big contract comes along and spikes the sales figures for a few months. Some call these ups and downs seasonality, or business cycles.

Whatever you call them, wild fluctuations in data can prevent you from effectively analyzing and presenting trends. Figure 7-31 demonstrates how highly volatile data can conceal underlying trends.

This is where the concept of smoothing comes in. Smoothing does just what it sounds like — it forces the range between the highest and lowest values in a dataset to smooth to a predictable range without disturbing the proportions of the dataset.

Now, you can use lots of different techniques to smooth a dataset. Take a moment to walk through two of the easier ways to apply smoothing.

Smoothing with Excel’s moving average functionality

Excel has a built-in smoothing mechanism, in the form of a moving average trend line — that is, a trend line that calculates and plots the moving average at each data point. A moving average is a statistical operation used to track daily, weekly, or monthly patterns. A typical moving average starts calculating the average of a fixed number of data points, and then with each new day’s (or week’s or month’s) numbers, the oldest number is dropped and the newest number is included in the average. This calculation is repeated over the entire dataset, creating a trend that represents the average at specific points in time.

Figure 7-32 illustrates how Excel’s moving average trend line can help smooth volatile data, highlighting a predictable range.

In this example, a 4-month moving average has been applied.

To add a moving average trend line, follow these steps:

1. Right-click the data series that represents the volatile data and then select Add Trendline from the menu that appears.

   The Format Trendline dialog box appears, shown in Figure 7-33.

2. In the Format Trendline dialog box, select Moving Average and then specify the number of periods.

   In this case, Excel will average a 4-month moving trend line.

Creating your own smoothing calculation

As an alternative to Excel’s built-in trend lines, you can create your own smoothing calculation and simply include it as a data series in your chart. In Figure 7-34, a calculated column (appropriately named Smoothing) provides the data points needed to create a smoothed data series.

In this example, the second row of the Smoothing column contains a simple average formula that averages the first data point and the second data point. Note that the reference to the first data point (cell D2) is locked as an absolute value with dollar ($) signs. This ensures that when this formula is copied down, the range grows to include all previous data points.

After the formula is copied down to fill the entire smoothing column, it can simply be included in the data source for the chart. Figure 7-35 illustrates the smoothed data plotted as a line chart.