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
Jane 100 Apples 50
Max 102 Oranges 60
Mark 105 Apples 70
Sara 250 Oranges 90
Jane 105 Apples 60
Max 206 Oranges 70
Mark 105 Apples 60
Sara 250 Oranges 70
Jane 250 Apples 90
Max 105 Oranges 60
Mark 206 Apples 90
Sara 105 Oranges 60

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.

  1. What is this data about
  2. What details are available
  3. 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.

Basic Pivot Table Demo

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″.

Pivot Table Data Range

Step 2: Click any where on the data range and click on “Insert” > “Pivot Table”

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

 

Congratulations for completing this totorial !

Click here for more information

 

4 Responses to Quick Pivot Table Tutorial

  • Roshni says:

    Can you please explain what the different fields are Report Filter,Column Labels, Row Labels, Values i.e. How to decide what is to be dragged onto what field?

  • IND says:

    Can you please explain what the different fields are Report Filter,Column Labels, Row Labels, Values i.e. How to decide what is to be dragged onto what field?

    already we have data in table format we can filter by required things to see the particular data in our excel sheet Na,

    my doubt is that , what is the specific advantage of using this pivot table please?

    & could you please let me know about VLOOKUP also please ?

    i have this much knowledge on vlookup is

    we can take the data by giving unique id by another excel sheet into our required sheet, by using vlookup,

    i have a doubt here is that, we can just copy & paste the selected columns from another sheet to our required sheet Na, instead of using vlookup.

    could you please clarify my doubts ?

    & about MID() function also,

    we can compare both the columns by using =A2=B2 just for an example, it’s giving true , false result.

    then why we need to go for MID() function please ?

    please let me know clearly about these differences as soon as possible please.
    thanks.

  • Rennie says:

    Below is a simple explanation of where to to drop what when creating a PivotTable report.

    Values – Any data field you can sum using the sum formula in excel goes into the Values Field – eg: Sales Amount, Number of Units

    Row Labels, Column Labels and Report Filter – Any field that contains text goes into the Row Labels, Column, Labels or Report Filter area. How you use these fields determine the layout of your report.

  • shubhra pashine says:

    i absolutely love it

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>