How To Create A Dynamic Chart From A Pivot Table [VIDEO]

Excel for Mac is lame - no pivot charts
Sorry, Mac users. No pivot charts for you!

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.

pivot charts in Excel 2013 and 2010
Click for larger image

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.

Caveat

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.)

Video Tutorial

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.

Comments

  1. victorpan says

    I chuckled when when I heard “command A – no wait that won’t matter” (since we know this won’t work on Macs anyways). Great tutorial Annie!

  2. Thomas Boshell says

    I found an easy workaround that seems to work for almost all the pivot charts created on a PC that are emailed to me. For instance, if I have a pivot table that includes monthly or weekly data on a particular customer that is also included in a chart, I simply change the customer name or the date range from the drop down selection in the pivot table. I then select the whole chart, use “command x” to cut it, and then I use “command v” to paste it, and it displays with the new chart data. Granted this is done automatically on a PC, but at least this is a way to continue to use a great machine (Mac) and still utilize Excel in the business world.

  3. cyrilbrd says

    Hi,
    downloaded the file advanced-filters.xlsx and modified it to take into consideration the pivot filters found in cell C4.
    used 3 name ranges and changed the sources of the chart to refer to those named ranges.
    works fine under Excel 2011 for MAC…
    It is now dynamic. and under MAC you can use the report filters…

    • Annie Cushing says

      I wouldn’t recommend using advanced filters to build dynamic charts in Excel for Mac. They have to be refreshed manually or with a macro. There are much more efficient ways to build dynamic charts using a combination of form controls and functions like SUMIF(S), IF, INDEX, and OFFSET (to name a few). I have a bunch of tutorials on Search Engine Land and Marketing Land that show you how to set these up.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>