How to use a Pivot Table in Excel
A pivot table in Excel is an ‘extraction’ or ‘resumé’ 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 ($).
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’.
Step 2 – Select a range
A popup appears titled ‘Create PivotTable’. Select a cell range or table in your workbook that is the source of your Pivot Table. In our example below you see the result of our selection Table/Range: Blad1!$A$1:$G$19. Press ‘OK’ when you’re done.
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.
In the ‘PivotTable Fields’ box we select ‘Donation ID’ and we drag-and-drop it to the ‘Rows’ box.
The new Pivot Table now shows the amount of times each Donation ID appears in the original table. This is, obviously, once per ID.
Next we drag the field name ‘Status’ to the ‘Columns’ box.
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 final composition of our Pivot Table now shows us that we received a total amount of $710 of completed donations in May. The other transactions were either abandoned or failed.
Try PerfectXL Now
PerfectXL provides full automatic clarity about all risks, mistakes and other problems in your spreadsheets, plus an extensive improvement plan. You don’t have to be an expert yourself and you don’t need much time to fix your file.Learn about the features
Prefer INDEX and MATCH over VLOOKUP
VLOOKUP is used a lot more often than the INDEX and MATCH combination, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.
INDEX & MATCH!