17.1. Charting 101
Excel provides a dizzying number of different chart types, but they all share a few things. In this section, you'll learn about basic Excel charting concepts that apply to almost all types of charts; you'll also create a few basic charts . At the end of this chapter, you'll take a chart-by-chart tour of each and every one of Excel's many chart types.
To create a chart, Excel needs to translate your numbers into a graphical representation. The process of drawing numbers on a graph is called plotting . Before you plot your information on a chart, you should make sure your data's laid out properly. Here are some tips:
Structure your data in a simple grid of rows and columns .
Don't include blank cells between rows or columns.
Include titles, if you'd like them to appear in your chart. You can use category titles for each column of data (placed in the first row, atop each column) and an overall chart title (placed just above the category-title row).
Tip: You can also label each row by placing titles in the far-left column, if it makes sense. If you're comparing the sales numbers for different products, list the name of each product in the first column on the left, with the sales figures in the following columns.
If you follow these guidelines, you can expect to create the sort of chart shown in Figure 17-1.
To create the chart shown in Figure 17-1, Excel performs a few straightforward steps (you'll learn the specifics of how to actually create this chart in the next section). First, it extracts the text for the chart title from cell A1. Next , it examines the range of data (from $14,000 to $64,000) and uses it to set the valueor Y-axisscale. You'll notice that the scale starts at $0, and stretches up to $80,000 in order to give your data a little room to breathe. (You could configure these numbers manually, but Excel automatically makes common-sense guesses like these by looking at the data you're asking it to chart.) After setting the vertical scale, Excel adds the labels along the bottom axis (also known as the X-axis or category axis), and draws the columns of appropriate height.
| || |
Figure 17-1. This worksheet shows a table of data and a simple column chart based on Excel's standard chart settings. Nothing fancy, but it gets the job done.
17.1.1. Embedded and Standalone Charts
The chart in Figure 17-1 is an embedded chart. Embedded charts appear in a worksheet, in a floating box alongside your data. You can move the chart by dragging the box around your worksheet, although depending on where you put it, you may obscure some of your data.
Your other option is to create a standalone chart, which looks the same but occupies an entire worksheet. That means that your chart data and your chart are placed on separate worksheets.
Usually, you'll use an embedded chart if you want to create printouts that combine both your worksheet data and one or more charts. On the other hand, if you want to print the charts separately, it's more convenient to use standalone charts. That way, you can print an entire workbook at once and have the charts and the data on separate pages.
Tip: If you use embedded charts, you still have the option of printing just the chart, sized so that it fills a full sheet of paper. Simply select the chart and then choose Office button Print. If you create a standalone chart, you dont have a choiceExcel always prints your chart on a separate page.
17.1.2. Creating a Chart with the Ribbon
So how do you create a chart like the one shown in Figure 17-1? Easyall it takes is a couple of clicks in the ribbon. Here's how it works:
Select the range of cells that includes the data you want to chart, including the column and row headings and any chart title .
If you were using the data shown in Figure 17-1, you'd select cells A1 to B7.
For speedier chart building, just position your cursor somewhere inside the data you want to chart. Excel then automatically selects the range of cells that it thinks you want. Of course, it never hurts to remove the possibility for error by explicitly selecting what you want to use before you get started.
Tip: And for even easier charting, start by creating an Excel table (Chapter 14) to hold the data you want to chart. Then, if you position yourself somewhere inside the table and create a new chart, Excel automatically selects all the data. It also automatically updates the chart if you add new rows or remove existing data.
Head to the ribbon's Insert Charts section. Youll see a separate button for each type of chart (including column charts, line charts, pie charts, and so on). Click the type you want .
When you choose a chart type, you get a drop-down list of subtypes (Figure 17-2).
The different chart types are explained in more detail later in this chapter. For now, it's best to stick to some of the more easily understood choices, like Bar, Column, or Pie. Remember, the chart choices are just the starting point, as you'll still be able to configure a wide range of details that control things like the titles, colors, and overall organization of your chart.
Click the subtype you want .
Excel inserts a new embedded chart alongside your data, using the standard options (which you can fine-tune later).
Note: If you don't want to make any choices, you can actually build a chart with one key press. Just highlight your data and press F11. This step creates a column chart on a new worksheet. Although you can't undo this operation, you can always delete the new chart worksheet and start over.
| || |
Figure 17-2. Under each chart choice are yet more subtypes, which add to the fun. If you select the Column type (shown here), you'll get subtypes for two- and three-dimensional column charts, and variants that use cone and pyramid shapes . If you hover over one of these subtypes, a box appears with a brief description of the chart.
17.1.3. The Chart Tools Ribbon Tabs
When you select a chart, Excel adds three new tabs to the ribbon under the Chart Tools heading. These tabs let you control the details of your charts, and they are:
Design . This tab lets you change the chart type (Section 17.2.4) and the linked data that the chart uses (Section 17.3.4). It also lets you choose a chart style and layout, two ways to improve the appearance of a chart that you'll consider in Chapter 18.
Layout . This tab lets you configure individual parts of the chart. You can add shapes, pictures, and text labels, and you can configure the chart's gridlines, axes, and background.
Format . This tab lets you format individual chart elements, so you can transform ordinary items into eye candy . You can adjust the font, fill, and borders uses for chart titles and shapes, among other things.
In this chapter, you'll spend most of your time using the Chart Tools Design tab. In the next chapter, you'll begin fine-tuning your charts, and you'll branch out to the other two tabs.
| 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 17-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". Lastly, the Insert Chart dialog box lets you use a custom chart template that you've previously prepared, as described in Section 18.4.5.
| || |
Figure 17-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.