In This Chapter
What is a chart?
How Excel handles charts
Embedded charts versus chart sheets
The parts of a chart
The basic steps for creating a chart
Working with charts
When most people think of a spreadsheet product such as Excel, they think of crunching rows and columns of numbers. But, as you probably know already, Excel is no slouch when it comes to presenting data visually, in the form of a chart. This chapter presents an introductory overview of Excel's charting ability, and contains enough information for a typical user to start creating and customizing charts.
I'll start with the basics. A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral Part of spreadsheets since the early days of Lotus 1-2-3. Charts generated by early spreadsheet products were extremely crude by today's standards. But, over the years, quality and flexibility have improved significantly. You'll find that Excel provides you with the tools to create a wide variety of highly customizable charts.
Note
In the first edition of this book, I offered an apology for the relatively poor visual quality of Excel charts. I'm pleased to be able to retract that apology for the Excel 2007 edition. The charting feature in Excel 2007 has come a long way, and Excel is now capable of producing charts with much better visual appeal.
Displaying data in a well-conceived chart can make your numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. Making a chart can often help you spot trends and patterns that might otherwise go unnoticed.
Figure 1-1 shows a worksheet that contains a simple column chart that depicts a company's sales volume by month. Viewing the chart makes it very apparent that sales were off in the summer months (June through August), but they increased steadily during the final four months of the year. You could, of course, arrive at this same conclusion simply by studying the numbers. But viewing the chart makes the point much more quickly.
A column chart is just one of many different types of charts that you can create with Excel. By the way, creating this chart was simple: I selected the data in A1:B13, and then I pressed Alt+F1.
Before you can create a chart, you must have some numbers — sometimes known as data. The data, of course, is stored in the cells in a worksheet. Normally, the data that is used by a chart resides in a single worksheet, but that's not a strict requirement. As you'll see, a chart can use data that's stored in any number of worksheets, and the worksheets can even be in different workbooks.
A chart is essentially an "object" that Excel creates upon request. This object consists of one or more data series, displayed graphically. The appearance of the data series depends on the selected chart type. For example, if you create a line chart that uses two data series, the chart contains two lines, and each line represents one data series. The data for each series is stored in a separate row or column. Each point on the line is determined by the value in a single cell, and is represented by a marker. You can distinguish the lines by their thickness, line style, color, or data markers.
Figure 1-2 shows a line chart that plots two data series across a 6-year period. The series are identified by using different data markers (squares versus circles), shown in the legend at the bottom of the chart. The lines also use different colors, which is not apparent in the grayscale figure.
A key point to keep in mind is that charts are dynamic. In other words, a chart series is linked to the data in your worksheet. If the data changes, the chart is updated automatically to reflect those changes.
After you've created a chart, you can always change its type, change the formatting, add new data series to it, or change an existing data series so that it uses data in a different range.
Charts can reside in either of two locations in a workbook:
In a worksheet (an embedded chart)
On a separate chart sheet
An embedded chart basically floats on top of a worksheet, on the worksheet's drawing layer. The charts shown previously in this chapter are both embedded charts.
As with other drawing objects (such as a text box or a shape), you can move an embedded chart, resize it, change its proportions, adjust its borders, and perform other operations. Using embedded charts enables you to view the chart next to the data that it uses. Or, you can place several embedded charts together so that they print on a single page.
When you create an Excel 2007 chart, it's always an embedded chart. The exception to this rule is when you select a range of data and press F11 to create a default chart. Such a chart is created on a chart sheet.
To make any changes to the actual chart in an embedded chart object, you must click it to activate the chart. When a chart is activated, Excel displays the three Chart Tools context tabs shown in Figure 1-3: Chart Tools
You can move an embedded chart to its own chart sheet, so you can view it by clicking a sheet tab. When you move a chart to a chart sheet, the chart occupies the entire sheet. If you plan to print a chart on a page by itself, using a chart sheet is often your better choice. If you have many charts to create, you may want to put each one on a separate chart sheet to avoid cluttering your worksheet. This technique also makes locating a particular chart easier because you can change the names of the chart sheets' tabs to provide a description of the chart that it contains.
Figure 1-4 shows a chart on a chart sheet. When a chart sheet is activated, Excel displays the Chart Tools context tabs, as described in the previous section.
Note
Previous versions of Excel have a Size with Window option for charts on a chart sheet. When this setting is enabled, the chart adjusts itself when you resize the workbook window (it always fits perfectly in the window). This feature is not available in Excel 2007.
A chart is made up of many different elements, and all of these elements are optional. Yes, you can create a chart that contains no chart elements — an empty chart. It's not very useful, but Excel allows it.
Refer to the chart in Figure 1-5 as you read the following description of the chart's elements.
This particular chart is a "combination" chart that displays two data series: Income and Profit Margin. Income is plotted as vertical columns, and the Profit Margin is plotted as a line with square markers. Each bar (or marker on the line) represents a single data point (the value in a cell).
The chart has a horizontal axis, known as the category axis. This axis represents the category for each data point (January, February, and so on). This axis doesn't have a label because the category units are obvious.
Notice that this chart has two vertical axes. These are known as value axes, and each one has a different scale. The axis on the left is for the column series (Income), and the axis on the right is for the line series (Profit Margin).
The value axes also display scale values. The axis on the left displays scale values from 0 to 250, 000, in major unit increments of 50, 000. The value axis on the right uses a different scale: 0 percent to 14 percent, in increments of 2 percent. For a value axis, you can control the minimum and maximum values, as well as the increment value.
A chart with two value axes is appropriate because the two data series vary dramatically in scale. If the Profit Margin data was plotted using the left axis, the line would not even be visible.
If a chart has more than one data series, you'll usually need a way to identify the data series or data points. A legend, for example, is often used to identify the various series in a chart. In this example, the legend appears at the bottom of the chart. Some charts also display data labels to identify specific data points. The example chart displays data labels for the Profit Margin series, but not for the Income series. In addition, most charts (including the example chart) contain a chart title and additional labels to identify the axes or categories.
The example chart also contains horizontal gridlines (which correspond to the values on the left axis). Gridlines are basically extensions of the value axis scale, which makes it easier for the viewer to determine the magnitude of the data points.
In addition, all charts have a chart area (the entire background area of the chart) and a plot area (the Part that shows the actual chart, including the plotted data, the axes, and the axis labels).
Charts can have additional parts or fewer parts, depending on the chart type. For example, a pie chart (see Figure 1-6) has "slices" and no axes. A 3-D chart may have walls and a floor (see Figure 1-7)
Several other types of items can be added to a chart. For example, you can add a trend line or display error bars.
Note
Refer to Chapters 4 and 5 for additional information about the elements available for various chart types.
The previous version of Excel featured a Chart Wizard that guided the user through the steps required in creating a chart. The Chart Wizard is not available in Excel 2007, but creating basic charts is still relatively simple. The following sections discuss how to create and customize a basic chart in Excel 2007.
Follow these steps to create a chart in Excel 2007:
Select the data to be used in the chart.
Make sure that you select the column headers, if the data has them. Figure 1-8 shows some data that's appropriate for a chart. Another option is to select a single cell within a range of data. Excel will then use the entire data range for the chart.
Click the Insert tab, and then click a Chart icon in the Charts group.
The icon expands into a gallery list that shows subtypes (see Figure 1-9).
Click the Chart subtype, and Excel creates the chart of the specified type.
Figure 1-10 shows a column chart created from the data.
Tip
To quickly create a default chart, select the data and press Alt+F1 to create an embedded chart, or press F11 to create a chart on a chart sheet. You can also use these keystrokes to duplicate a chart. If you select an embedded chart or chart sheet and press F11, a new default chart sheet is created using the selected chart's data. If you select an embedded chart and press Alt+F1, a new blank chart is created. Alt+F1 has no apparent effect on a chart sheet.
When Excel creates a chart, it uses a set of rules to determine whether the data is arranged in columns or in rows. Most of the time Excel guesses correctly, but if it creates the chart using the wrong orientation, you can quickly change it by choosing Chart Tools
The orientation of the data has a drastic effect on the look (and, perhaps, understandability) of your chart. Figure 1-11 shows the column chart after changing the orientation.
Notice that the chart now has three data series, one for each month. If the goal is to compare actual with projected for each month, this version of the chart is much more difficult to interpret because the relevant columns are not adjacent.
After you've created a chart, you can easily change the chart type. Although a column chart may work well for a particular data set, there's no harm in checking out some other chart types. You can choose Chart Tools
Note
If your chart uses more than one data series, make sure that a chart element other than a data series is selected when you choose the Chart Tools
In the Change Chart Type dialog box, the main categories are listed on the left, and the subtypes are shown as icons. Select an icon and click the OK button, and Excel displays the chart using the new chart type. If you don't like the result, click the Undo button.
Tip
If the chart is an embedded chart, you can also change a chart's type by using the icons in the Insert
Each chart type has a number of prebuilt layouts that you can apply with a single mouse click. A layout contains additional chart elements, such as a title, data labels, axes, and so on. This step is optional, but one of the prebuilt designs might be just what you're looking for. Even if the layout isn't exactly what you want, it may be close enough that you need to make only a few adjustments.
To apply a layout, select the chart and use the Chart Tools
The Chart Tools
Tip
The styles displayed in the gallery depend on the workbook's theme. When you choose Page Layout
In some cases, applying a chart layout (as described previously) gives you a chart with all the elements you need. Most of the time, however, you'll need to add or remove some chart elements and fine-tune the layout. You do this using the controls on the Chart Tools
For example, to give a chart a title, choose Chart Tools
Figure 1-14 shows our column chart after adding a title and specifying that the legend should appear at the bottom of the chart (using Chart Tools
Every element in a chart can be formatted and customized in many ways. Many users are content with charts that are created using the steps described earlier in this chapter. But because you're reading this book, you probably want to find out how to customize charts for maximum impact.
Excel provides two ways to format and customize individual chart elements. Both of the following methods require that you select the chart element first:
Note
The Ribbon controls allow only a small subset of the formatting options. For maximum control, use the Format dialog box.
For example, assume that you'd like to change the color of the columns for one of the series in the chart. Click any column in the series (which selects the entire series). Then, choose Chart Tools
Alternatively, you can select a column in the chart, press Ctrl+1, and use the Format Data Series dialog box shown in Figure 1-15. Note that this is a tabbed dialog box. Click a tab along the left side to view additional controls. It's also a stay-on-top dialog box, so you can click another element in the chart. In other words, you don't have to close the dialog box to see the changes you specify.
The following sections cover these common chart modifications:
Note
Before you can modify a chart, you must activate it. To activate an embedded chart, click it. Doing so activates the chart and also selects the element that you click. To activate a chart on a chart sheet, just click its sheet tab.
If your chart is an embedded chart, you can freely move and resize it with your mouse. Click the chart's border and then drag the border to move the chart. Drag any of the eight "handles" to resize the chart. The handles consist of three dots that appear on the chart's corners and edges when you click the chart's border. When the mouse pointer turns into a double arrow, click and drag to resize the chart.
When a chart is selected, you can use the Chart Tools
To move an embedded chart, just click its border at any location except one of the eight resizing handles. Then drag the chart to its new location. You also can use standard cut and paste techniques to move an embedded chart. Select the chart and choose Home
When you create a chart using the icons in the Insert
To convert an embedded chart to a chart on a chart sheet, select the chart and choose Chart Tools
Figure 1-16. Use the Move Chart dialog box to move an embedded chart to a chart sheet (or vice versa).
To convert a chart on a chart sheet to an embedded chart, activate the chart sheet and then choose Chart
To make an exact copy of a chart, select the chart (an embedded chart or a chart sheet) and choose Home
Another way to copy a chart is to press Ctrl while dragging an embedded chart (or while dragging a chart sheet's tab).
To delete an embedded chart, press Ctrl and click the chart (this selects the chart as an object). Then press Delete. When the Shift key is pressed, you can select multiple charts and then delete them all with a single press of the Delete key.
To delete a chart sheet, right-click its sheet tab and choose Delete from the shortcut menu. To delete multiple chart sheets, select them by pressing Ctrl while you click the sheet tabs.
To add new elements to a chart (such as a title, legend, data labels, or gridlines), use the controls in the Chart Tools
Some of the elements within a chart can be moved. The movable chart elements include the plot area, titles, the legend, and data labels. To move a chart element, simply click it to select it. Then drag its border. The easiest way to delete a chart element is to select it and then press Delete. You can also use the controls in the Chart Tools
Note that deleting a chart element by using the controls in the Chart Tools
A few chart elements consist of multiple objects. For example, the data labels element consists of one label for each data point. To move or delete one data label, click once to select the entire element and then click a second time to select the specific data label. You can then move or delete the single data label.
Many users are content to stick with the predefined chart layouts and chart styles. For more precise customizations, Excel allows you to work with individual chart elements and apply additional formatting. You can use the Ribbon commands for some modifications, but the easiest way to format chart elements is to right-click the element and choose Format from the shortcut menu. The exact command depends on the element you select. For example, if you right-click the chart's title, the shortcut menu command is Format Chart Title. Alternatively, you can press Ctrl+1 to display the Format dialog box for the selected element.
The Format command displays a stay-on-top tabbed dialog box, with options for the selected element.
Figure 1-17 shows the Format Axis dialog box, which I displayed by right-clicking the vertical axis and selecting Format Axis from the shortcut menu.
Figure 1-17. Each chart element has a formatting dialog box. This one is used to format a chart axis.
Tip
If you've applied formatting to a chart element and decide that it wasn't such a good idea, you can revert to the original formatting for the particular chart style. Right-click the chart element and choose Reset to Match Style from the shortcut menu. To reset the entire chart, select the chart area when you issue the command.
Note
In previous versions of Excel, double-clicking a chart element displayed its Format dialog box. That mouse action does not work in Excel 2007.
If you create a nicely formatted chart, and realize that you need to create several more charts that have the same formatting, you have these three choices:
Make a copy of the original chart, and then change the data used in the copied chart. One way to change the data used in a chart is to choose the Chart Tools
Create the other charts, but don't apply any formatting. Then, activate the original chart and press Ctrl+C. Select one of the other charts, and choose Home
Create a chart template, and then use the template as the basis for the new charts. Or, you can apply the new template to existing charts. See Chapter 2 for more information about chart templates.
When you activate an embedded chart, its name appears in the Name box (located to the left of the formula bar). It seems logical that you can use the Name box to change the name of a chart — but you can't.
To change the name of an embedded chart, use the Chart Tools
Why rename a chart? If a worksheet has many charts, you may prefer to activate a particular chart by name. Just type the chart's name in the Name box and press Enter. It's much easier to remember a chart named Monthly Sales as opposed to a chart named Chart 9.
Printing embedded charts is nothing special; you print them the same way that you print a worksheet. As long as you include the embedded chart in the range that you want to print, Excel prints the chart as it appears on-screen. When printing a sheet that contains embedded charts, it's a good idea to preview first (or use Page Layout View) to ensure that your charts do not span multiple pages. If you created the chart on a chart sheet, Excel always prints the chart on a page by itself.
Tip
If you select an embedded chart and choose Office
If you don't want a particular embedded chart to appear on your printout, select the chart, choose Chart Tools
