By popular demand, I’ve created a video walk through of how to create and customize pivot tables. If you haven’t use pivot tables, they are especially useful in large data
dumps exports, such as those you get from analytics applications like Google Analytics, Screaming Frog exports, and link reports (e.g., Open Site Explorer, Majestic, and Ahrefs).
Why? Because you can easily move data in and out of your pivot table, which is especially useful in forensic data dives, where you’re trying to diagnose an issue with a site.
What’s Covered In The Video
I cover how to:
- prepare your data by formatting as a table
- insert the pivot table
- structure your data in columns and rows
- move around values
- layer row labels
- customize pivot table labels (the easy way)
- group data in columns or rows, e.g., dates by month, quarter, or year
- include multiple values without trashing your pivot table
- insert a report filter drop-down menu
- change the report layout to one that’s less cramped
If you want to follow along, you can download the Excel file I used in this video. There’s a tab with the raw dataset, as well as one with the final pivot table.
You have several options to learn more:
- My Search Engine Land post
- Microsoft pivot table resource for Excel 2010 (PC)
- Microsoft pivot table resource for Excel 2011 (Mac)