zandax online course logo
Home
 
Info, Blogs, Contact & Login
Learn
Tests

The Ultimate Guide to Using Pivot Tables

From the ZandaX Microsoft Software Blog

Articles to help you improve your Microsoft software skills

Home  >  Blogs Home  >  Business Blog  >  Microsoft Software Articles  > 
The Ultimate Guide to Using Pivot Tables

The Ultimate Guide to Using Pivot Tables

A post from our Microsoft Software blog

      Written by Jordan James

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.

Want Better Excel Skills?

We have online courses with full 12-months' access.
RRP from $49 – limited time offer just $12.00



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.

Want Better Excel Skills?

We have online courses with full 12-months' access.
RRP from $49 – limited time offer just $12.00



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.

Want Better Excel Skills?

We have online courses with full 12-months' access.
RRP from $49 – limited time offer just $12.00

Back to the Microsoft Software blog

Click the button for more Microsoft Software articles.

The ZandaX Business Skills blog

Click a panel for great articles on business skills

ZandaX Blog Contents

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

Online courses to boost your skills
Click a button to see more about each course
Personal Development
Microsoft Software
 
 
Leadership & Management
 
 
ZandaX online training course logo
ZandaX – Change Your Life ... Today
All content © ZandaX 2022
Close menu element
See how you score on a range of skills that are critical to your well-being and performance
Communication Skill test
Communication Skills
How Can You Communicate Better?
Would you like to see what kind of communicator you are? And how you can improve the effectiveness of your communications?
Likeability test
Likeability
How Much Do People Like You?
Do you sometimes wonder just how likeable you are? And wouldn't you like to see how you can (genuinely) become more likeable?


Time Management test
Time Management
How Can You Make More Use Of Your Time?
Are you frustrated by how easily time slips away? Do you get frustrated when things don't get done just because you run out of time?
Assertiveness test
Assertiveness
Are you Passive, Aggressive or Assertive?
Would you like to know where you fall on the behavior spectrum? Does your response to events sometimes surprise you?


Close menu element
Information & Resources
ZandaX information
Information
Read more about us, our Privacy Policy and our Terms of Service
See how we want to help you, and how we make everything easy for everyone
Callback request
ZandaX Blogs
Articles to increase your knowledge and understanding in key areas of your life and career.
Read our blogs on Personal Development, Business Skills and Leadership & Management


Time Management test
Log In
Log in to your online dashboard
View your courses, review what you want and download your workbooks and certificates
Assertiveness test
Contact Us
An easy online form to get in touch
With options for More Information, Customer Service and Feedback


Close menu element
Learn About:
 
Personal Development
 
Leadership & Management
Sales & Presentations
Marketing
 
Microsoft Office
Microsoft Project
Microsoft Visio
 

[NOTE: Mouse over the titles above,
then to visit the website pages you want,
click on the links in the right hand panel]
Our courses
We have everything covered: learn all applications at all levels!
Microsoft Excel courses
Microsoft Excel 2021 / 365
Introduction to Advanced (*NEW*)
Microsoft Excel VBA
Introduction & Intermediate
Microsoft Excel 2013 / 2016
Introduction to Professional
Microsoft Powerpoint courses
Microsoft PowerPoint 365 (2021)
Ultimate all-in-one (*NEW*)
Microsoft PowerPoint 2019
Introduction to Advanced
Microsoft PowerPoint 2013/2016
Introduction & Advanced
Microsoft Word courses
Microsoft Word 365 (2019)
Introduction & Advanced
Microsoft PowerPoint 2013/2016
Introduction & Advanced
 
Microsoft Access courses
Microsoft Access 365 (2019)
Introduction & Advanced
Microsoft Access 2013/2016
Introduction to Advanced
 
Microsoft Outlook courses
Microsoft Outlook 365 (2019)
Ultimate all-in-one (*NEW*)
Microsoft Outlook 365 (2019)
Introduction to Advanced
Microsoft Outlook 2013/2016
Essentials
Microsoft Project
Enhance your project management with our two intensive but very easy-to-follow CPD certified Microsoft Project courses.
Microsoft Project courses
Microsoft Project Introduction
Get a solid foundation in Project software to create solid, resilient project plans.
You don't need prior experience with Project: just be able to use a PC with Microsoft Windows.
Microsoft Project Advanced
The Advanced course takes you to a level that will put you in complete control of your projects.
You should, of course, be fully conversant with the skills and concepts taught in the Introduction course.
Microsoft Visio
Become a Visio master with our two intensive but very easy-to-follow CPD certified Microsoft Visio courses.
Microsoft Visio courses
Microsoft Visio Introduction
Get a solid base for using Visio to create high quality, impressive diagrams.
You don't need prior experience with Visio: just be able to use a PC with Microsoft Windows.
Microsoft Visio Advanced
This course will enable you to use Visio to design graphics at the highest level.
You should, of course, be fully conversant with the skills and concepts taught in the Introduction course.
Take a look at our new Marketing section which we begin with two great books on Copywriting
... there will be more to follow, so stay tuned!
Copywriting books
Copywriting for Results
A two-book set that will give you all you need to write great copy every time.
Get the first book to learn the process, then the second to see how to apply it to all media types.
  • Copywriting for Results: Your Complete Guide
  • Copywriting for Results: Putting It Into Action
Watch This Space
We have more in the pipeline so be sure to check back soon to see what's new!
More marketing books
Take a look at our new Leadership & Management section which we begin with a superb course on Managing Teams
... there's lots more to follow, so keep in touch!
Team Leadership courses
Team Leadership & Line Management
For practical advice on managing teams for results.
Make your team successful and more positive with tons of real-world techniques that work.
  • Team Management for Line Managers & Supervisors
  • Building High Performing Teams (in production)
Watch Out For More!
We have more courses in the pipeline so check back soon to see what's new!
More team leader courses
Great, easy-to-follow courses on how to succeed in sales and presentations
Drive Your Sales to New Levels
Selling Skills course
Learn how to sell more, to more people
Deliver Presentations that Get Results
Presentation Skills course
Build and deliver great presentations
Your Keys to Success are here!
Sales Management course
Manage your team for great results
Great, easy-to-follow CPD certified courses on skills that will change your life!
Learn How to Stop Wasting Time!
Time Management course
Get more out of every day of your life ...
Boost Your Self Esteem: Be Assertive
Assertiveness course
Learn how to deal with bad behavior
Great Communications = A Happy Life!
Communication Skills course
Supercharge your communications
Improve Your Relationships
Building Relationships course
Learn how to be more likeable!
Get a Plan to Beat Your Stress
Stress Management course
Learn how to reduce & manage your stress
It's the Behavior, Not the Anger!
Anger Management course
Control anger in yourself and other people
Site Cookies
We have placed cookies on your device to help make this website better.

You can change your cookie settings in your browser. Otherwise, we'll assume you're OK to continue.

I'm fine with this