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:

DOWNLOAD EXCEL WORKBOOK

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.

Data Model and Relationships

DOWNLOAD EXCEL WORKBOOK

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.

Data Model and Relationships

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.

Data Model and Relationships

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

Data Model and Relationships

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

Data Model and Relationships

Click New.

Data Model and Relationships

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

Data Model and Relationships

Click Close.

Data Model and Relationships

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!

Data Model and Relationships

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:

3D Map in Excel

DOWNLOAD EXCEL WORKBOOK

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

3D Map in Excel

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!

3D Map in Excel

And just like that your 3D Map is now ready for your analysis!

3D Map in Excel

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!

3D Map in Excel

 

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]