• Marketing Land
  • Sections
    • CMO
    • Social
    • SEM
    • SEO
    • Analytics
    • Display
    • Retail
    • MarTech
    • Resources
    • More
    • Home
  • Follow Us
    • Follow
  • Marketing Land
  • CMO
  • Social
  • SEM
  • SEO
  • Analytics
  • Display
  • Retail
  • MarTech
  • Resources
  • More
    • Follow
  • SUBSCRIBE

Marketing Land

Marketing Land
  • CMO
  • Social
  • SEM
  • SEO
  • Analytics
  • Display
  • Retail
  • MarTech
  • Resources
  • More
  • Home
  • Newsletters
  • Home
Analytics & Conversion

How To Create Interactive Charts In Excel

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 […]

Annie Cushing on September 13, 2013 at 10:19 am
  • More

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


Opinions expressed in this article are those of the guest author and not necessarily Marketing Land. Staff authors are listed here.



About The Author

Annie Cushing
Annie Cushing 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.

Related Topics

AnalyticsAnalytics & Marketing ColumnChannel: Analytics & ConversionGoogle: AnalyticsHow To GuidesHow To Guides: Marketing Analytics

We're listening.

Have something to say about this article? Share it with us on Facebook, Twitter or our LinkedIn Group.

Get the daily newsletter digital marketers rely on.
See terms.

ATTEND OUR EVENTS

MarTech 2021: March 16-17

MarTech 2021: Sept. 14-15

MarTech 2020: Watch On-Demand

×

Attend MarTech - Click Here


Learn More About Our MarTech Events

February 23, 2021: SMX Report

April 13, 2021: SMX Create

May 18-19, 2021: SMX London

June 8-9, 2021: SMX Paris

June 15-16, 2021: SMX Advanced

August 17, 2021: SMX Convert

November 9-10, 2021: SMX Next

October 2021: SMX Advanced Europe

December17, 2021: SMX Code

Available On-Demand: SMX

×


Learn More About Our SMX Events

White Papers

  • 6 Powerful Ways Experience Analytics Can Help Your Business Now
  • Email Tune-Up: A 5-Point Inspection to Get Your Program in Gear
  • Digital Marketing Report Q4 2020: Benchmarks and Insights for 2021
  • Data SEO – The Next Big Adventure
  • Getting Started with Email Marketing Automation
See More Whitepapers

Webinars

  • The Secret Behind SEO Success: Predict Rank with the Power of Data Science
  • How to Avoid the Digital Transformation Trap
  • How to Build a Marketing System of Record
See More Webinars

Research Reports

  • Local Marketing Solutions for Multi-Location Businesses
  • Enterprise Digital Asset Management Platforms
  • Identity Resolution Platforms
  • Customer Data Platforms
  • B2B Marketing Automation Platforms
  • Call Analytics Platforms
See More Research

h
Receive daily marketing news & analysis.
Marketing Land
Download the Marketing Land app on iTunes Download the Marketing Land App on Google Play

Channels

  • MarTech
  • CMO
  • Social
  • SEM
  • SEO
  • Mobile
  • Analytics
  • Retail
  • Display

Our Events

  • MarTech
  • SMX

Resources

  • White Papers
  • Research
  • Webinars
  • MarTech Conference
  • Search Marketing Expo

About

  • About Us
  • Contact
  • Privacy
  • Marketing Opportunities
  • Staff
  • Connect With Us

Follow Us

  • Facebook
  • Twitter
  • LinkedIn
  • Newsletters
  • Instagram
  • RSS
  • Youtube
  • iOS App
  • Google Play

© 2021 Third Door Media, Inc. All rights reserved.

Your privacy means the world to us. We share your personal information only when you give us explicit permission to do so, and confirm we have your permission each time. Learn more by viewing our privacy policy.Ok