How to create a Pivot Table in Excel

Excel's Pivot Table feature is an incredibly powerful tool that makes it easy to tabulate and summarise data in your spreadsheets, particularly if your data changes a lot. If you are finding yourself writing lots of formulas to summarise data in Excel (using functions such as SUMIF and COUNTIF) then Pivot Tables can save you a lot of time and work and give you insights into your data that are otherwise too hard to discover. Not only that, but they also allow you to quickly change how your data is summarised with almost no effort at all. This lesson will show you how to create a simple pivot table in Excel to summarise a set of daily sales data for a team of several sales people.

This lesson shows you how to create a Pivot Table in Excel 2010 and Excel 2013. While Excel 2013 offers some additional tools for creating a Pivot Table, the fundamental steps remain the same as those for Excel 2010.

What you'll learn in this lesson

In this tutorial, you'll learn how to:

  • Set up your data in Excel so it is in a format that you can use for a pivot table.
  • Create a pivot table with that data
  • Change the pivot table report to reflect different views on the same data.

The data we'll work with in this example is an Excel table that has two months of daily sales data for a team of four sales people, broken down by product. The first few rows are shown below:

Excel, How to create a PivotTable, example sales data for analysis

In fact, this spreadsheet extends down for 688 rows of sales data, for all of January and February. So while you might look at the data in the table above and think "I could summarise that quickly by hand or with a few clever formulas", the likelihood is that it would all get too much - and would certainly take too long to do by hand. That's where pivot tables are by far the best solution - you'll be able to convert this data in under a minute, and be able to get different summaries with a few clicks of the mouse.

Getting started with Pivot Tables - make sure your data is ready

There are some important rules you need to follow if you want to create a pivot table from your data:

  • You need to have a your data organised in columns with headings. These headings will be used when you create the pivot table, and things will get very confusing without headings.
  • Make sure there are no empty columns or rows in your data. Excel is good at sensing the start and end of a data table by looking for empty rows and columns at which point it stops.
    • A quick tip to check if your data is formatted in one contiguous range (a fancy term way of saying "one block of data") is to click a single cell in the table then press SHIFT+* (or CTRL+SHIFT+8). This automatically selects the whole table. You'll then see if you have any problems with the layout of your table.
    • Note that empty cells are OK. What isn't OK is a whole row or a whole column of empty cells.
  • Consistent data in all cells.
    • If you have a date column, make sure all the values in that column are dates (or blank).
    • If you have a quantity column, make sure all the values are numbers (or blank) and not words.

At this point, if everything is looking OK, you're ready to move on to the next step.

Create a blank Pivot Table

To start your pivot table, follow these steps:

  • Click on a cell in the data table. Any cell will do, provided your data meets the rules outlined above. In fact, at this point it's all or nothing - select the whole table or just one cell in the table. Don't select a few cells, because Excel may think you are trying to create a pivot table from just those cells.
  • Click on the Insert menu and click the PivotTable button:
    Excel, How to create a PivotTable, Excel 2010 pivot Table toolbar button
  • The following dialog box will appear:
    Excel, How to create a PivotTable, Create Pivot Table dialog box
  • Note that the Table/Range value will automatically reflect the data in your table (you can click in the field to change the Table/Range value if Excel guessed wrong). Alternatively, you can choose an external data source such as a database (we'll cover that another day!)
  • Also notice that you can choose where the new PivotTable should go. By default, Excel will suggest a New Worksheet, which I think is the best choice unless you already know you want it on an existing worksheet.
    • Be warned that if your data changes a lot, or you find yourself changing the Pivot Table layout a lot, then refreshing the data in your Pivot Table can result in the Pivot Table changing shape and covering a larger area. If you have data or formulas in that area, they'll disappear. Therefore, putting a Pivot Table on the same page as data or other information can cause you real headaches later on, and thats' why New Worksheet is the recommended option.

Once you've completed your selections, click OK. Assuming you chose the New Worksheet option, Excel will create a new worksheet in the current workbook, and place the blank PivotTable in the worksheet for you. You are now ready to design your Pivot Table.

Designing your PivotTable layout.

  • When you switch to the worksheet with your new Pivot Table, you'll notice three separate elements of the Pivot Table on the screen, starting with the PivotTable report itself:
    Excel, How to create a PivotTable, blank pivot table report
  • Then you'll see the Pivot Table Field List and under that the field layout area. Note that it should show the column headings from your data table.
    Excel, How to create a PivotTable, PivotTable field list with nothing selected  Excel, How to create a PivotTable, PivotTable drag fields layout builder, no fields added
  • To create the layout, you need to first select the fields you want in your table, and then place them in the correct location.
    • You can check the boxes for the fields you want to include, and Excel will guess which area each field should be placed in. However, the Pivot Table is recalculated each time you check one of the boxes which can slow you down, especially if Excel places a field in the wrong place.
    • Therefore, I recommend you drag and drop each field to the area you want it to be.
  • As an example, here are the Field List and the Field Layout area above with the fields in place to show a report with:
    • Each day down the left, with each sales person listed separately for each day
    • Items shown across the top.
    • The total quantity of items sold for each cell in the Pivot Table.
  • Here is how to layout this report:
    Excel, How to create a PivotTable, PivotTable Field list with values selected Excel PivotTable field layout with values populated 
  • The report that this generates looks like this:
    Excel finished PivotTable example
  • Notice how the Pivot Table has automatically created a list of the sales people for each day covered in the source data.

Hopefully this lesson has got you started with PivotTables. If you're looking for more lessons, visit our Pivot Table page here. This book by Bill Jelen is also well worth a look.

Download worked examples

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.

Add a comment to this lesson

Comments on this lesson

Thank you!

I am amazed of the quality of this tutorial. You fully deliver the goods in five minutes! Please do keep your quality as high as this, and you will prosper.

A side note:
This cannot be done easily in oracle sql using the pivot statement, because Oracle is unable to make it dynamic.
Excel wins this round hands down :)

Hi Sandra

Hi Sandra

Can you provide more information? If you want to send an example of your spreadsheet for me to look at, use our Contact Form to get in touch and I'll reply with an email address that you can send the example to.

Regards

David

Format

How would keep the format you've given to the table after changing what it shows? I've changed the alignment while wraping text, but after filtering with a criteria it goes to the same format it had when first created, it does not keep it.

Amazing post - good prerequisite for things to come

Hey,

great article. I have referred several of my colleagues to this as a starting point. As the pivot table functionality has been improved so much since the old days (Excel 2003) the basic usage of it is really a prerequisite to use the "newer" tools like PowerPivot (which is so amazing as it drastically reduces time flattening your data as preparation for pivots).

Some of my colleagues stated that it took them a little longer than 5 minutes, but 10 or even 15 minutes of course is still fine for a topic with that complexity.

ms excel course online

If you want to learn Microsoft Excel, there are several places on the net that offer online courses. The one I personally like is http://www.onlinecourses.trainedge.co.in/ms-excel-training-with-microsof.... They have videos and pdf course packs like most online course. But they also have lots of assignments you can use to practice the concepts. There are simulated exercises also which are basically guided practice sessions and very helpful.

How to Learn Pivot Table in Excel 2010?

I am a new operator. I know how to work Excel but I can't operate Pivot Table. I want to create a web data/date sheet for my official work. I request to you please give me all of note and information that I can create data sheet and see all of data table easily.

This lesson should cover it

Hi Moshe

Thanks for your comment. I believe this lesson should have everything you need to know about setting up a Pivot Table in Excel 2010. If you have any specific questions after reviewing the lesson, please feel free to post them in a new comment.

Regards

David

Excellent Excel

I'm on excel from last 5 years, during my studies & now at work. It has got plenty of features, which I'm yet to learn & you're the one helping me with it.
Thank you!!

I have some doubts

First of all thanks for the tutorial. I tried applying the pivot table on my data but didn't find what I was looking for.
In the attached file is the data of my purchases from these creditors, now I want to know how much I have purchased from each creditor also I want the other figures mentioned in the headings, can I do that using Pivot Tables ?

Yes, a PivotTable is perfect for this

Hi Vaibhav

Your data is structured perfectly for what you're trying to do with a PivotTable.

  • Click on a cell anywhere inside your data table
  • Follow the steps above to create your PivotTable
  • Add the Name of Creditor field to the Row Labels area.
  • Add each of the columns you want to analyse to the Values area (Gross Total, Oms Purchases etc)
  • If the PivotTable gives you the Count of any of these columns, then click each field and choose Sum in the Summarize value field by section.

That should give you what you are looking for.

David

solution

How to find the similar data from 2 different tables or the data which is not in the second table ?
eg: as per attached excel, highlighted (yellow) are not in other table

PROCESSING POWER PROBLEM - PIVOT TABLES?

Very good tutorial.

I've successfully avoided the use of Pivot Tables, perferring to create tables on my own using the various formulae Excel provides, for well over a decade. Now I find my machine grinding to a halt when working with data blocks of about 65,000 rows and about a dozen columns. I've limited cell references (e.g., COUNTIF(A1:A65000,F2) rather than (COUNTIF (A:A,F2)) ,eliminated references across workbooks and even consolidated data on worksheets to limit cross-sheet references, but STILL it is taking eternities to process or freezing altogether, even when I move my work to more powerful work stations. I'm afraid to ask, but might Pivot Tables provide a solution or does a Pivot Table, say, counting 65,000 rows take just as much horsepower as a 65,000-row COUNTIF / COUNTIFS statement?

Help!
R

PivotTables perform far better for large data sets

Hi R.

I'm not sure I'd describe "avoiding" PivotTables as success .... if you spend the time to dig into the functionality they offer, I have no doubt you'll find yourself wondering why you put it off so long - and your performance issues will disappear, or will at least become much less of a problem. Bear in mind that COUNTIF and COUNTIFS have a lot of heavy lifting to do (as does any function with IF in its name)..

PivotTables work by taking a copy of your data and storing them in more efficient format than standard Excel rows and columns. This is known as the pivot cache. All changes you make to the table are applied to the data in the pivot cache rather than the raw data, and happen very quickly compared to what you're seeing when your spreadsheet recalculates. As new data is added to the source data, you can then manually refresh the pivot cache which in turn updates the PivotTable (in fact, you'll simply be "refreshing the data" - you won't see any references in Excel to "refreshing the pivot cache" or even any references to the pivot cache - that all happens behind the scenes).

Not only that, but Excel 2013 will automatically use the same pivot cache for each PivotTable you create in that spreadsheet file. This means you can have multiple worksheets with multiple PivotTables, all using the same source data, which makes PivotTables considerably more efficient than creating multiple worksheets with different reports that each references and calculates from the sheet containing the raw data.

If you're using earlier versions of Excel you have the option when creating a new PivotTable from the same source data to re-use the pivot cache by selecting "Yes" when asked if you want use less memory with your new PivotTable.

After all this, you might also like to consider PowerPivot, which was a free downloadable extension in Excel 2010 but is now a standard feature in Excel 2013. It's basically PivotTables on steroids - check out the videos on this page:

http://www.microsoft.com/en-us/bi/powerpivot.aspx

I hope that helps. Feel free to post again if you need help to get your PivotTables beyond the basics covered in this lesson.

Regards

David

 

Brilliant

This short tutorial is absolutely brilliant. My eyes have been opened to the usefulness of pivot tables. Thanks for this!

Excel pivot table

I had to laugh at your comment about Bill Jelen's book. I am looking at the internet and studying the book. You are right the combination is educational.

Pivot tables

Hi,
Is there a way we can compare 2 pivot tables with similar information to spit out the difference betwen the two ? The source of obtaining the data for both is different...
Example I have timesheets and have created a pivot table listing name, positions and days worked I now want to compare that with what actually gets paid by payroll. My issue is that being a large company personnel move from one cost centre to another filling in roles and sometimes payroll forget to change the cost centre thus creating the difference. Can you please suggest a way to compare the two? Thanks

Hi

It useful enough to the beginner user of Pivot Table in just few minutes....It would more worth-full if you could use a complex data sheet ..
However, this is great...have good insight about pivot tabble..

GR

Hi GR

Hi GR

There is a downloadable spreadsheet from this lesson available in the right sidebar.

Regards

David

Certain words or group of numbers should equal 1

I need to know how many of the "accepted" are in each worklist. I also need it to calculate from a starting number, since I'm just starting to track it this way, say 437 for worklist 3095 and 130 for worklist 3096 but, I also need it to calculate when something is removed from each or those lists from a seperate worksheet

MS EXCEL

If you want to learn Microsoft Excel, there are several places on the net that offer online courses. The one I personally like is http://www.onlinecourses.trainedge.co.in/ms-excel-training-with-microsof.... They have videos and pdf course packs like most online course. But they also have lots of assignments you can use to practice the concepts. There are simulated exercises also which are basically guided practice sessions and very helpful.

If you want to learn Microsoft project, there are several places
on the net that offer online courses. The one I personally like
is http://www.onlinecourses.trainedge.co.in/ms-project-2010-for-new-and-asp.... They have videos and pdf course packs like most online course. But they also have lots of assignments you can use to practice the concepts. There are simulated exercises also which are basically guided practice sessions and very helpful.

Hi

Hi
if i then add more data to my original table how does this get picked up into my pivot table?

For example with the example above, when marchs data is added to the origanl data table, how do you get this to appear in the pivot table, and therefore into my pivot graphs?
Thanks