Section 16.2. Basic Tasks with Charts | Excel 2010: The Missing Manual

16.2. Basic Tasks with Charts

Unlike the orderly rows of numbers and labels that fill most worksheets, charts float above your data, locked inside special box-like containers. To best take advantage of these chart boxes, you need to understand a little more about how they work.

16.2.1. Moving and Resizing a Chart

When you insert a chart into an existing worksheet, it becomes a floating object, hovering above your worksheet. Depending on where Excel puts it, it may temporarily obscure your data. The chart box doesn't damage your data in any way, but it can end up hiding your worksheet's numbers and text (both onscreen and in your printouts).

The solution is to learn to grab hold of these floating boxes and place them where you really want them. The process is pretty straightforward:

Click once on the chart to select it.

You'll notice that when you select a chart, Excel highlights the worksheet data the chart uses, as shown in Figure 16-4. At the same time, the Chart toolbar springs into visibility at the top of the Excel window.

Figure 16-4. You'll know you've selected the whole chart when the worksheet data is outlined with a colored border, and eight squares appear on the chart border (one on each corner and in the middle of each edge), as shown here.

Make sure you click the edge of the chart or on a blank space inside the chart, and not on any of the chart elements (like the legend, the individual chart bars, and so on). This distinction is a source of great chart frustration for Excel novices, because if you select a part of the chart instead of the whole chart, you can't move or manipulate it.
Drag with your mouse to move or resize the chart.

To move the chart, click one of the edges or a blank spot in the chart, and hold down the mouse button. The mouse pointer becomes a four-way arrow. Now you can drag the chart anywhere on your worksheet, releasing the mouse button when it's in the right spot.

To resize the chart, move your mouse over one of the tiny black boxes on the chart border. The mouse pointer changes to a resize arrow. Next , hold down the button and drag the border to make the chart larger or smaller. If you click one of the corner boxes, you'll maintain the chart's proportions as you resize it. If you click one of the boxes on the side, you'll squash the chart in one direction only. For example, if you click the box on the left side and drag to the right, you'll compress the width of the chart, making it thinner.
When you're finished, click a cell anywhere in the worksheet to go back to your data.

At this point, life returns to normal, and the Chart toolbar disappears.

Note: The only charts you can move or resize are embedded chartsthe ones that appear in floating boxes inside other worksheets. If you've created a standalone chart, you won't be able to move or resize it. Instead, it automatically shrinks or enlarges itself to match the display area in the Excel window.

UNDER THE HOOD
How Excel Anchors Charts

Although charts appear to float above the worksheet, they're actually anchored to the cells underneath. Each corner of the chart is anchored to one cell (these anchor points change, of course, if you move the chart around). This fact becomes important if you decide to insert or delete rows or columns anywhere in your worksheet.

For example, consider the chart shown in Figure 16-4. Its top edge is bound to row 9, and its bottom edge is bound to row 19. Similarly, its left edge is bound to column A, and its right edge to column D. That means if you insert a new row above row 9, the whole chart shifts down one row. If you insert a column to the left of column A, the whole chart shifts one column to the right.

Even more interesting is what happens if you insert rows or columns "underneath" the chart. For example, if you insert a new row between the current row 10 and row 11, the chart stretches , becoming one row taller. Similarly, if you delete column B, the chart compresses, becoming one column thinner.

Tip: To remove a chart in one fell swoop, just select it with the mouse and press the Delete key.

16.2.2. Changing Chart Options

Even if you don't want to move your charts around, it's very important that you understand how to select them. That's because selecting a chart lets you perform tasks like printing or customizing the chart.

To change chart options, simply right-click the chart. A pop-up menu appears with several useful choices, including:

Chart Type . Use this option to choose new types and subtypes for a chart. (For example, you can use this option to transform a column chart into a pie chart.) You'll see the same dialog box as in step 1 of the Chart Wizard.
Source Data . Use this option to specify the range of data for a chart. This dialog box shows the same information as step 2 of the Chart Wizard.
Chart Options . Use this option to set a range of miscellaneous options, including formatting, grid lines, and labels. This dialog box shows the same information as step 3 of the Chart Wizard.
Location . Use this option to change an embedded spreadsheet into a standalone spreadsheet, or vice versa. This dialog box shows the same information as step 4 of the Chart Wizard.
Chart Window . Use this command to open a new pop-up window containing only the chart. This window is a handy way to take a closer look at a chart that might be too small or too crowded to work with on the worksheet.

16.2.3. Editing and Adding to Chart Data

Every chart remains linked to the source data you used to create it. When you alter the data in your table, Excel refreshes the chart with the new information automatically. As long as Excel is set to automatic recalculations (and it almost always is), there's no such thing as an out-of-date chart.

Tip: There's no restriction on linking multiple charts to the same data. So you can create two different types of charts (for example, a pie and a column chart) that show the same data. You can even create one chart that plots all the data and another chart that just uses a portion of the same information.

However, there's one tricky point. Any range you define for use in a chart is static , which means it doesn't grow as your data grows. That means that if you add a new row at the bottom of the range, it doesn't appear on the chart because it's outside of the chart range.

If you do want to insert additional data to a range of data used in a chart, you have several options:

You can use the Insert Rows command. If you do, Excel notices the change, and automatically expands the range to include the newly inserted row. However, this command works only if you add a row into the middle of your data. If you try to tack a row onto the end or beginning of a table, however, Excel still ignores it, and you'll need to use the solution described in the next bullet point.
After you insert new rows, you can modify the chart range to include the new data. This approach is the most common, and it's quite painless. First, select your chart by clicking on the edge of the chart box, or on a blank space in the chart. Excel highlights the linked worksheet data with a colored border. Click this colored border, and drag it until it includes all the new data you want to include. When you release the mouse button, Excel refreshes the chart with the new information.
If you're using Excel 2003, you can use a data list (Section 13.1). A list is the most elegant solution, and it gives you some additional features, like sorting and filtering. See the box Sidebar 16.2 for more information.

Excel is smart enough to adjust your chart range in some situations. For example, if you drag your chart data to a new place on your worksheet, Excel updates the chart to match automatically.

POWER USERS' CLINIC
Charting a Data List

You can use the new list features in Excel 2003 with charts (Excel 2002 owners need not apply). Lists and charts make a perfect match. Lists grow and shrink dynamically in size as you add or delete records. If a chart is bound to a list, the chart updates itself as you add new information or remove old data.

You can bind a list to a chart in the exact same way as you bind a chart to a normal range. You simply select the range of cells that makes up the list, select Insert Chart, and follow the list of steps described earlier. But even if you've already created the chart with an ordinary range of cells, you can still use a data listall you need to do is convert the linked range to a list.

For example, in the sales report example shown in Figure 16-1, here's what you need to do:

Select the range of cells that contain the table of data, not including the chart's title (cells A2 to B7).
Select Data List Create List.

Now, as you add new items to the list, Excel inserts them into the chart immediately.

When you chart a data list, you also gain the ability to use other features, like sorting and filtering. For example, you can use sorting to determine the order that items appear within a chart (which is occasionally useful), and you can use filtering to hide rows and to chart only a portion of the data (which is often indispensable ). For example, you can apply a filter condition that shows only the three best performing regions , and the chart updates itself so that it shows only this data. This technique is particularly handy when you're creating charts that use multiple series, as described later in this chapter.

For more information about filtering and the ever-impressive data list, refer to Chapter 13.

16.2.4. Printing Charts

How you print a chart depends on the type of chart you've created. You can print embedded charts either with worksheet data, or on their own. Standalone charts, which occupy separate worksheets, always print on separate pages.

16.2.4.1 Embedded charts

There are two ways to print embedded charts. The first approach is to print your worksheet exactly as it appears on screen, with a mix of data and floating charts. In this case, you'll need to take special care to make sure your charts aren't positioned over any data you need to read in the printout. To double-check , select File Print Preview.

Your other option is to print out the embedded chart on a separate page, which is surprisingly easy. Just click the chart to select it, and then choose File Print (or File Print Preview to see what it'll look like). When you do so, Excel's standard choice is to print your chart using landscape orientation, so that the long edge of the page is along the bottom, and the chart is wider than it is tall. Landscape is usually the best way to align a chart, especially if it holds a large amount of data, so Excel automatically uses landscape orientation no matter what page orientation you've configured for your worksheet. If you want to change the chart orientation, select the chart, then choose File Page Setup, and then choose Portrait. Now your chart uses upright alignment, just as you might see in a portrait-style painting.

Note: If you select File

Page Setup with a chart selected, you won't end up configuring the orientation for the worksheet itself. Instead you'll configure the orientation of the embedded chart when you print it out on a separate page.

Excel also includes some Page Setup options that are specific to charts. To see these, select File Page Setup, and then choose the Chart tab, which appears only when a chart is currently selected. Figure 16-5 shows the Chart tab.

Figure 16-5. The Page Setup dialog box lets you print a chart using lower draft quality, and in black-and-white instead of color . You can also change a chart's printed size. Select "Use full page" to stretch the chart so that it takes up the whole page, or "Scale to fit" if you want to make the chart as large as possible but without changing the current proportions of the chart (known as the aspect ratio). Finally, choose Custom to print the chart centered on the page with the same size that it currently has on your worksheet.

16.2.4.2 Standalone charts

If you're using a standalone chart, your chart always prints out on a separate page, sized to fit the whole page. To print out just the chart page alone (rather than the whole workbook), switch to the chart's worksheet, and choose File Print. If you want to print out the entire workbook (which prints your data worksheet and chart worksheet on different pages), look in the "Print what" section and select the "Entire workbook" option.

Excel automatically sets all chart worksheets to Landscape orientation, which orients the page so that the long edge runs horizontally across the bottom. If this layout isn't what you want, choose File Page Setup before you print the chart. This dialog box gives you the chance to switch to portrait orientationjust select the Portrait option. When you click OK, Excel realigns the chart's worksheet. Remember, if you're still not sure how your printout will look, you can always use the handy File Print Preview command to see a sneak preview. For more information about printing Excel files, see Chapter 6.