- •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
Chapter 33: Getting Data from External Database Files
Note
When you add tables to a query, the Tables pane in Query connects the linked fields with a line between the tables. If no links exist, you can create a link yourself by dragging a field from one table to the corresponding field in the other table. n
Adding and editing records in external database tables
To add, delete, and edit data when you’re using Query, make sure that a check mark appears next to the Records Allow Editing menu item. Of course, you’ll need the proper permissions, and you can’t edit a database file that’s set up as read-only.
Caution
Be careful with this feature because your changes are saved to disk as soon as you move the cell pointer out of the record that you’re editing. You do not need to choose File Save. n
Formatting data
If you don’t like the data’s appearance in the Data pane, you can change the font used by choosing Format Font. Be aware that selective formatting isn’t allowed (unlike in Excel); changing the font affects all the data in the Data pane.
Tip
If you need to view the data in the Data pane in a different order, choose Records Sort (or click the Sort Ascending or Sort Descending toolbar icon). n
Learning More about Query
This chapter isn’t intended to cover every aspect of Microsoft Query; rather, it discusses the basic features that are used most often. In fact, if you use the Query Wizard, you may never need to interact with Query itself. But if you do need to use Query, you can experiment and consult the online Help to learn more. As with anything related to Excel, the best way to master Query is to use it — preferably with data that’s meaningful to you.
693
CHAPTER
Introducing Pivot
Tables
The Pivot Table feature is perhaps the most technologically sophisticated component in Excel. With only a few mouse clicks, you can slice and dice a data table in dozens of different ways and produce
just about any type of summary you can think of.
If you haven’t yet discovered the power of pivot tables, this chapter provides an introduction, and Chapter 35 continues with many examples that demonstrate how easy it is to create powerful data summaries using pivot tables.
About Pivot Tables
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data.
For example, a pivot table can create frequency distributions and crosstabulations of several different data dimensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovative aspect of a pivot table is its interactivity. After you create a pivot table, you can rearrange the information in almost any way imaginable and even insert special formulas that perform new calculations. You even can create post hoc groupings of summary items (for example, combine Northern Region totals with Western Region totals). And the icing on the cake: With a few mouse clicks, you can apply formatting to a pivot table to convert it into an attractive report.
IN THIS CHAPTER
An introduction to pivot tables
Types of data appropriate for a pivot table
Pivot table terminology
How to create pivot tables
Pivot table examples that answer specific questions about data
695
Part V: Analyzing Data with Excel
One minor drawback to using a pivot table is that unlike a formula-based summary report, a pivot table does not update automatically when you change information in the source data. This drawback doesn’t pose a serious problem, however, because a single click of the Refresh button forces a pivot table to update itself with the latest data.
Pivot tables were introduced in Excel 97. Unfortunately, many users overlook this feature because they think it’s too complicated. The pivot table feature was improved significantly in Excel 2007, and you’ll find a few new twists in Excel 2010. Creating and working with pivot tables is easier than ever.
A pivot table example
The best way to understand the concept of a pivot table is to see one. Start with Figure 34.1, which shows a portion of the data used in creating the pivot table in this chapter.
FIGURE 34.1
This table is used to create a pivot table.
This table consists of a month’s worth of new account information for a three-branch bank. The table contains 712 rows, and each row represents a new account. The table has the following columns:
696
Chapter 34: Introducing Pivot Tables
•The date the account was opened
•The day of the week the account was opened
•The opening amount
•The account type (CD, checking, savings, or IRA)
•Who opened the account (a teller or a new-account representative)
•The branch at which it was opened (Central, Westside, or North County)
•The type of customer (an existing customer or a new customer)
On the CD
This workbook, named bank accounts.xlsx, is available on the companion CD-ROM. n
The bank accounts database contains quite a bit of information. In its current form, though, the data doesn’t reveal much. To make the data more useful, you need to summarize it. Summarizing a database is essentially the process of answering questions about the data. Following are a few questions that may be of interest to the bank’s management:
•What is the daily total new deposit amount for each branch?
•Which day of the week accounts for the most deposits?
•How many accounts were opened at each branch, broken down by account type?
•What’s the dollar distribution of the different account types?
•What types of accounts do tellers open most often?
•How does the Central branch compare with the other two branches?
•In which branch do tellers open the most checking accounts for new customers?
You can, of course, spend time sorting the data and creating formulas to answer these questions. But almost always, a pivot table is a better choice. Creating a pivot table takes only a few seconds, doesn’t require a single formula, and produces a nice-looking report. In addition, pivot tables are much less prone to error than creating formulas. (Later in this chapter, you’ll see several pivot tables that answer the preceding questions.)
Figure 34.2 shows a pivot table created from the bank data. This pivot table shows the amount of new deposits, broken down by branch and account type. This particular summary represents one of dozens of summaries that you can produce from this data.
Figure 34.3 shows another pivot table generated from the bank data. This pivot table uses a dropdown Report Filter for the Customer item (in row 1). In the figure, the pivot table displays the data only for Existing customers. (The user can also select New or All from the drop-down control.) Notice the change in the orientation of the table? For this pivot table, branches appear as column labels, and account types appear as row labels. This change, which took about five seconds to make, is another example of the flexibility of a pivot table.
697
Part V: Analyzing Data with Excel
FIGURE 34.2
A simple pivot table.
FIGURE 34.3
A pivot table that uses a report filter.
Data appropriate for a pivot table
A pivot table requires that your data is in the form of a rectangular database. You can store the database in either a worksheet range (which can be a table or just a normal range) or an external database file. And although Excel can generate a pivot table from any database, not all databases benefit.
Generally speaking, fields in a database table consist of two types:
•Data: Contains a value or data to be summarized. For the bank account example, the Amount field is a data field.
•Category: Describes the data. For the bank account data, the Date, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field.
Note
A database table that’s appropriate for a pivot table is said to be “normalized.” In other words, each record (or row) contains information that describes the data. n
698
Chapter 34: Introducing Pivot Tables
Why “Pivot?”
Are you curious about the term “pivot?”
Pivot, as a verb, means to rotate or revolve. If you think of your data as a physical object, a pivot table lets you rotate the data summary and look at it from different angles or perspectives. A pivot table allows you to move fields around easily, nest fields within each other, and even create ad hoc groups of items.
If you were handed a strange object and asked to identify it, you’d probably look at it from several different angles in an attempt to figure it out. Working with a pivot table is similar to investigating a strange object. In this case, the object happens to be your data. A pivot table invites experimentation, so feel free to rotate and manipulate the pivot table until you’re satisfied. You may be surprised at what you discover.
A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters.
Exceptions exist, however, and you may find the Excel Pivot Table feature useful even for databases that don’t contain actual numerical data fields. Chapter 35 has an example of a pivot table created from non-numeric data.
Figure 34.4 shows an example of an Excel range that is not appropriate for a pivot table. You might recognize this data from the outline example in Chapter 26. Although the range contains descriptive information about each value, it does not consist of normalized data. In fact, this range actually resembles a pivot table summary, but it is much less flexible.
FIGURE 34.4
This range is not appropriate for a pivot table.
Figure 34.5 shows the same data, but normalized. This range contains 78 rows of data — one for each of the six monthly sales values for the 13 states. Notice that each row contains category information for the sales value. This table is an ideal candidate for a pivot table, and contains all information necessary to summarize the information by region or quarter.
699
Part V: Analyzing Data with Excel
FIGURE 34.5
This range contains normalized data and is appropriate for a pivot table.
Figure 34.6 shows a pivot table created from the normalized data. As you can see, it’s virtually identical to the non-normalized data shown in Figure 34.4.
On the CD
This workbook, named normalized data.xlsx, is available on the companion CD-ROM. n
FIGURE 34.6
A pivot table created from normalized data.
700