How to Create an Interactive Dashboard

Interactive dashboards are basically the sliced bread of data visualization. They are dynamic, easy to use, and compatible with Mac and PC. This tutorial is best applied to clients who are tracking a domain’s rankings in multiple locations. But, these principles can be used to make many interactive dashboards. This tutorial was heavily inspired by Annie Cushing, author of Annielytics, and her interactive chart blog post. Alright, let’s get started!

If you would rather watch the tutorial, here it is:

For you readers out there, carry on. There will be links to the specific part of the video tutorial for each of these steps.

Collecting the Data

I first started this dashboard by exporting the ranking data from my grouped site Olive Garden. Once you’ve collected the ranking data from each of your cities, compile that data into one document.

step1_compile exported raw data

Next, grab the Keywords, Google rank, and Search Volume for each of the cities and add that data to one table in your Raw Data tab. Pro Tip: The Raw Data tab is sacrosanct, it should be kept free of formulas and charts. Save that for the Calculated Data tab.

step3_raw data tab

Calculate the Data

The first step in calculating the data is creating a list of cities. You can type this out manually, use a VLOOKUP, or just copy and paste.

step4_list of keywords

The second step is a little bit more labor intensive. First make sure that you have the Developer tab open. You have to select this tab to viewable through File > Options > Customize Ribbon. Then you will need to create a Combo Box using the Form Controls.

step5_make combo box_step2

Once you have drawn out the Combo Box, right click to select Format Control.

step6_select formatting combo box_step2

Now here is the fun part. Under the Control tab, select the Input range to be the list of cities you made earlier. (That will be the list that your Combo Box shows.) Next, select your output, or Cell link. Lastly, change the Drop down lines to 4 (because we only have four cities).

step7_inputs combo box_step2

The third step is to put in an INDEX formula. This formula will allow you to look up the Cell Link we set up earlier, and output from our Cities list. This means that when you select Chicago, IL on the Dashboard tab, it will output the text, “Chicago, IL” in that cell.

step8_index formula_step3

Collect Location Specific Data

Bring over the data from the Raw Data tab with a SUMIFS formula. Never worked with SUMIFS? No problem, just follow this handy-dandy image. I would recommend referencing the video tutorial for this bit.

SUMIFS have two criteria that need to be met to output the data you’re looking for. The first criteria in our SUMIFS will be finding the Rank number for the Keyword “italian restaurant”.

step9_sumifs rank_step4

The second criteria will look to make sure that Keyword matches the location in cell F4 (where our INDEX formula is).

step10_sumifs rank_step4

The second SUMIFS formula is exactly the same as the first except for in the “sum_range” you select “Table1[Volume]” instead of Rank.

Concatenation Formula

In this step you will perform a simple concatenation formula. It will combine the text string “Keyword Ranks & Search Volume for ” and the city that the user will select from the Combo Box (which is in cell F4).

step11_sumifs rank_step5

Create the Chart

Create the chart by navigating to the Insert tab and selecting Recommended Charts > All Charts > Combo Charts. Make the Rank data a Line with Markers chart, and the Volume a Clustered Column chart. Also, move the Rank to a Secondary Axis. Move the chart to the Dashboard tab.

step12_creating the chart_step6

Format the Chart

Now that the Rank data is on a secondary axis, reverse the order of the values. This will allow you to view the Rank data in the order that one is positive, and 100 is negative.

step13_format axis_step6

I like to remove the line and just leave the markers. You can do this by right-clicking on the line and clicking “Format Data Series”. Also, add in the Primary and Secondary Vertical Axis Titles. That way we know which axis is Rank data and which is Volume data.

step14_format lines_marker_step6

Add the Chart Title

This is where the chart title we made in the Calculated Data tab will come into play. We want our chart title to automatically update when a user selects a different city.

step15_format lines_marker_step6


Congratulations on making your first Interactive Dashboard!

Share via
Copy link
Powered by Social Snap