All You Need to Know About

# Pivot Table Other Functions

Think you have learned everything about Excel Pivot Tables? We have a bunch of hidden gems for you to learn with the other functions of Pivot Tables!

Here are the top things on what you can do with the other functions of Pivot Tables in Excel:

## Sort Largest to Smallest Grand Totals With Excel Pivot Tables

I knew that I could sort virtually anywhere with Excel Pivot Tables, but I was surprised that I could even sort from largest to smallest with Grand Totals!
Below I have an ExcelÂ Pivot Table that consists of Sales Numbers over a three year period.
Make sure to download the Excel Workbook below so that we will have the same starting point:

In the example below I show you how to Sort by Largest to Smallest based on Grand Totals:

STEP 1:Â Right click on a Grand Total below at the bottom of the Pivot Table. Â Go to Â Sort >Â SortÂ Largest to Smallest
(If you cannot see the Grand Totals, click in your Pivot Table and go to the ribbon menu and select PivotTable Tools > Design > Grand Totals > On for Rows and Columns)

STEP 2:Â This will sort our grand totals by descending order.
See that our years are now arranged in this order: 2013, 2014, 2012.

## Data Model and Relationships In Microsoft Excel Pivot Tables

Ever had multiple related tables and wondering how to create a report that connects them together in a single Pivot Table? We have just the thing with Data Model and Relationships!
This is our data that we will use. What we want to do is create a report that shows the First Name of the student and the Number of Classes that the student has taken.
The tricky part here is the First Name is in theÂ Students Table, while the number of classes can be retrieved from theÂ Classes Table. Both tables are linked by theÂ StudentId column.

STEP 1:Â Select the Classes Table. Go toÂ Insert > Pivot Table > New WorksheetÂ
Make sure to tickÂ Add this data to the Data Model. Click OK.

STEP 2:Â Select the Students Table. Go toÂ Insert > Pivot Table > New WorksheetÂ
Make sure to tickÂ Add this data to the Data Model. Click OK.

STEP 3:Â Click All inÂ PivotTable Fields and you should see both tables there.

STEP 4:Â Now we need to link them together! Go toÂ PivotTable Tools > Analyze > Calculations > Relationships

Click New.

STEP 5:Â There are 2 sides of a relationship when we want to link them together.
The rule of thumb, is the primary table should have no duplicates. This is the Students table as it does not have duplicateÂ Student Ids.
Set the following then Click OK.
Table –Â Classes
Column – StudentId
Related Table – Students
Related Column – StudentId

Click Close.

STEP 6: In theÂ ROWSÂ section put in the Students(FirstName)Â field.Â Â In theÂ VALUESÂ section put in the Classes (ClassName)Â field.
With just that, you can see that Excel was able to show the results in a merged fashion!Â You can see Daisy has 2 classes enrolled. And from the individual tables, you would not have that information readily available!

## 3D Maps In Microsoft Excel

Did you know that you can create a 3D Map in Excel to visualize your geographic and temporal data?
You will be blown away by what it can show you, as it offers you a different perspective as compared to tables and charts!
We will be using data from a list of Power Station examples that we have downloaded from

Microsoft. This is our sample table with geographic location and specified times:

STEP 1:Â Ensure you have clicked on your data. Go toÂ Insert > Tours > 3D Map

STEP 2:Â All of a sudden you have a bare 3D Map!
Let us do the following, drag the following fields:

• Capacity (Megawatts) to Height – to show a tall bar per location according to the value
• Company to Category – to color code the bar according to the company’s portion
• Initial Date of Operation to Time – you will see something cool later, wherein an animation of a timeline will be generated!

STEP 3:Â Wait there’s more! Go toÂ Home > Tour > Play Tour
You will see an animation of the timeline on the growth of Capacity has went over time per location!

Latest Tutorials

July 19, 2023

April 1, 2021

March 28, 2021

July 31, 2020

July 14, 2020

April 19, 2019

## 3D Maps In Microsoft Excel

February 26, 2019

## Recommended Pivot Tables in Excel

February 19, 2019