How To Update An Entire Dashboard In Excel With One Radio Button [VIDEO]

I’ve been writing a series on how to create dashboards in Excel that started with this post on Search Engine Land and carried over to Marketing Land. Now, I’m going to show you a trick that will give you the ability to control an entire dashboard with one menu — in this example, a radio button menu.

In the sample file I’m including, I’ve put months in the menu at the top of the dashboard, but you could put anything in there to segment your charts and tables:

  • countries
  • marketing channels
  • departments in a company
  • sales staff
  • traffic source
  • affiliates
  • etc.

Just as I mentioned in my last post, when you’re creating an interactive dashboard, I highly recommend that you put your raw and calculated data into separate worksheets from your dashboard. I name my worksheets Dashboard, Raw Data, and Calculated Data (this is pretty standard among analysts), but you can use whatever best helps you to keep track of everything.

The Chart We’ll Be Creating

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

How to create an interactive dashboard in Excel

Click for larger image

Excel File To Download

If you want to download the Excel file I use in the demo, you can download it here.

Developer Tab

In order to use these fancy drop-down menus, radio buttons, etc., you’ll need to enable the Developer tab in Excel. If you’re on a PC, this post describes how. And if you’re on a Mac, you can check out this post.

Functions You Need

Here are the functions I used in this tutorial:

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

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

TEXT: I show you how to create a dynamic chart title that updates when you select a new month.** But when you concatenate a formatted value like a date, the number formatting you have applied doesn’t carry through. Instead, you get the raw value, which is not very useful at all. The TEXT function allows you to shoehorn your number formatting back in to your date. You can learn more about the parameters here.

**I probably wouldn’t have the month duplicated in every chart like I did in this example, but I added them for a couple reasons:

  • To demo how to create a dynamic chart title (because they’re super sexy).
  • To make the fact that we’re segmenting by month pop out a bit more. Radio buttons can’t be formatted (boooo!), and they’re not terribly obvious at the top of the dashboard. I wouldn’t normally use radio buttons for this purpose; I would use a combo box, which gives you a nice drop-down menu. But I used them for this tutorial because I already used the combo box in my last tutorial.

Date Formatting

If the TEXT function doesn’t make sense, you might want to check out this tutorial I wrote on date formatting.

Save Yourself Time With Chart Templates

There’s no need to recreate each of your charts every time. Excel gives you the ability to save any chart you create as a template that you can reuse later. These templates are saved to your hard drive though, so if you want them accessible in each of your computers, you’ll need to copy and paste your template files to the proper directories:

PC: C:/Users/[Your User Name]/AppData/Roaming/Microsoft/Templates/Charts

Mac: Users/[Your User Name]/Library/Application Support/Microsoft/Office/Chart Templates

When you’re finished with your changes, you can save it as your own template. On a PC, you would just choose Chart Tools > Design > Save As Template. (Note: In Excel 2013, Save as Template is no longer available on the ribbon. To save a chart as a template, right-click the chart, and then click Save as Template. Excel should have had this as an option in the contextual menu all along.)

On a Mac, the easiest way is to right-click on the chart and choose Save as Template. But if you prefer using the Ribbon, go to Charts > Change Chart Type > Other > Save as Template.

Video Tutorial

You can check out the steps I took to create this dashboard in the video below. If you like it, be sure to subscribe to my YouTube channel (and like the video if you’re so inclined).

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 | 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.
  • Benjamin Beck

    Annie, another great job!

    Thank you for taking the time to put the video together! Really helpful.

  • unutathis

    Hi, can you once again share the demo file? Excellent work!

  • unutathis

    If possible my email address is: cxwallace@gmail.com

 

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!