Of course, if you can’t find a template that fits your needs, you can always create your own from scratch.
To simplify this process, let’s break it down into four steps.
1. Get your data into Excel
For you to be able to create your dashboard in Excel, you have to import your data first. You can either copy and paste the data directly or use an external app to pass the data in real-time.
Then, set up an Excel table with the raw data on another tab. With an Excel table, you will be able to name your rows, columns, and cells however you like. This makes referencing easier later on.
2. Set up your dashboard tab
On this next step, you need to structure your workbook and place the dashboard as the first tab for easy access.
Following the instructions on the first step, you could end up with about three tabs all in all.
Here’s how it looks like:
This makes your Excel file look clean and neat.
3. Analyze your data
One of the things you have to consider is the purpose of your dashboard. What do you want to highlight or show on your dashboard?
It’s important since you have to make an analysis of your data to reflect your purpose.
For that, you will need to use different Excel features and functions such as:
This may seem intimidating at first since you’re looking at a list of features and functions. However, you have to remember that whatever tool you will be using depends on your purpose.
Figure out the purpose of your dashboard first and see what tools (features and functions) you need to use to analyze the data.
4. Create the dashboard
This step could be summarized by simply inserting the charts and visuals you need on your dashboard.
As complicated as it sounds, the final result is a spreadsheet with different visuals that would tell a story about your data and fulfill the purpose of your dashboard.
Consider using the new charts in Excel that was released as part of Excel 2016. You could also use a Gantt chart if there’s a timeline involved. Or maybe you can use a chart as simple as a bar graph.
Don’t forget to add customization to your visuals to reflect your brand. You can always change the chart colors, fonts, and even a little bit of Macro to add flair to your dashboard.
That’s it! All you have to do now is to continue refining your dashboard especially when you need to add new data into it.