Part III: Creating Charts and Graphics

Creating a Chart

Creating a chart is fairly simple:

1.Make sure that your data is appropriate for a chart.

2.Select the range that contains your data.

3.Choose Insert Charts and select a chart type. These icons display drop-down lists that display subtypes. Excel creates the chart and places it in the center of the window.

4.(Optional) Use the commands in the Chart Tools contextual menu to change the look or layout of the chart or add or delete chart elements.

Tip

You can create a chart with a single keystroke. Select the range to be used in the chart and then press Alt+F1 (for an embedded chart) or F11 (for a chart on a chart sheet). Excel displays the chart of the selected data, using the default chart type. The default chart type is a column chart, but you can change it. Start by creating a chart of the type that you want to be the default type. Select the chart and choose Chart Tools Design Change Chart Type. In the Change Chart Type dialog box, click Set As Default Chart. n

Hands On: Creating and

Customizing a Chart

This section contains a step-by-step example of creating a chart and applying some customizations. If you’ve never created a chart, this is a good opportunity to get a feel for how it works.

Figure 18.4 shows a worksheet with a range of data. This data is customer survey results by month, broken down by customers in three age groups. In this case, the data resides in a table (created by choosing Insert Tables Table), but that’s not a requirement to create a chart.

On the CD

This workbook, named hands-on example.xlsx, is available on the companion CD-ROM. n

Selecting the data

The first step is to select the data for the chart. Your selection should include such items as labels and series identifiers (row and column headings). For this example, select the range A4:D10. This range includes the category labels but not the title (which is in A1).

408

Chapter 18: Getting Started Making Charts

FIGURE 18.4

The source data for the hands-on chart example.

Tip

If your chart data is in a table (or is in a rectangular range separated from other data), you can select just a single cell. Excel will almost always guess the range for the chart accurately. n

Note

The data that you use in a chart need not be in contiguous cells. You can press Ctrl and make a multiple selection. The initial data, however, must be on a single worksheet. If you need to plot data that exists on more than one worksheet, you can add more series after the chart is created. In all cases, however, data for a single chart series must reside on one sheet. n

Choosing a chart type

After you select the data, select a chart type from the Insert Charts group. Each control in this group is a drop-down list, which lets you further refine your choice by selecting a subtype.

For this example, choose Insert Charts Column Clustered Column. In other words, you’re creating a column chart, using the clustered column subtype. Excel displays the chart shown in Figure 18.5.

You can move the chart by dragging any of its borders. You can also resize it by clicking and dragging in one of its corners.

Experimenting with different layouts

The chart looks pretty good, but it’s just one of several predefined layouts for a clustered column chart.

To see some other configurations for the chart, select the chart and apply a few other layouts in the Chart Tools Design Chart Layouts group.

409

Part III: Creating Charts and Graphics

FIGURE 18.5

A clustered columns chart.

Note

Every chart type has a set of layouts that you can choose from. A layout contains additional chart elements, such as a title, data labels, axes, and so on. You can add your own elements to your chart, but often, using a predefined layout saves time. Even if the layout isn’t exactly what you want, it may be close enough that you need to make only a few adjustments. n

Figure 18.6 shows the chart after selecting a layout that adds a chart title and moves the legend to the bottom. The chart title is a text element that you can select and edit (the figure shows the generic title). For this example, Customer Satisfaction by Age Group is a good title.

FIGURE 18.6

The chart, after selecting a different layout.

410

Chapter 18: Getting Started Making Charts

Tip

You can link the chart title to a cell so the title always displays the contents of a particular cell. To create a link to a cell, click the chart title, type an equal sign (=), click the cell, and press Enter. Excel displays the link in the Formula bar. In the example, the contents of cell A1 is perfect for the chart title. n

Experiment with the Chart Tools Layout tab to make other changes to the chart. For example, you can remove the grid lines, add axis titles, relocate the legend, and so on. Making these changes is easy and fairly intuitive.

Trying another view of the data

The chart, at this point, shows six clusters (months) of three data points in each (age groups). Would the data be easier to understand if you plotted the information in the opposite way?

Try it. Select the chart and then choose Chart Tools Design Data Switch Row/Column. Figure 18.7 shows the result of this change. I also selected a different layout, which provides more separation between the three clusters.

Note

The orientation of the data has a drastic effect on the look of your chart. Excel has its own rules that it uses to determine the initial data orientation when you create a chart. If Excel’s orientation doesn’t match your expectation, it’s easy enough to change. n

The chart, with this new orientation, reveals information that wasn’t so apparent in the original version. The <30 and 30–49 age groups both show a decline in satisfaction for March and April. The 50+ age group didn’t have this problem, however.

FIGURE 18.7

The chart, after changing the row and column orientation, and choosing a different layout.

411

Part III: Creating Charts and Graphics

Trying other chart types

Although a clustered column chart seems to work well for this data, there’s no harm in checking out some other chart types. Choose Design Type Change Chart Type to experiment with other chart types. This command displays the Change Chart Type dialog box, shown in Figure 18.8. The main categories are listed on the left, and the subtypes are shown as icons. Select an icon and click OK, and Excel displays the chart using the new chart type. If you don’t like the result, select Undo.

Tip

You can also change the chart type by selecting the chart and using the controls in the Insert Charts group. n

FIGURE 18.8

Use this dialog box to change the chart type.

Trying other chart styles

If you’d like to try some of the prebuilt chart styles, select the chart and choose Chart Tools Design Chart Styles gallery. You’ll find an amazing selection of different colors and effects, all available with a single mouse click.

Tip

The styles displayed in the gallery depend on the workbook’s theme. When you choose Page Layout Themes Themes to apply a different theme, you’ll have a new selection of chart styles designed for the selected theme. n

412