Chapter 10
IN THIS CHAPTER
Understanding the parts of a chart
Creating a chart
Editing a chart
Modifying the parts of a chart
Playing with pivot tables
Look at any Excel spreadsheet loaded with rows and columns of numbers and you may wonder, “What do all these numbers really mean?” Long lists of numbers can be intimidating and confusing, but fortunately, Excel has a solution.
To help you analyze and understand rows and columns of numbers quickly and easily, Excel can convert data into a variety of charts such as pie charts, bar charts, and line charts. By letting you visualize your data, Excel helps you quickly understand what your data means so you can spot trends and patterns.
To create charts that clarify your data (rather than confuse you even more), you need to understand the parts of a chart and their purposes, as shown in Figure 10-1:
Charts typically use two data series to create a chart. For example, one data series may be sales made that month, while a second data series may be the products actually sold.
The X-axis of such a chart would list the names of different sales people while the Y-axis would list a range of numbers that represent amounts. The chart itself could display different colors that represent products sold in different months, and the legend would explain what each color represents.
By glancing at the column chart in Figure 10-1, you can quickly identify
All this data came from the spreadsheet in Figure 10-1. By looking at the numbers in this spreadsheet, identifying the information just mentioned is nearly impossible. However, by converting these numbers into a chart, identifying this type of information is so simple even your boss can do it.
Although Figure 10-1 shows a column chart, Excel can create a variety of other types of charts so you can look at your data in different ways, as shown in Figure 10-2. Some other types of charts Excel can create include
Before you create a chart, you need to type in some numbers and identifying labels because Excel will use those labels to identify the parts of your chart. (You can always edit your chart later if you don’t want Excel to display certain labels or numbers.)
To create a chart, follow these steps:
Click the Insert tab.
A list of chart type icons appears in the Charts group.
Click a Chart icon in the Charts group, such as the Pie or Line icon.
A menu appears, displaying the different types of charts you can choose as shown in Figure 10-3.
Click a chart type.
Excel creates your chart and displays a Chart Tools Design/Format tab, as shown in Figure 10-4.
After you create a chart, you may want to edit it. Editing a chart can mean moving it to a new location, changing the data source (the numbers that Excel uses to create the chart), modifying parts of the chart itself (such as switching to a different chart type), or editing text (such as the chart title or legend).
When you create a chart, Excel plops it right on your displayed spreadsheet, which may not be exactly where you want it to appear. Excel gives you the option of moving a chart to a different place on the current worksheet page or on a different worksheet page altogether.
To move a chart to a different location on the same worksheet, follow these steps:
Hold down the left mouse button and drag (move) the mouse.
The chart moves with the mouse.
Rather than move a chart on the same sheet where it appears, you can also move the chart to another worksheet. That way your data can appear on one worksheet, and your chart can appear on another.
To move a chart to an entirely different sheet, follow these steps:
Click the chart you want to move to another worksheet.
The Chart Tools tab appears.
Click the Move Chart icon in the Location group.
The Move Chart dialog box appears, as shown in Figure 10-6.
As an alternative to Steps 1 through 3, you can right-click a chart; then when the pop-up menu appears, choose Move Chart.
Click OK.
Excel moves your chart.
You can always resize any chart to make it bigger or smaller. To resize a chart, follow these steps:
As soon as you create a chart or click an existing chart, Excel displays the Chart Tools tabs (Design and Format). If you click the Design tab, you’ll see tools organized into five categories:
After you create a chart, you can experiment with how your data may look when displayed as a different chart, such as switching your chart from a bar chart to a pie chart. To change chart types, follow these steps:
Click the chart you want to change.
The Chart Tools tabs (Design and Format) appear.
Click the Change Chart Type icon in the Type group.
The Change Chart Type dialog box appears, as shown in Figure 10-7.
Click a chart type, such as Pie or Column.
The dialog box displays a list of chart designs in the right panel of the dialog box.
Click OK.
Excel displays your new chart.
Another way to change the appearance of a chart is to change its data source (the cells that contain the actual data that the chart uses). To change a chart’s data source, follow these steps:
Click the chart you want to change.
The Chart Tools tabs (Design and Format) appear.
Click the Select Data icon in the Data group.
The Select Data Source dialog box appears, as shown in Figure 10-8.
Click OK.
Excel displays your chart, using the data you selected in Step 5.
When Excel creates a chart, it displays your data’s labels on the X- and Y-axes. However, you can switch these around, and Excel can show you how your chart may change.
To switch the rows and columns used to create a chart, follow these steps:
Click the chart you want to change.
The Chart Tools tabs (Design and Format) appear.
Click the Switch Row/Column icon in the Data group.
Excel switches the X-axis data to appear on the Y-axis and vice versa.
To make your charts more informative, you can add additional text, such as
With each part of a chart, Excel can either hide it completely or move it to a different location. To modify any part of a chart, follow these steps:
Click the chart you want to change.
The Chart Tools tabs (Design and Format) appear.
Click the Add Chart Element icon.
A menu of different chart elements appears, as shown in Figure 10-9.
Choose an option, such as Legend.
A submenu of options appears, as shown in Figure 10-10.
Choose an option.
As you move the mouse pointer over an option, Excel shows you how that option will change your chart. Now you no longer have to guess but can see exactly how each option will modify your chart.
Although you can add and modify the individual parts of a chart yourself, such as the location of the chart title or legend, you may find it faster to choose a predefined layout for your chart. To choose a predefined chart layout, follow these steps:
Click the chart you want to modify.
The Chart Tools tabs (Design and Format) appear.
Click the Quick Layout icon in the Chart Layouts group, as shown in Figure 10-11.
A pull-down menu appears. As you move the mouse pointer over each option, Excel shows you how that option will change the appearance of your chart.
Click a chart layout.
Excel changes your chart.
Charts may be nice to look at, but eventually you may want to delete them. To delete a chart, follow these steps:
Creating a chart can help you visualize your data, but sometimes a massive chart with legends, titles, and X/Y-axis can seem like too much trouble for just identifying trends in your data. For a much simpler tool, Excel offers Sparklines.
Sparklines allow you to see, at a glance, the relationship between values stored in multiple cells. Rather than look at a row or column of numbers to determine if the values are increasing or decreasing over time, you can create a Sparkline that condenses this information in a single cell that you can see at a glance. Excel offers three types of Sparklines, as shown in Figure 10-12:
To create and display a Sparkline in a spreadsheet, follow these steps:
Click the Line, Column, or Win/Loss icon in the Sparklines group.
The Create Sparklines dialog box appears, as shown in Figure 10-13.
Click the cell where you want the Sparkline to appear. (You can select two or more cells.)
The Location Range text box displays the cell reference that you chose.
Click OK.
Excel displays your chosen Sparkline in the cell that you selected.
After you’ve created one or more Sparklines, you can modify their appearance. To modify a Sparkline, follow these steps:
Click the cell that contains a Sparkline.
The Sparkline Tools Design tab appears.
Click a style in the Style group.
If you click the More button, you can view all the available styles.
Click the Marker Color icon.
A menu appears, as shown in Figure 10-14.
Move the mouse over an option such as Low Point or First Point.
A color palette appears.
Click a color.
Your Sparkline appears with the changes you chose.
After you’ve created one or more Sparklines, you may want to delete them. To delete a Sparkline, follow these steps:
Click the Clear icon in the Group category.
Your chosen Sparkline disappears.
Ordinary spreadsheets let you compare two sets of data such as sales versus time or products sold versus the salesperson who sold them. Unfortunately, if you want to know how many products each salesperson sold in a certain month, deciphering this information from a spreadsheet may not be easy.
That’s where pivot tables come in. A pivot table lets you yank data from your spreadsheet and organize it in different ways in a table. By rearranging (or pivoting) your data from a row to a column (and vice versa), pivot tables can help you spot trends that may not be easily identified trapped within the confines of an ordinary spreadsheet.
Pivot tables use the column headings of a spreadsheet to organize data in a table. Ideally, each column in the spreadsheet should identify a different type of data, such as the name of each salesperson, the sales region he or she works in, and the total amount of sales made, as shown in Figure 10-15.
After you design a spreadsheet with multiple columns of data, follow these steps to create a pivot table:
Click the PivotTable icon in the Tables group.
The Create PivotTable dialog box appears, as shown in Figure 10-16.
(Optional) Select the cells that contain the data you want to use in your pivot table.
You only need to follow Step 4 if you didn’t select any cells in Step 1, or if you change your mind and want to select different cells than the ones chosen in Step 1.
Click OK.
Excel displays a PivotTable Fields pane, as shown in Figure 10-17.
Mark (select) one or more check boxes inside the PivotTable Fields pane.
Each time you select another check box, Excel modifies how data appears in your pivot table, as shown in Figure 10-18.
A pivot table organizes data according to your spreadsheet’s column headings (which appear in a pivot table as row labels). The pivot table shown in Figure 10-18 shows sales divided by salesperson. Each salesperson’s amounts are further divided by sales region, and the names of the products sold.
However, you may be more interested in seeing the sales organized by sales region. To do this, you can modify which column heading your pivot table uses to organize your data first. To rearrange column headings in a pivot table, follow these steps:
Click the pivot table you want to rearrange.
The PivotTable Tools heading displays an Analyze and Design tab.
Click the Field List icon in the Show group.
The PivotTable Fields pane appears. A group called Rows appears in the bottom-left corner of the PivotTable Fields pane. This Rows box displays the names of your different PivotTable categories, such as Region, Product, and Sales Person.
Click a label in the Row box.
A menu appears, as shown in Figure 10-19.
Select one of the following:
Figure 10-20 shows different ways a pivot table can organize the same data.
Rows let you organize data according to different criteria, such as sales per region and then by product. For greater flexibility, you can also turn a row into a column heading. Figure 10-21 shows a pivot table where row labels are stacked on top of each other, and then the same pivot table where one row label (Products) is turned into a column heading.
To turn row labels into column headings in a pivot table (or vice versa), follow these steps:
Click the Show icon.
A pull-down menu appears.
Click the Field List icon.
The PivotTable Field List pane appears.
Click a heading in the Rows box near the bottom-left corner of the PivotTable Field List pane.
A pop-up menu appears.
Choose Move to Column Labels.
You can also drag headings from the Rows box to the Columns box and vice versa.
The more information your pivot table contains, the harder it can be to make sense of any of the data. To help you out, Excel lets you filter your data to view only certain information, such as sales made by each salesperson or total sales within a region. To filter a pivot table, follow these steps:
Click the Show icon.
A pull-down menu appears.
Click the Field List icon.
The PivotTable Fields pane appears.
Click a heading in the Rows or Columns group in the PivotTable Fields pane.
A pop-up menu appears.
Click Move to Report Filter.
Excel moves your chosen label into the Report Filter group in the PivotTable Field List pane, as shown in Figure 10-22.
A pivot table not only displays information, but it can also count the number of occurrences of information, such as the number of sales per sales region. To display a count of data, you need to move a heading in the Values group inside the PivotTable Field List pane by following these steps:
Click the Show icon.
A pull-down menu appears.
Click the Field List icon.
The PivotTable Fields pane appears.
Click a heading that you want to count in the PivotTable Field List pane.
A pop-up menu appears.
Click Move to Values.
Excel moves your chosen heading to the Values group inside the PivotTable Field List pane and displays a count of items under your chosen heading, as shown in Figure 10-23.
If your pivot table contains large amounts of data, trying to decipher this information can be difficult. Rather than display all your pivot table’s data, you can choose to slice the pivot table so it shows only the specific data you want to view.
To turn rows into columns in a pivot table (or vice versa), follow these steps:
Click anywhere inside the pivot table you want to modify.
The PivotTable Tools tabs (Analyze and Design) appear.
Click the Analyze tab, click the Insert Slicer icon in the Filter group, and choose Insert Slicer.
The Insert Slicers dialog box appears, as shown in Figure 10-24.
Select one or more check boxes in the Insert Slicers dialog box and click OK.
Slicer panes appear, listing the types of data. For example, a Sales Person slicer pane would list the names of all the salespeople, while a Product slicer pane would list the names of all products sold, as shown in Figure 10-25.
Click the item inside each slicer pane to display that data in the pivot table. (Hold down the Ctrl key and click to choose multiple items.)
If you want to view sales results from a single salesperson, select that salesperson’s name and select all the products you want to examine to see how many sales that person made for each product, as shown in Figure 10-26.
You can remove a slicer pane by right-clicking the slicer pane and, when a pop-up menu appears, choosing Remove.
Pivot tables can contain rows and columns of numbers that you may find easier to understand by converting them into a chart, called a PivotChart.
PivotCharts are like other types of charts except you can selectively display (or hide) different data. This lets you create a chart showing sales from all your salespeople, and then selectively hide all data except anything sold by a single salesperson, as shown in Figure 10-27.
To create a PivotChart, follow these steps:
Click the pivot table you want to turn into a chart.
The PivotTable Tools tabs (Analyze and Design) appear.
Click the Analyze tab and click the PivotChart icon in the Tools group.
An Insert Chart dialog box appears.
Click a chart type (such as Pie or Line), click a specific chart design, and click OK.
Your PivotChart appears, displaying your categories directly on the chart.
Click the downward-pointing arrow of a category that appears on your PivotChart.
A menu appears, as shown in Figure 10-28.
Clear the check boxes next to the items that you don’t want to display and select the check boxes next to the items that you do want to display.
Your PivotChart displays a chart that represents only your selected data, as shown in Figure 10-29.
3.145.162.235