Excel Pivot Tables

In Excel, Pivot Tables  are a quick and easy way to summarize, analyse and present data.  The speed that the tables can be put together and rearranged makes them an invaluable and important tool for analysis and decision making.

The more data you have to analyse, the more obvious the uses of Pivot Tables are.

I have created a file with 500 rows of data that we will analyse using Pivot Tables.

Input data for pivot tables

We have 7 columns of data in our example

  • Name – let’s assume that this is made up of sales reps’ names.
  • Area – the region or country is being split up into areas
  • Quarter – the year is split into four quarters with 1 representing January to March
  • Day – the day of the week between Monday and Friday
  • Sales – the value of sales made on the day
  • Expenses – the expenses submitted by the sales rep that day
  • Net – the difference between sales value and expenses submitted each day.

What makes up the above data is not important, I just came up with these for the sake of demonstration.

We will create an initial Pivot Table and then review the data in different ways.

Create A Pivot Table

Before continuing – make sure that every column has a heading – Pivot Tables do not like blank headings.

Insert Pivot Table Button

On the “Insert” tab of your Excel ribbon, you should see the first option as “Pivot Table”.

Select this and you will be shown a “Create Pivot Table” dialog box.

Create Pivot Table

 The top section of the box relates to the data to be analysed, this could be the table or cell range on your current file, or you can analyse data on another (external) file.  In the majority of cases you might find that you are analyzing data in your current file.  Excel will probably already have your data range selected.

The bottom part of the table relates to where you want the Pivot Table report to be placed, you can have it put on a new worksheet, or somewhere on an existing worksheet.

So far this may seem too easy to be true, but the truth is that Pivot Tables are that straightforward to create.

 

After you have selected your data source and where the data is to be placed, you will see a blank Pivot Table and also Pivot Table fields and options.

Pivot Table Options

Let’s start analyzing our data.

 

Who made the most sales?

If we want to know who made the most sales then drag the “Name”  from the list of fields to add to report and drop it under the “Rows” section.

Drag the “Sales” from the list of fields and drop it in the “Values” section.

Pivot table settings for most sales

Our Pivot Table now shows us the total (sum) of sales for each rep.

Pivot Table of sales by person

Where did we make the most sales?

To analyse the sales data by area, drag the “Area” from the list of available fields down to the Row section and remove the “Name” from the Row section

Pivot Data fields for sales by area

Our Pivot Table then looks like this

Pivot Table of sales by area

What quarter did we make the most sales in?

To get the total of sales by quarter, remove Area from the Row section and drag down “Quarter”.

Pivot Table Fields for sales by quarter

 

 

Pivot Table for sales by quarter

 

Further Analysis – Sales by person in each area

So far we have done simple analysis, who had the most sales, when did we have the most sales, where did we have the most sales.

Let’s take it a bit further and analyse who had sales in each area.

Set your Pivot Table fields as follows:

  • Row = Name
  • Column = Area
  • Values = Sales

Pivot Table Options for further analysis

Our Pivot Table lists the names down the rows and the column headers going across are now the areas.

Pivot Table of sales by person by area

 

Further Analysis – Sales by person in each area for each quarter

Are you seeing how easy it is to change and analyse data?  Let’s add a third element to our analysis.

We have seen an analysis of sales by person in each area, now let’s look at sales by person for each quarter in each area.

You can drag the “Quarter” from the available list of fields and drop it below the Name in the Row section.

3 Way Pivot Table Analysis

Pivot Table 3 Way Analysis

 

Changing the order

We can change our Pivot Table to analyse sales by quarter broken down by person.  To do this, just change the order in the Pivot Table options section

Pivot Table showing changing order of options

 

This then updates our Pivot Table as follows

Pivot Table after changing order of options

Beside each quarter we see the total value and below each quarter we see the name of each person and their sales for that quarter for each area.

 

Filtering

Taking our above table, we can filter to only show the details for quarter 2 by filtering for 2 only.

This is the same as standard filtering in Excel, the filter is to the right of “Row Labels”.

Filtering pivot tables

We can filter further to see the sales for the East region only, by filtering “Column Labels”

Filtering columns of pivot table

 

Even Further Analysis

We can add even more analysis to our table by adding “Day” to our Row section

More examples of pivot tables

 

Tabular Form

We can change the layout of the above Pivot Table, my personal preference is to use the “Tabular Form” layout

Right-click on the first Row field (2 in the case above) and select “Field Settings”.

On the “Layout & Print” tab, select “Show item labels in tabular form”.

Setting tabular form on pivot tables

This changes our table layout, I have also set the Day Rows to be in tabular form too.

Pivot Table Tabular Form

 

Number Formats

We will now change the number formats to use the 1,000 separator and 2 decimal places.

Click on any of the sales values and right-click and select Number Format.

Changing number formats on pivot tables

Then set the number format as you want it.

Pivot table with formatted numbers

 

Subtotal

At the minute each day of the week is subtotalled and also each area.  To remove the subtotal of the days, right-click on a day and untick “Subtotal Day”

To put the subtotal back, again right-click and click “Subtotal Day”.

If it was a different Row we were working on, the option would be Subtotal and then the row name.

Subtotalling Pivot Tables

 

 Summarize values by…

So far all the Pivot Tables have been the sum of the row and column data.

I have reset our table to just have the Name as the Row and Sales as the Value.

Pivot Table of sales by person

 

We have the total sales for each person.

Right-click on any of the sales values and go to “Summarize Values By”, another menu slides out and you are given options that you can summarize your data by, first pick “Average”.

Pivot Table of averages

This gives the average of each person’s sales.

Summarize the values by “Count” and you will see how many sales each person got.

Pivot tables of count

Summarize the values by “Max” and you will see what the highest day’s sales were for each person.

Pivot Table of max sales

Try It Yourself

As with most Excel functions and tools, the best way to see how they work or what they can do is to try them yourself.    If you don’t have a file you could practice on yourself, you could use this one pivot tables.

Quick, Easy & Flexible

I’m hoping that you can see how easily and quickly you can put together a variety of analyses and reports by simply dragging and dropping fields.

Pivot Table vs. Subtotals

For simpler analyses, Subtotal functions can be used to analyse data to a certain extent.

However, in my opinion, the options offered by Pivot Tables far outweigh those offered by Subtotals.

  • With Pivot Tables the data does not have to be sorted first
  • It is faster and easier to change the analysis options and fields on Pivot Tables
  • The Pivot Table field table gives a clearer visual showing the make up of the Pivot Table
  • The biggest benefit with Pivot Tables I find is the speed of them – each month I work on a file with over 20,000 rows of data and over 15 columns.  Running the Subtotals I need on this file takes an average of about 40 minutes.  The Pivot Table is completed within 2 minutes.

 

[grwebform url=”http://app.getresponse.com/view_webform.js?wid=2766903&u=CucD” css=”on”/]
email
Click Here to Leave a Comment Below 1 comments
Follow

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: