In this blog post, I want to go over an Interactive Excel Dashboard example to show you how to build one from scratch. For this example I will use some simple baseball team stats but it’s very simple so you don’t need to know baseball to understand this. You can apply this to many other real life examples.
Here is the Excel file so you can follow along as you read: Excel Dashboard 1
Download this blog post as PDF here: Dashboard Example 1
We will create this dashboard in 10 Steps. In the end, we want a dashboard that looks like this.
This is a very simple Dashboard that shows 25 years of wins in a chart.
The key features of this are:
1. Start year – This is controlled by the scroll bar at the bottom of the chart
2. Enter the Team – This is a dropdown to pick from all the available teams
3. Chart – This is a line graph of 25 years of wins by team
4. Scroll bar – This is what makes dashboard interactive. You scroll through the years and the chart will update itself.
We get this information from a table of baseball teams with years and wins. This is a small sample of it:
We will Create this Dashboard in 10 steps.
Our first step is to create a common field in the Teams table so we can pull in the wins based on Year & Team
So we insert a column in column B and insert a formula to combine the year and team
Then after you insert the formula =A2&F2 into cell B2, you need to copy it all the way down.
So you now have a table with a common field which we will pull data from.
Next step is to create a few tables to use for data in our dashboard. First we need a unique list of teams for our dropdown lost.
To do this, copy all the teams in column F on the Teams tab and copy it to column J. We then need to pull in unique values only. Hilight the full column and click on Remove Duplicates from the Data tab.
Then click ok on the wizard prompt
And it leaves you with just the teams.
Next we need do to a pivot table on the Teams table to come up with a league average of wins by year. We need this for our chart. So go to the Teams worksheet and put your cursor on the table and go to Insert then Pivot Table.
Then in the wizard box, it should show the full table hilighted. You then need to change the destination to cell L1 by clicking on Existing Table. This will allow you to keep the pivot table on this worksheet.
Once that is done, scroll over to the right and you will now create the pivot table.
Drag Year id to the Row Labels and then drag W to the values box.
If you look above, you will see that it says “Count of W”. You need it to say Average because that is what we are looking for: Average number of wins. To do that, click the dropdown on Count of W and go to Value Field Settings. Then just choose “Average from the wizard.
You should now see a list of the average wins.
Now we are going to fill in the Entry area of the Dashboard. So go to your dashboard tab and make a title and enter spots for your Start year and Team.
We need to populate this. First let’s fill in the team. Go to cell D5 and then go to Data — Data Validation
Then drop down to List and you need to put in the range of unique Teams on the other tab that we just created.
Then if you go back to cell D5 on your Dashboard, you should have a drop down list .
Our next step is to populate the Start Year cell, for use in the line graph.
To do this, we will need to use a scroll bar, so we can easily scroll through the years. To accomplish this, go to the Developer tab and then to Insert and then click the scroll bar button. See below:
(Note: If you do not have the Developer tab, you can add it by going to File—Options—Customize Ribbons and then find the tab and add it)
Your cursor will then become a plus sign, that you will need to click and hold the cursor where you want to put the scroll bar. I put it around cells G12 to K12.
Next week need to format it. Right click on it and go to Format Control. You will see the wizard appear. We will then fill it out so we can populate cell D3 (Start year).
Current value: This is the default start number. We want to be able to select any year from 1903 to 2013, so we will use 1903 as our default.
Minimum value: The earliest year we want allowed, 1903
Maximum Value: The latest year we want allowed, 2013.
Incremental change : This is how many years we want the number to change when we click the right or left arrow on the scroll bar.
Page change : this is the number of years we want the year to change when we click on the scroll bar, to make the number increase or decrease more.
Cell link: This is the cell we want changed. It would be cell D3
So once you do all this, click on the scroll bar and you should see the year in D3 changing.
Next step is to create the actual table to create the line chart off of. In our chart, we will want to have Year, Team Wins and Average wins. So we need a 3 column table.
Let’s go to our Teams worksheet and make this interactive table. First let’s get the headings. I am putting it into cells P,Q and R.
Let’s build this table!
The start year is the year in cell D3 on our Dashboard tab. So let’s go to cell P2 and enter the formula to refer to D3 on the other page.
Now we need to finish off the years. We want 25 years of data . so let’s go cell P3 and put in the formula of P2+1. This will add one year to the initial year. Then we copy it down 24 rows so we have 25 total years.
Next, we move onto Team wins. For this one, we need to use a vlookup. We are looking for a certain team and a year and pull in the wins. So if we look at our main table of data, we have:
We can’t pull in by team as each team has many years. We can’t pull in by year as there are many teams each year. So we have the combined unique field of Year & Team, which is column B. We need to look for this info in the table and then pull in the column for wins.
So let’s go to our table in columns P to R. Go to cell Q2 and enter in the vlookup formula.
Let’s go over this.
The first part: P2 & ‘Team pivot’!$D$5 – This is what we are looking up. It’s our unique value. Cell P2 is simply the year we need. Then we use the & sign to combine fields to make it match what is in our Team win table. Then we enter Team Pivot $d$5, which is the cell where we did the dropdown to pick the team we wanted.
Next part – B:C – This is the table we are looking at. It’s the screen shot from just above, where column B is our unique value of year & Team.
Third part is a 2. That just means we are pulling the second column which is Team wins (column C)
Last part is False – This just means we are pulling exact match.
Then just copy it down to populate the table.
Now we just have to populate the final column, which is Average wins. We made a pivot table earlier of average wins, so we will just use a Vlookup and refer to that table.
So go to cell R2 and enter the Vlookup formula:
Let’s breakdown this formula.
Lookup Value is the year we are looking at. That is cell P2
Table array is the table we are looking in, which is columns L and M. Column L has the year in it.
Col Index is the column we are pulling which is 2, which means we pull column M which is average wins
Range Lookup is false, to show we are looking up an exact match.
Then copy it down 25 rows and we now have our Table to make the chart from!
We are down to our final step. Making the chart!
To do this, we need to hilight the table that we just made. This is what our chart will be based off of.
Now, go to Insert—-Line —- then drop down to a 2D line chart
Once you click it, the table will appear on your page. It will still be on your Teams tab, so we need to move this to our Dashboard tab.
Right click on the table and go to Copy. then go to where you want the chart to be and right click and choose Paste. Then just adjust it slightly or resize it to fit. you should now have this:
Final step, we need to format the chart to how we want it to look.
The chart Tools tab should appear at the top when the chart is clicked. Click on The Design tab and the Select Data Box. This will bring up the wizard.
On the wizard we need to only show the fields on the left that we want to show in the chart.
Since Years is on the left, we need to click it and then click Remove to get rid of it. Then we need to put the Year on the Horizontal Axis. So now click the Edit button under the Horizontal Axis part on the right side.
You will see a box like below, where you have to put in the range of years, from your table.
Once you do that you will have your chart nearly complete.
Finally, you can change the range of values for wins. Since we will never have less than 50 wins or more than 110 wins, we can change the Max and Min values of the vertical axis to make the chart more readable.
Right click right on the wins part of the chart and go down to Format Axis
Then we simply change the max and min values
Click ok and we are DONE! Finally! Congratulations, you just made an interactive dashboard! Just click the scroll bar and see the line chart move.
Now that you did this for wins, you can do this chart for ERA or homeruns or anything else and make it a full page of charts!