Creating Excel 2007 Charts That Show Trends

This chapter is from the book

Charts and Graphs for Microsoft Office Excel 2007

Learn More Buy

This chapter is from the book

This chapter is from the book 

Charts and Graphs for Microsoft Office Excel 2007

Learn More Buy

Using a Chart to Communicate Effectively

A long time ago, in a past job, 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 would be turned sideways, and they would build a landscape chart that was an awesome communication tool simply by using a pencil. After drawing the charts by hand, they would send off the charts to someone in the home office who would generate the charts on a computer. This was a great technique. Long before touching Excel, someone would sit down to figure out what the message should be.

You should do the same thing: 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. Rather than use 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 to highlight a portion of a column chart. In these examples, you will often be spending 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 are the readers of your chart. Rather than hoping that the reader will discover the message you are trying to make, why not add the message as the title of the chart?

Figure 3.25 shows a default chart in Excel. Both the legend and the title use the "Market Share" heading from cell B71. You certainly don't need those words on the chart twice.

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

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

1. From the Layout ribbon, choose Legend, None and then choose 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 2002.
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. The border becomes solid. You can now use the formatting icons on the Home ribbon to format the title.
5. On the Home ribbon, choose left-justified 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.26, provides a message to assist the reader of the chart.

Figure 3.26 You can tell the reader the point of the chart with the title.

Resizing a Chart Title

When you click a chart title to select it, a bounding box with four resizing handles appears. At least they look like resizing handles; actually, they are not. You do not have explicit control to resize the title. It feels like you should be able to stretch the title horizontally or vertically, as if it was a text box, but you cannot. The only real control you have to make a text box taller is by inserting carriage returns in the title, but you can only type carriage returns when you are in Text Edit mode.

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 ribbon to format the title. You 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, you could carefully right-click the bounding box and choose Edit Text. However, simply left-clicking a second time inside the bounding box also puts the title in Text Edit mode, as indicated by a dashed line in the bounding box. In 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 formatting commands available there. You can edit specific characters within the title in order to create a larger title and a smaller subtitle, as shown in Figure 3.27.

Figure 3.27 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, you right-click the title and choose 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.28 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.

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

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

1. Delete the original title by choosing Chart Title, None from the Layout ribbon. Excel resizes the plot area to fill the space that the title formerly occupied.
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 in order to make room for the title.
3. On the Insert ribbon, 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 don't 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 ribbon 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 ribbon 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 ribbon. When you use the WordArt menus on a text box, however, all the Transform commands are available (see Figure 3.29).

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

Because a text box is resizable and that you can use WordArt Transform commands, a text box works perfectly: You can move or resize the chart, and 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. While the tools on the Design ribbon don't allow this, you can easily achieve the effect quickly by using the Format ribbon.

To create the chart in Figure 3.30, you follow these steps:

1. Create a column chart by choosing Column, Clustered Column from the Insert ribbon.
2. Click any of the columns to select the entire series.
3. On the Format ribbon, choose Shape Fill, White. At this point, the columns are invisible. (Invisible bars are great for creating waterfall charts, discussed in Chapter 4, "Creating Charts That Show Differences.") However, in this case, you want to outline the bars.
4. From the Format ribbon, choose Shape Outline, Black. Choose 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 you accidentally click outside the series, you might inadvertently deselect the series.) If all the columns have handles, click Friday again.
6. From the Format ribbon, choose Shape Fill, Black.
7. On the Layout ribbon, turn off the legend and the gridlines.
8. Type a title, as shown in Figure 3.30, pressing Enter after the first line of the title. On the Home ribbon, change the title font size to 14 point, left-aligned.
9. Right-click the numbers along the vertical axis and choose Format Axis. Change Major Unit to Fixed, 500.

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

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

Replacing Columns with Arrows

You can use columns shaped like arrows 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 in order to further indicate the positive growth.

You 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 ribbon, choose Shapes, 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, choose Format Selection from the Format ribbon. Reduce the gap setting from 150% to 75% to make the arrows wider.

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

Figure 3.31 Arrows further emphasize the upward growth of sales.

Highlighting a Section of Chart by Adding a Second Series

The chart in Figure 3.32 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.32 It would be best to highlight the road construction months in the chart to further emphasize the title.

The title calls out the July and August time period, but it would be cool to actually highlight that section of the chart. You 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 June, July, and August.
2. Click 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 ribbon, use the Current Selection drop-down to choose Series 2. This is the series you just added.
4. While Series 2 is selected, choose 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 ribbon, use the Current Selection drop-down to reselect Series 2. Then choose Shape Fill, White, Background 1, Darker 25%.

The top chart in Figure 3.33 shows the gray highlight extending from the horizontal axis up to the data line for the two line segments.

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

Alternatively, you could replace the numbers in column C with 70,000 in order to draw a gray rectangle behind the months, as shown in the bottom chart in Figure 3.33.

Changing Line Type Midstream

Consider the top chart in Figure 3.34. 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.34 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 could change the line type at that point to indicate that the last two data points are forecasts. To do so, you 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 2007, move the numbers for Q3 and Q4 of 2007 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 ribbon, choose Legend, Legend at Right.
8. Click the red line. In the Format ribbon, 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, choose Design, Change Chart Type. Choose a chart type that does not have markers.
11. Because the chart title indicates that a new management team arrived, but it does not indicate when the team arrived, change the title to indicate that the team arrived in Q3 of 2006.
12. On the Insert ribbon, select Shapes, Line. Draw a vertical line between Q2 and Q3 of 2006, holding down the Shift key while drawing to keep the line vertical.
13. While the line is selected, on the Format ribbon, choose 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.34.