Questions
About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars
Message Board
MrExcel Store
Podcast
Search
Media
Contact
Home
Having an Excel Emergency?


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.

All contents Copyright 1998-2009 by MrExcel Consulting.