How to Create Dashboard in Excel – In this article you will learn about creating dashboards in excel, both from scratch and from a template. Dashboard software for excel is also discussed here. What is a dashboard? A dashboard in excel is a compilation of multiple reports with charts and graphs in a single place with a single source of data. It shows real-time results right on the dashboard so that the user can have the overall performance at one place. Dashboard makes it very easy for users to get the big picture quickly and take instant action if required.
Table of Contents
What Is a Dashboard?
Dashboards track KPIs, metrics, and other data points in one visual, central place. They give you a high-level view of work, helping you make quick decisions and keeping everyone up to date. A dashboard’s visual nature simplifies complex data and provides an at-a-glance view of current status or performance in real time.
Dashboards are made up of tables, charts, gauges, and numbers. They can be used in any industry, for almost any purpose. For example, you could make a project dashboard, financial dashboard, marketing dashboard, and more.
Like this…
There are three common types of dashboards:
- Strategic dashboards
- Analytical dashboards
- Operational dashboards
All these types of dashboards will help you save time in the analysis and presentation of your data.
Before Building the Dashboard: Research, Questions, & Things to Consider
Before you start building your dashboard, first take some time to reflect on why you need a dashboard, what purpose it will serve, where the data will come from, and what you capabilities you do and don’t need.
It might also be helpful to mockup your Excel dashboard on a piece of paper. Draw boxes for each data type to get a sense of the layout and add quick sketches of the type of graphs you want to include. This mockup will help get everyone on the same page and let you get approval from stakeholders before you start spending time and money on the actual dashboard.
Questions to Ask Yourself
- Why are you creating this dashboard? Are you trying to prove or disprove a hypothesis? Is this dashboard for a specific task, like showing status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will help guide the design and data.
- Do you need to track certain KPIs? Your dashboard should only highlight data that adds value. Make sure you understand the most important KPIs and create the dashboard around those metrics. Anything outside those main KPIs aren’t necessary.
- Who needs to see the dashboard? Is this for a colleague, manager, stakeholder, external vendor, or C-level executive? How do these people prefer to digest information? How much time do they have to look at this dashboard? Think about who you’re making the dashboard for and keep their preferences in mind.
- Where will the data come from? Do you need to manually import data into your dashboard or will you use an integration or connector to automatically sync and refresh data? What other tools do you use to gather data?
- How up to date does the dashboard need to be? Can you update your dashboard weekly or monthly, or does it always need to show real-time, updated information? Depending on what you chose, this will change the way you build your dashboard.
- What format does the dashboard need to be in? Are you emailing a static dashboard or providing a link to a dynamic one? Does the dashboard need to be embedded in presentations or decks? Do you want to share read-only access or do you want to provide editing capabilities to certain people?
Things to Consider: How to Design the Dashboard
- Dashboard elements: What do you want to include on your dashboard? You can choose from static tables, pivot tables, dynamic charts, Excel gauge widgets, or non-charting objects, like auto-shape objects. Do you want to add a lot of small charts or a couple big charts? Identifying the elements you want to add to your dashboard will help you group similar data together and give you an idea of the layout.
- Dashboard background color: How much color do you want to incorporate in your dashboard? Do you want to add a dashboard background color to make the dashboard elements pop? Do you want to color-code similar charts?
- Enhancing the dashboard UI: How important is ease of use? Do you want to spend time enhancing the dashboard UI? You could add hierarchy to the layout for easy navigation, add drop-down lists, add labels to each graph with auto-shape objects, or use freeze panes to prevent users from scrolling.
How to Create Dashboard in Excel
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.
Like this:
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.
Conclusion
Dashboard in excel template is mostly used for making management reports for saving time and effort. An Excel spreadsheet which contains a number of tabs organized in a way to provide ease of managing the data that is displayed. Dashboards in excel can be created by using free templates that are basically designed with the help of MSoffice excel.