How to use Pivot Table in MS Excel

Pivot Tables in Excel: How to Use the PivotTable Function

The PivotTable function is one of the most widely used features of Microsoft Excel. It allows you to analyse and visualise data in various ways that can provide deep insights. If you have never used pivot tables in Excel or would like to build on your basic knowledge, you’re in the ExcelzerotoHero

(Note: PivotTable is the trademarked term for Microsoft Excel’s pivot table function)

By the time you finish this article, you should have a firm grip on how to create a pivot table in Excel. We will also cover the basics of how to view the same data in different ways using the PivotTable feature. We’ve also provided the worksheet shown in the examples so you can follow each step as you read.

First, imagine a simple dataset like the following.



This is a simple data set, but large enough to work with. Notice that all the rows do not fit on the current page – now imagine there were even more columns. Though you can filter data in this table, we’re about to see how PivotTable can make things much more efficient.

FIRST STEPS TO CREATING A PIVOT TABLE

Make sure your source data has no blank rows. That is not to say you cannot have some blank cells, but an entire blank row will cause problems.



In the above spreadsheet, the blank row at line 17 would be a problem. We need to prep our worksheet to make sure it consists of adjacent data. To quickly remove these go to Home > Find> GoTo Special > Blanks > Delete Rows.
Now simply click on one of the cells in the source data and click on the ‘Insert’ tab. Once there, find the ‘Tables’ group and click ‘PivotTable’. The Create PivotTable wizard should appear.
Sort keys for Pivot Table - Alt,D,P,F



Note that it pre-selects your data and shows the range in the top section of the wizard. You can change this if necessary but as long as your source data is an adjacent range, it should be correct. This is why we make sure there are no blank rows before we start.
Then leave the PivotTable placement option on the default ‘New Worksheet’ and click OK.
Excel then opens a new worksheet and places our PivotTable there. It might not look like much, but we have created our PivotTable.



This is our blank slate. You should also see something new on the right edge of this worksheet. This is where you will find your available PivotTable fields and the four areas you can place them.



If you do not see this, click into the interior of the PivotTable on the left hand side of this worksheet. If you still don’t see the PivotTable fields, you need to check the ‘Show’ group of the ‘Analyze’ tab to make sure ‘Field List’ is selected. Make sure the background is dark gray by clicking on ‘Field List’.



Note that the ‘Analyze’ tab is only visible if you have clicked into the interior of PivotTable1.

NEXT STEPS: BRINGING YOUR DATA TO LIFE

Now we are set to begin placing our data in the areas that will bring things to life. Let’s do something simple like drag ‘Sales Person’ from the fields list and drop it into the ‘ROWS’ area.



Now our blank pivot table has rows.



Next, let’s drag the ‘Sales’ field and drop it into the ‘VALUE’ area.



Now we can view sales totals by Sales Person and it only took a few mouse clicks.



While this is definitely a great way to visualise our data, let’s continue to explore the possibilities. Maybe we want to view total sales by territory. We can remove ‘Sales Person’ from the ‘ROWS’ area and drop ‘Territory’ there instead. This will give us the following visualisation.



If you think that’s cool, now drop ‘Sales Person’ under ‘Territory’ in the ‘ROWS’ area. The visualisation of the data becomes even more meaningful.




If you click on the minus (-) sign to the left of the territory labels, you can collapse the list of sales reps for each.



Then you can get the same visualisation we had before we dropped ‘Sales Rep’ in below ‘Territory’ but with the option to expand to see sales rep level detail.



You can also filter on specific Row Labels. For instance, maybe you want to view just the Northeast and Southeast territories. Click on the down arrow for the Row Labels autofilter and uncheck all the boxes except the labels you want.




Now you have another different view of the data.

ADVANCED VISUALISATION TECHNIQUES

Now let’s see what happens when we move ‘Territory’ to the ‘FILTERS’ area. We’ll also change out ‘Sales’ for ‘Commissions’ in the ‘VALUES’ area.



This is just another way to visualise the data. We can select the commissions by sales rep for any of the territories. Filter on ‘Central’.



This allows us to filter through each set of sales reps per territory efficiently. With the Row Labels autofilter, we can also sort the rows.

VALUE FIELD SETTINGS

One particularly useful trick when using PivotTables is changing the ‘Value Field Settings’. You do this by clicking on the down arrow on the right side of the field in the ‘VALUES’ area.



Then the ‘Value Field Settings’ wizard will appear.



This is where you can change how you want your Value field summarised. Change the setting to Average and click OK.



You can also access this by right clicking on the column in the PivotTable and going to ‘Summarize Values By’.



You can select ‘Count’ if you wanted a quick tally on the number of sales by sales rep.



Another very cool feature is ‘Show Values As’. We will change our values back to sum of sales. Let’s make sure our territory filter is set to ‘All’. Then we right click on the column in the PivotTable and select ‘Show Values As’.



Notice all the different options. Let’s select ‘% of Column Total’. Now we can see sales by rep as a percent of total sales.




One last point we have not covered up to this point is the ‘COLUMNS’ area. Let’s move the ‘Territory’ field from the ‘FILTERS’ area to ‘COLUMN’. Now we get a brand new view of our data. This is just one more example of the flexibility pivot tables offer you for viewing data.



Hopefully this demonstration has shown why the Excel PivotTable function is so widely relied upon for data analysis. Furthermore, you can see how simple it is to get started creating one and visualising your data in many different ways.