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 take advantage of these chart boxes, you need to understand a little more about how they work. POWER USERS' CLINIC
Browsing Excel's Chart Gallery | | Excel pros sometimes find that the ribbon approach is a bit awkward when you're trying to find a less commonly used chart type. In this situation, you may prefer to look at the full list of chart types and subtypes . To do so, head to the ribbon's Insert  Charts section, and then click the dialog launcher (the square-with-an-arrow icon in the bottom-right corner). You see the Insert Chart dialog box (Figure 9-3). The Insert Chart dialog box doesn't just let you create charts. You can also designate the default chart type (the one that's used if you select some cells , and then press F11 to create a chart in a single bound). To designate a default chart, select it, and then click "Set as Default Chart". |
 | Figure 9-3. The gallery on the Insert Chart dialog box's right side has a thumbnail of every chart subtype, grouped by type. You can scroll through them all, or you can choose a type from the list on the left to jump straight to a specific section. When you find what you want, click OK to create it. | |
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). You have 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. At the same time, three new tabs appear in the ribbon, under the Chart Tools heading (Figure 9-4).  | Figure 9-4. You'll know you've selected the chart when three new charting tabs appear in the ribbon under the Chart Tools heading. | | -
Hover over the chart border until the mouse pointer changes to a four-way arrow . Figure 9-5 shows what you're looking for.  | Figure 9-5. The four-way arrow is a signal that you can click here to move the chart. If you move to one of the corners, you'll see an angled two-way arrow, which lets you resize the chart. | | -
Click and drag with your mouse to move or resize the chart . Using the four-way arrow, you can drag the chart anywhere on your worksheet, releasing the mouse button when it's in the right spot. Using the two-way arrow (which appears if you move to one of the chart's corners), you can drag the border to make the chart larger or smaller.
Tip: To remove a chart in one fell swoop, just select it with the mouse, and then press Delete.
-
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 Tools tabs disappear.
Note: You can resize a chart in another, slightly more circuitous way. You can set the Height and Width boxes in the Chart Tools Format Size section of the ribbon. Although this isnt as quick as dragging the chart edge, it lets you set the size exactly, which is indispensable if you have several charts on the same worksheet and you need to make sure they're all the same size.
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 9-1. Its top edge is bound to row 2, and its bottom edge is bound to row 12. Similarly, its left edge is bound to column C, and its right edge to column I. That means if you insert a new row above row 2, the whole chart shifts down one row. If you insert a column to the left of column C, the whole chart shifts one column to the right. Even more interesting is what happens if you insert rows or columns in the area that the chart overlaps. 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 D, the chart compresses, becoming one column thinner. If it bugs you, you can change this sizing behavior. First, select the chart and head to the ribbon's Chart Tools Format Size section. Then, click the dialog launcher (the square-with-an-arrow icon in the bottom-right corner). When the Size and Properties dialog box appears, choose the Properties tab. Youll see three "Object positioning" options. The standard behavior is "Move and size with cells", but you can also create a chart that moves around the worksheet but never resizes itself ("Move but don't size with cells") and a chart that's completely fixed in size and position ("Don't move or size with cells"). |
9.2.2. Creating a Standalone Chart Even without your input, Excel usually makes common-sense choices, so you can often build a chart without needing to tweak any of these options. You have two options for placing charts in a workbook. You can create an embedded chart, which appears in an existing worksheet (usually next to the appropriate data), or you can create a standalone chart, which appears in a new worksheet of its own (Figure 9-6).  | Figure 9-6. A standalone chart lives in a separate worksheet that doesn't have any other data and doesn't include the familiar grid of cells. | |
Ordinarily, when you pick a chart type from the ribbon, Excel creates an embedded chart. However, you can easily switch your chart over to a new worksheet if you're running out of roomjust follow these steps: -
Right-click the chart, and then choose Move Chart (or, select the chart, and then choose Chart Tools Design Location Move Chart) . The Move Chart dialog box appears (Figure 9-7). -
Choose "New sheet", and then enter the name for the new worksheet . -
Click OK . Excel creates the new worksheet and places the chart on it. The new worksheet goes in front of the worksheet that contains the chart data. (You can always move the worksheet to a new position in your workbook by dragging the worksheet tab.)  | Figure 9-7. Using the Move Chart dialog box, you can transfer the chart to a standalone worksheet (as shown here) or shuffle it over to another worksheet and keep it as an embedded worksheet. (If you want the latter option, it's just as easy to select the chart and use a cut-and-paste operation to move it to a new worksheet.) | |
Note: You can move or resize only embedded chartsthe ones that appear in floating boxes inside other worksheets. If you've created a standalone chart, you can't move or resize it. Instead, it automatically shrinks or enlarges itself to match the Excel window's display area.
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 worksheet, Excel refreshes the chart with the new information automatically.
Note: Excel's got no restriction on linking multiple charts to the same data. So, you can create two different types of charts (like 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 a couple of options: -
You can use the Home Cells Insert Insert Sheet 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, Excel still ignores it, and youll 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 the edge of the chart box, or 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. When you release the mouse button, Excel refreshes the chart with the new information. See the box in Section 9.2.5 for more information. Excel is smart enough to adjust your chart range in some situations. If you drag your chart data to a new place on your worksheet, Excel updates the chart to match automatically. 9.2.4. Changing the Chart Type When you create a chart, you choose a specific chart type. However, in many situations you may want to try several different chart types with the same data to see which visualization tells your story better. Excel makes this sort of experimentation easy. All you need to do is click your chart to select it, and then make a different choice from the ribbon's Insert Charts section. You can use this technique to transform a column chart into a pie chart. You can also choose Chart Tools Design Type Change Chart Type to make a choice from the Change Chart Type dialog box, which looks just like the Insert Chart dialog box shown in Figure 9-3. POWER USERS' CLINIC
Charting a Table | | You can use the Excel table feature (discussed in Chapter 8) with charts. Tables and charts make a perfect match. Tables grow and shrink dynamically in size as you add or delete records. If a chart's bound to a table, the chart updates itself as you add new information or remove old data. You've already learned how to build a new chart using an existing table. (Just move inside the table, and then make a selection from the ribbon's Insert Charts section). But even if youve already created the chart with an ordinary range of cells, you can still use a tableall you need to do is convert the linked range to a table. In the sales report example shown in Figure 9-1, here's what you'd need to do: -
Select the range of cells that contain all the data, not including the chart's title (cells A2 to B7). -
Select Insert Tables Table. Now, as you add new items to the table, Excel adds them to the chart immediately. When you chart a table, you also gain the ability to use other features, like easier sorting and filtering. 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). If you apply a filter condition that shows only the three best performing regions , the chart updates itself so that it shows only this data. You'll find this technique 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 table feature, flip back to Chapter 8. |
9.2.5. 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. 9.2.5.1. Embedded charts You can print embedded charts in two ways. The first approach is to print your worksheet exactly as it appears on the 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 , use Page Layout view (choose View Workbook Views Page Layout View). You could also print out the embedded chart on a separate page, which is surprisingly easy. Just click the chart to select it, and then choose Office Button Print (or Office Button Print 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's 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 Page Layout Page Setup Orientation Portrait. Now your chart uses upright alignment, just as you may see in a portrait-style painting.
Note: If you select an orientation from the Page Layout Page Setup Orientation list while your chart is selected, you don't end up configuring the orientation for the worksheet itself. Instead you configure the embedded chart's orientation when you print it out on a separate page. If you want to configure the orientation for the whole worksheet, make sure nothing else is selected when you choose an orientation.
Excel also includes some page setup options that are specific to charts. To see these options, head to the Page Layout Page Setup section, click the dialog launcher in the bottom-right corner to show the Page Setup dialog box, and then choose the Chart tab (which appears only when youve got a chart currently selected). You'll see an option to print a chart using lower print quality ("Draft quality"), and in black and white instead of color ("Print in black and white"). 9.2.5.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 then choose Office Button Print. To print out the entire workbookwhich prints your data worksheet and chart worksheet on different pageslook 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, before you print the chart, choose Page Layout Page Setup Orientation Portrait. Remember, if youre still not sure how your printout will look, you can always use the handy Page Layout View to see a sneak preview. For more information about printing Excel files, see Chapter 6. |
