9.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.
9.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).
Note: To remove a chart in one fell swoop, just select it with the mouse and press the Delete key.
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.
Notice that when you select a chart, Excel highlights the worksheet data the chart uses, as shown in Figure 9-4. At the same time, the Chart toolbar springs into visibility at the top of the Excel window.
Figure 9-4. Before you can move a chart, you must first select it. You know you've selected the whole chart when Excel outlines the worksheet data with a colored border and squares appear along the periphery.
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 and drag one of the corner boxes, you maintain the chart's proportions as you resize it. If you click and drag one of the boxes on the side, you stretch or squash the chart in one direction only. For example, if you click the box on the left side and drag to the right, you 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.
9.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 customize a chart by changing the 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, gridlines, 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's too small or too crowded to work with on the worksheet.
9.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: Excel lets you link 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, Excel still ignores the row, and you need to use the solution described in the next bullet point.
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.
9.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.
220.127.116.11. 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 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 itll 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, choose File Page Setup, and then choose Portrait. Your chart now uses upright alignment, just as you might see in a portrait-style painting.
Note: If you select File Page Setup while you have a chart selected, you
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. As you can see in Figure 9-5, the Chart tab lets you print a chart using lower draft quality and in black-and-white instead of color. You can also change a charts 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 without stretching. Finally, you can choose Custom to print the chart centered on the page with the same size that it currently has on your worksheet.
18.104.22.168. 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.
Figure 9-5. This is the Chart tab of the Page Setup dialog box, and "Scale to fit page" has been chosen.
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 charts 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.