fbpx

Learn Excel Pivot Tables

Creating an Excel Pivot Table

This article is based on Excel 2019.  It is still applicable to earlier versions of Excel, but there may be some differences in menus and options

Creating an Excel Pivot Table is a powerful way of being able to transform and analyze tabular data in a spreadsheet.  It will allow the user to present the data in any format, using different headings as rows and columns in the pivot table, and will also allow the user to drill down on specific numbers to see the detail behind.

The pivot table is very easy to edit to show different views on the same data

There is also a very useful Excel formula called GETPIVOTDATA that allows specific numbers to be returned based on user criteria.  It is very similar to creating a query on a database table, except that you do not need any knowledge of how to write a query.  This is extremely useful if you wish to incorporate specific pivot table results into a dashboard within the workbook.

Initial Data

Pivot tables need tabular data as the data source.  Here is some sample data:

Every Pivot Table Starts From An Initial Set Of Data

This is a simple table of people who have various computer hardware and software

In this example, there is a numeric field of quantity, but you do not have to have a numeric field for the value field in the pivot table. Instead, you could have the value field as Count of Product, which would the number of identical products against each person

Creating an Initial Pivot Table

On the Excel ribbon, click on Insert and then click on “Pivot Table”.  A pop-up window will appear which will allow you to choose a range of data as the source and a location in the workbook

The Pivot Table Create Dialog Box Allows You To Specify Where The Data Comes From As Well As Where You Want The Pivot Table To Be

You choose a table of data by entering either a range name or data coordinates into the first input box on the pop-up.  You can select a range using the mouse by clicking on the up arrow on the right-hand side of the text box.

You can choose where you wish to see your pivot table located in the second text box.  There is an option to place it on a new worksheet or the existing worksheet

Click OK and an initial pivot table will be created, and a pane will show on the right-hand side of the screen which will allow you to design how you wish to see your data

The Pivot Table Fields Dialogue Box Allows You to Set The Fields You Want To See In The Table

To create a simple pivot table, you will need at least 1-row heading, 1 column heading, and a value

To set these headings, you simply drag and drop the field names into the appropriate box.  For example, you may want Name to be a row heading, in which case you click on Name in the field list and drag it to the Rows box.

A tick will appear against each field name as it is dragged into the appropriate box.  Your pivot table should now look like this, depending on how the rows and column headings have been arranged.

Selecting Pivot Fields Will Determine What Data Is Displayed On Rows And Columns

If you want to remove a field name, simply untick the box against the field name list at the top of the pane.

You can also change the row and column headings by dragging and dropping the field names.  Here is a different pivot view based on the same data:

Adding Filters To A Pivot Table Can Assist In Limiting The Data To Only The Specific Data Points You Want To See

Product is now a row heading and category is now a column heading

Formatting the Pivot Table

Click Here To Access The Value Field Settings Of A Pivot Table
The Pivot Table Value Field Summary Gives You Control Over The Way A Column Summarizes Data And Displays It
Control How The Table Looks Using Layout Settings.

The pivot table initially uses default settings for items such as number format, headings, and where totals appear. All of these can be changed very easily.

Each field name in the design pane has a Down Arrow next to it.  Click on this for the value field and you will see a pop-up menu.

Click on Value Field Settings and you will see a further pop-up window that will allow you to format the numbers and make other changes.

For example, you may not like the heading of ‘Sum of Quantity’.  You can enter a new heading on this window.  The one restriction is that you cannot change it to an existing field name e.g. Quantity, so you would need to call it something like ‘Quantity Total’

Number Format allows the use of all the standard Excel numeric formats, and you are not restricted to just using Sum.  You can use averages, counts, maximum, and minimum, etc.

On the ‘Show Values As’ tab there is a huge number of options for calculations

If you look at the Field Settings on a row or column heading, you will see options for ‘Layout & Print’ which allows further formatting options

When you click on a pivot table, an extra ribbon appears called PivotTable Tools.  The Design tab gives access to many different styles and formats

Filtering Data on the Pivot Table

Learn To Filter A Pivot Table Column

The pivot table provides a number of ways of filtering data so that you only see what you choose to see

The pivot table allows filtering on the first row or column label simply by clicking the dropdown.

This allows you to select specific names and your pivot table will shrink to only show that name.

However, suppose you want to filter by the second-row label (Product)?  This can be achieved by adding the second-row label into the filter box in the design pane.

This keeps the filter for ‘Name’ on Row Labels, but now allows you to filter by ‘Product’ as well

Adding Filters To A Pivot Table Can Assist In Limiting The Data To Only The Specific Data Points You Want To See

Drill Down Facility

Excel pivot tables have a very powerful drill-down facility which will give you an audit trail on any number.  Final users of data that is summarised like this will frequently challenge numbers, particularly if they are not what is expected, and fortunately, there is a simple way to provide the explanation behind the numbers

Simply double-click on any number within the pivot table and the full data behind that number will be shown on a new tab.

Double-clicking on the Grand Total numbers will provide everything

GETPIVOTDATA Formula

This is a very useful formula to interrogate your pivot table to produce values within cells

An example is:

=GETPIVOTDATA(“Quantity”,G3,”Product”,”Laptop”,”Name”,”John Smith” )

In this example, the numeric field to be returned is called ‘Quantity’ and the first cell in the pivot table is G3.  You can then enter pairs of criteria e.g. Product and Laptop where Product=Laptop and Name=John Smith

This will produce a value of 2 based on this pivot table (John Smith has 2 laptops):

Use A Special Formula To Get Data From A Pivot Table

The disadvantage is that it can only pull back one value.

If you tried using =GETPIVOTDATA(“Quantity”,G3,”Product”,”Windows 10″) then you would get a #REF! error because there is more than one number for the product ‘Windows 10’.  If you remove ‘Name’ from the pivot table so that it summates products, you will get the correct result of 3

You would also get this error if you entered a product that does not exist in the pivot table

Fortunately, you can use the IFERROR formula to stop this happening

=IFERROR(GETPIVOTDATA(“Quantity”,G3,”Product”,”Windows 7”),”Not found”)

Further Steps

Please try all these tips out using your own data.  The best way to learn pivot tables is to experiment around and see what you can produce.  Don’t forget that there is always the Undo function if you get stuck and things do not happen as intended

Like this article?

Share on facebook
Share on Facebook
Share on twitter
Share on Twitter
Share on linkedin
Share on Linkdin
Share on pinterest
Share on Pinterest

Leave a comment