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
The Video
Download File
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.
Learn More
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)
For more beastly data visualization tips, check out out my Annielytics Dashboard Course offerings.
A fantastic overview of pivot tables. I use them a lot in my keyword research projects but your step by step explanations still come handy. Thanks for your effort!
You’re very welcome! Hope you were able to glean something new.
Just what I was looking for. Thanks so much.
My pleasure!
One of my greatest regrets in life was not mastering Excel early. It’s on my list of things to learn this year along with Spanish. Pivot Tables are still “over my head”, but your video just became a training session for me. Thanks for doing this!
They’re really not as scary as they seem once you get in there and start just dragging and dropping. Pinky promise. 🙂
Thank you for writing this Annie! I know I’ll be using this as a resource 🙂 Loved this as a refresher for Pivot tables – you make it seem so darn easy!
That’s because it is! 🙂
Thank you Annie! And I lub the Kitteh!
Thanks! 🙂
Thank you!
You’re welcome!
Hi Annie. I saw you at #mozcon 2013. I’ve just used this video to create a pivot chart of orders over days for a client of mine. You are Gold. Thankyou.
Yes! These are always my fave comments! You may want to check out this video I did specifically about pivot charts: https://annielytics.com/blog/excel-tips/how-to-create-a-dynamic-chart-from-a-pivot-table/.
Thanks Annie. Really appreciate your resources.
Hi Annie, thanks so much for this! Really funny that I was watching this this morning and needed to use Pivot Tables for the first time in a consultancy meeting! Thanks so much!
Serendipity at its finest! 🙂
Great video. I’ve always wanted to learn pivot tables. Thanks.
There’s no turning back now. 🙂
Excellent video! I love the clear explanations and tips that you provide. I searched for a couple of hours looking for the best video to show the basics. I found it! I can’t wait to get to work tomorrow and begin working with pivot tables. Thank you very much!
Oh man. Comments like these are always my fave! Happy to help!
Hi Annie, the video is great. I tried to download the excel file but I keep getting ‘Access to this link has been disabled’ notice. Can you please enable it again.
tnx!
Dropbox disabled all links (https://blog.dropbox.com/2014/05/web-vulnerability-affecting-shared-links/), but I’ve re-enabled mine. It should work now.
Hi Annie, the files are still missing when try to open. Can this be resolved?
The download works fine for me. All you have to do is click the Download button. It’s a Dropbox link.
Hi Annie,
Excellent video. Short sharp and effective! Cheers
Thanks, Dave. 🙂
Is there supposed to be narration on the video? I hear the intro music and thats all… video plays fine.
It still works for me, and I haven’t had any other complaints. Maybe try opening it in another browser?
Still having difficulty in following the code…. can anyone help … I need to retain a set of first pass commentary and then on the second refresh retain the first pass commentary to view alongside with the final numbers
Many thanks
What?
The video isn’t showing up, is it still available?
Thanks 🙂
Thanks for the heads up, Tim! It’s fixed now.
Thank you for writing this Annie! I know I’ll be using this as a resource 🙂 Loved this as a refresher for Pivot tables – you make it seem so darn easy!