|
I was recently asked how to make a chart to represent a set of numbers with widely varying values. The values ranged from percentages, some negative and some positive, to numbers in the thousands to tens of millions. The table below shows a sample of such data.
Table 1. Sample data for this exercise.
This article demonstrates a number of techniques for effective charting in Microsoft Excel. These techniques include laying out chart data, selecting and changing chart data, formatting charts, changing the chart type of a data series, and using secondary axes.
Planning the Chart
Charts are one of the most versatile and effective tools you can use to share information. One of the principles of effective data presentation, is to keep it simple. To present information effectively, a chart should clearly show data without distortion, without clutter, and without distracting visual effects.
This table contains information of vastly different value and "type" (i.e., values vs. percent change). You should try to avoid putting a set of data like this into a single chart. Instead, it's better to break up the information into several charts, which are easier to comprehend. Figuring out a complex chart takes a long time and a lot of mental energy.
This data would be difficult to show all at once in a single chart. The data ranges from percentages up to tens of millions. A quick look at your data shows that the data is grouped into three pairs of related columns. Each category (Spending, Segments, and Projects) has a value and a corresponding percent change of the value, year over year.
An effective way to display this information is to show three charts with one chart for each category. Each chart uses a series of columns for the values, with the primary Y axis showing these values. The percent change is plotted using a line series, with percentages on the secondary Y axis.
Selecting Data to Chart
When you create a chart, the first step is to select the data. If the data is laid out properly, you can construct the chart easily. Figure 1 shows an appropriate selection, A2:C7.
Figure 1. Selection of a data range for a chart.
This selection would result in a chart that uses the data in the first three columns. The cells in the first row of the selection (Row 2) contain the series names, Amount, and % Change, while those in the first column of the selection (Column A) contain the category labels, which are the years 2000 through 2004.
Notice that cell A2 is blank: When Excel finds a blank cell in the top left corner of the selected chart data range, it automatically uses the row and column containing this blank for the series names and category labels. It takes a lot of willpower to resist the urge to put a label in every column, but not doing so makes charting much easier. If you need a label in the final worksheet, temporarily delete it, create your chart, then restore the label.
If you don't want to use an entire contiguous range, you can select a range with multiple areas, for example A2:A7 and D2:E7 as shown in Figure 2. Select the first area, A2:A7, then press the Ctrl key while selecting the second area, D2:E7.
Figure 2. Selection of a discontiguous data range.
You can select as many areas as you like, as long as they are similar and line up. A range like A2:A3,A5:A7,D2:E3,D5:E7 is okay (see Figure 3), because the separate areas line up. The range is shaped like a rectangle containing A2:E7, with columns B and C and row 4 unselected.
Figure 3. Another appropriate discontiguous data range.
If you select A3:A6, then D2:E7 (the discontiguous range shown in Figure 4), Excel would still create a chart, but it would not come out the way you expect. The two areas begin on different rows and contain a different number of rows.
Figure 4. An improper discontiguous range.
Making Combination Charts on Two Axes
Now you can try making a combination chart by following these steps.
1. Select the data.
Select the range A2:C7, and run the Chart Wizard. This button is on the Standard toolbar and looks like a miniature column chart. You can also launch the Wizard by choosing Insert|Chart.
2. Select a chart type.
In step one of the Chart Wizard, you select a chart type. A tab shows standard types and another shows custom types. Some custom types are built-in and you also can add user-defined custom types. You may even have noticed a built-in custom type called Line - Column on Two Axes. Even though that chart type sounds like what you might need, it is not as flexible as making your own.
For example, if two series are added to an existing two-series chart of this type, Excel would assign the first two to the primary axis columns and the last two to secondary axis lines, which changes the original designation of the second series. The bottom line is that the key to successful combination charts is to design them yourself from scratch.
To make a combination column-line chart, select a standard clustered column chart in the first step of the Chart Wizard. Press the Finish button to skip through the rest of the Wizard, to generate the chart shown in Figure 5. This article concentrates on charts embedded in the worksheet rather than charts on their own sheets. Embedded charts offer more control over size, and permit the chart and its data range to be visible on the same sheet.
Figure 5. A simple clustered column chart.
3. Change the chart type of one (or more) series.
The chart in Figure 5 contains two series, as shown by the legend, but the percentages are so small compared to the values that the second series is invisible. You can still select the second series, though. Click carefully on the X axis where one of the flat columns would be, or simply select the first series and press the up arrow key once. Now choose Chart|Chart Type and select a line chart style. The result is shown in Figure 6. Note: if the entire chart is selected, the Chart Type command changes the style of the entire chart.
Figure 6. A combination column-line chart.
4. Change the axis of one series.
The combination chart is nearly finished; all that's missing is the secondary axis. There must be at least two series in a chart: one for each axis group. To create the percentage axis on the right side of the chart, select the new line series. Choose Format|Selected Data Series or press Ctrl+1, to open the Format dialog box. On the Axis tab, select Secondary, to produce the desired style chart shown in Figure 7.
Figure 7. Combination column-line chart on two axes.
Formatting a Chart
The last chart shows the data the way you want, but you can format it judiciously to make it easier to read. In Excel, you can format an object in several ways. First you select the object, then you can open the dialog box one of these ways:
- Choosing Format|Selected <Item> (where "Item" is the name of the item you have selected);
- Pressing Ctrl+1 (the numeral one); or
- Right clicking the object, and choosing Format <Item> or Selected Object from the pop up menu.
Note the last option. The right mouse button is a very useful tool. Whenever you move the cursor over an object, clicking the right mouse button pops up a context menu with a list of options for the object that is under the cursor.
You can make other changes using the Chart Options dialog, which is the same as step 3 of the Chart Wizard. You can open this dialog by:
- Selecting the chart, and choosing Chart|Chart Options; or
- Right clicking the Chart, and selecting Chart Options from the pop up menu.
When formatting charts, a good rule of thumb is to keep it simple. Reduce the number of features and lines to only what is necessary to convey the information. Use simple color schemes and limit your charts to only a few colors. Also consider using shades of the same color rather than contrasting colors. Lighter shades are better for filled areas and darker shades are better for lines and markers. Avoid introducing false extra dimensions of data, which include color gradients and 2D charts with 3D formatting.
In Figure 8, the chart's outside border has been removed because it detracts from the chart. The dull gray plot area background has also been changed to white, the plot area border to black, and the gridlines to light gray. Axis titles and a chart title describe the chart. The legend has been moved so it now sits between the plot area and the title. The border has been removed from the legend and it has been stretched it so the two legend entries are side by side. The plot area was expanded to fill the entire chart area, which minimizes the white space Excel usually leaves around the chart.
Your chart formatting may go through some evolution. An earlier version of this chart had a red line series, but the red lines vibrated against the blue columns. Besides, red is usually associated with a limit of some sort, or with bad news. Red would be appropriate to display increasing business losses. It's good to ponder these types of color associations as you format your chart.
Figure 8. Format the chart for improved readability.
The final adjustment is to change the Y axis scale to use millions. You make this change with a custom number format. Double-click the axis, and on the Number tab, select Custom from the list on the left. Now enter 0.0,, in the Type box, and press Enter. For each comma at the end of the number format, Excel leaves off three digits. Two commas change 1,000,000 to 1,000 to 1. The overall effect is to maximize the portion of the chart that contains the data you want to show.
Figure 9. The finished chart.
Making Two More Charts
To make two or more charts to show the rest of the data you need to select the new data range, and rerun the Chart Wizard. Repeat all of the steps described above. In general, you probably wouldn't want to build a new chart completely from scratch because you have to repeat all the formatting operations. It's much easier to just copy a chart and change the data range of the new chart.
Copying a Chart
You can copy a chart in several ways. If it is a chart on its own chart sheet, you can choose Move or Copy Sheet from the Edit menu, right click on the sheet tab and select Move or Copy, or simply hold down the Ctrl key and drag the tab to the location of the copy.
You can select an embedded chart and use Copy and Paste from the Edit menu, you can use the Ctrl-C and Ctrl-V keyboard equivalents, or you can hold down the Ctrl key while dragging the chart with the mouse to a new location. If you also hold the Alt key while moving or resizing a chart, the edges of the chart line up with the cell boundaries.
Make sure you select a cell for the location of the pasted chart. Pasting with a chart selected copies each series from the copied chart onto the selected chart.
Changing a Chart's Data Range
You can change a chart's data range in several ways.
Use the Source Data dialog.
The Source Data dialog is the same as step 2 of the Chart Wizard. Select the chart and choose Source Data from the Chart menu, or right-click on the chart, and choose Source Data from the pop up menu. There are two tabs available to you:
- Data Range - This part of the dialog allows you to select a data range for the entire chart, as if selecting a range prior to running the Chart Wizard. Select the contents of the Data Range box and use the mouse to select the desired range, or edit the address in the box. You can select multiple area ranges as described above. You can also change whether the chart uses rows or columns for each series.
- Series - This tab allows you to add or delete series, and to change the cell ranges used for the name of a series and its X and Y values. Select a series from the list and click in the box of the range you want to change, then select a new range with the mouse or edit the address in the box.
Edit the SERIES formula.
Select the series that you want to change. Figure 10 shows a chart with a series selected, and it shows the Excel Formula bar. The Name box in the top left tells you the name of the selected series, "Amount." The formula displayed in the Formula bar is:
=SERIES(Sheet1!$B$2,Sheet1!$A$3:$A$7,Sheet1!$B$3:$B$7,1)
Figure 10. The SERIES formula for a selected chart series.
Within the parentheses of the SERIES formula are four arguments:
=SERIES(<Series Name>,<X Values>,<Y Values>,<Plot Order>)
You can edit any of the characters in the formula, for example, changing B to D to change the reference to the Y values range. You can also select an entire argument, including the sheet reference, then select a new range with the mouse. Press Enter to accept the change, or Esc to cancel. If you don't like the change you made, press Ctrl-Z, or choose Edit|Undo.
Move or resize the highlighted data ranges.
Moving or resizing the highlighted data ranges is the easiest way to change an embedded chart's source data range. Unfortunately, it doesn't work with chart sheets because you can't see the chart and the data at the same time. Select the Plot Area of the chart and look at the source data (Figure 11). The Names of the series are in the range outlined in green, the Category labels are in the range outlined in purple, and the Y data range is outlined in blue. Compare these highlighted ranges with the range you initially selected to create the chart (Figure 1).
Figure 11. Chart data range highlighted in the worksheet.
You can drag a highlight rectangle by its border to move it to another range. You can drag one of the small square handles on the corners of the highlight rectangles to change the size of the chart's source data range. When you move or resize one highlight rectangle, the other two adjust as necessary to maintain a uniform range.
Click on a series, and see how the highlighting changes. Now the Name, Categories, and Y values of just the selected series are highlighted (see Figure 12). Moving or resizing the highlight rectangles now adjusts the data range of the selected series only. These three ranges move independently, and allow you to select misaligned ranges to conform to an irregular data layout.
Figure 12. Series data range highlighted in the worksheet.
In Figure 13, the highlight rectangles have been moved to change the data which is plotted in the chart. The chart title and the Y axis title have also been changed.
Figure 13. Moving the data range highlights changes the chart source data.
The primary and secondary Y axis scales have been changed, too. This process was more complicated than just letting Excel pick new minimum and maximum values. The first chart was effective because the secondary axis scale matched up with the primary axis scale, and the gridlines matched up (gridlines can only be associated with the primary axis). A $0.5M change on the primary axis lined up perfectly with a 10% change on the secondary axis. In the second chart, you not only need to line up gridlines, but you also must line up the zeros on each scale.
Fortunately, the numbers in this chart worked in your favor. The primary axis ranges from 0 to 40 million, and the secondary from -20 to +40%. The simple thing to do is also the right thing to do: scale both axes from -20 to +40, million on the left and percent on the right. One reason for using light gray gridlines in your first chart was to make them less distinct than the X axis. Even if you don't use gridlines in your chart, it is very important to line up the X axis with the zero values of both Y axis scales.
The Category labels were moved to the bottom of the chart so they didn't interfere with any of the negative data. Double click the Category axis, select the Patterns tab, and select Low for Tick Mark Labels. Figures 14 and 15 show the second and third charts made from the data in Table 1, following the patterns developed for the first chart (Figure 9).
Figure 14. The second finished chart.
Figure 15. The third finished chart.
|
Department
