>
0 items - \$0.00

### Your shopping cart is empty

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

# What is a Pivot Table?

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

 Transaction Description Debit (in R100) Bakery 50 Bakery 10 Juice 7 Dairy 20 Dairy 10 Dairy 30 Meat 10 Meat 30 Tea & Coffee 40 Dry Store 20 Dry Store 50 Fruit & Veg 30 Fruit & Veg 30

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 Description Total Bakery 60 Dairy 60 Dry Store 70 Fruit & Veg 60 Juice 7 Meat 40 Tea & Coffee 40 Grand Total 337

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

 Transaction Description Debit (in R100) Salaries Oct-09 10 Salaries Oct-09 7 Salaries Oct-09 13 Cozens - Casual Wages 7 Cozens - Casual Wages 4

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.

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:

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

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.

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

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.

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.

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

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.