Create an Interactive Dashboard with Excel
A hands-on, step-by-step guide with a simulated dataset you can create on your own
Data Visualization with Excel
Now is the time that we are almost drowning in data. We generate an astronomical amount of data each day, and it will be more difficult for all of us to avoid working with data to make sense out of tons of rows and columns of numbers (if you are not doing so already.)
If you use Excel, chances are you have to use it to visualize some data. You may already be familiar with creating different types of charts with Excel. In some (or most) projects, you have to make several charts. However, you will need to create a dashboard to tell a story from the ‘big picture’ by connecting those charts, like putting jigsaw pieces together.
A dashboard (or a data analysis dashboard) is simply a collection of colorful and meaningful charts that show key business performances based on different metrics. A dashboard doesn’t live up to its full potential, though, if it is static. A dynamic, interactive dashboard tells a smooth data story and may lead to interesting insights that are not readily available from a static one.
The following is a hands-on, step-by-step guide to creating an interactive dashboard from one of the most popular spreadsheets out there, the good old Microsoft Excel.
NOTE I:
- This article aims to be a hands-on Excel guide/refresher, focusing on creating an interactive dashboard. All data, including all names of the people or organizations, is purely imaginative and programmatically simulated for educational purposes only. The data has nothing to do with any actual person, organization, or situation on this plane of reality.
- This is a 20-min read article, but once you get a hang of the techniques, you will be able to create a dynamic, interactive dashboard in a much shorter time.
NOTE II: ‘Excel’ in this article refers to Microsoft Excel 365 (subscription) runs on Windows 10
Creating an Interactive Dashboard with Excel
1. Getting a dataset
As mentioned in NOTE I, the data used in this article is ‘purely imaginative and programmatically simulated.’ We will use Excel to create an imaginary dataset filled with random names of persons, organizations, locations, and numbers of items sold, prices, and revenue. But first, we need to imagine a situation.
Let’s come up with something fun!
Let’s imagine that we are an employee at ‘Q-Gadgets,’ a B2B company selling computer accessories operating in four states: New York, Oregon, Texas, and Kansas.
The company has eight salespersons responsible for customers in different states (regions). New York is for Singh and Ken, Oregon is for Suzuki and Sandra, Texas is for Josh and Roland, and Kansas is for Rosa and Mario.
We have local businesses buying our computer accessories in each state. We have ‘SJB Tech,’ ‘Game Smart,’ and ‘Com & Co’ in New York, ‘DigiFun’ and ‘GameSignal’ in Oregon, ‘A Plus,’ ‘Digital Garage,’ and ‘DC Gear’ in Texas, and, finally, we have ‘BoardSix’ and ‘SpaceEngie’ in Kansas.
It’s clear that there are connections between the salesperson, region of operation, and the customer, as you can see in the following table:
Now that we already have lists of the basic stuff set up, we are still missing our items to sell. Let’s say that Q-Gadgets sells only eight types of computer accessories: keyboard (each for $199), mouse (each for $25), VR headset (each for $290), AR headset (each for $390), joypad (each for $49), mouse pad (each for $19), headset (each for $39), and webcam (each for $129).
The following table summarizes our items and their sale prices:
A (imaginary) fun fact about Q-Gadgets!
One of the perks of being a salesperson at Q-Gadget is that if you already sell ten items on any day, you can feel free to have the rest of the day for yourself. You can’t expect to sell ten items every day, though. On some slow days, you may sell only 1 item. But because of the attractiveness of our goods, at least one sale per day is guaranteed. This is why the minimum amount sold per day is 1, and the maximum is 10 — talking about being imaginative!
Simulating the Data
Let’s imagine that it’s time to visualize (and analyze) our beloved Q-Gadgets’ performance with data logged from the beginning of 2018 to the end of 2019. There are a total of 1000 records of transactions (Q-Gadgets is a small company). Let’s create an artificial randomized sales dataset for Q-Gadgets.
Randomizing the transaction dates
Imagine a transaction data set logged on an Excel spreadsheet: the first column would most probably be the ‘date.’ Now, it’s only reasonable that this data would be recorded in chronological order. But, in reality, we can’t say how many salespeople sell which item and how many times on which date. Therefore, we need to create a random list of dates. First, we will create a list of dates from January 1ˢᵗ, 2018, to December 31ˢᵗ, 2019. For the randomization process, we also need to create a list of running numbers starting from 1 next to the date column:
We can use Excel’s built-in Auto FIll capability to quickly populate the ‘id date’ and the ‘date’ columns. First, type in a starting value in a cell; in this case, it is 1-Jan-2018, then you can activate Auto Fill by moving the mouse cursor to the lower right corner of that cell until it changes into a black + sign. Now you can click and hold the button while drawing the mouse down until you reach your desired value. In this case, you’ll need 730 rows to fill in all the dates from January 1ˢᵗ, 2018 to December 31ˢᵗ, 2019:
NOTE III: Excel’s built-in Auto Fill can automatically populate columns or rows with either increasing or decreasing values. In some cases, you may have to type in the second value, then select both cells before activating the Auto Fill capability.
We can store all the dates and the previous information about salespersons, items, customers, and regions on one sheet. We will use all this information to generate randomized records to create data for our dashboard. In this case, I stored the information in a sheet called ‘ForRandomize.’
Now that we already have the ‘id date’ and ‘date’ columns filled. Let’s create a new worksheet and name it ‘Data.’ In this worksheet, let’s create 1000 records of random dates with RANDBETWEEN and VLOOKUP functions. We will use RANDBETWEEN to randomize the numbers in the id date column and then use VLOOKUP to look up the dates related to each randomized number.
NOTE IV:
- Excel also stores the dates in sequential serial numbers for calculation purposes. But in the serial number format, 1-Jan-18 is 43102, which is not easy to understand. To simplify things, we will not randomize those serial numbers of dates but randomize the numbers in the ‘id date’ column instead and use those randomized numbers to link to the dates later.
- Use RANDBETWEEN to randomize numbers in the ‘id date’ column
- Use VLOOKUP to pick up a date related to each randomized number and fill the adjacent cell with that date
- You can directly copy and paste any formula in the gray boxes on your Excel worksheet and try them out
We already know that there are 730 days during our period of interest. So, we will first create a list of random numbers from 1 to 730 in the ‘Data’ worksheet by the following formula:
#=RANDBETWEEN(first number, last number)=RANDBETWEEN(1,730)
Then use Auto Fill to populate 1000 records with this formula quickly:
Next, we will use the randomized numbers as indices to link to the dates and fill in the adjacent column with VLOOKUP:
#VLOOKUP(look for what?, where to look?, which column has the values you are looking for?)=VLOOKUP(C1,ForRandomize!$A$2:$B$731,2)
The above VLOOKUP formula looks for indices in column C (starts at cell C1) in the ‘Data’ worksheet. Then we switch to the ‘ForRandomize’ sheet where we store the original dates list. We will then look for the dates in the range A2:B731 in the ‘ForRandomize’ worksheet and use the date values in column number two (Column “2”) in the range to fill in our data table in the ‘Data’ worksheet.
In the formula, I included the $ signs in the range reference to make absolute reference to all the cells in that range. Notice that each number in column C links to each date via the numbers in the ‘id date’ column. At this step, don’t worry if the randomized dates become serial numbers. We can change the format of the entire column to ’long date’ or ‘short date’ to get a proper date format.
Now that we have a list of random dates, let’s create a list of random salespersons next to it. We will use the same method, but, in this case, the first and the last number in the RANDBETWEEN function will be 1 and 8, respectively. There are only eight salespersons in Q-Gadgets, after all.
Now we have the randomized data for ‘when’ (dates) and ‘who’ (salespersons), we will generate a list for ‘where’ (regions) next. This step is a little trickier, and you will see why very soon.
Our salespersons form up teams that operate in different states, as mentioned earlier. So, Ken can’t have sales in Texas because he only works in New York. According to our information, if the salesperson is Ken (or Singh), the state must be New York. This condition applies to all the rest of the salespersons.
To apply the condition, we need to use the IF function and OR logical operator. It is the same as coding with IF-ELSE conditions in any other programming language, save for the syntax:
#We refer to the salespersons in column D2, in this case, to fill in #the states for each sales person=IF(OR(D2="Singh",D2="Ken"),"New York",IF(OR(D2="Suzuki",D2="Sandra"),"Oregon",IF(OR(D2="Josh",D2="Roland"),"Texas",IF(OR(D2="Rosa",D2="Mario"),"Kansas"))))
The above function is a nested IF function. To translate the syntax into layman’s language: If what in cell D2 is “Ken” or “Singh,” fill “New York” in the current cell. Else, if what’s in cell D2 is “Suzuki” or “Sandra,” fill “Oregon” in the current cell,…and so on. Use Auto Fill to populate all the records with this function.
Next, use the same randomization method to generate another ‘who’ (customers) list. This randomization step is more complex than the earlier ones because there are also conditions to consider.
A salesperson can only make sales to any company in their area. So, Sandra cannot make sales to BoardSix because she operates in Oregon, while BoardSix is in Kansas.
Again, we still need to use the nested IF function. First, we will generate a list of random numbers to use as indices for our customer names. Let’s say that the region data begins at the cell E2, we can write the following function and use Auto Fill to fill in our customer names:
=IF(E2="New York",RANDBETWEEN(1,3),IF(E2="Oregon",RANDBETWEEN(4,5),IF(E2="Texas",RANDBETWEEN(6,8),IF(E2="Kansas",RANDBETWEEN(9,10)))))The above syntax translates as: If what in cell E2 is “New York,” generate randomized numbers between 1 and 3. Else, if what in cell E2 is “Oregon,” generate randomized numbers between 4 and 5,… and so on. Remember that in the ‘ForRandomize’ worksheet, the number 1 refers to SJB tech in New York, and the number 2 refers to Game Smart in New York,… and so on.
Next, we use the mentioned randomization method to get a list of ‘logically randomized’ customer names in the next adjacent column.
Almost there! Now we only have to create the randomized sales data, including items, prices, amount sold (volume), and revenue.
Since any salesperson can sell any of the eight items (keyboard, mouse, VR headset, AR headset, joypad, mouse pad, headset, and webcam), we can use the previous randomization method to generate the list of items sold. But this is another special randomization case. We need more than the item names, in this case, we also need their prices. So, we have to specify both the column containing item names and prices in the VLOOKUP formula.
In this case, we will start by looking for randomized item numbers in column H2 in the O2:Q9 range in the “ForRandomize” sheet. Remember to use absolute reference when referring to the range ($O$2:$Q$9). We need to get both the item names and prices from column “2” and column “3,” respectively.
=VLOOKUP(H2,ForRandomize!$O$2:$Q$9,{2,3})#The numbers 2 and 3 in the curly brackets indicate that we want to #get the data from the Column "2" (item names) and Column "3" (price) in the data range #we supplied to the VLOOKUP function.
From the formula above, to get data from more than one column with VLOOKUP, the syntax rule dictates that we need to specify those column numbers in curly braces “{ }”. In this case, the data is in columns “2” and “3”.
Now we have only two more columns to create to complete our ‘simulated dataset.’ There is nothing complicated about these two columns. The first one is the amount (volume) sold. You can just use the RANDBETWEEN function to generate randomized numbers between 1 to 10 for this purpose. Next, we create the revenue column by multiplying the sale price of each item with the volume sold.
If you have been following all these steps to the letter, your dataset should look similar to this now:
Until now, if the information in your dataset is different from what’s shown here, don’t worry. It should be, and you are on the right track. If you still have randomization formulas in the cells, Excel will refresh the randomized values every time you perform actions such as copy and paste.
You can prevent this by copy the entire column and paste it in the same place as values by right-clicking and choosing paste options, then click paste values. This action will remove all the formulas and leave you with only the values in those cells. These values will no longer change with any further actions you perform on the spreadsheets.
Let’s clean the data!
Before moving on to the next step of creating an interactive dashboard, let’s clean up this dataset and make it neat. You will notice columns with randomized numbers in front of columns whose values we will use to create the dashboard. Those numbers are irrelevant now. We can remove them.
You can also format the values in the price and revenue columns to display the $ sign without any decimal points.
Add a final touch by filling in a nice soft-looking color and bold the column names’ font.
Your cleaned dataset should look similar to this:
2. Creating Charts
Now we have come to the second part of this fun project — creating charts!
We already know that a dashboard is a collection of charts that can help us see the big-picture of an organization’s performance. Before creating any chart, however, we just need to imagine what’s important for us to know.
In this specific imaginary business case, the performance is the revenue. The higher the revenue, the better the performance. But what are the factors affecting the revenue? We may want to judge the performance based on these factors (or metrics, in data analytics speaking).
Let’s come up with the metrics we can use to judge the performance!
You may come up with something different, but I came up with these metrics that should affect the performance:
- Different times in the years (DATE)
- Customers (COMPANY)
- Salesperson (SALES REP.)
- Region/state (REGION)
- Types of the item (ITEMS)
Let’s create pivot tables to summarize the revenues from these metrics!
Another step before creating any chart, in this case, is that we have to create a pivot table for each of the metrics we’ve come up with. A pivot table summarizes or aggregates the effect each item in our big dataset has on our targeted ‘performance.’
For example, if we are to see how the performance changes with time, we can create a pivot table with days or months of the years in a column and the summation of revenues in another, the table will show the total revenue made on 1-Jan-2018 (and on every unique day) regardless of how many salespersons made how many sales on this day.
To create a pivot table, just click anywhere inside our data table and click the ‘insert’ tab in the menu bar towards the top left of the screen, then choose ‘Pivot Table’ towards the top left of the ribbon. Let’s create a pivot table based on dates and revenue data in a new worksheet:
As you can see, the Pivot Table Fields selector appeared automatically on the right side of the screen once I created the pivot table on a new worksheet.
Inside the selector section to the right of the screen, I selected the DATE as the metric field. Excel then selected the Quarter and the Years fields automatically.
When I checked on the REVENUE field checkbox, Excel was smart enough to know that I wanted to see the sum of the revenues according to the metric fields I chose. In this case, I unchecked the Quarter metric and got a table with the sum of each month’s revenue for 2018 and 2019.
Let’s create our first chart!
Now it’s time to have even more fun! We are transforming all those numbers in our data table into meaningful and, in most cases, colorful shapes and lines. Once transformed, we can use those colorful shapes and lines to tell stories about our data.
On the worksheet with our pivot table, click anywhere inside the table and go to the ‘insert’ tab again. Now move your mouse towards the top-middle of the screen. You’ll see different kinds of chart icons. Click on the ‘Insert Line or Area Chart’ button, then choose ‘Line with Markers’ chart type:
voilà! You have a line chart with markers showing the ups and downs of Q-Gadgets’ monthly performance throughout 2018–19. This chart will be one of the jigsaw pieces we are putting together to create our interactive dashboard.
At this point, you can move your mouse cursor onto any field button on the chart, right-click, then choose ‘Hide All Field Buttons on Chart.’ You can also remove the chart title and legend as we won’t need them in our dashboard.
The following animation shows how you can remove unnecessary stuff from your chart at this point:
Just keep in mind that when your mouse cursor is on the plotting area or the chart area (just outside the plotting area), you can right-click and choose to format that area to change the appearances. We can do this later in the last steps.
NOTE V: It is possible to create a different chart type. Use your imagination to visualize what chart type best represents your data’s character and create it accordingly. In this case, you can also try to create a bar chart.
Another thing to keep in mind is to change a worksheet name according to its content. For example, this worksheet’s name can be ‘DatePivot.’ You can do this by double-clicking on the worksheet tab on the lower left of the screen and type in a new name for it.
Let’s create more charts!
The next step is to create more charts out of different metric fields. Let’s just make a copy of our worksheet with our first chart by clicking on the sheet tab and holding the mouse button while holding down the CTRL key at the same time, then move the mouse a little to the right and release the mouse button.
Delete the chart and select a new metric field to create a new pivot table on the newly copied worksheet. The following animation shows you exactly how to do so; this time with the salesperson (SALES REP.) as a metric field:
Now, you have a new pivot table based on the salespersons’ data. Create a chart of some type out of this pivot table on this new worksheet. Use all the mentioned techniques to create charts for all the rest of the metrics.
Special Case: The Map Chart
It is worth mentioning that the map chart is a special case because we cannot create a map chart directly out of a pivot table.
We will create a map chart based on the state (REGION) data from the pivot table, but before that, we will need to create another simple table with values from that pivot table.
We can do this simply by copying and pasting values from the pivot table to create a new table:
Next, we must link the values from the new table to the original pivot table. Doing so will create a ‘dynamic connection’ between the original dataset and the new simple table we are creating. We can do this easily by typing = in the cell with the revenue value and then click on the corresponding cell in the pivot table:
From the animation, you can see that once you typed = and clicked on the corresponding cell on the original pivot table, Excel automatically filled in the cell in the new table with a formula. Pressing enter resulted in the same value as that in the cell of the pivot table you clicked. The is the method to link the values in the new table to the original pivot table.
Once you clicked on the cell in the pivot table, you got the following formula:
=GETPIVOTDATA("REVENUE",$A$3,"REGION","Kansas")#"Kansas" refers to the region with revenue in the cell
We can use Excel’s Auto Fill once again to fill in the rest of the new table’s cell with that formula, but we need to change the region reference (“Kansas”) to its corresponding cell notation first:
=GETPIVOTDATA("REVENUE",$A$3,"REGION",D4)#We can change "Kansas" to D4 and then use the Auto Fill to fill in #the rest of the cells in the new table.
To prevent errors, we can give proper names to the column in our new table before creating a map chart. If we provide an appropriate name such as “states” to the column containing state names, Excel will automatically recognize that the values in that column represent states and won’t cause any fuss. You can also change the format of the revenue columns to display currency properly:
From the table above, we can now create a map chart. Excel will automatically recognize the geographical locations from the names in the ‘States’ column and automatically display a correct map:
3. Putting the Pieces Together
Now that you have different charts for each pivot table, let’s create a new ‘Dashboard’ worksheet and copy and paste each chart onto it.
Once all the charts are on the ‘Dashboard’ worksheet, try to perform basic tasks such as rearranging or resizing them, changing the font size on their axes, or changing the unit notation for the numbers to be easier to read and understand.
There you go! Your dashboard is almost done! Now your dashboard is a static, non-interactive one and that’s OK at this point. We will make it interactive very soon.
It is also OK if your dashboard doesn’t look like what’s shown above. You can choose a different chart type and a different arrangement for each metric.
4. Making your Dashboard Interactive!
Now is the time we have been waiting for (too long)! Making your dashboard interactive means making your dashboard responsive to a set of filters you link to it. With Excel, a filter for an interactive dashboard is called a slicer.
You can add a slicer to each chart by clicking on any chart to activate it, then click on the ‘insert’ tab and then choose ‘slicer.’ A popup window will then appear to prompt you to decide what metrics you want to use as filters for your dashboard:
Congrats! Now each of your charts has its own slicer. At this point, each slicer is still not connected to the other.
We need to connect all slicers together because, in reality, every single metric affects the performance as a whole.
To connect all the slicers, we need to report to Excel that each slicer connects to the same data source by right-clicking on a slicer, then choose ‘Report Connections…’. A popup window will appear, prompting you to decide which pivot table to connect to that slicer. Just select every pivot table by checking every checkbox. Do this with all the rest of the slicers:
Good news! Once you’re done reporting connections of every slicer to every pivot table, your dashboard is now interactive. Try to click on a button on any slicer and see the visualization changes accordingly.
Now you can rearrange and format the appearance of the slicers by going to the ‘Slicer’ tab and tweaking their configurations:
You can change your dashboard appearance to make it easier to understand, more attractive, and more effective in telling stories from your data!
My version of the dashboard looks like this:
In my version, all charts have the same color tone. I moved the slicers to the bottom of the screen, changed their color, resized, and changed their shapes. I added a computer-related background image to the dashboard to enhance its looks. I also put more context to the dashboard by giving short descriptions on top of each chart and adding presentable icons in front of those descriptions. Another thing that shouldn’t be left out is the name of the dashboard. In this case, it’s ‘Q-Gadgets: 2018–19 Performance Dashboard’.
NOTE VI: Use the same color tone to colorize all the charts in a dashboard may not be the best idea, especially when it comes to visualizing pie or donut charts, but just take this as an example of what you can do to customize your dashboard.
If you’ve already finished customizing your interactive dashboard with beautiful (and meaningful) colors, shapes, and sizes, I have even more good news to tell you! Your dashboard is not only interactive but also dynamic! This means that if there is any change in the original data table, the charts in your dashboard will also change automatically. Give it a try!
5. Let the Dashboard Tell Your Data’s Stories
It may not mean much to tell data stories from this simulated dataset. However, it is still a good practice for our data story-telling skills.
So, here goes my version of data story from the dashboard:
“At a glance, Q-Gadgets’ revenue peaked at the beginning of the second quarter and in the middle of the last quarter each year. There was, however, a revenue spike in the third quarter of 2018. AR Headsets are best-sellers in both years, probably due to the global technological trend. Our best month was November of 2018 and our worst month was August of 2019. The year 2019 saw an overall decrease in sales, however, there was a distinct increase in webcam sales in Kansas. Two of our best salespersons, Josh and Suzuki, worked in different states from where we also got most of the sales. Our best customer was BoardSix from Kansas, and their most favorite items are AR Headsets, VR Headsets, and keyboards. Overall, 2018 was the better year. However, we may need to urgently increase customer awareness in New York. We also need to improve our relationship with some customers, especially with SJB tech in New York.”
6. Conclusion
- You have just learned/refreshed your skills to quickly create a dynamic, interactive dashboard in Microsoft Excel 365 from an imagined randomized data set.
- Your dashboard is flexible. It can look different from the examples in this article, but it is still meaningful and can tell stories from the data you used to create it.
- Today, you can use data analysis and visualization software such as Tableau or Power BI to create impactful and stunning dashboards. However, you can still create such dashboards from scratch using only Excel.
I hope you find this article useful. Until next time, happy creating dashboards!
