Creating Microsoft Excel Charts That Show Trends

This chapter is from the book

Charts and Graphs: Microsoft Excel 2010

Learn More Buy

This chapter is from the book

This chapter is from the book 

Charts and Graphs: Microsoft Excel 2010

Learn More Buy

Communicate Effectively with Charts

A long time ago, a McKinsey & Company team investigated opportunities for growth at the company where I was employed. I was chosen to be part of the team because I knew how to get the data out of the mainframe.

The consultants at McKinsey & Company knew how to make great charts. Every sheet of grid paper was turned sideways, and a pencil was used to create a landscape chart that was an awesome communication tool. After drawing the charts by hand, they sent off the charts to someone in the home office who generated the charts on a computer. This was a great technique. Long before touching Excel, someone figured out what the message should be.

You should do the same thing today. Even if you have data in Excel, before you start to create a chart, it's a good idea to analyze the data to see what message you are trying to present.

The McKinsey & Company group used a couple of simple techniques to always get the point across:

• To help the reader interpret a chart, include the message in the title. Instead of using an Excel-generated title such as "Sales," you can actually use a two- or three-line title such as "Sales have grown every quarter except for Q3, when a strike impacted production."
• If the chart is talking about one particular data point, draw that column in a contrasting color. For example, all the columns might be white, but the Q3 bar could be black. This draws the reader's eye to the bar that you are trying to emphasize. If you are presenting data on screen, use red for negative periods and blue or green for positive periods.

The following sections present some Excel trickery that allows you to highlight a certain section of a line chart or a portion of a column chart. In these examples, you will spend some time up front in Excel adding formulas to get your data series looking correct before creating the chart.

Using a Long, Meaningful Title to Explain Your Point

If you are a data analyst, you are probably more adept at making sense of numbers and trends than the readers of your chart. Rather than hoping the reader discovers your message, why not add the message to the title of the chart?

Figure 3.21 shows a default chart in Excel. Both the legend and title use the "Market Share" heading from cell B71. These words certainly do not need to be used twice on the chart.

Figure 3.21 By default, Excel uses an unimaginative title taken from the heading of the data series.

Follow these steps to remove the legend, add data labels, and add a meaningful title:

1. From the Layout tab, select Legend, None, and then select Data Labels, Outside End.
2. Click the title in the chart. Click again to put the title in Edit mode.
3. Backspace to remove the current title. Type Market share has improved, press Enter, and type 13 points since 2007.
4. To format text while in Edit mode, you would have to select all the characters with the mouse. Instead, click the dotted border around the title. When the border becomes solid, you can use the formatting icons on the Home tab to format the title. Alternatively, right-click the title box and use the Mini toolbar to format the title.
5. On the Home tab, select the icon for Align Text Left, and then click the Decrease Font Size button until the title looks right.
6. Click the border of the chart title and drag it so the title is in the upper-left corner of the chart.

The result, shown in Figure 3.22, provides a message to assist the reader of the chart.

Figure 3.22 Use the title to tell the reader the point of the chart.

Resizing a Chart Title

The first click on a title selects the title object. A solid bounding box appears around the title. At this point, you can use most of the formatting commands on the Home tab to format the title. Click the Increase/Decrease Font Size buttons to change the font of all of the characters. Excel automatically resizes the bounding box around the title. If you do not explicitly have carriage returns in the title where you want the lines to be broken, you are likely to experience frustration at this point.

When you have the solid bounding box around the title, carefully right-click the bounding box and select Edit Text. Alternatively, you can left-click a second time inside the bounding box to also put the title in Text Edit mode. Note that the dashed line in the bounding box indicates the title is in Text Edit mode. Using Text Edit mode, you can select specific characters in the title and then move the mouse pointer up and to the right to access the mini toolbar and the available formatting commands. You can edit specific characters within the title to create a larger title and a smaller subtitle, as shown in Figure 3.23.

Figure 3.23 By selecting characters in Text Edit mode, you can create a title/subtitle effect.

You cannot move the title when you are in Text Edit mode. To exit Text Edit mode, right-click the title and select Exit Edit Text or simply left-click the bounding box around the title. When the bounding box is solid, you can click anywhere on the border except the resizing handles and drag to reposition the title.

Deleting the Title and Using a Text Box

If you are frustrated that the title cannot be resized, you can delete the title and use a text box for the title instead. The title in Figure 3.24 is actually a text box. Note the eight resizing handles on the text box instead of the four resizing handles that appear around a title. Thanks to all these resizing handles, you can actually stretch the bounding box horizontally or vertically.

To create the text box shown in Figure 3.24, follow these steps:

1. From the Layout tab, delete the original title by choosing Chart Title, None. Excel resizes the plot area to fill the space that the title formerly occupied.
   

   Figure 3.24 Instead of a title, this chart uses a text box for additional flexibility.

2. Select the plot area by clicking some whitespace inside the plot area. Eight resizing handles now surround the plot area. Drag the top resizing handle down to make room for the title.
3. On the Insert tab, click the Text Box icon.
4. Click and drag inside the chart area to create a text box.
5. Click inside the text box and type a title. Press the Enter key to begin a new line. If you do not press the Enter key, Excel word-wraps and begins a new line when text reaches the right end of the text box.
6. Select the characters in the text box that make up the main title and use either the mini toolbar or the tools on the Home tab to make the title 18 point, bold, and Times New Roman.
7. Select the remaining text that makes up the subtitle in the text box and use the tools on the Home tab to make the subtitle be 12 point, italics, Times New Roman.

Microsoft advertises that all text can easily be made into WordArt. However, when you use the WordArt drop-downs in a title, you are not allowed to use the Transform commands found under Text Effects on the Drawing Tools Format tab. When you use the WordArt menus on a text box, however, all the Transform commands are available (see Figure 3.25).

Figure 3.25 Using a text box instead of a title allows more formatting options.

A text box works perfectly because it is resizable and you can use WordArt Transform commands. If you move or resize the chart, the text box moves with the chart and resizes appropriately.

Highlighting One Column

If your chart title is calling out information about a specific data point, you can highlight that point to help focus the reader's attention on it as shown in Figure 3.26. Although the tools on the Design tab do not allow this, you can achieve the effect quickly by using the Format tab.

Figure 3.26 The column for Friday is highlighted in a contrasting color and it is also identified in the title.

To create the chart in Figure 3.26, follow these steps:

1. Create a column chart by selecting Column, Clustered Column from the Insert tab.
2. Click any of the columns to select the entire series.
3. On the Format tab, select Shape Fill, White. At this point, the columns are invisible. Invisible bars are great for creating waterfall charts, which is discussed in Chapter 4, "Creating Charts That Show Differences." However, in this case, you want to outline the bars.
4. From the Format tab, select Shape Outline, Black. Select Shape Outline, Weight, 1 point. All your columns are now white with black outline.
5. Click the Friday column in the chart. The first click on the series selects the whole series. A second click selects just one data point. If all the columns have handles, click Friday again.

   TIP

   If you accidentally click outside the series, you might inadvertently deselect the series. Click back on the series to re-select it.

6. From the Format tab, select Shape Fill, Black.
7. On the Layout tab, turn off the legend and the gridlines.
8. Type a title, as shown in Figure 3.26, pressing Enter after the first line of the title. On the Home tab, change the title font size to 14 point, left aligned.
9. Right-click the numbers along the vertical axis and select Format Axis. Change Major Unit to Fixed, 500.

The result is a chart that calls attention to Friday sales.

Replacing Columns with Arrows

Columns shaped like arrows can be used to make a special point. For example, if you have good news to report about consistent growth, you might want to replace the columns in the chart with arrow shapes to further indicate the positive growth.

Follow these steps to convert columns to arrows:

1. Create a column chart showing a single series.
2. In an empty section of the worksheet, insert a new block arrow shape. From the Insert tab, select Shapes, Blck Arrows, Up Arrow. Click and drag in the worksheet to draw the arrow.
3. Select the arrow. Press Ctrl+C to copy the arrow to the Clipboard.
4. Select the chart. Click a column to select all the columns in the data series.
5. Press Ctrl+V to paste the arrow. Excel fills the columns with a picture of the block arrow.
6. If desired, select Format Selection from the Format tab. Reduce the gap setting from 150 percent to 75 percent to make the arrows wider.

The new chart is shown in the bottom half of Figure 3.27. After creating the chart, you can delete the arrow created in step 2 by clicking the arrow and pressing the Delete key.

Figure 3.27 Arrows can be used to emphasize the upward growth of sales.

Highlighting a Section of Chart by Adding a Second Series

The chart in Figure 3.28 shows a sales trend over one year. The business was affected by road construction that diverted traffic flow from the main road in front of the business.

Figure 3.28 Highlight the road construction months in the chart to emphasize the title further.

The title calls out the July and August time period, but it would be helpful to actually highlight that section of the chart. Follow these steps to add an area chart series to the chart:

1. Begin a new series in Column C, next to the original data. To highlight July and August, add numbers to Column C for the July and August points, plus the previous point, June. In cell C7, enter the formula of =B7. Copy this formula to July and August.
2. Click on a blank area inside of the chart. A blue bounding box appears around B2:B13 in the worksheet. Drag the lower-right corner of the blue bounding box to the right to extend the series to include the three values in Column C. Initially, this line shows up as a red line on top of a portion of the existing blue line.
3. On the Layout tab, use the Current Selection drop-down to select Series 2, which is the series you just added.
4. While Series 2 is selected, select Design, Change Chart Type. Select the first area chart thumbnail. Click OK. Excel draws a red area chart beneath the line segment of June through August.
5. On the Format tab, use the Current Selection drop-down to reselect Series 2. Open the Shape Fill drop down. Choose a grey fill color. The 4th row, 1st column offers a tooltip f White, Background 1, Darker 25% and is suitable.

The top chart in Figure 3.29 shows the gray highlight extending from the horizontal axis up to the data line for the two line segments. Alternatively, you can replace the numbers in Column C with 70,000 to draw a gray rectangle behind the months, as shown in the bottom chart in Figure 3.29.

Figure 3.29 A second series with only three points is used to highlight a section of the chart.

Changing Line Type Midstream

Consider the top chart in Figure 3.30. The title indicates that cash balances improved after a new management team arrived. This chart initially seems to indicate an impressive turnaround. However, if you study the chart axis carefully, you see that the final Q3 and Q4 numbers are labeled Q3F and Q4F to indicate that they are forecast numbers.

Figure 3.30 It is not clear in the top chart that the last two points are forecasts.

It is misleading to represent forecast numbers as part of the actual results line. It would be ideal if you change the line type at that point to indicate that the last two data points are forecasts. To do so, follow these steps:

1. Change the heading above Column B from Cash Balances to Actual.
2. Add the new heading Forecast in Column C.
3. Because the last actual data point is for Q2 of 2011, move the numbers for Q3 and Q4 of 2011 from Column B to Column C.
4. To force Excel to connect the actual and the forecast line, copy the last actual data point (the 7 for Q2) over to the Forecast column. This one data point—the connecting point for the two lines—will be in both the Forecast and Actual columns.
5. Change the last two labels in Column A from Q3F to just Q3 and from Q4F to just Q4.
6. Click the existing chart. A bounding box appears around B2:B9. Grab the lower-right blue handle and drag outward to encompass B2:C9. A second series is added to the chart as a red line.
7. On the Layout tab, select Legend, Legend at Right.
8. Click the red line. In the Format tab, you should see that the Current Selection drop-down indicates Series "Forecast."
9. Select Format, Shape Outline, Dashes and then select the fourth dash option. The red line changes to a dashed line.
10. While the forecast series is selected, select Design, Change Chart Type. Select a chart type that does not have markers.
11. The chart title indicates that a new management team arrived, but it does not indicate when the team arrived. To fix this, change the title to indicate that the team arrived in Q3 of 2010.
12. On the Insert tab, select Shapes, Line. Draw a vertical line between Q2 and Q3 of 2010, holding down the Shift key while drawing to keep the line vertical.
13. While the line is selected, on the Format tab, select Shape Outline, Dashes and then select the fourth dash option to make the vertical line a dashed line. Note that this line is less prominent than the series line because the weight of the line is only 1.25 point.

The final chart is shown at the bottom of Figure 3.30.