Want to create an Excel automated dashboard?
Do you need help to turn your complex data sets into easy-to-understand dashboards? But… you do not want to spend additional costs and time learning BI tools like Tableau and Power BI and if you are wondering if creating a dashboard in Excel that updates automatically is possible?
YES! You can absolutely do that – take it from someone who has lost count of the number of Excel Dashboards she has created (both static and dynamic) 😉
In this post, I will share the 9 easy steps to create an Excel automated dashboard including dashboard mockup, data collection methods, excel functions, excel tools, excel dashboard examples, and more.
This article highlights how to create an Excel automated dashboard easily.
Step 1: Define your goals
The first step to any dashboarding project is to define the objective.
There are so many available metrics, so defining the goals helps you shrink the area and identify the relevant data. This way, you won’t spend unnecessary time on tasks that won’t help you reach your goals.
At this step, you also need to identify the KPIs that you MUST track and check whether it is possible to collect or calculate them. If you can’t do both, switch out the KPI.
Step 2: Design a mockup of your dashboard
Next, before you dive straight into Excel, get a pen and paper and design a mockup of your dashboard. It can look as simple as this (see image below).
<image>
The main goal is to visualise how the dashboard looks like and where each graph should be placed. Ideally, this should be a one-pager.
If you want to be thorough and save even more time in the later process, you may want to fill in more details for each visual such as:
- Metrics on the x and y axes
- Graph used
- Data needed
Resource for you: 5 Must-Know Dashboarding Best Practices Every Analyst Can’t Ignore
Step 3: Collect Data
Now, it is time to go into the platforms where you want to collect the data and import them into Excel.
Step 4: Import Data into Excel
After importing them into Excel, name the tab “RAW” as best practice. Do not make any changes to this tab.
Example:
Step 5: Prepare your Data
Next, duplicate the “RAW DATA” tab and name the new tab “CLEANED DATA”.
In the “CLEANED DATA” tab, add relevant columns and formulas to calculate the KPIs needed for the dashboard.
As best practices, columns that are calculated (ie derived from other columns) are coloured (see Columns F to I below).
Step 6: Build Pivot Tables
Now, it is time to build the pivot tables that are needed to create the charts.
Refer to the mockup that you have drawn in step 2, and create all pivot tables needed.
- Highlight all the columns in the tab.
- Go to the “Insert” tab.
- Click “PivotTable”.
- Drag the relevant data into Rows, Columns, and Values when necessary.
Examples:
Resource for you: How to create a PivotTable to analyze worksheet data
Step 7: Create Charts from Pivot Tables
Next, create charts from these pivot tables you have built.
- Click on the Pivot Table
- Navigate to the “Insert” Tab
- Click on the chart you want to create
Resource for you: Create a PivotChart
Step 8: Format your Charts
After creating the charts, it is time to beautify them (see example below).
Here is a simple checklist to make sure that your dashboard is both functional and pretty:
- Colour code your charts
- Insert legend
- Round up the metrics to at least 1 decimal place or to the ten place
- Insert the chart title in an Excel cell, instead of adding to the chart
Step 9: Add Filters
The last step to building an interactive Excel dashboard is to add filters. This is the ultimate trick 😉
Follow these steps carefully:
- Click on the first table
- Navigate to the “Insert” tab
- Click on Slicer
- Select all the relevant filters you want to add.
- Click “OK”
*All the slicers should appear on your sheet
- Right-click on 1 of the filters
- Click on ‘Report Connections’
- Select all the pivot tables that you want to ‘connect’ to this filter
- Repeat steps 5 to 7 for all your filters
If you would like to, you can also colour-code your slicer to match the aesthetic of the dashboard.
Example:
TADA! And you just created your own customised Excel dashboard without using any complicated coding or macro functions.
Excel Dashboard Templates
I can’t deny the fact that when it comes to building an Excel dashboard takes significant time. But when it comes to dashboarding, it is always a one-time pain kind of thing and you can save lots of time in the analysis process.
I know it can be scary and complicated for some of you to navigate in Excel, especially if you are not a technical person.
Don’t worry.
Here are some handy downloadable Excel Dashboard Templates to get you started.
1) Social Media Performance Dashboard Template
This dashboard template helps to track monthly performances across various social media platforms (TikTok, Instagram, Facebook) so that you can focus on engaging your audience and growing your account.
2) Etsy Sales Dashboard Template
This template helps Etsy owners track their monthly sales performance in their Etsy shop so they can grow their revenue.
This post highlights the 9 steps to create an Excel Automated Dashboard with ease.
If you love to see more content like this, connect & follow me on social media to stay updated with the latest information.
Other posts you may like:
- The Importance of Data Analytics in Ecommerce
- 5 Common Data Cleaning Tips Business Analysts Need to Check
- How to choose the perfect analytics course for beginners?
