In a post I wrote last week, I demonstrated in a video how to use advanced filters (one of Excel’s best-kept secrets) to divide a list of keywords into categories. I promised a commenter I would create a follow-up video demonstrating how I took those data sets and created a pivot chart that updated when you choose a new category.
Download Excel File
If you’d like to download the Excel file I used, you can access it here. Feel free to kick it around and test out some of the filters I demonstrate in the video.
Much to my chagrin, pivot charts are PC swim only. You can’t create a pivot chart in Excel 2011 for Mac. (Boo hiss!!!) But not only can you not create one, you can’t even use a report filter on the Mac. You can see this epic fail in action here. But if you’re creating a pivot chart, and there’s the chance that anyone might have to access it from a Mac, you cannot use report filters.
I created a really cool pivot chart for Loren Baker to show a client once, but he was using a Mac and couldn’t use the report filter drop-down. He was in a hurry and asked me to just create screenshots of each of the charts, and he showed those to the client instead. I’ve never quite forgiven Microsoft for that. :/ (Yes, I take making data sexy very seriously. And don’t see the light of day much.)
I created a video demonstrating how to take the data spawned from the advanced filters. And even though I said I wasn’t going to get into chart formatting since I’ve covered that in this post from my SMX Advanced presentation and this Search Engine Land post, I did anyway. Because it’s a compulsion. Have you ever tried to control a compulsion? Not. Easy.
Anyway, check it out.
How To Change Dashes To Zeros
As I mention in the video, I really don’t like how Excel formats zeros as dashes in charts. But you can see a quick tutorial on how to change those dashes to zeros or check out my more comprehensive post on number formatting on the Search Engine Land site.