- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Part V: Analyzing Data with Excel
Grouping by time
Figure 35.10 shows a set of data in columns A:B. Each row is a reading from a measurement instrument, taken at one-minute intervals throughout an entire day. The table has 1,440 rows, each representing one minute. The pivot table summarizes the data by hour.
On the CD
This workbook, named hourly readings.xlsx, is available on the companion CD-ROM. n
Here are the settings I used for this pivot table:
•The Values area has three instances of the Reading field and each instance displays a different summary method (Average, Minimum, and Maximum). To change the summary method for a column, right-click any cell in the column and choose the Summarize Values By and then appropriate option.
•The Time field is in the Row Labels section, and I used the Grouping dialog box to group by Hours.
FIGURE 35.10
This pivot table is grouped by Hours.
Creating a Frequency Distribution
Excel provides a number of ways to create a frequency distribution (see Chapter 13), but none of these methods is easier than using a pivot table.
722
Chapter 35: Analyzing Data with Pivot Tables
Figure 35.11 shows part of a table of 221 students and the test score for each. The goal is to determine how many students are in each 10-point range (1–10, 11–20, and so on).
FIGURE 35.11
Creating a frequency distribution for these test scores is simple.
On the CD
This workbook, named test scores.xlsx, is available on the companion CD-ROM. n
The pivot table is simple:
•The Score field is in the Row Labels section (grouped).
•Another instance of the Score field is in the Values section (summarized by Count).
The Grouping dialog box that generated the bins specified that the groups start at 1, end at 100, and are incremented by 10.
Note
By default, Excel does not display items with a count of zero. In this example, no test scores are less than 21, so the 1–10 and 11–20 items are hidden. To force the display of empty bins, choose PivotTable Tools Options
Field Settings to display the Field Settings dialog box. Click the Layout & Print tab, and select Show Items with No Data. n
Figure 35.12 show the frequency distribution of the test scores, along with a pivot chart. (See “Creating Pivot Charts,” later in this chapter).
723
Part V: Analyzing Data with Excel
FIGURE 35.12
The pivot table and pivot chart show the frequency distribution for the test scores.
Note
This example uses the Excel Grouping dialog box to create the groups automatically. If you don’t want to group in equal-sized bins, you can create your own groups. For example, you may want to assign letter grades based on the test score. Select the rows for the first group, right-click, and then choose Group from the shortcut menu. Repeat these steps for each additional group. Then replace the default group names with more meaningful names. n
Creating a Calculated Field or
Calculated Item
Perhaps the most confusing aspect of pivot tables is calculated fields versus calculated items. Many pivot table users simply avoid dealing with calculated fields and items. However, these features can be useful, and they really aren’t that complicated once you understand how they work.
First, some basic definitions:
•A calculated field: A new field created from other fields in the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated field is to add a new column to the table, and create a formula to perform the desired calculation. A calculated field must reside in the Values area of the pivot table. You can’t use a calculated field in the Column Labels, in the Row Labels, or in a Report Filter.
•A calculated item: Uses the contents of other items within a field of the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated item is to insert one or more rows and write formulas that use values in other rows. A calculated item must reside in the Column Labels, Row Labels, or Report Filter area of a pivot table. You can’t use a calculated item in the Values area.
724
Chapter 35: Analyzing Data with Pivot Tables
The formulas used to create calculated fields and calculated items aren’t standard Excel formulas. In other words, you don’t enter the formulas into cells. Rather, you enter these formulas in a dialog box, and they’re stored along with the pivot table data.
The examples in this section use the worksheet table shown in Figure 35.13. The table consists of five columns and 48 rows. Each row describes monthly sales information for a particular sales representative. For example, Amy is a sales rep for the North region, and she sold 239 units in January for total sales of $23,040.
FIGURE 35.13
This data demonstrates calculated fields and calculated items.
On the CD
A workbook demonstrating calculated fields and items is available on the companion CD-ROM. The file is named calculated fields and items.xlsx.
Figure 35.14 shows a pivot table created from the data. This pivot table shows Sales (Values area), cross-tabulated by Month (Row Labels) and by SalesRep (Column Labels).
The examples that follow create
•A calculated field, to compute average sales per unit
•Four calculated items, to compute the quarterly sales commission
725
Part V: Analyzing Data with Excel
FIGURE 35.14
This pivot table was created from the sales data.
Creating a calculated field
Because a pivot table is a special type of range, you can’t insert new rows or columns within the pivot table, which means that you can’t insert formulas to perform calculations with the data in a pivot table. However, you can create calculated fields for a pivot table. A calculated field consists of a calculation that can involve other fields.
A calculated field is basically a way to display new information (derived from other fields) in a pivot table. It essentially presents an alternative to creating a new column field in your source data. In many cases, you may find it easier to insert a new column in the source range with a formula that performs the desired calculation. A calculated field is most useful when the data comes from a source that you can’t easily manipulate — such as an external database.
In the sales example, for example, suppose that you want to calculate the average sales amount per unit. You can compute this value by dividing the Sales field by the Units Sold field. The result shows a new field (a calculated field) for the pivot table.
Use the following procedure to create a calculated field that consists of the Sales field divided by the Units Sold field:
1.Select any cell within the pivot table.
2.Choose PivotTable Tools Options Calculations Fields, Items & Sets Calculated Field. Excel displays the Insert Calculated Field dialog box.
3.Enter a descriptive name in the Name box and specify the formula in the Formula box (see Figure 35.15). The formula can use worksheet functions and other fields from
726
Chapter 35: Analyzing Data with Pivot Tables
the data source. For this example, the calculated field name is Average Unit Price, and the formula is
=Sales/’Units Sold’
4.Click Add to add this new field.
5.Click OK to close the Insert Calculated Field dialog box.
FIGURE 35.15
The Insert Calculated Field dialog box.
Note
You can create the formula manually by typing it or by double-clicking items in the Fields list box. Doubleclicking an item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds single quotes around the field name. n
After you create the calculated field, Excel adds it to the Values area of the pivot table (and it also appears in the PivotTable Field List). You can treat it just like any other field, with one exception: You can’t move it to the Row Labels, Column Labels, or Report Filter areas. It must remain in the Values area.
Figure 35.16 shows the pivot table after adding the calculated field. The new field displayed Sum of Average Unit Price, but I shortened this label to Avg Price. I also changed the style to display banded columns.
Tip
The formulas that you develop can also use worksheet functions, but the functions can’t refer to cells or named ranges. n
727
Part V: Analyzing Data with Excel
FIGURE 35.16
This pivot table uses a calculated field.
Inserting a calculated item
The preceding section describes how to create a calculated field. Excel also enables you to create a calculated item for a pivot table field. Keep in mind that a calculated field can be an alternative to adding a new field to your data source. A calculated item, on the other hand, is an alternative to adding a new row to the data source — a row that contains a formula that refers to other rows.
In this example, you create four calculated items. Each item represents the commission earned on the quarter’s sales, according to the following schedule:
•Quarter 1: 10% of January, February, and March sales
•Quarter 2: 11% of April, May, and June sales
•Quarter 3: 12% of July, August, and September sales
•Quarter 4: 12.5% of October, November, and December sales
Note
Modifying the source data to obtain this information would require inserting 16 new rows, each with formulas. So, for this example, creating four calculated items may be an easier task. n
To create a calculated item to compute the commission for January, February, and March, follow these steps:
1.Move the cell pointer to the Row Labels or Column Labels area of the pivot table and choose PivotTable Tools Options Calculations Fields, Items & Sets Calculated Item. Excel displays the Insert Calculated Item dialog box.
728
Chapter 35: Analyzing Data with Pivot Tables
2.Enter a name for the new item in the Name field and specify the formula in the Formula field (see Figure 35.17). The formula can use items in other fields, but it can’t use worksheet functions. For this example, the new item is named Qtr1 Commission, and the formula appears as follows:
=10%*(Jan+Feb+Mar)
3.Click Add.
4.Repeat Steps 2 and 3 to create three additional calculated items:
Qtr2 Commission: = 11%*(Apr+May+Jun)
Qtr3 Commission: = 12%*(Jul+Aug+Sep)
Qtr4 Commission: = 12.5%*(Oct+Nov+Dec)
5. Click OK to close the dialog box.
FIGURE 35.17
The Insert Calculated Item dialog box.
Note
A calculated item, unlike a calculated field, does not appear in the PivotTable Field List. Only fields appear in the field list. n
Caution
If you use a calculated item in your pivot table, you may need to turn off the Grand Total display for columns to avoid double counting. In this example, the Grand Total includes the calculated items, so the commission amounts are included with the sales amounts. To turn off Grand Totals, choose PivotTable Tools Design Layout Grand Totals. n
729
Part V: Analyzing Data with Excel
After you create the calculated items, they appear in the pivot table. Figure 35.18 shows the pivot table after adding the four calculated items. Notice that the calculated items are added to the end of the Month items. You can rearrange the items by selecting the cell and dragging its border.
Another option is to create two groups: One for the sales numbers, and one for the commission calculations. Figure 35.19 shows the pivot table after creating the two groups and adding subtotals.
FIGURE 35.18
This pivot table uses calculated items for quarterly totals.
FIGURE 35.19
The pivot table, after creating two groups and adding subtotals.
730