Pivot tables are a powerful tool for data analysis in Excel. Large datasets can be easily digested in a fraction of a second to provide you with more relevant and actionable information.
Imagine a data file with every purchase you made last year. How would you calculate how much you spent at at Starbucks? What about the average amount? Without pivot tables, you would filter the data, then copy it into another tab, and then use a sum on it. With a pivot, you could select Starbucks and your total expenses in three clicks.
Step by Step Instructions
- Highlight the cells you want to pivot
- Open the “Insert” tab which is located towards the top of the Excel window
- Click the Pivot Table button
- When the Pivot Table window pops up, click “ok” to create a pivot table in a new tab
Calculated Fields
Pivot tables have a secret feature that allows you to create an entire new field in the pivot table which can help create ratios using two columns from your data. In the image below, I’m calculating the average fare of a taxi ride in New York
City by dividing the Fare (Sum of Fare) and dividing it by the Trips (Sum of Trips).
examples of a calculated field
Digital Advertising Calculated Fields
Click Through Rate = Clicks/Impressions
eCPM = 1000 * Ad Revenue/Impressions
CPC = Ad Revenue/ Clicks
taxi Calculated Fields
Tip % = Tips/Fare
Average Fare = Fare/Trips