Continuing with my dashboard series that began on Search Engine Land and continued over to Marketing Land when the entire analytics column migrated to Marketing Land, this post is going to talk interactive charts. When you’re creating a dashboard, real estate is minimal, so you find ways to consolidate your data and allow the dashboard user to manipulate a single chart different ways.

Excel offers a panoply of interactive controls that make this possible. I’ll be the first to admit that working with interactive controls were really intimidating to me when I first started. They seemed to have a lot of moving parts. But once you understand how they work and learn a couple basic functions, they’re actually quite intuitive to use.

The Chart We’ll Be Creating

Here is the chart you’ll learn how to create in the video below:

 

final interactive chart in Excel

Functions You Need

Here are a few functions I use on a regular basis with interactive charts:

VLOOKUP: This is arguably the Swiss army knife of Excel for marketers. It allows you to pull values from a data set by matching one common data point from another data set. You can learn more from this Excel resource by Distilled.

INDEX: The INDEX function is a more flexible version of VLOOKUP. You can learn more about how to use it here.

IF: The IF function allows you to apply Boolean logic to your data. You can learn how to use this powerful function in marketing here.

I only used the INDEX and IF functions to create the chart pictured above.

Example Uses

There are so many ways you can use Excel’s interactive charts with analytics data. For example, you can set it up to only show traffic (or revenue or whatever) from a particular:

  • year
  • country
  • search engine
  • medium
  • social network
  • platform (mobile, tablet, desktop)

You get the idea. Basically, any way that you can segment your data in your analytics software, you can segment in Excel charts or tables using interactive controls.

Excel File To Download

If you want to download the Excel file I use in the demo, you can get it here. I have it broken into three sheets:

  • Raw Data
  • Calculated Data
  • Chart

Feel free to poke around a bit. I highlighted cells with formulas in them as visual cues. But the idea behind the chart is you use an IF statement to match the metric chosen from the drop-down. If it matches, you pull in the data from the Raw Data sheet; if it doesn’t, you use NA(), which fills the cells with the #N/A error, which Excel ignores when charting. It will (hopefully) make sense when you watch the video below.

Data Prep

If you want to use the Google Analytics (GA) custom report I used for this tutorial you can get it here. Just make sure you’re logged in to GA in the the browser in which you open the link, or you’ll get a nasty 404 error.

Caveat: The format of GA’s Month of Year dimension is jacked up because they don’t use separators, like a dash or forward slash, to separate month and year. So you’ll need to use this hack of the DATE function in Excel to clean it up and make it useful. Then I just used custom number formatting to format the date (mmm yy). You’ll also be able to see the formula in the Excel demo file download.

Video Tutorial

YouTube Preview Image

Opinions expressed in the article are those of the guest author and not necessarily Marketing Land.

Related Topics: Analytics | Analytics & Marketing Column | Channel: Analytics | Google: Analytics | How To Guides | How To Guides: Marketing Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.



Sign Up To Get This Newsletter Via Email:  


Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. You can read more about our comments policy here.
  • http://www.volumeseo.com/ Keller Tiemann

    Great Excel tip. I used to work as a tax consultant and would go out of my way to learn Excel tips and tricks to improve my work. Happy to stumble on this for my SEO clients!

  • http://www.annielytics.com/ Annie Cushing

    Happy to help, Keller!

  • http://www.websiteoptimizers.com/ Tom Bowen

    So cool. Like you Annie, I was a bit intimidated going into areas of Excel I haven’t used before. The video makes it so easy. Can’t wait to give my customers more!

  • http://www.annielytics.com/ Annie Cushing

    Awesome to hear, Tom! Sometimes I wish I could be a fly on the wall to see how marketers take these ideas, pivot on them, and create super sexy data.

  • Mikael Thuneberg

    You can easily automate this even further with our GA functions for Excel: http://supermetrics.com/interactive_chart.gif. Get the Excel file here: https://dl.dropboxusercontent.com/u/8653266/interactive-chart.xlsm. Get a token from analyticsfunctions.com, paste it to the Raw Data sheet, and you’re good to go. Need to have Windows Excel with macros enabled.

  • danleibson

    For people using Excel 2010 or Excel 2007 this is how you add the developer tab to the ribbon:

    http://www.addintools.com/documents/excel/how-to-add-developer-tab.html

  • http://www.annielytics.com/ Annie Cushing

    I’ll check it out. Thanks.

  • http://www.annielytics.com/ Annie Cushing

    Thanks, Dan.

 

Get Our News, Everywhere!

Daily Email:

Follow Marketing Land on Twitter @marketingland Like Marketing Land on Facebook Follow Marketing Land on Google+ Subscribe to Our Feed! Join our LinkedIn Group Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Marketing News!

Marketing Day is a once-per-day newsletter update - sign up below and get the news delivered to you!