AL Chapter 1 TE RI Introducing Excel Charts MA In This Chapter ◆ What is a chart? D ◆ How Excel handles charts ◆ The parts of a chart TE ◆ Embedded charts versus chart sheets GH ◆ The basic steps for creating a chart RI ◆ Working with charts CO PY 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.
10 Part I: Chart Basics 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.
Chapter 1: Introducing Excel Charts 11 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.
12 Part I: Chart Basics Embedded Charts 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.
Chapter 1: Introducing Excel Charts 13 Chart Sheets 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.
14 Part I: Chart Basics What’s new in Excel 2007? In Microsoft Office 2007, the charting feature has undergone some major changes. If you’ve used the charting feature in a previous version of Excel, this sidebar provides a summary of what’s new and what’s changed. The Office 2007 charting engine is compatible with other Office 2007 apps such as Word and PowerPoint. In most (but not all) cases, charts created in a previous version can render without a problem in Office 2007.
Chapter 1: Introducing Excel Charts 15 • Pattern fills: Pattern fills (such as diagonal lines) for chart elements are no longer supported. However, if you open an older file that uses pattern fills, the patterns will continue to display in Office 2007. • Double-clicking to format: In the past, double-clicking a chart element (for example, an axis) displayed a formatting dialog box appropriate for that element. Double-clicking no longer displays a formatting dialog box.
16 Part I: Chart Basics Parts of a Chart 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. Figure 1-5: Parts of a chart This particular chart is a “combination” chart that displays two data series: Income and Profit Margin.
Chapter 1: Introducing Excel Charts 17 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).
18 Part I: Chart Basics Figure 1-7: A 3-D column chart Chart limitations The following table lists the limitations of Excel charts. Item Limitation Charts in a worksheet Limited by available memory Worksheets referred to by a chart 255 Data series in a chart 255 Data points in a data series 32,000 Data points in a data series (3-D charts) 4,000 Total data points in a chart 256,000 Most users never find these limitations to be a problem.
Chapter 1: Introducing Excel Charts 19 Basic Steps for Creating a Chart Creating the Chart Follow these steps to create a chart in Excel 2007: 1. 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. 2.
20 Part I: Chart Basics Figure 1-9: The icons in the Insert➪Charts group expand to show a gallery of chart subtypes. Figure 1-10: A column chart with two data series Switching the Row and Column Orientation When Excel creates a chart, it uses a set of rules to determine whether the data is arranged in columns or in rows.
Chapter 1: Introducing Excel Charts 21 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.
22 Part I: Chart Basics Figure 1-12: The Change Chart Type dialog box TIP If the chart is an embedded chart, you can also change a chart’s type by using the icons in the Insert➪Charts group. In fact, this method is more efficient because it doesn’t involve a dialog box. Applying a Chart Layout 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.
Chapter 1: Introducing Excel Charts 23 Part I Figure 1-13: One-click design variations of a column chart TIP The styles displayed in the gallery depend on the workbook’s theme. When you choose Page Layout➪Themes to apply a different theme, you’ll see a new selection of chart styles designed for the selected theme. Adding and Deleting Chart Elements In some cases, applying a chart layout (as described previously) gives you a chart with all the elements you need.
24 Part I: Chart Basics How to master Excel charting I’ve had the pleasure of meeting some of the world’s leading Excel chart experts. These people come from a variety of disciplines, but they all have two attributes in common: They are creative, and they are not afraid to experiment. Yes, dear reader, the key to mastering Excel charting is to think outside the box (as they say) and experiment. Just because Excel doesn’t support a particular type of chart doesn’t mean that you can’t create such a chart.
Chapter 1: Introducing Excel Charts 25 Excel provides two ways to format and customize individual chart elements. Both of the following methods require that you select the chart element first: Use the Ribbon controls on the Chart Tools➪Format tab. • Press Ctrl+1 to display the Format dialog box that’s specific to the selected chart element. NOTE The Ribbon controls allow only a small subset of the formatting options. For maximum control, use the Format dialog box.
26 Part I: Chart Basics Working with Charts The following sections cover these common chart modifications: • Moving and resizing a chart • Converting an embedded chart to a chart on a chart sheet • Copying a chart • Deleting a chart • Adding chart elements • Moving and deleting chart elements • Formatting chart elements • Copying a chart’s formatting • Renaming a chart • Printing charts NOTE Before you can modify a chart, you must activate it. To activate an embedded chart, click it.
Chapter 1: Introducing Excel Charts 27 Converting an Embedded Chart to a Chart Sheet When you create a chart using the icons in the Insert➪Charts group, the result is always an embedded chart. If you’d prefer that your chart be located on a chart sheet, you can easily move it. To convert an embedded chart to a chart on a chart sheet, select the chart and choose Chart Tools➪Design➪Location➪Move Chart to display the Move Chart dialog box shown in Figure 1-16.
28 Part I: Chart Basics Deleting a Chart 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.
Chapter 1: Introducing Excel Charts 29 The Format command displays a stay-on-top tabbed dialog box, with options for the selected element. 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.
30 Part I: Chart Basics Copying a Chart’s Formatting 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➪Design➪ Data➪Select Data command and make the changes in the Select Data Source dialog box.
Chapter 1: Introducing Excel Charts 31 TIP If you don’t want a particular embedded chart to appear on your printout, select the chart, choose Chart Tools➪Format, and then click the dialog box launcher in the Size group (it’s the small icon to the right of the word Size) to display the Size and Properties dialog box. In the Size and Properties dialog box, click the Properties tab and deselect the Print Object check box (see Figure 1-18).
32 Part I: Chart Basics
