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.