Pivot table

A pivot table in Excel is an extraction or recap of your original table with source data. A pivot table can provide quick answers to questions about your table that can otherwise only be answered by complicated formulas.

Example of a pivot table

Below you’ll find a table with donations made to a charity organization in May. Each donation has an ID and a donor. Each donor also had a unique ID. Donors can donate in two ways: they can donate money without anything in return, or they can buy a t-shirt. The payment can be made by credit card, Paypal or cash and can reach three different statuses: completed, failed and abandoned. Last, but not least, every donation has a value in Dollars ($).

Table with donations to a charity in Excel // PerfectXL Academy

In the table above we see a total amount of donations of $895. This total amount however is a sum of all donations, including the ones that were abandoned and the ones that failed. The amount that will be transferred to the bank account will only equal the sum of transactions with status ‘Completed’. To find this sum, we’ll use a pivot table.

Step 1: insert a pivot table

Under ‘Insert’ in the navigation bar, choose ‘PivotTable’.

Excel ribbon - How to insert a PivotTable // PerfectXL Academy

Step 2: select a range

A popup appears titled ‘Create PivotTable’. Select a cell range or table in your workbook that contains the source data. In our example below you see the result of our selection Table/Range: Blad1!$A$1:$G$19. Press ‘OK’ when you’re done.

Select a table or range to analyze // PerfectXL Academy

Step 3: compose your pivot table

Next you can assemble your PivotTable in the screen ‘PivotTable Fields’. Ask yourself which information you are looking for. In this example we are looking for the total amount of ‘Completed’ donations.

Pivot table fields in Excel // PerfectXL Academy

In the ‘PivotTable Fields’ box we select ‘Donation ID’ and we drag-and-drop it to the ‘Rows’ box.

Pivot table source data - rows // PerfectXL Academy

The new table now shows the amount of times each Donation ID appears in the original table. This is, obviously, once per ID.

Compile the values of a Pivot Table row // PerfectXL Academy

Next we drag the field name ‘Status’ to the ‘Columns’ box.

Compile values of a Pivot Table column // PerfectXL Academy

The Pivot Table we’ve created now shows per donation whether it was completed, abandoned or failed. We know the sum of donations per status, but we don’t know the sum of amount per status yet. To find the answer we’ll select and drag field name ‘Amount’ to the ‘Values’ box and we’ll remove the current content (Count of Donations ID).

N.B. You can remove content by simply dragging it outside the box (make sure you don’t accidentally drop it inside another box!)

The result of all values in a Pivot Table in Excel // PerfectXL Academy

The final composition of our Pivot Table shows us that we’ve received a total amount of $710 of completed donations in May. The other transactions were either abandoned or failed.