Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Microsoft Excel Online

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Microsoft Excel Online« Back to blogAboutArchivesAsk an Excel Question TrainingProducts
Chandoo.org - Learn Excel and Charting Online

Kickass Stuff!
Excel Tips
Excel Charting
Excel Dashboards
Project Mgmt.
Formulas
Downloads
Subscribe

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one
Posted on August 19th, 2009 in Excel Howtos , Featured , Learn Excel , Pivot Tables & Charts - 48 comments

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data.

In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.

In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.

Excel Pivot Tables: Tutorial

Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial
Example uses of Pivot Tables

As I said before pivot tables are very powerful and useful. There are numerous uses of pivot tables that we can talk about them until Christmas.

Here are some example uses of pivot tables:

Summarizing data like finding the average sales for each region for each product from a product sales data table.
Listing unique values in any column of a table [learn more]
Creating a pivot report with sub-totals and custom formats
Making a dynamic pivot chart
Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
Transposing data – i.e. moving rows to columns or columns to rows. [learn more]
Linking data sources outside excel and be able to make pivot reports out of such data.

Excel Pivot Table Tutorial: How to create your first pivot table

Let us make your first pivot table. We will use example data in the following format. Download the excel pivot tables tutorial workbook with the data.
Pivot Tables in Excel - Tutorial

Step 1: Select the data
Select the data range from which you want to make the pivot table.

Insert Pivot Table in Excel WorksheetStep 2: Go to Insert ribbon and click on new Pivot table option
To insert a new pivot table in to your spreadsheet, go to Insert ribbon and click pivot table icon and select pivot table option.

Step 3: Select the target cell where you want to place the pivot table. For starters, select New worksheet.
Excel will display a pivot table wizard where you can specify the pivot table target location etc. Select “New worksheet” option and your pivot table will be placed in newly created worksheet.

Step 4: Make your first pivot report

The pivot report UI is very intuitive and sandbox like. To make powerful analysis, all you have to do is drag and drop fields in to the pivot table grid area. In excel 2007, you can also control this by using the “Pivot table panel”.

The pivot report is divided in to header and body sections. You can drag and drop the fields you want in each area. The body itself contains three parts. Rows, Columns and Cells. You can use any fields in these areas too.

For the above sample data, I have set this criteria:

Pivot Table Settings - Row, Column, Header and content settings

And the outcome is this pivot report.

Example Pivot Report - Excel Pivot Tables

It might be a bit difficult to understand how this works. But believe me, if you have seen any reports or worked with any other reporting systems, then the idea of pivot tables, pivot reports and pivot charts becomes quite simple to you.

You can use the excel pivot table features to make a more complicated pivot report like this in no time.
Example Pivot Report - A very detailed Pivot Table with sub-totals and totals
Some useful tips on Excel Pivot Tables

You can apply any formatting to the pivot tables. MS Excel has some very good pivot table formats (and they are better in Excel 2007 and 2010).
You can easily change the pivot table summary formulas. Right click on pivot table and select “summerize data by” option.
You can also apply conditional formatting on pivot tables although you may want to be a bit careful as pivot tables scale in size depending on the data.
Whenever the original data from which pivot tables are constructed, just right click on the pivot table and select “Refresh Data” option.
If you want to drill down on a particular summary value, just double click on it. Excel will create a new sheet with the data corresponding to that pivot report value. (This is extremely useful)
Making a pivot chart from a pivot table is very simple. Just click on the pivot chart icon from tool bar or Options ribbon area and follow the wizard.
More Pivot Table Tips & Tricks

Download the excel pivot tables tutorial workbook and practice yourself

Click here to download the excel pivot tables tutorial workbook. [.zip version of tutorial here]The workbook has sample data and one pivot table in it. You can play with it to learn more.
Checkout the video tutorial to make excel pivot tables

Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial
Share your experiences of using pivot tables

Tell me how you use pivot tables, your favorite tricks using comments.
Join Excel School & Learn Pivot Tables, Data Analysis & More

I run an online Excel training program called as Excel School where you can learn Pivot tables, data analysis, dashboard reporting, charting, formulas and so much more in a step-by-step fashion.

Learn more about Excel School.

Spread some love,
It makes you awesome!

Posts & Navigation

« Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]
50 Best Cities for Finding a Job [Incell Dashboard using Excel] »
Go Back to Home Page
Have an Excel Question?

Tags: Analytics, data, data processing, downloads, Excel Howtos, Learn Excel, pivot charts, pivot tables, spreadcheats, spreadsheets, tutorials


Trackbacks & Pingbacks

Pingback by Pivot Table in Excel - Video Tutorial | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on August 25, 2009 @ 9:36 am

Pingback by Microsoft Excel Table Tips and Tricks - Learn Data Tables and Become a Data God | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on October 13, 2009 @ 9:12 am

Pingback by What is Excel PowerPivot and How to use it? - Review of Microsoft PowerPivot Addin | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on January 8, 2010 @ 9:24 am

Pingback by Excel Pivot Table tricks & tips | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on January 27, 2010 @ 11:40 am

Pingback by Profit & Loss Reporting using Microsoft Excel - Accounting & Excel - Part 1 of 6 | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on February 4, 2010 @ 9:19 am

Pingback by MS-Excel Tipps für Projektleiter on March 3, 2010 @ 11:53 am

Pingback by 31 Excel Tutorials – Learn and Be Awesome in Excel | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on March 11, 2010 @ 1:01 pm

Pingback by Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula] | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on April 29, 2010 @ 12:46 am

Pingback by Percentage Difference From One Year to Prior Year « Empower Your Business with Excel on May 17, 2010 @ 9:20 pm

Pingback by Remove duplicates & sort a list using Pivot Tables | Chandoo.org - Learn Microsoft Excel Online on September 27, 2010 @ 11:06 am

Pingback by An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial] | Chandoo.org - Learn Microsoft Excel Online on November 25, 2010 @ 1:26 am

Pingback by Show Top 10 Values in Dashboards using Pivot Tables | Chandoo.org - Learn Microsoft Excel Online on December 1, 2010 @ 10:18 am

Pingback by Make Dynamic Dashboards using Excel 2010 [Video Tutorial & FREE Download Workbook] | Chandoo.org - Learn Microsoft Excel Online on December 8, 2010 @ 8:36 am

Pingback by What are Pivot Table Report Filters and How to use them? | Chandoo.org - Learn Microsoft Excel Online on April 20, 2011 @ 8:59 am


Comments
1) Glen Feechan August 20, 2009

Nice clear article, good to see someone else trumpeting the value of pivot tables. If you read this and are still not sure, you can learn by video:

Excel 2003 version

Excel 2007 version

It is well worth making the effort to learn pivot tables – you won’t look back once you do.
2) Andrea November 5, 2009

Dear Expert,
How can I replace the “Sum of” to “Different of” in a pivot table? I have a set of data that consists of both 2007 and 2008 sales, however these info were in one same column that name “period”. Could you kindly show me the formula that I can add into the Pivot table to show the comparisions?
Thanks a zillion
3) Meikel November 16, 2009

Hi Chandoo,

Well structured and presented as usual.
I’m doing a lot with pivot tables. What I am looking for is a tool where you can change the data directly in the pivot table.
Do you have an idea?

br, Meikel
4) louis November 17, 2009

I want to apply the same type of pivot table to a different file I get each month (but same format and type of data). Can I “save” a custom pivot table format ? (excel 2003)

Thanks
5) srikanth January 17, 2010

hi chandoo,
thanks. it was very simple and clear. really it is very usefull. it was well presented. great job.
6) TMS January 18, 2010

Thanks for posting this info. I’ve been struggling with pivot tables for some time now and this has cleared up a lot of my confusion. Nice job.
7) Chandoo January 18, 2010

@Louis… yes, you can save custom pivot formats…

@Srikant & TMS: thank you very much. I am happy you liked this tutorial.
8) Jeremiah Minifield February 4, 2010

Will this cover Excel 97 and Excel 2002?
9) Chandoo February 9, 2010

@Jeremiah… The steps are more or less similar in Excel 2002. I dont remember Excel 97 so cant say for it.
10) payal March 26, 2010

I have two data point per condition, is it possible to display both the data points either bewlo one another or next to each other in a pivot table. If so, how would i do it.
Since right now I can just display average, count or sum but not the actual raw data points.

Also can we have error bar in the pivot chart for a bar graph?
thanks for the help
11) baidi March 31, 2010

mantataf excelnya…………….. top markotop
12) michaelG May 6, 2010

My question is:

I have a body of data that I have placed in a pivot table.. I understand how to manipulate it to shpow me eithert the min, sum, average etccc of the data
however I dont know how I can have the pivot table express 2 different options like the min and the average??

in the corresponding chart i would like to see the min amount in each period however I would like for the graph to also show me the average in each period

for example the min would be displayed as a bar chart and the avg would be a line running through,

can anyone help me with this??
13) Chandoo May 10, 2010

@MichaelG: Just add the value field again to the “value field area” of pivot table. Now set value field settings to change the summary type to “average” for first value and “min” for second value. That is all.
14) Deep May 12, 2010

gr8
15) Hemant August 19, 2010

i want to learn excel, best and also focus on audit and accounts point of view so pls suggest me what should i do?
is there any simple but strong book or any other on line site for it.
16) Chandoo August 24, 2010

@Hemant: Try one of these books Excel Accounting books

PS: I have not read any of them
17) Shefeeque K A August 27, 2010

Its a great skill to present these complicated thing in such an easy way.
I tried a lots of Ebooks from online non of them give me currect use of pivot table but ur presentation helped me a lot. Good Job.

Thanks a lot.
18) rajesh August 30, 2010

how to create pivot table,macros, vlookups with examples please send
19) Glen Feechan August 30, 2010

In reply to Rajesh:

I recently posted the following post that has links to video tutorials on pivot tables, vlookups amd macros:

Top 5 Tips to make Excel Spreadsheets work for you

I hope they’re helpful.
20) 6tel October 10, 2010

Excellent information! My congratulations on this post. However, I was wondering how to edit a pivot table, because I’m having troubles on a pivot table I created four days ago… My pivot table is doing just fine (it’s a list of people to an event I’m organizing), but I’d like to add the information of a column I forgot to include days ago.

This pivot table has the people who’s most likely to come in a row category named “Yes”, and the ones that are most likely not coming on a row category named “No”. The pivot table shows the results of these two categories (final add of “yes” and “no”, both separately and altogether), and the names of each people on the “yes” and “no” category, but I’d like to add their e-mail information just beside the name of the person who’s coming and not coming without messing up the final total of the “yes” and “no” adding…

I tried editing all the conventional way (dragging and dropping the “e-mail” tag on the pivot table I once did), but it says I can’t overwrite (?) a pivot table with another pivot table (when I say “overwrite” I mean some verb that sounds like it but I’m not sure… My Excel software launches these alerts in spanish, since english is not my native language). I also tried dragging the “e-mail” tag to other parts of the pivot table but final result is not the one I’m looking for. Can anyone please help me? Thanks in advance.
21) John October 12, 2010

Hi 6tel,

Add the email column to the source table, this way…

Name Coming email
John Yes John@gmail.com
Ana Yes Ana@gmail.com
Will Yes Will@gmail.com
Albert No Albert@gmail.com
Susan Yes Susan@gmail.com

And then include the email column in the Pivot Table (in the Row Labels area)

You will have a summary report like this one…

Row Labels Count of Name
No 1
Albert@mm.com 1
Yes 4
Ana@mm.com 1
John@mm.com 1
Susan@mm.com 1
Will@mm.com 1
Grand Total 5

Give a look at this screen I captured for you…
http://www.excelcream.com/images/helpingothers/PivotTableAddField.jpg

Let me know if this helped you?
22) 6tel October 14, 2010

Wonderful, John. ¡Muchas gracias!
23) John Franco October 14, 2010

You are welcome!
24) John October 15, 2010

Very nice tutorial. Your article along with a video I saw on YouTube [ http://www.youtube.com/watch?v=HVa7PIDfi5A ] has now made me a champion of pivot tables!
25) Top10Lists October 26, 2010

This is a great tutorial with nice graphic to visualize the whole process. I would like to complement this post by pointing you to some great videos that teach you Excel pivot tables: http://www.squidoo.com/excel-pivot-tables

Hope this helps!
26) Anita October 29, 2010

Great tutorial…. Very precise & easy to understand. Thank you Chandoo….
27) Jeremy October 29, 2010

Excellent tutorial. I supplemented this to the video @ http://www.youtube.com/watch?v=HVa7PIDfi5A .. learned PivotTables in less than 30 mins after struggling for over a week!
28) SHRI November 11, 2010

I want to prepare a pivot table from data in an excel spreadsheet.
The data table lists activities in Col. A and dates for each of the activities in 2-set columns for each project across the sheet.
I want to be able to make a pivot table which will enable me to list activities and projects corresponding to each date.
How can I do this? Will you be able to show me how to if I send you the spreadsheet by email?
29) GVK November 18, 2010

Chandu sir,

You have very clearly explanined the pivot tables concept. And, it is very easy learn.I got the confident with one example that you have given, that I can do big things with data pilots. Thank you sir.
30) rediet February 27, 2011

thank u for the useful information
31) Shalini March 2, 2011

Hi, I am trying to use Pivot and i need the count of a field status (which has options sich as Filled, Offered, Will Hire, Will not Hire, On Hold Positions), now i need a count of these Status under different departments however I don’t want to include the “Will Not Hire” and “On Hold” numbers in the total, can that be done.
32) Chandoo March 2, 2011

@Shalini.. Set up the pivot table with departments in row label area and status in column area, and dropping names in value field to show counts.
Now, just filter-out the status codes you do not want by applying filters on column labels.
33) Jack Wilsons April 2, 2011

Here is a nice explanation of a pivot table. You might take a look too.
http://books.zkoss.org/wiki/ZK_Pivottable_Essentials/Quick_Start/Concept
34) Mohamed Mahfouz April 22, 2011

Thank you for your help very nice site

RSS feed for comments on this post. TrackBack URI
Leave a comment

Name (required)

E-mail (required, never displayed)

URL

Notify me of when new comments are posted via e-mail

FREE E-mail Updates
Add to your RSS reader
Subscribe to Feed [Chandoo.org - Learn Excel Online - RSS] Add to Google Reader or Homepage Add to Yahoo more
Meet Chandoo
Chandoo - AvatarAt Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. Read more.
[Excel School- Online Excel Training Program by Chandoo]
New to Excel?
1. 100 Excel Tips & Tricks
2. Excel Pivot Tables - Tutorial
3. 51 Excel Formulas in Plain English
4. VLOOKUP Formula for Dummies
5. Free Excel Chart Templates
6. Excel Tutorials for Beginners*
Advanced Excel Tricks
1. Excel Dynamic Charts
2. Learn Conditional Formatting
3. Making Dashboards using Excel
4. Project Management with Excel
5. Working with Excel Tables
Topics & Archives
1. Learn Excel - Topic-wise
2. Charting Tips, Tricks and Tutorials
3. Ask an Excel Question
4. Best Excel & Charting Tips
5. Excel Tips, Tricks and Tutorials
6. Excel & Charting Quick Tips
7. Free Excel Templates
Excel Project Management Templates

Latest Excel Tips
Dummy Data – How to use the Random Functions
80% Discount on PUP & PowerPivot Contest – Hurry up!
Online VBA Classes by Chandoo.org – Details & Dates
Place Key Information in Golden Triangle on your Reports, Dashboards etc. [Quick Tips]
Update Report Filters using simple macro – a Dynamic Pivot Chart Example
RSS Top Excel & Charting Tips
Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]
How to cook a delicious dynamic chart that will have your boss drool
How do you make charts when you have lots of small values but few extremely large values? [Debate]
What is the most unusual thing you have used Excel for? [Quick Poll]
How Francis Landed on Chandoo.org, Become Awesome and Made a Superb Dashboard, all in ONE Weekend
Disclaimer
This is a personal blog, don't take it too personally. I post whatever I want, you read whatever you like. Oh yeah, Chandoo.org tastes better with a latte, caramel if you please. So read, repeat and enjoy. Our Policies | Contact Details

« Home About Online Excel Classes Project Management Dashboards Excel Templates Join our Free News-letter Special Holiday Gift for our readers
Copyright © 2011 Chandoo.org Theme by PremiumThemes