Mar 17, 20232 min read

Pivoting into Data Analytics? – Must Know – Pivot Tables

Introduction -

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

I am using Car Inventory Dataset

To Create a Pivot Table –

1. Click on any column having Data

2. Go To Insert

3. Click on Pivot Table

1. Let us put the Pivot Table in same worksheet

2. Select option Existing Worksheet and select a cell where you want to place the Pivot Table

3. Click OK and you will get following layout for Pivot Table

1. Now we will design our First Pivot Table

Example 1 - To keep it super simple we will check Average Milage of Every Make

1. Drag Model to the Row area

2. Drag Milage into Values area

1. Here you see Milage came as Sum, but we need average instead.

2. To do so, click on Sum of Mileages

1. Select Value Field Settings from the Pop-up Menu

2. Select Average from the Summarize Value By Field

1. Click on OK

Here is our First Pivot table is ready. It shows Average of Every Car Model

2. To change the Row Labels – Go To Design Tab > Report Layout > Show in Outline Form

This will label the Row Label as Model.

Create another Pivot Table

1. Select Range

2. Place in Existing Worksheet

3. Click OK

1. Drag Make and Model into Rows

2. As we want to calculate How many Models we have ?, Drag Models to value

3. Go To Design Tab and Select Report Layout as ' Show in Outline Form '

4. Ta Da .. Pivot Table take it as Count of Models and

5. here our Multi level Pivot Table is ready

Let us Format it More

Here you can see We got +/ - buttons

You can Collapse and Expand the Section

If you don't want these buttons you can manage that. Go to Pivot Table Analyze tab , turn off from here