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 they are in their element with huge amounts of data laid out in a tabular format.
Forget Filters and Subtotal, Excel Pivot Tables can do both of these and more in a few seconds.
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 an Excel Pivot Table
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 it there.
4) I’ve chosen to insert mine 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 chose in the previous step, 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 Field List window.
a. By ticking the Fields from the list you can tell Excel which fields you want in your 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 Field List.
6) To insert the Pivot Table shown in the above example, and below, my 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, now let’s look at how we can customise it.
Pivot Table Styles
In 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.
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 Default Value Calculation
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
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 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’.
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’. 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 row labels.
Filters can also be applied to column labels.
Changing the Orientation
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? It would look like this:
Simply rearrange the fields in the Field List by dragging and dropping the fields to the area you want.
You will also notice you now have two new tabs in the Ribbon for Pivot Table Tools; Options and Design.
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 Field List disappears click anywhere on the Pivot Table and it will reappear.
Did you find this useful, or did it open a can of worms? Let us know by leaving a comment.
Why not visit our index of Excel tutorials. You'll find a huge range of topics incuding formulas (all explained in plain English), plus more on PivotTables and other Excel tools and tricks. Enjoy 🙂