Crunching Data with Pivot Tables in Excel 2003

Learn the ins and outs of Pivot Tables with Bill Jelen

Seven videos covering over 1 hour of Pivot Table tips. Learn how to create your first Pivot Table and progress to really crunching the numbers in your data by using calculations and creating reports.

Click on a video to watch it. Click on the four arrows to enlarge the video to full screen. Click on the four arrows again or press escape on your keyboard to return to this page.

Part 1 - Creating Your First Pivot Table
After watching this video, you should be able to take a transactional data set in Excel and create a 1-page summary table in six clicks.

Part 2 - Pivot Table Gotchas
After viewing this video you will understand the problems with pivot tables:

Pivot tables do not automatically recalculate until you click the red exclamation button to refresh.
You can not move or change part of a pivot table.
Problems when formulas outside the pivot table point inside the pivot table.

Part 3 - Formatting a Pivot Table
This chapter talks about how to make your pivot table look good. You will learn how to:

Replace blanks with zeroes
Change number formats
Multiple fields in data area
Avoid built-in formats
Create your own formats
Manually resequence
Fill in blanks in column area

Part 4 - Pivot Table Calculations
Learn how to really crunch the numbers in your data by using calculations such as:

Sum, Count, Max, Min
% of Total
Running Total
Calculated Fields

Part 5 - Pivot Table Power
Learn about these power features in pivot tables:

Drill down
Expand & collapse
Grouping daily dates to months
AutoSort
Top 10 Report
Adding new data to original data set
Replicating a report for every customer

Part 6 - Pivot Table Applications
Chapter 6 shows some common applications that can be solved with pivot tables such as:

Find unique list of customers
Matching two lists
Pareto analysis
Fixing customer names

Part 7 - Making Data Pivotable
To have success with pivot tables, you need to start with good data. This chapter talks about a data set with horrible data and how to fix it. During this lesson, you will learn how to:

Deal with a column that contains both Model and Region.
Break the model headings out to a new column.
Fill in blank cells in an "outline view" of the data.
Delete blank rows by sorting to the bottom.
Convert time columns that go across the worksheet into rows that go down the worksheet.

The book Pivot Table Data Crunching by Bill Jelen is another resource for Pivot Table information. Pivot Table Data Crunching for Microsoft Excel 2007 is also available.

There are over 150,000 pages at MrExcel.com. Use Google to search our site:

MrExcel Consulting can be hired to implement this concept, or many other cool applications, with your data.

Privacy Policy

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.