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 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:

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

Some opinions expressed in this article may be those of a 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.