The Pivot Table feature in Excel is a powerful tool that makes it almost effortless to summarise, tabulate and analyze data in your spreadsheets. Learning how to create pivot tables in Excel will make you far more productive in your work compared to using commands like TOTAL, SUBTOTAL and others to summarize and tabulate data.
The basic steps to create a pivot table are still the same. Hence, you will be able to use this lesson whether you are using Microsoft Excel 2010 or Excel 2013 which offers some additional tools for creating a Pivot Table.
Get Your Data Ready For Pivot Table
The first step is to get your data ready for creating a pivot table. In this case we have created a simple data table containing the computer sales figures from two stores labelled Store#1 and Store#2 for convenience.
As you can see in the image above, each column containing data is properly labelled and nothing is left blank. This is really important if you are going to use pivot tables. Make sure that your data is properly labelled and there is no column that has a blank label.
To summarize, here is what you need to make sure before creating a pivot table.
1. Organise your data with proper column headings. Without proper column headings you wont be able to make any sense of your data when you create a pivot table.
2. Absolutely make sure there is no data column that has an empty label. You will not be able to create a pivot table if say the column C in picture above has an empty or a blank title (instead of its current title ‘UNIT’). You can label the column with any letter or charector say ‘Z’ or ‘X’ or anything in case you do not want to label a column – but you cannot leave it blank.
You are now all set and ready to create your first pivot table.
How to Create Pivot Tables
1. You can create a pivot table on the same sheet where your data is located. In this case we have chosen to create a pivot table in a new sheet conveniently labelled PIVOT TABLE. This in our opinion keeps things simple, clean and protects the data from being deleted or disturbed due to accidental user errors.
2. As you can see in image above, our data is located in a sheet labelled ‘Sales Data’ and we now want to create Pivot Table in sheet 2 and label it as PIVOT Table. To do this click on sheet 2, next right click, choose rename from the menu and simply rename the sheet by typing in its new name (PIVOT TABLE).
3. Next go to the newly renamed Pivot Table sheet by clicking on it and place your cursor on wherever you want the Pivot Table to be created. As you can see in image below, the cusror is placed on Cell B3 and this is where our Pivot Table will show up.
4. Click on Insert and then click on PivotTable.
5. Microsoft Excel will present you with a dialogue box (see picture)
6. Complete the table/range input by going to the SALES DATA sheet and choosing the complete data table from Cell A1 to Cell E11.
7. The location of the Pivot table will be automatically filled and there is nothing more to do in this dialogue box. Hence, click OK to be done.
8. Once you click OK you will have an empty pivot table ready to work with.
Working with a Pivot Table
What you are seeing in image below is a ‘PivotTable Field list which usually pops up to the right of your worksheet. We have brought it within the visible worksheet area to present a screen shot.
In order to see an example of working with pivot tables, let us assume that we are trying to find the total number of computers sold by each store.
First get familiar with the PivotTable Field list – This is where you will be dragging and dropping your Report Labels or Field Items to extract and arrange data in a meaningful way.
Getting started with your very simple example – We are trying to get the total number of computers sold by each store.
1. Just click on Store in ‘Choose fields to add to report’ area of the Field List. You will immediately see the Store label appearing in the ‘Row Labels’ area.
2. Next click on Quantity in ‘Choose fields to add to report’ section. You will see the Quantity label added to Row Labels area. However this is not where we want to see the quantity label (see step 3).
3. In order to see the total number of computers sold by each store you need to move the Quantity field from the ‘Row Labels’ area to area labelled ‘∑ Values’.
4. Once you have the ‘Store’ and ‘Quantity’ labels in the right areas of the ‘PivotTable Field List’ you will see your data magically organized as shown in the image above – With Store#1 and Store#2 as row labels and the quantity of computers sold by each store indicated under ‘Sum of Quantity’.
This is just a very simple example to show you how to work with pivot tables. In order to fully understand how pivot tables work, you will need to practice a lot.
Start by practicing this lesson and play around to see what happens when you move around data filelds in different input areas of the PivotTable Field List. Once you have understood the concept you will be able to accomplish a lot using pivot tables.