## Introducing the Concepts of Pivot Tables

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