Quick Pivot Table Tutorial
Introduction to Pivot Tables
Pivot Table feature is one of the most powerful features in Microsoft Excel. If you have been using Microsoft Excel and have not used the Pivot Table feature you have wasted your life!
My aim is to show every Excel user this wonderful Pivot Table feature. Below is a free tutorial that would take you about 15 minutes to go through. Remember this is only a taster. I will be publishing more tips in the future.
If you have any Pivot Table related questions please post them on this page. I will answer them for you.
So what is a Pivot Table?
If you have your data in a tabular format you can use the Pivot Table tool to analyse the data to get insightful information.
Let us work of the following data in an excel spreadsheet.
|Sales Person||Sales Units||Product||Total Sales Amount|
Let’s get some insight from the data above using the Pivot Table feature. Now before you do that you will need to look at the data to determine what insight can be had from the data. This step is the most important step in producing your Pivot Table report. In other words you must understand you data.
If I was trying to understand the data I will want the answers to the following questions. This will determine if the Pivot Table feature can be used to summarise the data.
- What is this data about
- What details are available
- Are there data fields I can summarise
Try to answer these questions yourself – below are my answers
1. The data is about the sales achievements of some sales people.
2. I know the following details for each record of data.
- The name of the sales person
- The number of units sold
- What they sold
- The amount of money they made on the sale
3. The following fields can be summarised by Sales Person and Product Type.
- Sales units
- Amounts of sale
Hurrah! This is perfect for creating a Pivot Table report. Now let us go ahead and answer the following questions using a Pivot Table report.
I strongly suggest you do the exercise with me. Copy the data range from above and paste it into a blank sheet in an Excel workbook (or spend 5 minutes to key the data in).
Now that we understand the data let us try to answer the following questions using the Pivot Table feature in Excel. Can I also challenge you at this point to use any other method you know to get the answers to the following questions as well. This will help you appreciate the power of the Pivot Table feature more. If you are taking up the challenge please time yourself.
1. Number of sales units by sales person
2. Amount of sales made my each sales person
Follow the link below for a movie demo for the solution.
If you can’t open the video link follow the steps below.
Step 1: Copy the range from above in to a blank excel worksheet into range “A1”.
Step 2: Click any where on the data range and click on “Insert” > “Pivot Table”
Step 3: The Pivot Table Wizard appears on the screen. Notice that the Pivot Table wizard has correctly selected the data range to create the Pivot table. At this stage just select the defaults offered by the Pivot Table Wizard and click ok.
Step 4: The Pivot Table wizard creates a shell to place the data “Fields” found in the data range.
You can place the data based on how you want to view the data. See below for a demonstration