zandax online course logo
 
 
 
 
zandax 10 year anniversary
 
 
 
 
 
 
Home   >  ZandaX Blogs   >  Business Blog   >  Microsoft Software Articles   > 
The Ultimate Guide to Using Pivot Tables

The Ultimate Guide to Using Pivot Tables

 
Improving your Microsoft software skills
 
Article author: Jordan James
      Written by Jordan James
       (21-minute read)

What is a Pivot Table?

Many of us use Excel to work with data and these days, a lot of us work with lots of data.  It's not uncommon to find people working with Excel databases containing hundreds of thousands of rows of information – and it is not unreasonable to think that there must be an easy way to extract meaningful information from our data sources.

Excel, of course, has a number of tools to help you organize and navigate these data sets. We can sort, filter and group them, or even create subtotals – but the big daddy of data analysis in Excel is definitely the pivot table.

Pivot tables are a great way to quickly extract data from, and summarise, large data sets. They allow you to turn this:

Large data sets in Excel

Into this:

Pivot Tables in Excel

In this article, we will show you how to produce expert pivot tables with just a few clicks of the mouse.

Laying Out Your Data

Before we dive in and create our first pivot table, we first want to make sure our data is suitable.  The normal rules of data tables in Excel apply, so:
  • The top row should be a header row
  • The data in each column should be consistent (i.e.  all dates, all numbers)
  • There should be no fully blank rows or columns
  • Any non-related data should be separated by a blank column (best on another sheet)
In addition, you may want to think about the general structure or layout of your data.  A typical layout that we see very often looks something like this:

Typical data layout in Excel

Whilst it looks good, this is in fact a very inefficient way of organising raw data. If, let's say, you wanted to see all the monthly totals over £20,000, you would have to filter each column separately and add a new column each month.  A better way of storing this information would be like this:

Simple layout in Excel

In this format, you can quickly filter for sales over £20,000 in just one column. With the top example, it is easy to see the monthly sales for each customer but it is hard to manipulate it further. The lower example is hard to read, initially, but is much easier to extract information from. While you could throw both examples in a pivot table, only the bottom set is going to be particularly useful.

Improve Your Excel Skills


If you'd like to learn more about Microsoft Excel, why not take a look at how we can help?

We have a whole range of online courses for all skill levels.
RRP from $39 – limited time offer just $8.99



Creating the Pivot Table

Now that we understand, broadly, what a pivot table is and how we should structure our data, let's have a look at how to create one.

Select a single cell in your data table:

Select a single cell in Excel

Head for the Insert tab and click "Pivot Table":

Pivot Table

At this point, you should see the "Insert Pivot Table" dialog:

Insert Pivot Table dialog

The dialog has a number of options:

Pivot Table options in Excel

In this case, the data is in Excel and we want the pivot table on a new sheet, so we just click OK.
At this point, a blank pivot table should be on the left side of the sheet.

Blank pivot table

As long as we have a cell in the pivot table area selected, we will also see the pivot table Field List and a couple of context sensitive tabs on the Ribbon; Analyse and Design (In Excel 2007 and 2010, the new tabs are called Options and Design).

Pivot Table field listThe "Field List" lists all of the field names from the header row of the original data table. The four boxes at the bottom are the areas on the Pivot Table where data can be displayed.

If we have lots of fields, we can use the Search box at the top of the Field List to quickly find a field.

The Options button allows us to change the layout of the Field List and alter the sort order of the fields.

If we have a large data set or perhaps a slower machine, changes to the pivot table can take a few seconds to appear. If we are making a number of changes to the table, however, this can get quite annoying. In these cases, ticking the Defer Layout Update option at the bottom of the field list will allow us to make a number of changes to the pivot table without triggering an update. Once the changes are complete, just click the update button and all changes are applied at once.

It is also worth mentioning that pivot tables do not have a live link to the source data. When the table is created, the source data is copied into a Pivot Cache, and all pivot table operations are performed on the cached data - changes to the original source data will not be reflected in the pivot table.

If your data source has changed and you need to show the new data, click the Refresh button on the Analyse tab. This will force Excel to rebuild the Pivot Cache and the new data will be displayed.

Adding Fields to the Pivot Table

Now that we have our pivot table created, it is time to start adding data to it.  If you want to view total sales for each country, tick the Country field or drag it into the Rows section of the pivot table field list.

Country field in Excel

A unique list of countries is placed into the left column of the pivot table.  When we tick a field, the table determines the type of data it contains; text and date values will by dropped into the Rows section, while numeric values will be dropped into the Values section, and summed. Ticking the "total" field will cause it to be dropped into the Values section and summed.

Values field in Excel

We can quickly change the data by ticking/unticking fields or dragging fields on/off the pivot table.
If we select multiple data fields, the pivot table will automatically group the data.

Unticking fields in Excel

In this case, we have selected both the Category and SalesPerson fields. The pivot table displays the total for each category, then shows the breakdown for each sales person within each category.

We can easily change the display by dragging the fields within the Rows section to change their positions:

Dragging fields in Excel

When pivot table data is grouped in this fashion, each group header will display a small minus button.  Clicking this button allows us to collapse the details for that group.

Collapse details in Excel

In a similar way, clicking the + button will expand the details for the group. To expand or collapse an entire field, select one of the main field entries and then click the Expand Field / Collapse Field buttons on the Analyse tab.

Expand field in Excel

Adding multiple fields to the Rows section may cause the pivot table to be very long, but we can also display data along the top of the table, in the Columns section.

Rather than ticking a field, drag it from the field list into the Columns section or, drag a field from Rows into Columns:

Columns section in Excel

In this example, the pivot table will be reoriented to show SalesPerson field on the left and Categories running along the top.

Pivot table example

As you can see, a pivot table allows large data sets to be laid out and summarised very quickly, with just a few clicks. Now that we have the basics out of the way, let's have a look at some other pivot table features.

Filtering the Pivot Table

Rather than showing the data for all salespeople, we have decided that we only want to see the data for a selection of them. To do this, click the Filter button to the right of the Row Labels section:

Filters in Excel

This will drop down a Filter list, which will be familiar if you have Auto-Filter on the worksheet. Here, just select the required entries and click OK.

The un-ticked entries will now be removed from the pivot table, and the Grand Total row is updated to show the totals for only the visible entries.

To remove the filter, just click the Filter button, then choose the Clear Filter From "SalesPerson" option.

Clear filter in Excel

The Filter list also contains a couple of additional filter methods, such as Label Filters and Value Filters.

Label Filters can be used to filter longs lists of label entries more effectively. For instance, if we only want to see the salespeople whose names begin with a D, we could use the "Begins With" Label Filter.

Label filter option in Excel

Value filters, on the other hand, allow us to filter the pivot table entries based on the calculated values for that particular field.

For example, if we wanted to show only those salespeople who had sold over £2,500,000, we could use the "Greater Than" Value Filter.

The same filters are available for the Column fields as well.  For more global level filtering we use the Filters section of the Field List:

Filter section in Excel

In the example above, the Customers field has been dragged into the Filters section. This adds the country field to the top of the Pivot Table, which acts on the whole report.

Customer field in Excel

Using the Report level filter, we can quickly select a particular Customer, or group of Customers, and the data on the pivot table will change.

Select one particular customer in Excel

Just remember to switch all of these filters back to showing all data when you have finished with them. Filters are maintained and are re-applied if a field has been removed from the table, and added back again.

Slicing Data and Dates

Like filters, Slicers allow us to quickly focus on a particular sub-set of our data, while the Timelines function allows us to more effectively slice date fields.  Slicers were introduced in the 2010 version of Excel; but Timelines are only applicable to Excel 2013 onwards.

To use this function, select the Analyse (or Options, if you are using Excel 2010) tab, then click the Insert Slicer button:

Insert slicers in Excel

Select the field (or fields) to slice your data by and click OK:

Slice data in Excel

To filter the pivot table, all we have to do is click the slicer buttons.

Slicer button in Excel

Slicer button

Hold down the Ctrl key to select multiple items, then click the Clear button at the top of the slicer to remove the filters.

The slicer has its own context sensitive tab on the ribbon. By using the Slicer Options tab, we can change its appearance and also control the number of columns.

Slicer options tab in Excel

Setting the Slicer column count to 7 will allow us to display all the countries without having to scroll up and down a long list.

If you want to delete a slicer, just select the slicer border and press delete on the keyboard.

Timelines (only used from Excel 2013 onward) allow us to slice date fields.  Select your pivot table and then, from the Analyse tab, click Insert Timeline.  Select the appropriate date field and click OK.

Timeline feature in Excel

The Timeline allows us to filter the pivot table by date period.  Click a month, or drag over multiple months and the Pivot Table will be filtered. We can adjust the time periods displayed by clicking the period button at the top of the Timeline.

Timeline settings in Excel

Like the Slicer, the Timeline also has a context sensitive tab.  Using the "Timeline options tab", we have control over the display of various elements on the Timeline:

Timeline options tab

Both Slicers and Timelines allow us to quickly filter the pivot table to drill down to the data we are interested in. Because they are intuitive to use, they are perfect additions to Data Dashboards, allowing users with no previous experience to modify the pivot table report.

Changing Calculation Methods

By default, when we add a field to the Values area of our pivot table, Excel will sum numeric values and count text values – but this may not always be what we want. In the following example, we have added the OrderID field to the values section and, because the OrderID is numeric, the pivot table Sums it.

Pivot table sum

The sum of all OrderIDs is of no use, however – what we really need is the count of OrderIDs.

To do this, we need to change the calculation method. Click the drop down arrow next to the field name in the Values section of the field list and choose, Value Field Settings.

The Field Settings Dialog allows us to change the displayed name of the field, the Number Format and the function applied to the field.

Field settings dialog

In this case, select Count and click OK. The Pivot Table is now updated to show the count of orders in each category rather than the sum.

From Excel 2010 on, the common calculation methods can be changed by right clicking the pivot table and going to "Summarise Value By".

In the next example, the pivot table correctly sums the "Total" field, and we could easily change it to show the average if needed. This time, however, we need to see the Sum and the Average together.

Sum and average in Excel

We already have the Total Field ticked, but we can still drag it, a second time, into the Values section:

Values section

We can now go to the Field Settings for Sum of Total 2 and change it to Average:

Average in Excel

When we have multiple items in the Values section, we also need to consider how these will be displayed in on the pivot table. In the above example, you can see that the Sum and Average columns are being displayed for each category.

Multiple values are displayed in columns by default, and the columns section now contains a Values item.

Column section values

Swapping the displayed field and the Values item around will display the sums for all categories, followed by their average.

Swap displayed field in Excel

Sums in Excel

Alternatively, we can drag the Values item from the Columns section into the Rows section:

Dragging values item in Excel

This time, we have the calculated value show under each item in the row headers. There is no right or wrong with this, how you display your values is a matter of personal preference.

Improve Your Excel Skills


If you'd like to learn more about Microsoft Excel, why not take a look at how we can help?

We have a whole range of online courses for all skill levels.
RRP from $39 – limited time offer just $8.99



Grouping Data

Automatic data grouping in ExcelWhen we add multiple fields to the Row or Column sections of our pivot table, Excel automatically groups the data.

In the example to the left, we can see that both the SalesPerson and Shipper fields have been added to the rows section and Excel shows the total of sales for each Sales person and then breaks that down, or groups, for each Shipper.

This happens by default for multiple fields – but what if we want to group other data? This is common when we are looking at date or numeric data in the Row or Column sections.

OrderDate field in ExcelIn the example on the right, we added the OrderDate field to the Rows section and Excel displayed each individual date – in this case, over 3800 dates!

However, it is unlikely that we would want to see this many unique dates – we would be much more likely to want to see the data organized by years, quarter or months.

This is where grouping comes in.

To group the dates in the example, we can select one of the dates, then, from the Analyse tab, choose "Group Field".

Group field option in Excel

Grouping in Excel

The Grouping dialog has identified that we are dealing with a date field, and presented us with relevant date grouping options.  So now you can select the appropriate groups for your data… here we grouped data by years and quarters:

Years and quarters

Of course, we can always change our minds by clicking "Ungroup" on the Analyse tab, or clicking "Group Field" again and changing the way the data is displayed.

Excel 2016 users will also find that date fields are automatically grouped by year, quarter and month.

Numeric fields can be grouped in a similar way. In the example below, the OrderID field has been added to the Rows section, but we need to group the orders in 1000's.

Excel grouping example

Again, select the field and then click "Group Field" on the Analyse tab.

Group field option

Excel also lets you set the start and end points for grouping – in this example, we're going to change these to 10,000 and 18,000 in order to keep the groups in logical sections. You can also set the grouping interval, 1000 in this case.  When you click OK, the grouping will be complete.

Grouping start and end points

Formatting and Summarising

In the example below, we are showing the total sales for each category of product, arranged by Sales Person and Shipper:

Total sales for different products

Even though the Total field in the source data is formatted as a currency, when it gets to the pivot table, it is classed as a number. We need to change the format of the field, so it displays as a currency.

To do this, right click one of the values on the pivot table, and choose "Number Format":

Number format option in Excel

Note that we are not choosing "Format Cells" here. Our intension is to format the Total field, not the cells.  You can get to "Number Format" via the field settings dialog as well:

Field settings dialog number format

The standard number format dialog will then appear, where we can set the required format, (in this case, currency is this case) and click OK.

Because we are formatting the field rather than the cells, all of the values are formatted.
Other formatting options can be found on the "Design" tab of Pivot Table Tools.

Design tab in Excel

By using the "Design" tab, we can quickly change the overall style of our Pivot Table by applying a Pivot Table Style. We can also control the display of subtotals and grand totals, and, by using "Report Layout", change the general appearance of our pivot table.

Pivot table in compact layout (which is the default option):

Pivot Table compact layout

Pivot table in outline layout (this places multiple fields into their own columns):

Pivot table outline layout

Getting a Bit Flashier

As well as showing raw summarised data, pivot tables give a number of other options as to how the value data is represented. In the example below, we can see the total sales for each sales person for each year. However, rather than viewing the raw data, we want to see the data as percentages:

Number of total sales in Excel

To do this, select one of the values in the pivot table and then click "Field Settings" on the Analyse Tab. Once the "Value Field Settings" dialog is open, click on the "Show Values As" tab.

Show values as option in Excel

Using the dropdown menu under "Show values as", find % of Column Total, then click OK.

The pivot table now shows the sum of total sales for each sales person in percentages for each year.

We now want to find the relative rank of each sales person for each year. To do this, we will go to the Field Settings dialog, and change "Show Values As" to "Rank Largest to Smallest".

Rank largest to smallest

There are a number of other ways to display your pivot table data and it is well worth experimenting to find which ones are most useful to you. Bear in mind that each version of Excel introduces new ways of displaying your data, so not all of the "Show Values As" filters may be available to you.

To reset the data back to the original display, set Show Values As to "No Calculation".

Another useful pivot table tool is the Drill Down option. Drill Down allows you to easily extract a sub-set of data from your original data source.  For instance, in the above example, we can see that the last placed sales person in 2016 was Andrew Fuller. We need to review the data for Andrew's sales in 2016.

Hover the cursor over the data value for Andrew Fuller in 2016, and double click.

The pivot table will extract all of the data that feeds into the double clicked value and display it on a separate sheet. Here we can see the data for Andrew Fuller's sales in 2016.

Excel table example

One Step Further: Power Pivot

One of the limitations of pivot tables is that they draw data from a single source. This could either be from an external database or from worksheet data. However, complex data systems usually store data across multiple tables, and pivot tables can only access one of them.

In the past, the solution to this problem would have been to create a query on the database that combined the required data into one source for the pivot table.  Now we can achieve the same thing using Power Pivot.

Power Pivot was introduced as an add-in to Excel 2010, but is now built into Excel 2013 and 2016, allowing us to pivot multiple data sources as if they were one.

In the example below, sales data is spread across 4 separate worksheets and we need to combine the data in a pivot table.  The data on each sheet has been defined as a data table.

Data table in Excel

We're going to create a pivot table based on the Customers' sheet first, but knowing that we want to use the data from the other sheets, in the "Create Pivot Table" dialog tick "Add this to the Data Model".

Create pivot table

We can view and add the fields from the Customers table as normal.  In this example, we have added the Country field to the Rows Section:

Country field rows

Now we want to show the order dates from the Orders Sheet.  In the Pivot Table Field List, click the "All" option at the top. The Field list shows the other tables present in the workbook:

Pivot table fields

Now we can expand out the "Orders" table and add OrderDate to the Columns Section.

Let's take this one step further and add Sales Total from the LineItems table:

LineItems table

At this point, we can see that something is wrong. It's possible that we sold exactly £1,449,367.31of goods to each country every year, but it's more likely that it's wrong. In this case, Excel doesn't understand how the data on the 3 sheets relates to each other. And it shows this in the Field List:

Excel field list

Excel has identified that relationships are required between the sheets for the pivot table to work correctly. We can do this by manually creating the relationships, or clicking Auto-Detect and seeing if Excel can determine them for us. In this case, click Auto-Detect:

Excel auto detect

Excel has determined the relationships between the Customers, Orders and LineItems tables and the pivot table should now show the correct results.

Correct Pivot table results

If Excel fails to do this, you will have to do it manually. We can see the relationships by clicking "Relationships" on the Analyse tab:

Relationships analyse tab

In this example, we have one further relationship to make, and that can be done by clicking the "New" button in the Relationships window.

Relationship window in Excel

In the "Create Relationship" window, you select the two tables to be related and the fields that will join them.  This, of course, depends on you having an understanding of how the tables are related – in this case, each row of data in LineItems has the number of the product that is being ordered. It is this that is linked to the number of the product in the Products table.

Once you are done, click OK and the relationship is created. You can then close down the Relationship dialog and the newly related table can be used in the pivot table.

Power Pivot is an incredibly powerful tool and we have just looked at the easy method of connecting multiple data sources to the Power Pivot data model.  If you think that it may be useful to you, there is a lot more to explore.

We hope you enjoyed this guide to pivot tables, and feel inspired to explore using your own data.

Improve Your Excel Skills


If you'd like to learn more about Microsoft Excel, why not take a look at how we can help?

We have a whole range of online courses for all skill levels.
RRP from $39 – limited time offer just $8.99

More Articles on Microsoft Software

Version History of Microsoft Word
Version History of Microsoft Word
Jordan James
Author: Jordan James
About the article
Summary
Read about the different versions of Microsoft Word, from Activia Training, providers of flexible, cost effective Word training courses.
[ close ]
10 Ways Excel Pivot Tables can help you.
10 Ways Excel Pivot Tables can help you.
Jordan James
Author: Jordan James
About the article
Summary
Pivot Tables are extremely useful in analysing data and seeing how its linked. This looks at 10 ways to use Pivot Tables properly.
[ close ]
How to Highlight Data in Different Colors in Microsoft Word
How to Highlight Data in Different Colors in Microsoft Word
Jordan James
Author: Jordan James
About the article
Summary
Learn how to highlight data in different colors in Word with this guide on the ZandaX website.
[ close ]
How to Add Special Characters in Word with Keyboard
How to Add Special Characters in Word with Keyboard
Jordan James
Author: Jordan James
About the article
Summary
Find out how to add special characters, such as the trademark and copyright symbols in Microsoft Word, using the keyboard.
[ close ]
Enhancing Legal Operations: The Power of Microsoft Integration
Enhancing Legal Operations: The Power of Microsoft Integration
Jordan James
Author: Jordan James
About the article
Summary
With greater efficiency needed by corporate legal operations, this article looks into the advantages and applications of Microsoft integration.
[ close ]
Simplify Microsoft Excel with the Right Training Course
Simplify Microsoft Excel with the Right Training Course
Jordan James
Author: Jordan James
About the article
Summary
You can play around with Microsoft Excel for hours, and still get nowhere! Here, we give you tips on how to find a training course to help
[ close ]
Our Course Of The Month – Microsoft Project
Our Course Of The Month – Microsoft Project
Jordan James
Author: Jordan James
About the article
Summary
The course of the month this month is Microsoft Project. As a project manager, are you using the tools that are available to you?
[ close ]
What Is Microsoft Visio Used For?
What Is Microsoft Visio Used For?
Jordan James
Author: Jordan James
About the article
Summary
Microsoft Visio can be used for a lot more than people realise. Popular uses include creating organisation charts, floor plans and timelines
[ close ]
7 Reasons Why You Should Learn How to Use Excel
7 Reasons Why You Should Learn How to Use Excel
Jordan James
Author: Jordan James
About the article
Summary
The Article shows how to boost productivity with Excel, Improve Quality of Work, Versatility, You will become a God in the Office!
[ close ]
What are Macros used for in Excel?
What are Macros used for in Excel?
Jordan James
Author: Jordan James
About the article
Summary
Macros in Excel are incredibly powerful tools that can provide the user with large benefits. This article looks at what macros are for.
[ close ]
How to Use Format Painter in Excel for Multiple Cells
How to Use Format Painter in Excel for Multiple Cells
Jordan James
Author: Jordan James
About the article
Summary
Learn how to copy formats from one cell to another using the Format Painter tool in Excel, with this tutorial from Activia Training.
[ close ]
How To Use Animation Triggers In PowerPoint
How To Use Animation Triggers In PowerPoint
Jordan James
Author: Jordan James
About the article
Summary
Learn how to use animation triggers in Microsoft PowerPoint from this tutorial from Activia Training.
[ close ]
 

Write for us on the ZandaX blog

We're always looking for guest contributors to increase the variety and diversity of what we present.

Click to see how you can write for us:

 

The ZandaX Business Skills blog categories

Click a panel to visit the main category pages for the blog
Career Success
Career Success
Marketing
Marketing
Presentation Skills & Public Speaking
Presentation Skills & Public Speaking
Customer Service
Customer Service
Microsoft Software
Microsoft Software
[ This category ]

ZandaX Blog Contents

Want to see them all? Click to view a full list of articles in our blogs.

 
zandax online courses logo
"ZandaX courses are such great value, and with the help and support they give, there's no better option in the market"
ZandaX LinkedIn logo
ZandaX YouTube logo
ZandaX FaceBook logo
Course Categories
 
All content © ZandaX 2024