Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Jul 03 2013

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.

Learn More

You can learn more about data formatting in my Annielytics Dashboard Course, a video course that will teach you how to put your data in stilettos and work the pole. 🙂

Written by Annie · Categorized: Excel · Tagged: Advanced Filters, Excel 2010, Excel 2013, Excel for Mac, Pivot Charts

Comments

  1. victorpan says

    July 3, 2013 at 4:29 PM

    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!

    Reply
    • Annie Cushing says

      July 3, 2013 at 11:02 PM

      Thanks! And, yeah, I almost edited that out, and then I liked the reminder about how lame Excel for Mac is. 🙂

      Reply
  2. Thomas Boshell says

    October 4, 2013 at 5:30 PM

    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.

    Reply
    • Annie Cushing says

      October 4, 2013 at 6:55 PM

      Welcome to the outermost circle of Excel Hell …

      Reply
  3. Brenda Malone says

    October 5, 2013 at 8:24 AM

    Thanks Annie, very nice.

    Reply
    • Annie Cushing says

      October 5, 2013 at 1:50 PM

      You’re very welcome.

      Reply
  4. David says

    August 15, 2014 at 1:59 AM

    At least it is not as lame as Numbers…

    Reply
    • Annie Cushing says

      August 15, 2014 at 4:22 PM

      That’s a pretty low bar.

      Reply
  5. cyrilbrd says

    September 10, 2014 at 2:15 AM

    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…

    Reply
    • Annie Cushing says

      September 11, 2014 at 11:33 AM

      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.

      Reply

Leave a Reply Cancel reply

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

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2022 annielytics.com