Excel Pivot Tables Explained

Pivot Tables in Excel are one of the most powerful tools, and once you understand how they work, they’re actually quite easy to insert and modify.  In this example we’re going to work with a small amount of data for illustration purposes, but Pivot Tables are in their element with huge amounts of data laid out in a columnar format.

Forget Filters and Subtotal, Pivot Tables can do both of these and more in a few seconds.

Note: if you’d prefer to watch a video tutorial on Pivot Tables that will teach you how to create Pivot Tables, choose fields, Pivot Table Design, filtering Pivot Tables, Modify Pivot Table data, and insert Pivot Charts sign up for our Microsoft Office Online Training

OK, looks like you’re happy to read the tutorial…..take the data below for example.

Let’s say we wanted to SUM the number of viewers by program, going down the rows, then by Region going across the columns and only show Q1 (you can’t see it here but there is data in this table for Q1 through to Q4).  The easiest solution is to insert a Pivot Table like this:

Note: In the above example we summed the viewers, but instead of, or in addition to SUM, we can COUNT, AVERAGE, PRODUCT and more.

We can also change the formatting and customise the default ‘Row Labels’, ‘Column Labels’ and ‘Sum of Viewer’ headings to make the report more polished.  We’ll get to that soon.

How to Insert a Pivot Table in Excel

Download the workbook and practice as you go.

1)      Click anywhere in your data

2)      On the ‘Insert’ tab click the ‘PivotTable’ button and select ‘PivotTable’.

3)      The Create PivotTable dialog box will open.

a.       Excel will automatically select the range of data, but you can change this here if you need to by modifying the range in the Table/Range field.  You can even choose an external source but for most people using your own data will be all you want, so we’re not going to cover that here.

b.      Tell Excel if you want your Pivot Table in a New Worksheet or in the Existing Worksheet.  If you choose Existing Worksheet you will need to tell Excel the top left cell that you would like your Pivot Table to begin in.  If you choose New Worksheet Excel will insert a new worksheet in your file and insert your Pivot Table there.

4)      I’ve chosen to insert my Pivot Table in cell F2 on the sheet where my data is for this tutorial.  Below is how your worksheet will look after step 3. In the right hand section of your screen the Pivot Table Field List window will open and a place holder will be entered beginning in the cell you’ve chosen to insert your Pivot Table, in my case F2:H19.

5)      The first thing you need to do is choose the fields you want included in your Pivot Table Report. We do this in the Pivot Table Field List window.

a.       By ticking the Fields from the list you can tell Excel which fields you want in your Pivot Table report.

b.      By default it will add any labels to the ‘Row Labels’ area, and any columns it detects as values will go into the ‘Values‘ area.  To move them, simply drag and drop the fields to the area you want.

c.       If you’re inserting your Pivot Table on the existing sheet you will see it take shape as you make your selections in the PivotTable Field List.

6)      To insert the Pivot Table shown in the above example, and below, my PivotTable Field List looks like this:

You can see I’ve set the Period Q1 (cell G1) as a ‘Report Filter’, the programs are my Row Labels, the Regions are my column labels and the Sum of Viewers are my Values.

Ok, so now you’ve inserted your Pivot Table, let’s look at how we can customise it.

Pivot Table Styles

In Excel Pivot Table styles enable you to make your Pivot Table cool with very little effort.

You’ll notice you now have two new tabs in your Ribbon.  Go to the Design tab and here you can choose from a huge range of predefined styles.  You can even save your own in keeping with your corporate image.

Just look at how much more professional mine looks with a few clicks of the mouse.

I’ve also changed my ‘Row Labels’ and ‘Column Labels’ headings by typing new names directly into the cell.

Preserve PivotTable Formatting

You can also format your Pivot Table Report manually using the Fonts etc. on the Home tab of the Ribbon, plus you can resize columns and rows.

If you do this there are a few preferences you should set so that your formats aren’t lost on refresh.

1)      Right click anywhere on the Pivot Table.  Select  ‘PivotTable Options’.  The following dialog box will open.  Make sure you tick the ‘Preserve cell formatting on update’ preference and ‘Autofit column widths on update’ is NOT ticked.

Change Pivot Table Value Calculation from the default SUM

Remember at the beginning I said in the Values you aren’t limted to just SUM.  You can also COUNT, AVERAGE, PRODUCT and a few more.

By clicking on the down arrows beside the Report Filter, Column Labels, Row Labels or Values areas you can access tools that will allow you to modify the settings.  This is also where you can change whether the Values are SUM of, COUNT of, AVERAGE of and so on.

To change the VALUES from the default, select ‘Value Field Settings’ from the list by clicking the down arrow beside, in our case, Sum of Viewers.

The ‘Value Field Settings’ dialog box will open and you can choose a different calculation from the list on the ‘Summarize by’ tab as shown below.

You can also give the field a custom name.

Note: you can have more than one value in your Pivot Table.  For example, you might want to SUM and COUNT the values.  Simply drag the field you want from the ‘Choose Fields to add to report’ list in the Pivot Table Field List into the Values area and alter the Value Field Settings as listed above.  (See the next section for instructions on how to add another value with screen shots.)

Insert a predefined calculated field to your Pivot Table

You can enhance your Pivot Table by inserting a calculated field.  Excel has a list of predefined calculations you can select from.  Note: You can also insert a custom calculated field, but we’re not going to cover that here as I think these are better added to your source data and brought into your Pivot Table as a field.  It’s less prone to error with this approach.

To insert a calculated field from the predefined list available:

1)      First you have to add another Value field to your Pivot Table by dragging, in our case, ‘Viewers’ from the ‘Choose fields to add to report’ section down to the ‘Values’ area. You’ll notice that Excel will put a Values field in the ‘Column Labels’ area as well as the Values areas.  This is because it’s performing the calculation for each column of data.  You can change it to calculate for each row by dragging it to the ‘Row Labels’ area.

2)      Open the ‘Value Field Settings’ dialog box (click the down arrow and select it from the list) and select the ‘Show values as’ tab.

3)      From the drop down list you can choose the type of calculation you want.

4)      Give your calculation a custom name before clicking OK.

In my example below I inserted a ‘% of total’ field and gave it the custom name ‘% of Viewers’.

Filter Pivot Table Data

What say we wanted to only show data for a few of our programs?  We can filter our row labels by clicking on the down arrow beside ‘Row Labels’ in our Pivot Table.  And just like regular filters we can instruct Excel to only display the values we choose.

Note: in this filter we can also sort our PivotTable row labels.

Filters can also be applied to column labels.

Changing the Orientation of your Pivot Table

You can have more than one field in each area.  For example, what if we wanted to see the data grouped by region down the rows?  Our Pivot Table would look like this:

Simply rearrange the fields in the Pivot Table Field List by dragging and dropping the fields to the area you want.

Pivot Table Tools

You will also notice you now have two new tabs in the Ribbon for Pivot Table Tools; Options and Design.

There’s too much to work through all of the features here but free video tutorials are available. Simply sign up for our free Microsoft Office Online Training and watch the video tutorials on Pivot Tables to learn how to create Pivot Tables, choose fields, Pivot Table Design, filtering Pivot Tables, Modify Pivot Table data, and insert Pivot Charts.

Pivot Table Source Data Rules

1)      No blank rows or columns.

2)      Each column must have a heading.  This heading will be carried over to label your Pivot Table rows and columns.

3)      Make sure your source data is formatted correctly.  That is if they’re dates, format them as dates and so on.

4)      If the Pivot Table Field List disappears click anywhere on the Pivot Table and it will reappear.

Download the workbook and practice what you’ve learnt.

Did you find this useful, or did it open a can of worms? Let us know by leaving a comment.

Share the knowledge with your friends and colleagues on Twitter, Facebook etc. using the shortcuts below.

Want more? Sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you’ll get our 100 Excel Tips & Tricks e-book.

Share This

Tweet

Please share this or leave a comment and I'll make sure you get a personal reply.

Tagged as: excel pivot table tutorial, excel pivot tables, Excel PivotTable, pivot table, pivot tables excel pivot table in excel