Pivot Table in Excel

[The Complete Guide]

This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you
can still follow the exact same steps.

Table of Content

 Chapter 1: What is a Pivot Table? And why use it?

 Chapter 2: Grab your free exercise file here!

 Chapter 3: Prepare your data (video)

 Chapter 4: How to create a Pivot Table

 Chapter 5: Update Pivot Table range

 Chapter 6: How to use a Pivot Table

 Chapter 7: How to sort and filter a Pivot Table

 Chapter 8: Formatting your Pivot Table

 Chapter 9: Visualize your data using Pivot Charts

 Chapter 10: Is your Pivot Table not working? Fix it here!

 Chapter 11: Grab your FREE assignments here!

 Chapter 12: Wrapping things up and what to do now…

What is a Pivot Table and why use it?

Ah yes, the Pivot Table. No doubt, one of the most loved yet maligned Excel tools.

Many people have heard of Pivot Tables but fewer know exactly what they are.
If you are a business professional, you have likely seen Pivot Tables at work. If you are a job
seeker, you might have noticed Pivot Table skills are highly sought after.
So, what is a Pivot Table?

The best way to define it is: It’s a built-in feature of Excel that allows you to take data and re-
arrange it.

It creates a table that allows you to choose what and how you want to view your data.

Why use Pivot Tables?

For starters, they improve the ability to draw actionable conclusions from your data. This is
especially true of large sets of data with many different attributes.

Later, I’m going to show how you manipulate the filters, columns, and rows of a Pivot Table.
This allows you to quickly summarize and visualize the data from many different perspectives.

How to create a Pivot Table

Alright, you’re here to learn how to create a Pivot Table.

Follow the 6 steps to a Pivot Table outlined below. In a matter of minutes, you have
created your very first Pivot Table 🙂

Then you’ll learn how to create a Pivot Table from multiple sheets (which is
surprisingly handy).

Let’s get right into how to create a Pivot Table.

About the data set

We will use a data set that contains sales totals for some products of varying size and
price. The data set also includes data of each transaction as well as the wholesale price
for each.

How to create a Pivot Table in 6 (easy) steps

1: Ensure that all your data columns have headers.

2: Ensure that your data contains no blank rows

3: Click on any part of the data table

4: In the ‘Tables’ group on the ‘Insert’ tab, click on ‘PivotTable’. The ‘Table/Range’ is
selected by default as your contiguous range of data (ensured by steps 1 and 2). Leave
the defaults selected in the ‘Create PivotTable’ dialog box.
5: Click ‘OK’ and your new worksheet will now be activated.
6: Choose fields to add to your Pivot Table as needed from the Pivot Table ‘Field List’.

Note: The ‘Field List’ is only visible if you click the cursor into the Pivot Table itself. If
you click into a cell outside the Pivot Table, the ‘Field List’ will become hidden.

The resulting Pivot Table is shown in the next figure. Note that ‘Month’ is set as the
columns while ‘Location’ shows as the rows, just as we set it up.

The Pivot Table sums up all the sales figures by location and month for us. This is just the
beginning of what Pivot Tables can do for you!
 How to create a Pivot Table from multiple sheets
Now you know how to create a Pivot Table from data on a single worksheet.

Let’s look at how to create a Pivot Table from multiple worksheets. 

In our example, we have three separate data worksheets for our locations. They are included as
sheets in the example workbook file.

Here, their names are ‘Chicago’, ‘Nashville’, and ‘San Francisco’.

Let’s select the first worksheet of the three, ‘Chicago’.

Then go ahead and follow the next 11 steps…

1: Press Alt + D, then press P to make the ‘PivotTable and PivotChart Wizard’ appear.
2: Under “Where is the data you want to analyze?” select the radio button for “Multiple
consolidation ranges” and then click ‘Next’.

This takes you to ‘Step 2’ of the wizard.

3: Select the radio button “I will create the page fields” and click ‘Next’.

4: The next thing you need to do is select your first data range.

In the example file, this will be the data table on the ‘Chicago’ tab and you need to select
the range from A2:H7.
5: Repeat the same process for both the ‘Nashville’ and ‘San Francisco’ location sales
data.

6: Now, highlight the first range in the ‘All ranges’ section. Then click the radio button
next to ‘1’ in the ‘How many page fields do you want?’ section.

7: Under ‘Field one:’ type in a name for the ‘page field’. In this example, simply type
‘Chicago’.
8: Now repeat this for the remaining two ranges. Then click ‘Next.

9: In step 3 of the wizard, leave the default setting of ‘New worksheet’ selected and click
‘Finish’.
The resulting Pivot Table should look like the following figure.

This Pivot Table has combined the sales figures from all three location worksheets.

One thing to note is the filter in cell B1. This filter allows you to select one or more of
locations.
Make sure the box next to ‘Select Multiple Items’ is checked. Now you can select or
unselect any combination of the filter items as you see fit.

Update Pivot Table range

Has your source data for an existing Pivot Table changed?

Then, you need to update your Pivot Table range.

Sometimes existing data changes. In this case, refreshing the Pivot Table is necessary.

In other cases, you may add more data.

To include this new data, you need to change your data source.

Here is a closer look at those 2 methods.

Here is a closer look at those 2 methods.

 Method 1: Change Data Source

 Method 2: Refresh a Pivot Table

Method 1: Change Data Source

Do you want to add new data to your Pivot Table source data?

Then, you need to change your data source to expand it to the new range.
Let’s say you add October data to your original data. This original data has data from April
through September. You will need to expand the data source range to include the new rows of
Please note that there is a worksheet in the example file with a tab name of ‘Oct Data To Add’.

This can be copied and pasted to the original raw data on the ‘Sales Data Raw’ worksheet. This
makes it a bit easier to follow along with the next few steps.
Once you have added the new October data, go to your Pivot Table and click into any cell within
the Pivot Table.

Now click on ‘Change Data Source’ in the ‘Data’ group of the ‘Analyze’ tab.

Make sure ‘Table/Range’ (in the ‘Change PivotTable Data Source’ window) matches the range
that includes your new data.

Then click ‘OK’.

If it doesn’t match, you may need to manually select the entire range.
Just click a cell in the data, and use the shortcut Ctrl + A to expand to the new used range.

You should now be able to see the new October data as its own column in your Pivot Table
now.

Excel also allows for using ‘dynamic named ranges’ as data sources.

This can simplify the process of changing your data source.

For more information about how to create ‘dynamic named ranges’ in Excel, check out this
resource.

How to create a dynamic defined range in an Excel worksheet

Summary

In Microsoft Excel, you may have a named range that must be extended to include
new information. This article describes a method to create a dynamic defined name.

Note

The method in this article assumes that there are no more than 200 rows of data. You
can revise the defined names so that they use the appropriate number and reflect the
maximum number of rows.

How to use the OFFSET formula with a defined name

To do this, follow these steps, as appropriate for the version of Excel that you are
running.

Microsoft Office Excel 2007,  Microsoft Excel 2010 and Microsoft Excel 2013

1. In a new worksheet, enter the following data.

 A B

1 Month Sales

2 Jan 10

3 Feb 20

4 Mar 30

2. Click the Formulas tab.

3. In the Defined Names group, click Name Manager.

4. Click New.

5. In the Name box, type Date.

6. In the Refers to box, type the following text, and then click OK:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

7. Click New.

8. In the Name box, type Sales.

9. In the Refers to box, type the following text, and then click OK:

=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

10.Click Close.

11.Clear cell B2, and then type the following formula:

=RAND()*0+10

Note

In this formula, COUNT is used for a column of numbers. COUNTA is used

for a column of text values.

This formula uses the volatile RAND function. This formula automatically
updates the OFFSET formula that is used in the defined name "Sales" when
 you enter new data in column B. The value 10 is used in this formula because
10 is the original value of cell B2.

Microsoft Office Excel 2003

1. In a new worksheet, enter the following data:

A B

1 Month Sales

2 Jan 10

3 Feb 20

4 Mar 30

2. On the Insert menu, point to Name, and then click Define.

3. In the Names in workbook box, type Date.

4. In the Refers to box, type the following text, and then click OK:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).

5. Click Add.

6. In the Names in workbook box, type Sales.

7. In the Refers to box, type the following text, and then click Add:

=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

8. Click OK.

9. Clear cell B2, and then type the following formula:

=RAND()*0+10

Note

In this formula, COUNT is used for a column of numbers. COUNTA is used

for a column of text values.
 This formula uses the volatile RAND function. This formula automatically
updates the OFFSET formula that is used in the defined name "Sales" when
you enter new data in column B. The value 10 is used in this formula because
10 is the original value of cell B2.

You also need to know how to refresh your Pivot Table…

And that’s exactly what you’ll learn in method 2 below!

Method 2: Refresh a Pivot Table

Another method you need to know about is how to refresh a Pivot Table.

This is necessary when your source data changes.

Let’s say you need to make a price change:

 You want to increase the price from $275.79 to $299.99

 This applies to the product code BP110966-XL

You can find this in your original data for your Pivot Table.

The incorrect price is causing the sales numbers to be understated.

To solve this, filter on that product code and change the sales price manually.

The new price for all these rows of data will have an impact on the amounts in the ‘Total’
column.

The next step is to go back to the pivot table and click into it so that we can see the Analyze tab.
Now find the ‘Data’ group and click ‘Refresh’.

Notice in the following figure how the ‘Grand Total’ has increased due to the price change we
made.

This only took effect once we refreshed the Pivot Table.

How to use the Pivot Table

Your Pivot Table is created and you’re super excited to use it.

But how do you use the Pivot Table?

That’s what this section is all about.

Topics:

 Fields
o Adding columns
o Report filters
 Value field settings
o Average
o Count
o Distinct count
 Grouping
 Calculated fields
 Drill down
 Use data from a Pivot Table in a formula

Using Pivot Table Fields

A Pivot Table ‘field’ is referred to by its header in the source data (e.g. ‘Location’) and contains
the data found in that column (e.g. San Francisco).

By separating data into their respective ‘fields’ for use in a Pivot Table, Excel enables its user to:
  Display datasets in a more logical output e.g. ‘Total Units Sold’ in San Francisco. This is covered
in greater detail later when we discuss ‘Value Field Settings’.
 Extract and display relevant data by organizing it in a way that is more suitable – either
figuratively or aesthetically.
 Filter the data accordingly based on values found in the selected ‘field’.

Adding Columns to Your Pivot Table Fields

Let’s take a step back and recreate the Pivot Table with our added October data:

Now we have our blank table with which to start arranging the data with our Pivot Table ‘fields’.

All available fields found in the data set – as defined by its headers – are listed on the right-hand side in
the ‘PivotTable Field List’ area.

To add data columns into the table, drag and drop the desired field into
‘Column Labels’, ‘Row Labels’, or ‘Values’ (these 3 are also covered in more detail
later).
This example setup would list the data in rows separated by ‘Location’ and ‘Item’.
Columns are separated by ‘Month’.

This forms a grid to display the ‘Total of Units Sold’ in each ‘Location’ by ‘Month’.

The output of this arrangement looks like the following:

When we select ‘Location’ and ‘Item’ for our rows, something cool happens:

1. The ‘Location’ totals for each month are now viewed as subtotals for each month.
2. The breakdown by ‘Item’ is also visible within each ‘Location’.

To hide ‘Item’ rows, click on the minus next to the ‘Location’ rows.

This leaves only the subtotals visible for that ‘Location’.

Using Report Filters

With the Pivot Table set up, you can see the breakdown of total sales across the chosen
ranges (‘Month’, ‘Location’, and ‘Item’).

What if you needed to provide a look at units sold by item for all the locations?

This is where ‘Report Filters’ come into play.

You drag and drop ‘Item’ into the ‘Report Filter’ area.

Let’s also set ‘Rows’ to ‘Month’ and ‘Columns’ to ‘Location’.

This gives you a brand-new perspective on the data.

This adds another row above your Pivot Table with the selected ‘Filter Field’.

There’s also a drop-down box to make a selection…

This allows the user to select only the relevant data required for the query:

If you want to select multiple entries from the filter, check the box next to ‘Select
Multiple Items’ at the bottom of the drop-down.

Then you will be able to select all available entries in the filter in whatever combination
you need.

Here we will select “Car” Rack and note the change in values:
How to Create a Drop-Down List in Excel:

A drop down list is actually a tiny window from where you can peek into some other data you
have constructed.

This also gives you the possibility of controlling exactly what can be entered into a cell. This
makes the drop-down menu a very common and powerful data validation tool.

Before we start the clock, let’s take a look at the data in the sample file.
First we have a sheet with all the names of our employees.

This is the data we would like to see when opening our drop-down menu.

Then we have the “gateway” where we want to be able to scroll through all the names on the
other sheet.

This “gateway” is placed in the “Dropdown” sheet of the Excel sample file.

We’ll put our drop-down menu in cell B1.

Step 1: Go to the “Data” tab

This step is easy!

Simply move your cursor to the “Data” tab and click it.
Step 2: Click “Data Validation”
Now click the button “Data validation” in the middle of the ribbon (do not click the little
arrow – simply click the top part of the button).

Step 3: Choose the “List”

In the pop-up box, you need to click the drop-down menu to pick what kind of data validation
method you want to use (how meta is that?!).

Click the arrow next to the field and click “List” from the… well… list!

Then click “OK” to move on to the next step.

Step 4: Select the source

Remember the data sheet?

That’s where I’ve put all the employees for our drop-down list.
Now it’s time to tell Excel that our drop-down should be connected to that data.

Click once in the “Source:” field.

Then click the “Data” sheet and select all the employees.

When you’re done click “OK”.

OK”.

Protip: Want to speed up this process even more?

There’s a neat shortcut you can use to select all the employee names in (almost) an instant.

Redo and Repeat an Action

Both ‘Redo’ and ‘Repeat an Action’ have the same shortcut — ‘F4’ or ‘Ctrl + Y’ (‘⌘ + Y’ for
Mac). However, they have entirely different functions.

Here’s a quick differentiation between the two:

 Redo is the opposite of ‘Undo’. It’s useful when you want to reverse an undo action.
 Repeat an action lets you… Well… Repeat an action! 😊

Like ‘Undo’, ‘Redo’ has certain limitations: 

 By default, you can only redo up to 100 steps.

 You can’t redo an action that wasn’t caused by an undo action.

Here’s a good example of when to use the ‘Redo’ function:

You made a weekly expenses tracker and you just finished writing a ‘SUM’ formula of all
your expenses during the week. 

However, you mistakenly pressed ‘Ctrl + Z’ thinking you missed the first amount and
deleted the formula.

Instead of rewriting it, all you have to do is use the ‘Redo’ function
by pressing ‘F4’ (‘Ctrl/⌘ + Y’ if ‘F4’ doesn’t work).
By the way, there’s no way you can use the ‘Redo’ and ‘Repeat an Action’ functions at the
same time.

Once you ‘Undo’, Excel automatically sees the shortcut as ‘Redo’. But if you did
something like highlight a certain cell, the shortcut is in ‘Repeat an Action’ mode.

To illustrate, let’s say you want to highlight all the amounts under the “Groceries”
category: 

All you have to do is highlight the first one manually.

Then, select the cells you want to highlight and press ‘F4’ (‘Ctrl/⌘ +
Y’ if ‘F4’ doesn’t work).
 Autosum
There are many ways to add numbers in excel.

Oftentimes, we place the sum total at the end of a column or row with the values we
want to add.

When adding numbers in a column or row, the usual method is by using the ‘SUM’
function and entering the range of the addends.

But what if you have more than 100 addends?

Autosum makes it easy by simply pressing ‘Alt + =’ (‘⌘ + Shift + T’ for Mac). Excel

automatically writes the ‘SUM’ formula for the entire column range in that cell.

Press ‘Enter’ and you’re done!

 Direct Precedents: Select Cells Referenced by the Formula

If you need to check whether the formula you entered covered all the cells
needed, simply use the shortcut ‘Ctrl + [‘ (‘^ + [‘ for Mac).
 Show/Hide Formulas

If you like to check the formula itself, you can show or hide the formulas
by pressing ‘Ctrl + `’ (‘^ + `’ for Mac). The (`) is the grave accent mark key located
in the upper left corner of your keyboard.
 Insert/Delete a Row/Column
Using the mouse to insert a column/row is done by either: 

 Clicking the ‘Insert’ button under the ‘Cells’ category on the Ribbon 
 Right-clicking the headings and selecting ‘Insert’ where you want to insert a row/column

The faster way, however, is to use shortcuts.

To add a row/column, select the row/column where you want to insert the new
row/column and press ‘Ctrl + Shfit + +’ (‘^ + I’ for Mac).
To delete a row/column, press ‘Ctrl + -’ (‘^ + -’ for Mac).

Hide a Row/Column
If you wish to hide rows and columns without deleting the data, you can simply format
them to do so.

The ‘Hide & Unhide’ formatting is found in the Ribbon under the ‘Cells’ category as
shown below:
The shortcuts are much easier.

To hide a row, select the row and press ‘Ctrl + 9’ (‘^ + 9’ for Mac).

To hide a column, select the column and press ‘Ctrl + 0’ (‘^ + 0’ for Mac).
 Unhide Shortcuts

If you wish to unhide the hidden rows/columns, simply add ‘Shift’ to the set of

keys.

To unhide a row, select the row and press ‘Ctrl + Shift + 9’ (‘^ + Shift + 9’ for

Mac).
To unhide a column, select the column and press ‘Ctrl + Shift + 0’ (‘^ + Shift
+ 0’ for Mac).
If you’re using Windows 10, the shortcut for column ‘Unhide’ may not work due to
a keyboard switching shortcut. If so, simply use the button from the Excel
Ribbon to unhide your rows/columns.

Apply/Remove Filter
Using filters let you slice and dice your data.

Excel has AutoFilter and advanced filters to help you hone in on the information you want to
see.

The ‘Filter’ button is found on the Data tab under ‘Sort & Filter’ category of the Ribbon.

The shortcut for applying (and removing) a filter is ‘Ctrl + Shift + L’ (‘⌘ + Shift

+ L’ for Mac). Before applying the filter, make sure to select a cell (any cell will
do) within your data range.
 Insert Current Date
Let’s say you want to log the current date.

Instead of manually writing down the date, you can simply use the shortcut — ‘Ctrl
+ ;’ (‘^ + ;’ for Mac).
 Apply Date Format

The date you’ll get after using the shortcut is in dd mm yyyy format.

If you want to apply the dd mmm yy date format, press ‘Ctrl + Shift + #’ (‘^ +

Shift + #’ for Mac).

Insert Current Time

Lastly, you can also insert the current time.
Press ‘Ctrl + Shift + ;’ (‘^ + Shift + ;’ for Mac) to insert the current time.

Conclusion
When selecting the cells for the source, select the first name you encounter (in cell A2) – then
hold down Ctrl + Shift and click the down arrow on your keyboard. You’ve now selected all the
names in the sheet in just a few seconds.
Step 5: Here’s the end result!

Value Field Settings

In the previous example, we used the ‘Sum Value Field Setting’.

This displays ‘Total Units Sold’ for each ‘Month’ and ‘Location’ listed in the Pivot Table.

By using different ‘Value Field Settings’, the data in the field presented
under ‘Values’ is presented in different ways.

To access a field’s ‘Value Field Settings’, click on its entry. Then select it from the
resulting drop-down:
There are several possibilities for ‘Value Field Settings’. I’m going to show you the most
common.

These settings are only available for fields placed in the ‘Values’ area.

It’s not possible to apply them elsewhere.

Average
Click on ‘Value Field Settings’ and select ‘Average’.

This tells the Pivot Table to average the content of all the entries in that field and display
that as the value.

But, this doesn’t tell you anything about how many entries (transactions) there are.

Neither does it tell how they’re spread across all products.

It gives you the average number of products sold.

Count
Selecting ‘Count’ tells the Pivot Table to add up the number of entries in that field.

Then display that as the value.

In this case, this translates to the number of transactions (or rows) in our
original data.

Performing this action on the example data yields the following:

Notice how the upper left cell has changed to show it is now displaying values as ‘Count
of Units Sold’.
This data shows that there are 25 transactions occurring in April at Chicago.

Distinct Count (Count unique values)

Selecting ‘Distinct Count’ is like ‘Count’.

But it will ignore (not count) any entries with a value it

has already encountered.

Using the example data with ‘Units Sold’, let’s say two transactions in April at Chicago
both sold 5 units.

Only one is unique. So, it’s only counted as one.

Notice how the upper left cell has changed to show it is now displaying values as
‘Distinct Counts’.
Using ‘Distinct Count’ is a useful ‘Value Field Setting’.

For instance, if you wanted to count how many types of an Item was sold in each month
at each location.

Grouping
Still using the above example, the fields have been set up.

Any relevant filters are applied and the ‘Value Field Settings’ have been set to ‘Sum’.

This shows us ‘Total Units Sold’.

What if you no longer want to see it monthly, but want to see the data organized
into financial quarters?

That’s where ‘Grouping’ comes into play!

First, select any entries you wish to group (this is only applicable for ‘Row’ and ‘Column’
fields).

In this case, we will group April, May, and June.

Right click and select ‘Group’:

This will display a new column preceding it with a defaulted group name.

Doing this for the remaining months splits them into quarters as follows:

Now, this operation has achieved the technical task of grouping the data.

But visually, it does not portray what we are trying to achieve.

To rename the separate group entries, click on their cell and replace the text.

To rename the separate group entries, click on their cell and replace the text.

In this case, ‘Group 1, 2, and 3’ are replaced with ‘Quarter 1, 2, and 3’.

Perform the same to ‘Month2’ to achieve the following:

Finally, you need to display the values in these groups.

Add ‘Subtotals’ to the groups by right clicking on ‘Quarter’ and select ‘Subtotal “Quarter”’.
Now you will have subtotals for ‘Units Sold’ by quarter.

This displays subtotals in addition to the separate entries in the group.

If the sub-entries are no longer needed, collapse each group using the +/- box next to each
‘group field’.

This displays the table with collapsed groupings:

 Calculated Fields

Calculated Fields’ allow you to insert values into the Pivot Table based on formulas.

These formulas can use the values of fields that are present.

Let’s say the manager at each location gets a bonus percentage added to his salary. This bonus
is based on the number of units sold each month.

If that bonus % was equal to Units Sold / 100 we’d have to add more data to the original
table to be able to calculate this.

But, with ‘Calculated Fields’, this isn’t necessary!

Select the Pivot Table.

Then, navigate to the ‘Analyze’ tab.

Find ‘Fields’, ‘Items’ and ‘Sets’ in the ‘Calculations’ group.

Clicking ‘Calculated Field’ here opens the ‘Calculated Field’ dialogue.

Experiment using different operations and using different fields to see what is
possible.

Using the example of ‘Bonus’ as a new ‘Calculated Field’, inserting it in the ‘Values’ area
yields:
Now it’s easy to see what percentage bonus each manager should get in each
month.

All this without adding any extra data to the original source.

Pivot Table Drill Down

The primary purpose of the Pivot Table is to combine multiple ‘fields’ of the same type.

Then display them in the desired way.

It’s also handy to be able to do the opposite:

Namely, view the results that comprise the given value entry on the table.

Consider the scenario where a ‘Count of Units Sold’ is provided.

This is broken down into ‘Month’ and ‘Location’ on a 2×2 grid layout (as in the earlier
examples).

Let’s say you are asked for a list of transactions that comprise the entry in May at San
Francisco.

Further, it requires all data associated with those transactions.

Then the Pivot Table’s collapsible functionality has hidden these details from view!

The operation of drilling counters this. Instead, every record associated with that field
entry is displayed.

Using the above example, right click the ‘Value’ cell associated with May and San
Francisco.

Then select ‘Show Details’:

This will drill that data point to gather all its associated entries.

These will then be displayed in another sheet by default:

This is a way of backtracking to the original data.

You now have an auto-generated list of entries for this data point.

Use data from a Pivot Table in a formula with GETPIVOTDATA

So far, I’ve shown you how to use a Pivot Table with data that comes from somewhere
else.

For example, you use a Pivot Table to create a report from a set of sales data.

Although a Pivot Table is powerful, you still have plenty of uses for other
formulas in Excel. Sometimes, these formulas refer to data inside a Pivot Table.

This poses an issue; what happens when the Pivot Table is rearranged?
Excel has made the ‘GETPIVOTDATA’ function to help you with references to Pivot
Tables.

This function glues together the references and the cell they are referring to. It’s just like using
relative references.

The ‘GETPIVOTDATA’ function is unique. As opposed to other functions, all you need to
do is to click a cell.

Start a formula in cell G5.

Type the equal sign and click a cell with data within the Pivot Table. Then hit ‘Enter’.

It will look like this:

The entire ‘GETPIVOTDATA’ function is constructed for you by Excel.

This is the syntax of the function:

GETPIVOTDATA(Data_field, Pivot_location, [field1, item1, field2, item2], …

)

Which turns into:

GETPIVOTDATA(“Units Sold”,
$A$3,”Location”,”Nashville”,”Month”,”April”)

 ‘Data_field’ = Name of the field whose value is desired. In this case, “Units Sold”.
 ‘Pivot_location’ = Cell reference to the top left cell of the Pivot Table. In this case, an
absolute reference to A3.
 The fields and items are extra identifiers to tell where the cell is located.

You can edit a ‘GETPIVOTDATA’ function but don’t try to create it from scratch
yourself. Let Excel do all the work and just click the cell within the Pivot Table when you
need it.
 Pro tip

Note that ‘GETPIVOTDATA’ only returns data that is visible.

Try to remove a field from the field list. If that field removes the cell entirely, the
function returns an error.

Put the field back.

The function recognizes the reference again and returns the result.

Sorting and Filtering

Your Pivot Table is now ready to sort and filter.

Let’s take a closer look at that!

Topics:

 Sorting by value
 Sorting by date
 Sort Top 10 using the value filter
 Filter and organize data using ‘Slicers’

For all examples in this section, you need to set up your Pivot Table in a specific way.

Use the raw sales data with the following ‘field’ structure:
This lists all the ‘Transactions’ as ‘Rows’. ‘Total Units Sold’ as ‘Values’ separated into ‘Columns’
depicting ‘Months’.

Sorting by Value
Ensure the ‘Item’ filter is set to include ‘All Values’.

Notice, the ‘Transaction field’ contains data in the format of an 8-digit number (e.g.
20100000).

When the Pivot Table is formed in the above way, the rows are populated and sorted in
ascending numerical order:
By default, Excel will sort any numerical, alphabetical or dated list. All it requires is a logical,
natural order. Such as 1 2 3, a b c, Jan Feb Mar etc.

Overriding this sorting mechanism is done in a few, easy, steps.

Access the drop-down arrow next to the field of your choice to see available sort options.

Just as in the picture below…

  Selecting ‘Sort Smallest to Largest’ will cause a sort in ascending order.
 ‘Sort Largest to Smallest’ will cause a sort in descending order.

In this example, ‘Transaction’ will be sorted in descending order:

Notice how the data has now been flipped upside down. This is confirmed by entries
showing for October instead of April. This is indeed in the last segment of the data.

Sorting by Date
Dates (including day names, month names, years) are logically understood by Excel
as ordered lists.

The above method can be applied to the ‘Month field’ in the columns to sort them into
their reverse order:
 Using the Value Filter to Sort Top 10
Besides the mentioned ‘Sort’ methods, Excel has a built-in method to present the ‘Top
10’ records.

This is based on the selected ‘Value field’.

As an example, say we want to see the ‘Top 10’ transactions for total units sold.

To do this, select the drop-down next to the ‘Transaction’ entry.

Then navigate through ‘Value Filters’ and ‘Top 10’:

You will be prompted to select how many ‘Top’ entries to display (in this case, we want
the top 10).
You also need to select which ‘Value Field’ to base the selection on. As we only have
‘Units Sold’ listed as values, currently that is the only option.

Proceed with the suggestion in the dialogue:

We will now be presented with the Top 10 transactions.

In this case, transactions with 6 units sold make it into the Top 10:

Slicers
‘Slicers’ offer a more intuitive way to filter and organize the data within a Pivot Table.

To insert a ‘Slicer’ linked to your Pivot Table, just do the following:

Ensure the Pivot Table is selected and navigate through ‘Options’ -> ‘Insert Slicer’ from
the Excel Ribbon:
In the proceeding dialogue, you will be asked to select which field(s) to be toggled from
this ‘Slicer’.

For this example, select ‘Month’ and move the resulting ‘Slicer’ to the right of your Pivot
Table:
The ‘Slicer’ is now populated with all available values for ‘Month’.

These are based on all its findings within the source data for the Pivot
Table.

Proceed to toggle each month’s visibility in the Pivot Table by selecting/deselecting it

from the ‘Slicer’.

To do this, click on the month in the ‘Slicer’.

This user interface avoids the need to enter the Pivot Table manually to apply your
filters.

Instead, this is a much more intuitive approach.

‘Slicers’ can even contain fields that are not currently presented in the Pivot
Table. Let’s say the table was sliced on ‘Item’. This could be used to only show values for
‘Units Sold’ for each item separately. It could also be grouped however you desire.

Pivot Table formatting

Excel offers many built-in options for formatting the Pivot Table.

In this part of the tutorial, you’ll learn about the most common and best options.

Topics:

 Pivot Table styles

 Banded rows
 Report layouts
o Compact form
o Outline form
o Tabular form
 Conditional formatting for a Pivot Table
  Number formats
 Removing blanks
 Change name of Row Labels

Pivot Table Styles

With the Pivot Table selected, navigate through the Excel Ribbon to ‘Design’:

The rightmost part of this tab’s content is the ‘Styles’ section.

These are predefined graphical layouts for your Pivot Table.

These layouts are available in a range of color schemes. These color schemes are grouped into
three categories; Light, Medium, and Dark.

These categories might vary a bit in your end.

If you have another version of Excel, it might be categorized a bit different. Some third
party add-ins also affect the categorization.

Hovering over a style will reveal its name in a tooltip.

The variety that suits your report the best will be dependent on any current color
schemes you have.
It also depends on whether you have some other formatting to consider.

The ‘Style’ formatting takes a low precedence. This gives way to any ‘hard’ formats
you may have already applied to cells in the Pivot Table manually. This would include
formatting such as ‘Background Fills’, ‘Font Color’, etc.

Banded Rows
Excel also enables you to ‘band’ the rows within the selected ‘Style’ and color scheme.

This makes it much easier to trace rows from the initial column through to
its contained data.

To activate ‘Banded rows’, navigate back to the ‘Design’ tab.

Select the checkbox ‘Banded Rows’ within the ‘Style Options’ group for the Pivot Table.

The depth of color is dependent once again on the style already selected.

Report Layouts
Excel allows 3 main types of report layouts to be applied to you Pivot Tables.
These are:

 Compact
 Outline
 Tabular

Here’s a quick run-down of each one…

Compact Form

This is the default layout for a Pivot Table.

It has the following characteristics:

1: All row labels are displayed in their own row

2: The ‘row field’ label is always above the labels for its inner fields

3: All nested ‘field’ labels are indented to highlight the hierarchy

4: All ‘row fields’ occupy a single column

5: Subtotals can be shown at the top or bottom of groups

6: Subtotals for ‘column fields’ are always shown at the bottom

Outline Form

This layout minimizes the width of the Pivot Table.

The benefit?

You reduce the need for scrolling (which is always a nice thing!).

This layout form has the following characteristics:

1: All row labels are displayed in their own row

2: The ‘row field’ label is always above the labels for its inner ‘fields’

3: There is no indented hierarchical structure

4: Instead of indentation, each ‘row field’ occupies a different column

5: Subtotals can be shown at the top or bottom of groups

6: Subtotals for ‘column fields’ are always shown at the bottom

Tabular Form

This layout form has the following characteristics:

1: All row labels for outer fields are on the same row as their first inner field

2: There is no indented hierarchical structure

3: Instead of indentation, each ‘row field’ occupies a different column

4: Subtotals for ‘row fields’ are always shown at the bottom of each group

5: Subtotals for ‘column fields’ are always shown at the bottom

 Conditional formatting for a Pivot Table
You can use Conditional Formatting on rows, columns or even individual cells in a Pivot
Table.

Just like you know it from any type of data set.

Highlight the relevant data and navigate to Home -> conditional formatting.

In this example, we will look at highlighting all items that have sold more than 50
units in a month.

Follow the prompt that opens and enter 50 as the boundary for the formatting causes
the following:

Different effects can be achieved by using the other options available.

Find them under ‘Highlight Cells Rules’ such as ‘Less Than’, ‘Between’ and ‘Equal To’.
Each is geared to highlighting data that can be segregated in some way against a
benchmark.

To learn more about conditional formatting in Excel, read our in-depth guide here.

The difference between conditional formatting in a Pivot Table and normal

cells is this:

To apply a conditional formatting rule to the entire Pivot Table, use the 2nd or 3rd
options in the button that appears after using conditional formatting.

Number formats
Some data displays in an inherent logical way (e.g. currency starting with $ or £).

In other cases, it might be a case of how precise the data should be (e.g. number of
decimal places).

Modifying these characteristics in the display of a Pivot Table allow a more appropriate
representation of the data.

Consider the following data:

Total sales for the 5 items listed are displayed for the months of April, May and June:
While we might be able to guess that the numbers relate to money values, it is not immediately
obvious.

To solve this:

Select the values in the Pivot Table, right-click and select ‘Format Cells’.

In the resulting window, select ‘Currency’ from the ‘Category’ box.

Then make your choice for which format you want in the ‘Negative numbers’ box.
Select 2 in the ‘Decimal places’ combo box and click OK.

Now you have your totals in currency format to 2 decimal places.

It is now easy for the user to see the type of data being displayed.

The decimal places have all been set to a uniform figure.

Removing blanks
If there are gaps in the source data, these will be detected by the Pivot Table and
identified as (blanks).
Following the ‘Filtering’ sections of this guide, you have come across these already.

To remove the blanks from an active filter, uncheck the box next to it from the filter
menu.

Blank values don’t affect anything calculable within the Pivot Table. But, they damage the look
and feel of it. Blank values make it more difficult to view the data. This completely
defies the point of using a Pivot Table in the first place.

It is worth noting that the same also applies to any ‘Slicers’ that are linked to the Pivot
Table.

Simply deselect the entry for ‘(blank)’ and they are removed from the Pivot Table.

Change name of Row Labels

Sometimes the headers in the source data don’t describe the nature of what the data is
in the correct context.
By default, this header is what is pulled through for each ‘field’ name and is displayed on
the Pivot Table.

As a result, you might need to change this name to better reflect the nature
of the data presented.

Consider the following:

What does ‘Sum of Total’ mean?

In the context of the original source data, this was more obvious based on the columns
that surrounded it.

But, now it has been extracted for a more streamlined view in a Pivot Table.

So, it is no longer as obvious.

Something like ‘Total Turnover’ or ‘Total Sales’ is more appropriate in this situation.

To apply this change, we need to access the ‘Field Settings’ for this field.

Select the cell containing ‘Sum of Total’ and navigate to the ‘Options’ tab in the Excel
Ribbon.

Under the ‘Active Field’ group, you will find ‘Field Settings’.
Now open the ‘Field Settings’ menu.

Set the value of ‘Custom Name’ within this dialogue.

Selecting ‘OK’ will update the field name on the Pivot Table:
The label is now more representative of the data being displayed and more obvious to
whoever views it.

Pivot Charts
Pivot Charts visualize your Pivot Table in an instant.

Topics:

 What exactly is a Pivot Chart?

 Creating a Pivot Chart
 Using the Pivot Chart

What exactly is a Pivot Chart?

The relationship of Pivot Charts to Pivot Tables is like normal Excel charts to their
source data.

“Pivot” simply indicates the extra functionality granted in Pivot Tables.

This becomes available for use in its graphical counterpart – the Pivot Chart.

In short, a Pivot Chart takes the data contained in the Pivot Table and outputs it
in a graphical medium.

This is done by using one of Excel’s built-in chart types.

The most common types are:

 Bar Charts
 Line Graphs
 Pie Charts

In this example, we use a “Pie Chart“.

 Creating a Pivot Chart
Select the Pivot Table.

Navigate to the ‘Insert’ tab on the Excel Ribbon and select the desired chart from the
‘Charts’ group:

Excel offers a wide array of choices for each type of graph available.

For the sake of illustration, a simple ‘2D Pie Chart’ will suffice.

Click it to insert it into the active sheet:

 Using the Pivot Chart
The Pivot Chart is manipulated in much the same way as its corresponding Pivot Table.

In the ‘field list’ on the right, the ‘Areas’ section at the bottom changes.

This happens to better represent what can be modified on your Pivot Chart.
Notice that ‘Row Labels’ has changed to ‘Axis Fields’.

‘Column Labels’ has changed to ‘Legend Fields’.

These are the corresponding areas on graphs of all kinds, hence the use of ‘Axis’ even on a Pie
Chart. The Pivot Table information will be pulled through these areas.
With the set up on the left using the data from the ‘Sales Data Raw’ (Price change)
worksheet, a pie chart’ is displayed.

This pie chart shows the ‘Total Sales’ in its values, split into categories defined by Item
type.

As pie charts are 2-dimensional, the equivalent ‘Columns’ entry (now a ‘Legend Field’)
cannot be displayed.

Effectively it acts as a filter, allowing the pie charts to be cycled through

each entry in ‘Month’.

Any field entered into the areas ‘Legend Field’, ‘Axis Field’ or ‘Report Filter’ displays a button
on the Pivot Chart…
In the previous illustration, the month as a ‘Legend Field’ can be manipulated.

For instance, to present single months or multiple months (in the case of a pie chart,
acting as a filter).

If this was a bar chart, these would be plotted as separate entries on the same graph as
opposed to swapping with the data currently present.

The most important point to note is: The Pivot Chart has fantastic abilities to filter and
represent the data. Just like the Pivot Table achieves the same.

Troubleshooting your Pivot Table

The most common pitfalls encountered while dealing with Pivot Tables are covered
here.
Topics:

 Pivot Table won’t sort

 Pivot Table ‘field’ name is not valid
 Pivot Table ‘field’ name already exists
 Where’s the Pivot Table wizard?
 Is the ‘Field List’ missing?
 Pivot Table not updating or refreshing
 Pivot Table cannot group that selection
 How to display text as a value field

Pivot Table won’t sort

There can be many different causes of sorting issues.

Let’s look at an example of a Pivot Table we have set up with the month as our rows.

We want the ‘Month’ values to sort naturally the way they occur
sequentially through the year.

But, something is wrong with our source data so our values are sorting alphabetically.

Notice there are duplicate values for ‘Month’ in the filter list.

This is a huge tip-off on what is likely causing our issue.

Can you guess what it might be?

This is not what we want from our sorting.

We need to see the ‘Month’ values sorted in the order they fall in the year.

We need to check our source data!

One of the most common causes of this is trailing spaces. These are not
visible to the naked eye. So, we need to ensure that our ‘Month’ values do not have
extra spaces by using the trim function.
Simply add a column next to ‘Month’ and type in the ‘TRIM’ function as shown in the
previous figure.

Copy and paste all the way down.

Then Copy the entire column less the header and paste as values in the ‘Month’ column.

Also, delete the ‘TRIM’ column.

Everything looks just like it did before, but now we know that our ‘Month’ values
contain no extra spaces. This has cleaned our values and changed how Excel will now
detect them.

Upon refresh of the Pivot Table, the ‘Month’ rows now sort naturally in sequential order.

Awesome, right?!
 Pivot Table field name is not valid
Sometimes your source data may have a missing column header.

This could be due to a blank column mixed in with the source data.

It could also be a column with data that just has a blank header like the
following:

Note columns F and H: This is one example of a completely blank column and the
other with a missing header for a column of data.

If you attempt to create a Pivot Table with this data set, Excel will return the following
error:

To solve this issue, you need to remove blank columns.

Then you (manually) need to type in any missing headers for data columns:
Be aware that this doesn’t only effect Pivot Table creation!

Let’s say this source data is changed after a Pivot Table is created and a header is
removed.

When the Pivot Table undergoes an update/refresh the same error will be
encountered (the same solution applies).

Pivot Table field name already exists

When forming the source data in Excel there is no validation against two or more
columns having the same designated header.

That is unless it is explicitly formatted as a ‘List Object’ (such as the built-in ‘Table’

functionality).

When it comes to pulling this data into a Pivot Table, any ‘fields’ formed must have
unique names (as it too is a ‘List Object’ like a normal ‘Table’).

If Excel comes across several columns with the same header, it tries to handle
it. This is done by suffixing an incremental number to the ‘field’ name. This happens
correspondingly to the left-to-right order the columns are found in the data.
You might run into this kind of issue where the ‘Month’ column header is actually a
duplicate ‘Date’ header.

When you go to create your Pivot Table, you will notice that Excel has added a ‘2’ on the
end of the second ‘Date’ header.
Recall from the earlier section about renaming Pivot Table ‘fields’?

Here, I showed how you can easily change this in your Pivot Table itself.

Just keep in mind that this does not change the header in the source data. For ‘value fields’
Excel will not allow the reuse of ‘field’ names that already exist in the source
data.

Where’s the Pivot Table wizard?

In Excel 2016, the Pivot Table wizard is not shown in the Ribbon by default.

To add it to your view, navigate to ‘File’ -> ‘Options’ as in the next figure.
From here select the sub menu ‘Customize Ribbon’.

Select ‘Commands Not in the Ribbon’ in the drop-down menu for ‘Choose Commands’
from:
Add this to whichever Ribbon entry you desire. It makes the most sense to place it on ‘Insert’
where the Pivot Table ‘Insert’ button is.
 Activate the Pivot Table / Chart wizard with this shortcut

Alternatively, the Pivot Table / Chart wizard is directly accessible using the following
keyboard shortcut:

Alt + D, then P

Either option will offer the same outcome.

Is the ‘Field list’ missing?

The ‘Field List’ is the display on the right-hand side of the Excel window when a Pivot
Table is selected.

It contains all the available ‘fields’ from the source data used at the top.

The display gives four areas where fields can be dragged.

These are:

 Filters
 Columns
 Rows
 Values

The ‘Field List’ is hidden by default until you select the Pivot Table. Can’t find it?
Then your first step is to make sure that the cell selected on the active worksheet belongs to the
Pivot Table.

If the list is still invisible after this, it is likely it has been manually hidden.

To unhide, navigate to the ‘Show’ group on the ‘Analyze’ tab and click on ‘Field List’.
Alternatively, you can also go to the ‘Show’ group on the ‘Analyze’ tab and see that ‘Field
List’ is unhighlighted.

Simply click to highlight.

Your ‘Field List’ should now appear in its usual place on the right-hand side of the
worksheet.

So, you can hide the Field List with the same procedure. Just click it in the ribbon to
return it to its unhighlighted and hidden state.

Pivot Table not updating/refreshing?

You may find that when you add source data to your Pivot Table, that the Pivot Table
doesn’t update when you refresh.
An often-overlooked detail when adding new data to your source
is changing your data source.

You must ‘Change Data Source’ to ensure that your Pivot Table data range
includes any new data. You do this by clicking into your Pivot Table to make sure the
‘Options’ tab is visible in the Ribbon. Then simply click on ‘Change Data Source’ in the ‘Data’
group.

The ‘Change PivotTable Data Source’ dialog appears showing the current selected table
or range.

The best way to update all the way to the last row of your data (assuming there were no
columns added) is to press:

Ctrl + Shift + the down arrow.

Does the table/range box indicate that entire columns are selected? Then press Ctrl + Shift +
the up arrow to bring the bottom of the range to the last row of data.

Now you simply need to click ‘OK’ and then click ‘Refresh’ once again.

This should now update your Pivot Table to include the newly added data.

Pivot Table cannot group that selection

When grouping ‘fields’ together on your Pivot Table, you might run into the “Cannot
Group that Data” error.

This is likely due to one of two causes.

1: At least one of the ‘fields’ contains data formatted as different types (e.g.

numerical and text within the same ‘field’).

Excel must apply the same grouping logic to all entries in the ‘field’. This is obviously not
possible if they are of different types.
2: At least one of the ‘fields’ contains blank cells (gaps in the data).

Excel cannot handle an empty value when trying to group with another ‘field’ that has
no empty values.

The error appears as a dialogue when such an operation is attempted:

Does point 1 apply to your data set?

Then go back to the source data and ensure the column in question is all formatted as
the same type (e.g. ‘Currency’ for price/money entries, ‘Text’ for textual entries etc.).

If point 2 applies, it is rarely an acceptable solution to add data into the blanks to
make grouping work. This data has no true meaning and is false.

The question instead is whether this column makes sense to be grouped with another.
Perhaps the answer is no and a different approach should be undertaken.

How do I display text as a value field?

A Pivot Table can only summarize data that are numerical.

If you want to summarize text data, you must use another tool or use a
workaround.

This great guide by Chandoo tells you how to deal with summarizing text data as a
‘Value Field’.

Try these free assignments

By practicing what you learn, you remember everything much better.

Additionally, you will be able to apply what you’ve learned to a broader range of real life
scenarios.

I’ve made it easy for you to practice what you’ve learned in this tutorial.
 FREE Assignment workbook

Download this assignment workbook and start practicing using pivot tables.

DOWNLOAD FREE ASSIGNMENT WORKBOOK

Wrapping up
As you’ve just learned, Pivot Tables is one of the most powerful tools in Excel.

Why? Because no other tool in Excel has this much impact on your daily work.

Reports that normally would take hours to complete you can now finish in minutes.

Does your boss want you to change the report right before a meeting?

No problem! Just a few clicks and it’s ready.