How to Build an Interactive Dashboard in Excel
Are you tired of a lot of data in Excel? With an Excel dashboard or Modern UI Dashboard, you can learn the most important information in just one click.
What is Excel Dashboard?
“A dashboard is a visual representation of key metrics that allow you to quickly view and analyze your data in one place. The Excel Dashboard is used to display overviews of large data tracks. Excel Dashboards use dashboard elements like tables, charts, and gauges to show the overviews. The dashboards ease the decision-making process by showing the vital parts of the data in the same window.”
This is an example of data that often frustrates us because of the large amount of data that needs to be analyzed quickly.
There are several ways to create a dashboard in Excel, we can use VBA or PivotTable, and the easiest is to use a PivotTable.
Step 1
Prepare your data in Excel
Step 2
Create a PivotTable
Click cell anywhere in your table, then go to the Tab Insert, and search for the PivotTable feature.
If you click the PivotTable, it will look like this, you can choose where you want the PivotTable report to be placed.
- New Worksheet, if you haven’t set up a new sheet.
- Existing Worksheet, if you already have a new sheet.
You can select the data you want to create.
The formula you will need:
MAX
- The MAX function returns the largest numeric value in the data provided. The MAX function can be used to return the largest value from any type of numeric data.
=MAX(B68:B79)
INDEX and MATCH
- The INDEX function can return an item from a specific position in a list.
- The MATCH function can return the position of a value in a list.
=INDEX($A$68:$A$79;MATCH($D$68;$B$68:$B$79;0))
PERCENT or AVG, to make a percentage. The average is calculated by dividing your score by the total number of marks and multiplying the fraction by 100.
=GETPIVOTDATA(“Total Data A ”;$A$119;”Year”;2020)/GETPIVOTDATA(“Total Data A”;$A$119)
=GETPIVOTDATA(“Data A”;$B$4;”Data B”;”Data C”)/GETPIVOTDATA(“Data A”;$B$4)
COUNT, function to get the number of entries in a number field that is in a range or array of numbers.
=GETPIVOTDATA(“Total Data A”;$A$119;”Year”;2022)
The formula is flexible, you can combine the formulas based on the data you want to create.
Step 3
Design your dashboard.
Every dashboard has a character, so make it your own way.
Step 4
Combine with Slicer.
Slicers provide buttons that you can click to filter tables or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed. You can use a slicer to filter data in a table or PivotTable with ease.
How to activate Slicer
Click the chart-Tab Analyze-click Slicer-Checklist
To activate your Slicer, you can choose PivotTable connection and connect your PivotTable.
Reference:
Excel & VBA Tutorial
Other Level’s
Excel Bootcamp
