>
0 items - $0.00

Your shopping cart is empty

Why not add some items in our Shop
Call Us at +27 21 786 1224
Home / Blog /

Introducing the Concepts of Pivot Tables

Introducing the Concepts of Pivot Tables

by Brilliance Computer Training Academy & Services

What is a Pivot Table?

A Pivot Table gives a summary of information. But let me try and explain it as follows.

Transaction DescriptionDebit (in R100)
Bakery50
Bakery10
Juice7
Dairy20
Dairy10
Dairy30
Meat10
Meat30
Tea & Coffee40
Dry Store20
Dry Store50
Fruit & Veg30
Fruit & Veg30

 

If you look at these transactions and I ask you how much was spent on Diary. If you add together 20 + 10 + 30 = 60 (i.e. R6000) you indirectly already know what a pivot table does at its essence. Each time you go down column A and find Diary, you jump right to the Debit amount and add the expense.

Pivot tables works with some set of conditions - one condition, in this case the sum. Pivot tables gives a summary report.

 

Transaction DescriptionTotal
Bakery60
Dairy60
Dry Store70
Fruit & Veg60
Juice7
Meat40
Tea & Coffee40
Grand Total337

But suppose we are looking for the average Casual Wages paid per month from the table below:

Transaction DescriptionDebit (in R100)
Salaries Oct-0910
Salaries Oct-097
Salaries Oct-0913
Cozens - Casual Wages7
Cozens - Casual Wages4

You might want to ask: What is the average spent on wages?

Now the condition is to find average values. Pivot tables gives a summary report.

 

First Pivot Table

Below are payments made for a list of transactions by a hotel.

first_pivot_data

 Create the Pivot table: Summarize Transactions in Existing Worksheet

Click in any one cell inside the table. Then on the ribbon click: Insert, Tables, Pivot Tables

Select: Existing Worksheet, Location: '1st Pivot Table'!$F$6 (or anywhere on the existing sheet), and click OK

Note, that whenever you change the settings of the pivot table, the dimensions of the resultant table might change and will consequently overwrite any cells with other data or text in it, should it overlap. Click on the Pivot Table and in the Pivot Table Fields window that appears which looks like this:

first_pivot

Drag Transaction Description to Rows and Debit (in R100) to Σ Values. This Pivot Table summarizes the total fees paid by category by the hotel.

 first_pivot_results

 

Let us have a look at some of the requirements we need before we create a Pivot table

It must be a Proper Dataset

  • The Field Names (Column Headers) must be in the First Row of the Table. 
  • The Records are given in Rows. 
  • You must have Empty Spaces OR Row Headers OR Column Headers all around the dataset. 
  • There cannot be empty cells in the Field Names.
  • Every column must have a Field Name. It is best not to have empty cells inside the table

 

Listed below are some important concepts relating to Pivot Tables which might help you decide on what to summarize:

  • Try to visualize your report first. 
  • Where is the conditions or criteria? If it is the rows, then this is what gets dragged to Rows in the Pivot Table setup. 
  • Determine what sort of calculation is required.

Suppose we have a list of clients who orders Champagne from a Champagne Maker. Listed below are the clients, with Qualites and Volumes ordered.

pivot_data

Below is a Pivot Table which summarizes the total volumes ordered for each Qualite.

reqpt1

Consider only Brut. In which case the Condition (in green) is BRUT, and the calculation (total volume ordered) is 23648.25 (units of measurement is unknown). 

But sometimes we may have many conditions and in the case below the Client Name and Qualite are both conditions.

reqpt2a

In red you can see that the client Amicale Du Personnel has ordered a total volume of 76.5 of Rose champagne.

To set up this pivot table, click in a cell in the table.

  • On the ribbon, click on Insert, Tables, Pivot Tables. 
  • Check New Worksheet, OK. 
  • Click on the Pivot Table. Notice, the Field Names appear in the Pivot Table Fields list. 
  • Drag Client to Rows.
  • Notice how a unique list of Clients Names are listed. Drag Qualite to Columns. 
  • Drag Volume to Σ Values. 
  • Click on any total value, right click and select Value Field Settings, Summarize Values By, Average. 
  • Click on any total value, right click and select Value Field Settings. 
  • Change the Custom Name to Average Volume Sold. 
  • Click on any total value, right click and select Number Format, Number, change the Decimal Places to 0, and tick off Use 1000 Separator.

Note: DO NOT use: Right Click, Number Format… because as soon as you update your Pivot Table or change its orientation, the number formatting will be reset.

The average volumes ordered per client per Qualite are now listed.

reqpt2b

  • We would like to add a descriptive title for the rows instead of leaving it as Row Labels. 
  • To do this, click on the Design Tab, Layout Group, and select Show in Tabular Form. Note the row title in yellow below. 

reqpt2c

Below I have listed some handy Keyboard shortcuts.

Handy Keyboard shortcut keys:

  • Ctrl + Arrow: Jumps to end of data 
  • Ctrl + Home: Jumps to Top of data 
  • Shift + Spacebar: Select a Row 
  • Ctrl + Spacebar: Select a Column 
  • Shift + Ctrl + Spacebar: Select the Current Region. that is the whole block of data.

 

Share this article

Leave a comment

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