Contents
Comparing Chart Types
Column Chart
Line Chart
TIP
Pie Chart
TIP
Scatter Chart
FIGURE 8–5 Scatter chart
Creating a Chart
Selecting the Data to Chart
Selecting a Chart Type
Choosing the Chart Location
EXTRA FOR EXPERTS
TIP
TIP
Step-by-Step 8.1
TECHNOLOGY CAREERS
Updating a Data Source
TIP
Step-by-Step 8.2
Designing a Chart
Selecting Chart Elements
Choosing a Chart Layout and Style
Arranging Chart Elements
Step-by-Step 8.3
Creating a 3-D Chart
Step-by-Step 8.4
Formatting and Modifying a Chart
Step-by-Step 8.5
Formatting a Chart
Step-by-Step 8.6
Editing and Formatting Chart Text
Step-by-Step 8.7
Changing the Chart Type
TIP
Step-by-Step 8.8
Inserting Sparklines
EXTRA FOR EXPERTS
Step-by-Step 8.9
Lesson 8: Working with Charts: Summary
Lesson 8: Working with Charts: Vocabulary Review
Lesson 8: Working with Charts: Review Questions
TRUE / FALSE
FILL IN THE BLANK
MATCHING
Lesson 8: Working with Charts: Projects
PROJECT 8–1
PROJECT 8–2
PROJECT 8–3
PROJECT 8–4
PROJECT 8–5
PROJECT 8–6
PROJECT 8–7
PROJECT 8–8
Lesson 8: Working with Charts: Critical Thinking
ACTIVITY 8–1
ACTIVITY 8–2
Comparing Chart Types
You can create a variety of charts in Excel. Each chart type works best for certain types of data. In this lesson, you will create four of the most commonly used charts: a column chart, a line chart, a pie chart, and a scatter chart. These charts as well as several other types of charts are available in the Charts group on the Insert tab on the Ribbon, as shown in Figure 8–2.
FIGURE 8–2 Charts group on the Insert tab
Bookmark Title:
Top of Form
Bottom of Form
Column Chart
A column chart uses bars of varying heights to illustrate data in a worksheet. It is useful for showing relationships among categories of data. For example, the column chart in Figure 8–1 has one vertical bar to represent the population of each city in four different years. The bars show how the population of one city compares to populations of other cities.
Line Chart
A line chart is similar to a column chart, but it uses points connected by a line instead of bars. A line chart is ideal for illustrating trends over time. For example, the line chart shown in Figure 8–3 illustrates the federal budget debt from 1995 to 2009. The vertical axis represents the federal budget debt in billions of dollars, and the horizontal axis shows the years. The line chart makes it easy to see that the federal budget debt has increased over time. A line chart can include multiple lines to compare two or more sets of data. For example, you could use a second line to chart the tax revenue received during the same time period.
TIP
Businesses often use column, bar, and line charts to illustrate growth over several periods. For example, a column chart can illustrate the changes in yearly production or income over a 10-year period.
FIGURE 8–3 Line chart
Bookmark Title:
Top of Form
Bottom of Form
Pie Chart
A pie chart shows the relationship of parts to a whole. Each part is shown as a “slice” of the pie. For example, Figure 8–4 shows a pie chart that illustrates the grades earned by students in a class. Each slice represents one letter grade, and its size corresponds to the number of students who earned that grade.
TIP
Businesses often use pie charts to indicate the magnitude of certain expenses in comparison to other expenses. Pie charts are also used to illustrate a company's market share in comparison to its competitors'.
FIGURE 8–4 Pie chart
You can pull one slice or multiple slices away from the pie to distinguish them, creating an exploded pie chart . This is helpful when you want to emphasize a specific part of the pie.
Scatter Chart
A scatter chart , sometimes called an XY chart, shows the relationship between two categories of data, such as a person's height and weight. One category is represented on the vertical axis, and the other category is represented on the horizontal axis. Because the data points on a scatter chart are not related to each other, they are usually not connected to each other with a line, like they are in a line chart. For example, Figure 8–5 shows a scatter chart with one data point for each of 12 individuals, based on the person's height and weight. In most cases, a taller person tends to weigh more than a shorter person. However, because some people are underweight and others are overweight, you cannot use a line to represent the relationship between height and weight.
FIGURE 8–5 Scatter chart
Creating a Chart
The process for creating a chart is similar no matter which chart type you want to create. First, you select the data you want to use for the chart. Second, you select a chart type. Finally, you select the chart location. In this section, you will create a column chart.
Selecting the Data to Chart
Charts are based on data. In Excel, the chart data, called the data source , is stored in a range of cells in the worksheet. When you select the data source for a chart, you should also include the text you want to use as labels in the chart. You can also choose whether to chart more than one series of data. A data series is a group of related information in a column or row of a worksheet that is plotted on the chart.
Bookmark Title:
Top of Form
Bottom of Form
Selecting a Chart Type
The next step is to select the type of chart you want to create, such as a column, pie, or line chart. Each chart type has a variety of subtypes you can choose from. The chart types are available on the Insert tab in the Charts group. You can click the button for a specific chart type and then select the subtype you want. The Insert Chart dialog box, shown in Figure 8–6, provides access to all of the chart types and subtypes. You open the Insert Chart dialog box by clicking the Dialog Box Launcher in the Charts group on the Insert tab.
FIGURE 8–6 Insert Chart dialog box
Choosing the Chart Location
After you select a chart type and subtype, the chart is inserted in the center of the worksheet, as shown in Figure 8-7. This is called an embedded chart . When a chart is selected, the Chart Tools appear on the Ribbon with three tabs: Design, Layout, and Format. Also, a selection box with sizing handles appears around the chart. The embedded chart can be viewed at the same time as the data from which it is created. When you print the worksheet, the chart is also printed.
FIGURE 8–7 Embedded chart
EXTRA FOR EXPERTS
You can keep a chart's height and width in the same proportion as you resize it by pressing Shift as you drag a corner sizing handle.
An embedded chart might cover the data source or other information in the worksheet. You can quickly move and resize the embedded chart so it fits better within the worksheet. You move an embedded chart by dragging the selected chart by its selection box to a different part of the worksheet. You resize an embedded chart by dragging one of the sizing handles, which are indicated by the dots at the corners and sides of the selected chart.
You can move an embedded chart to a chart sheet , which is a separate sheet in a workbook that stores a chart. A chart sheet does not contain worksheet cells, data, or formulas. A chart sheet displays the chart without its data source, and is convenient when you plan to create more than one chart from the same data or want to focus on the chart rather than its underlying data.
TIP
You can rename a chart sheet like any other worksheet. Right-click its sheet tab, and then click Rename on the shortcut menu. Type a descriptive name for the chart sheet, and then press the Enter key.
Bookmark Title:
Top of Form
Bottom of Form
To move an embedded chart to a chart sheet, click the Chart Tools Design tab on the Ribbon. Then, in the Location group, click the Move Chart button. The Move Chart dialog box appears, as shown in Figure 8–8. You can choose to move the chart to a new chart sheet that you name or to any worksheet in the workbook as an embedded chart. You can use the same process to move a chart from a chart sheet to any worksheet as an embedded chart.
TIP
Embedded charts are useful when you want to print a chart next to the data the chart illustrates. When a chart will be displayed or printed without the data used to create the chart, a separate chart sheet is usually more appropriate.
FIGURE 8–8 Move Chart dialog box
Step-by-Step 8.1
1. Open the Education.xlsx workbook from the drive and folder where your Data Files are stored. Save the workbook as Education Pays followed by your initials. Column A contains education levels, and columns B and C contain the median incomes of men and women for each corresponding education level.
2. Select the range A3:C8. This is the data you want to chart.
3. Click the Insert tab on the Ribbon. In the Charts group, click the Column button. A gallery of available column chart subtypes appears.
4.In the 2-D Column section, point to Clustered Column (the first chart in the first row). A ScreenTip appears with a description of the selected chart: Clustered Column. Compare values across categories by using vertical rectangles.
5. Click the Clustered Column button. The 2-D clustered column chart is embedded in the worksheet. A selection box with sizing handles appears around the chart, as shown in Figure 8–7.
6. Point to the selection box. The pointer changes to the move pointer
. Drag the selected chart so that the upper-left corner of the chart is in cell A13. The chart is repositioned in the worksheet.
7. Drag the lower-right sizing handle to cell D26. The chart is sized to cover the range A13:D26.
Bookmark Title:
Top of Form
Bottom of Form
8. On the Ribbon, click the Design tab, if it is not already selected.
9. In the Location group, click the Move Chart button. The Move Chart dialog box appears, as shown in Figure 8–8.
10. Click the New sheet option button. The text in the New sheet box is selected so you can type a descriptive name for the chart sheet.
11. In the New sheet box, type Column.
12. Click OK. The embedded chart moves to a chart sheet named Column, as shown in Figure 8–9. The chart illustrates the value of education in attaining higher income. The columns get higher on the right side of the chart, indicating that those who stay in school are rewarded with higher incomes.
FIGURE 8–9 Column chart sheet added to workbook
3. Save the workbook, and leave it open for the next Step-by-Step.
TECHNOLOGY CAREERS
Excel worksheets are used in education to evaluate and instruct students. Instructors use worksheets to track student grades and to organize the number of hours spent on certain topics. Charts help illustrate these numerical relationships.
Updating a Data Source
Charts are based on the data stored in a worksheet. If you need to change the data in the worksheet, the chart is automatically updated to reflect the new data. You switch between a chart sheet and a worksheet by clicking the appropriate sheet tabs.
TIP
A chart, whether embedded in a worksheet or on a chart sheet, is considered part of a workbook. When you save the workbook, you also save the charts it contains.
EXPAND ALL
Step-by-Step 8.2
1. Click the Sheet1 sheet tab. This worksheet with the data source appears.
2. Click cell A5, and then enter High school diploma.
3. Click the Column sheet tab to display the chart sheet. The label for the second column reflects the edit you made to the data source.
4. Save the workbook, and leave it open for the next Step-by-Step.
Designing a Chart
Most charts include some basic elements, such as a title and legend , which you can choose to include or hide. You can also choose a chart style and layout to give the chart a cohesive design. Finally, you can add labels and other elements to make the chart easier to understand and interpret and more attractive.
Selecting Chart Elements
Charts are made up of different parts, or elements. Figure 8–10 identifies some common chart elements, which are described in Table 8–1. Not all elements appear in every type of chart. For example, a pie chart does not have axes. Also, you can choose which chart elements to use in a chart.
FIGURE 8–10 Chart elements
ELEMENT / DESCRIPTION
Chart area / The entire chart and all other chart elements
Plot area / The area that displays the graphical representation of data
Data series / Related information in a worksheet column or row that is plotted on a chart; many charts can include more than one data series
Data marker / A symbol (such as a bar, line, dot, slice, and so forth) that represents a single data point or value from the corresponding worksheet cell
Data label / Text or numbers that provide additional information about a data marker, such as the value from the worksheet cell (not shown in Figure 8–10)
Axes / Lines that establish a relationship between data in a chart; most charts have a horizontal x-axis and a vertical y-axis
Titles / Descriptive labels that identify the contents of the chart and the axes
Legend / A list that identifies the patterns, symbols, or colors used in a chart
Data table / A grid that displays the data plotted in the chart (not shown in Figure 8–10)
TABLE 8–1 Chart elements
Bookmark Title:
Top of Form
Bottom of Form
The quickest way to select a chart element is to click it. You can tell that you are clicking the correct element by first pointing to the element to display a ScreenTip with its name. A selected chart element is surrounded by a selection box. You can also use the Ribbon to select chart elements. When the chart is selected, click the Format tab or the Layout tab on the Ribbon. In the Current Selection group, click the arrow next to the Chart Elements box. A menu of chart elements for the selected chart appears. Click the name of the element you want to select.
After you select a chart element, you can modify it. For example, you can select the chart title or an axis title, and then enter new text for the title. You can also use the standard text formatting tools to change the font, font size, font color, and so forth of the selected title.
Choosing a Chart Layout and Style
You can quickly change a chart's appearance by applying a layout and style. A chart layout specifies which elements are included in a chart and where they are placed. Figure 8–11 shows the chart layouts available for column charts. For example, the legend appears above, below, to the right of, or to the left of the chart in different layouts.
FIGURE 8–11 Chart Layouts gallery for column chart
A chart style formats the chart based on the colors, fonts, and effects associated with the workbook's theme. Figure 8–12 shows the chart styles available for column charts.
FIGURE 8–12 Chart Styles gallery for column charts
Bookmark Title:
Top of Form
Bottom of Form
You can quickly choose a layout and style for a selected chart from the Ribbon. Click the Design tab on the Ribbon. In the Chart Layouts group, click the chart layout you want to use. In the Chart Styles group, click the chart style you want to use.
Arranging Chart Elements
You can modify a chart's appearance by displaying and hiding specific chart elements and rearranging where they are positioned. For example, you can choose when and where to display the chart title, axis titles, legend, data labels , data table , axes, gridlines, and the plot area . First, select the chart. Then click the Layout tab on the Ribbon. The Labels, Axes, and Background groups on the Layout tab contain buttons for each chart element. Finally, use the commands on the appropriate button to display that element in a particular location in the chart area or hide it.
Step-by-Step 8.3
1. On the Ribbon, click the Design tab, if it is not already selected.