ExcelCream.

com

The Pivot Table Tutorial

The

Pivot Table Tutorial

By John Franco

www.ExcelCream.com

2 | Page

The Pivot Table Tutorial The Pivot Table Tutorial by John Franco 2010 by ExcelCream.com Notice of Rights All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher. Notice of Liability The author and publisher have made every effort to ensure the accuracy of the information herein. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors and ExcelCream.com, nor its dealers or distributors, will be held liable for any damages to be caused either directly or indirectly by the instructions contained in this book, or by the software or hardware products described herein. Trademark Notice Rather than indicating every occurrence of a trademarked name as such, this book uses the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark. Cover image Kurhan - Fotolia.com

www.ExcelCream.com

3 | Page

The Pivot Table Tutorial

TOC
TOC INTRODUCTION 4 5

PRINCIPLE #1: MAKE A CLEAR DISTINCTION OF THE SOURCE TABLE FIELDS 7 PRINCIPLE #2 MAKE A CLEAR DISTINCTION OF THE PIVOT TABLE REPORT ELEMENTS PRINCIPLE #3 DROP THE FIELDS TO THE CORRECT FIELD AREA PRINCIPLE #4 THE ORDER OF THE CATEGORY FIELDS COUNTS 9 13 17

PRINCIPLE #5 YOU CAN SUMMARIZE THE NUMERIC FIELDS IN SEVERAL WAYS 19 PRINCIPLE #6 THERE ARE MAINLY 5 TYPE OF REPORTS PRINCIPLE #7 YOU WILL ALWAYS ENCOUNTER SOME PROBLEMS PIVOT TABLE RESOURCES ABOUT JOHN 20 25 29 30

www.ExcelCream.com

4 | Page

The Pivot Table Tutorial

Introduction
A Pivot Table allows you to quickly slice and dice information from any large table you can imagine. For example: you can crunch the "Source Table" shown below in several ways

Sales by Group Total sales for each product group. See the table below...

Product Sales Total sales for each product, organized by group Q1 & Q2 & Q3 & Q4 compares the sales between quarters. See the table below...

www.ExcelCream.com

5 | Page

The Pivot Table Tutorial

Multiple Subtotals calculates additional summaries: the average, largest, and smallest sales for each group Average Sales provides the average sales amount for each product Top 3 Products Identifying the three best-selling products within each group All Quarters Sales for each product and group by quarter And more ways It appears complex at first but dont worry, if you can drag the mouse, you can create a basic Pivot Table Report. Open your mind.... and lets grasp the 7 Timeless Principles of working with Pivot Tables. This is my promise... You will use PTs intuitively for the rest of your life!

www.ExcelCream.com

6 | Page

The Pivot Table Tutorial

Principle #1: Make a Clear Distinction of the Source Table Fields

Every "Source Table" has two types of fields: category field and numeric field, let's see a clear example:

Now, let's explore some details...

Category field
In the above "Source Table", you have three Category fields: Group, Product and Quarter. Imagine categories as the units of data you want information about. For example, you can slice the Sales by Group, by Product and by Quarter. A category field usually contains texts that are repeated across the lines. For example, the "Quarter field" contains: Q 1, Q 2, Q 1, Q 1, Q 4, etc. See the table above

Numeric field
In the above "Source Table", you have one Numeric field: Sales.

www.ExcelCream.com

7 | Page

The Pivot Table Tutorial Imagine a numeric field as the source of information for categories; for example: you can know the total, the average, or the max Sales of any category: Group, Product or Quarter. There are other types of numeric fields like: Profit, Losses, Salaries, etc. Take into account that this type of field is necessarily a number. See the table above

www.ExcelCream.com

8 | Page

The Pivot Table Tutorial

Principle #2 Make a Clear Distinction of the Pivot Table Report Elements

Use the sample file provided with my e-book, (the-essential-pivottable guide_example.xls) to make it by yourself and get a better understanding. First, launch the Pivot Table wizard; do it this way Excel 2007 users must do the following: 1. Place inside the table range (data region) and then 2. Go to Insert>Tables>Table>Ok or press CTRL + T. The table is created 3. Go to Design>Tools>Summarize with Pivot Table 4. Choose the destination of your PT in the Create Pivot Table wizard: new or existing worksheet and 5. Press Ok Excel 2003 users must do the following: 1. Place inside the table range (data region) and then 2. Go to Data>Pivot Table and PivotChart Report 3. Choose Next in the wizard step 1 of 3 4. Choose Next or change the range for the data source in the wizard step 2 of 3 5. Choose the destination of your PT: new or existing worksheet, and 6. Press Finish in the wizard step 3 of 3 Once you have done the above steps, you will have two main components: The Drop Data panel The Pivot Table Field List dialog

www.ExcelCream.com

9 | Page

The Pivot Table Tutorial

The drop data panel

This is an area of the worksheet which is especially created by Excel to receive the Fields from the Pivot Table Field List dialog. See below

www.ExcelCream.com

10 | P a g e

The Pivot Table Tutorial

The Pivot Table Field List dialog

This is an area of the worksheet which is created by Excel, specifically to show the Fields from the Source Table It is called the Pivot Table Field List dialog. See below

It is important to note that the Excel area (as highlighted in the graphics above) is reflected from the "Source Table". In other wordsthose fields you see there are the fields of the "Source Table". These fields can be dragged and dropped to the four main data areas Row labels (row area for Excel 2003) Column labels (column area for Excel 2003) Values (data area for Excel 2003) Report Filter (page area for Excel 2003)

www.ExcelCream.com

11 | P a g e

The Pivot Table Tutorial To create a Pivot Table report just drag and drop the fields appropriately to the drop data area (see Principle #3).

www.ExcelCream.com

12 | P a g e

The Pivot Table Tutorial

Principle #3 Drop the Fields to the Correct Field Area

You already know the building-block concepts to build a Pivot Table report: 1) you have made the distinctions of the "Source Table" fields and 2) you are familiar with the elements of the Pivot Table command.

Now you will learn how to configure a PT report. A Pivot Table report is built in front of you as you drop the fields to the data area APPROPRIATELY. Drag the fields from the Pivot Table Field List dialog to the Drop Fields area. Excel 2007 users can drag and drop the fields to the User area shown below.

www.ExcelCream.com

13 | P a g e

The Pivot Table Tutorial

Excel 2003 users can add fields to the appropriate areas by using the Add to button in the User area shown below.

www.ExcelCream.com

14 | P a g e

The Pivot Table Tutorial Or you can drag and drop the fields from the Pivot Table Field List dialog directly to the Drop Fields area. See below

Now; each time you drop a field, you will have the report taking shape in front of you. Finally you have it

www.ExcelCream.com

15 | P a g e

The Pivot Table Tutorial Important reminder: Drop the Numeric fields to the Data area only. Can you drop a Category field to the Data area? The answer is YES. You can put a non-numeric field (category field) on the Data area, but you can only count texts, not average or sum them. You can try it A Sum will result in 0 A Max and Min will result in 0 An Average will result in #DIV/0! The graphic below has the Field called Group on the Data area

www.ExcelCream.com

16 | P a g e

The Pivot Table Tutorial

Principle #4 The Order of the Category Fields Counts

Place the fields in the order that you need them to be summarized in the report. For example:

Product and Quarter (in the row area)

Quarter and Product (in the row area)

www.ExcelCream.com

17 | P a g e

The Pivot Table Tutorial

Quarter and Group (in the column area)

Group and Quarter (in the column area)

www.ExcelCream.com

18 | P a g e

The Pivot Table Tutorial

Principle #5 You can Summarize the Numeric Fields in Several Ways

By default, Numeric Fields are summarized with the SUM function. But you can use various computation options SUM COUNT MAX, MIN AVERAGED And other numerical computations Excel 2007 users, click on the field arrow in the Values Area and choose Value Field settings, then set the parameters as you want, or right click over the cells that contain the data and choose Value Field settings from the menu Excel 2003 users right click over the cells that contain the data and choose Field settings from the menu

You can also set the number format. See the image above

www.ExcelCream.com

19 | P a g e

The Pivot Table Tutorial

Principle #6 There are Mainly 5 Type of Reports

Report #1 Single row

www.ExcelCream.com

20 | P a g e

The Pivot Table Tutorial

Report #2 Multiple row

www.ExcelCream.com

21 | P a g e

The Pivot Table Tutorial

Report #3 Columns

www.ExcelCream.com

22 | P a g e

The Pivot Table Tutorial

Report #4 Page Filters

www.ExcelCream.com

23 | P a g e

The Pivot Table Tutorial

Report #5 Combination of all the above types

www.ExcelCream.com

24 | P a g e

The Pivot Table Tutorial

Principle #7 You will Always Encounter some Problems

Dont panic if you dont find the Fields
If you don't see the PivotTable Field List, make sure that you click the PivotTable. If you still don't see the PivotTable Field List, do the following: Excel 2007 users: on the Options tab, in the Show/Hide group, click Field List Excel 2003 users: in the Pivot Table toolbar, choose Hide Field List. If you don't see the fields in the Field List that you want to use, refresh the PivotTable report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

Different Entries Result in a Different Category

If a given Product name (in the Product field) contains spaces or wrong spelling, the Pivot Table will show it as a different item, for example: Boston Crab and Boston Crabs are different entries, they will be summarized accordingly. If you want Excel to summarize these entries as one, you must uniform the data in the "Source table".

www.ExcelCream.com

25 | P a g e

The Pivot Table Tutorial

Dont frustrate too quickly when you cannot set the order of the fields in the Drop Fields Area
Dont lose sight of the four areas of the Drop Data panel. Be aware of this when you have already dropped and dragged fields. See the graphic below

www.ExcelCream.com

26 | P a g e

The Pivot Table Tutorial

Too many columns to the right

If you drop a category field to the column drop area, you will have as many columns as groups of data to the right. For example: if you drop the Product field to the column area you would have as many columns as Products. Unfortunately, you cannot specify in advance the set of Products you want to show. I recommend that you apply a filter to the desired field after dropping it. Click the arrow that is shown for each field in the drop panel and pick the items you want to show.

www.ExcelCream.com

27 | P a g e

The Pivot Table Tutorial Well You are ready; this is all you need to know to get a good strong start on Pivot Tables. Whats next? Go and explore your chunk of data. You will truly become a Pivot Table master when you make sense of your own data. DONT BE AFRAID OF MAKING MISTAKES, if the produced report is not the one you want, just drag and drop another field, move a field from one data area to the other, change the order, etc. Enjoy your data!

I am John Franco - of Ecuador South America. I invite you to visit me at my blog www.ExcelCream.com where we can become better acquainted. I will show you many amazing truths of Excel.

John Franco ExcelCream.com

www.ExcelCream.com

28 | P a g e

The Pivot Table Tutorial

Pivot Table Resources

Excel 2007 Pivot Table video tutorial Excel 2003 Pivot Table video tutorial http://www.lacher.com/toc/tutpiv.htm http://www.lacher.com/toc/tutpiv.htmhttp://www.lacher.com/t oc/tutpiv.htm http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://office.microsoft.com/en-us/training/ default.aspx

www.ExcelCream.com

29 | P a g e

The Pivot Table Tutorial

About John
John Franco is native of Ecuador, he is a Civil Engineer and a Bachelor in Applied Linguistics with focus on creating systems for work, his long term objective in life is helping others to gain momentum in the application of ideas. For over seven years, he worked as a Civil Engineer for Norberto Odebrecht Construction Company (the 44th largest construction contracting firm from around the world according to Engineering News Record 2008). During that time, He had an Excel Maven Boss; Nilton Teti, one of those old-time-Excel-geniuses who makes precious models, uses just the keyboard and hates BI corporate packages. His almost religious fervor for Excel, and his amazing knowledge, was the source of his great interest in Spreadsheets. During all these years he really experienced the professional benefits of using Excel to accomplish his duties; he says to you that being skilled in Excel gives you a tremendous advantage at the office and in your career! Having always been very entrepreneurial in his nature, he quit his job at Norberto Odebrecht in order to devote his full passion and knowledge of advanced Excel methods to others around the world who can benefit from it. His first entrepreneurial initiative was the site www.Excel-Spreadsheet-Authors.com, that later evolved to http://www.ExcelCream.com; a blog dedicated to Mid/Advanced Excel users so they know the quintessence of Excel spreadsheet and reach higher productivity and clarity. Email him at: john@excelcream.com www.ExcelCream.com 30 | P a g e