Accelerate your thought leadership by contributing to our blog. Join our community of experts now!
Excel has always been one of the main go-to tools for executives who are looking to create spreadsheets in which they can gather data and track how their projects are progressing.
However, as helpful as spreadsheets can be, they are essentially just a compilation of different numbers and information.
To make sense of these numbers, it’s much more practical to use Excel Dashboards.
Excel dashboards are one of the best ways to track KPIs and key metrics, compare data points, and provide shareholders with an overview of data-backed information that will help them make everyday decisions.
In this guide, we will show you how to create a comprehensive Excel dashboard (both in the native tool and Databox), some things you should keep in mind when building one, best practices, Excel dashboard use cases, and much more.
Let’s dive in.
Before we move on to the actual creation process, it’s important that you understand exactly what is a dashboard in Excel.
Excel dashboards are visual portrayals of data. They provide a complete overview of large amounts of data, which means you can use them to quickly pull out and check a specific metric or KPI.
This is immensely important in situations where time is of the essence and you need to make some urgent decisions – going through large volumes of information simply isn’t an option.
There is also the ‘aesthetic’ component.
Let’s face it, there is nothing pleasant about looking at raw data. Of course, there is plenty of important information included, but with hundreds of different rows and columns, understanding the data becomes mission impossible.
Excel dashboards do a tremendous job of transforming this dry data into meaningful insights through the use of visualization tools like charts, tables, or other interesting elements.
Nowadays, dashboards can be ‘hired’ for a number of different jobs and companies use them for better budgeting, project management, marketing, sales reporting, and more.
The three most commonly used Excel dashboards are Strategic dashboards, Analytical dashboards, and Operational dashboards.
In summary, Excel dashboards are a great way to make sense of extensive data and present it in an understandable way to the highest-ranking members of the company.
Creating an Excel reporting dashboard is much more than following a series of steps, it also involves a certain amount of planning and research.
Here are the four main things you have to consider before you start building your Excel dashboard.
First of all, you need to determine the purpose of your dashboard.
Will it be for a specific task, like monitoring the progress of a project, or do you want to achieve some sort of a bigger goal, such as tracking a department’s performance?
Knowing exactly what you want to achieve with the dashboard will directly make the data gathering and design process a whole lot easier.
Your Excel dashboard should revolve around only the most important KPIs.
Take some time to filter out the data that adds the most value and make sure you understand the KPIs you will be including.
A common practice is to use around 10 KPIs in the dashboard. While there might be a dozen more useful metrics, you won’t need all of them, so be thorough when categorizing the data.
What you include in the dashboard and the way in which you include will depend a lot on the audience.
Will the dashboard mainly be used by your colleagues, key stakeholders, investors, or highest-level executives?
Each one of them has a different preference when it comes to digesting information, which will hugely influence the way you create the dashboard.
Related: Reporting Strategy for Multiple Audiences: 6 Tips for Getting Started
Will a static dashboard be enough to do the trick, or will you need an automated Excel dashboard?
Using the wrong format for your dashboard can be a costly mistake, especially if you create a static dashboard and it turns out that your stakeholders want the data updated as soon as any real-time changes occur.
Okay, so now you know what an Excel dashboard is and you have done proper planning regarding the audience, KPIs, and the overall goal of your dashboard.
Time to show you how to create a dashboard in Excel.
Here are the steps you have to follow.
Creating a layout to determine what your Excel dashboard will look like is much more effective than improvising each step along the way.
Some of you might be used to using a pen and paper for drawing out layouts, but we recommend Microsoft Excel – after all, you will be using it the whole time.
To create a logical structure, you can take advantage of Excel Workbook.
There are plenty of worksheets available, but you only need these three:
An additional piece of advice – spare some time to create an outline in PowerPoint (or another presentation tool) that you can show your stakeholders or executives, and ask them about their opinions.
This way, you will know exactly what they are looking for and you can create a dashboard that brings them the most value.
Here is an example of what a proper layout should look like:
To create an Excel data dashboard, you first need, well, the data.
You can import data into excel by either copying and pasting it directly, or using the external data sources.
Click on the ‘Data’ tab and choose one of the available import options. Everything is pretty simple and straightforward.
In this picture, we chose the Text/CSV option to create the starting dataset.
Go to the ‘Raw Data’ tab, you have the option to either copy-paste your data or import it directly from an external source.
For an easier overview, use a tabular format so each data point is stored in an individual cell.
In the picture below, we used columns like Project Name, Start Date/End Date, Duration, Number of Team Members, Project/Actual Budget, Risks, and Pending Actions.
To add up all the values in a column, you can use one of Excel’s formulas.
For instance, we used the Project Budget column, clicked on the last empty cell, and typed in =SUM – this automatically calculates the cells you choose.
This part will probably require the most time, so we will explain everything gradually.
Let’s start with cleaning the data.
There are plenty of different ways you can do it, but here are some of the main things you should do:
Duplicates usually cause the most problems, so here is how you can remove them:
If you don’t know how to eliminate the leading and trailing space, you can do it by going to the Formula tab and using the TRIM function.
Next, copy the formula and use data cleansing tools to make sure there aren’t any errors.
Okay, so that does it for the cleaning part and we can move on to the filtering.
Once you have your data in an Excel worksheet, you should use the tabular format to make the filtering easier. Each data point should be stored in a separate cell (city name, address, phone number, etc.).
Then, convert the tabular format into an Excel table and select a specific data range. To do this, go to ‘Insert Tab’ and click ‘Table’.
Now that you have your data organized in a table, it’s time to take a step back and observe it.
What should you highlight? Is there anything you need to remove? Do you need to display all the data?
Answer these questions based on the story you are trying to communicate to the readers of the dashboard.
To analyze the data, there is a variety of different methods and Excel formulas that you can use.
Here are some examples:
As for the Excel functions, some of the important ones are XLOOKUP, SUMIF, COUNTIF, NAME MANAGER, ROW, OFFSET, COUNT, and VLOOKUP.
If this all seems far too confusing, don’t worry, it’s actually easier than it seems. Once you determine the purpose of your dashboard, you’ll know which methods will work best for the analysis process.
The final stop – building the Excel dashboard!
Let’s go through the features you’ll need.
The Gantt chart is used for visually displaying the timeline of your project.
To create this chart, press ‘Insert’ in the main Dashboard sheet.
Next, click the bar chart icon in the ‘Charts’ tab and choose the second option.
Lastly, connect the bar chart to columns like Project Name, Start Date, and Duration.
This is what a Gantt chart looks like:
In case you need some other type of chart, you can find them in the ‘Insert’ tab as well.
We will use a column chart as an example.
Firstly, press ‘Select Data’ after right-clicking the chart.
Choose the ‘Add in Legend Entries’ option. You can choose the title of the columns in the ‘Series name’ field, and the data in the ‘Series values’ field.
In most cases, the X-axis won’t be labeled properly. You can fix this by going to the ‘Horizontal Axis Labels’ tab and clicking ‘Edit’.
For formatting, you should click on the ‘Design’ tab.
Here you can add a title by following Add Chart Element > Chart Title > Above Chart.
That’s it! Here is an example of what the chart should look like:
Pivot tables are one of the best ways to single out the crucial metrics and highlight them.
To create a pivot table, open the ‘Insert’ tab once again and choose ‘Pivot Table’.
Click the last icon in the ‘Table/Range’ field and select the entire data table.
On the right, you will see a ‘Pivot Table Field List’ in which you can choose which data subsets you want to include in the pivot table.
If you use Excel a lot, you probably also use Google Sheets. And if you handle large amounts of data, you probably use Pivot tables quite often. Here is a useful guide we have created on building and using Pivot tables in Google Sheets.
Before you turn off your laptop and start celebrating, there are still a few more components your Excel metrics dashboard needs.
One of them is a Scorecard.
For a scorecard, you will need to combine Label, Actual value, Annual trendline, and Variance.
Since you will probably need the extra space, you can merge the cells together by clicking on the ‘Merge cells’ button.
Next, connect the label name that is included in the ‘Data’ sheet.
Do the same with the data, and link it from the ‘Data’ sheet to the ‘Dashboard’ sheet.
Now, open the formula tab, type in an equal sign, and select the ‘Data’ sheet value. To create a trendline, you can use the yearly data and add a line chart.
There is a trick you can use to highlight the variance – open the ‘Calculation’ tab and choose a helper table.
Here, you should set up the new conditional formatting rules.
Choose a cell that includes variance and copy it. Go to the main ‘Dashboard’ sheet and select ‘Paste Special’ after right-clicking.
Then, press ‘Paste as Linked Picture’.
Here is a picture in case you get lost:
Lastly, adding dynamic texts to the main sheet is great for updating the key metrics once a change occurs.
By connecting a text to an object within the Excel dashboard, every time you change the value in the external source, the target cell will display it.
This is a great feature and you can incorporate it for different charts and textboxes.
Now the fun part!
By now, you should have all of the elements laid out, so the only thing left to do is customize the design, colors, and overall typography.
You can change the color in any part of the chart.
Simply select the specific part and choose which color you want it to be through the ‘Font’ tab.
To set the background color, you should right-click the chart and press ‘Format Chart’. Choose ‘Solid Fill’ and pick out a color you think is fitting.
In case you want to change the title, you can do so by clicking on it and selecting the font size, color, and type.
In case you were wondering how to build interactive excel dashboards, this part will interest you.
There are a couple of different methods to add interactivity.
Let’s start with a drop-down list (aka data validation).
The drop-down list can be a great method if you are a bit more experienced with Excel and know how the different formulas work (especially VLOOKUP).
By integrating drop-down lists, the audience can choose specific criteria to filter out, while the chart will automatically update based on that criteria.
The next method is Macros.
To use macros, you will need to know your way around Visual Basic (Excel’s coding language). This means that there is no manual automation involved, the macro takes care of the entire process if you write it properly.
Macros can also be used for developing dashboard buttons – once you click on them and select criteria, the charts will automatically update to reflect it.
Lastly, we want to mention Slicers.
Slicers essentially provide you with an additional layer to filter out your pivot table. You can take advantage of a variety of different visual filters and complete the filtering in just a few clicks.
Also, there are specific buttons that allow you to see which filter you are viewing and then use it to segment the data.
Once you set up a pivot table and place the slicer next to the chart, you can select some of the different buttons to see how the chart changes interactively.
Building a business dashboard Excel is a hefty process and there are dozens of different things you have to keep an eye on.
This can get very tiresome and it’s not impossible to suddenly lose track of something important.
To help you out, we have prepared a clear overview of the dos and don’ts of Excel dashboards.
Here are some of the best practices:
Now, let’s check out the don’ts:
There is no doubt that Excel can still come in handy when it comes to data organization, after all, it has been one of the most useful tools for SMB businesses for decades.
However, there are quite a few limitations to Excel, and with new project management tools constantly developing, the difference has become extremely noticeable.
Here are some of the limitations of Excel dashboards.
While you might have had the chance to see some exceptional Excel dashboards at some point, did you ever stop and wonder how long the creation process took?
Sure, the layout is clean and well-organized, and there are some impressive data sets and charts.
But what you don’t know is that the person who created that dashboard lost both his nerves and an immense amount of time during the process.
This is because Excel dashboards require too much manual data feeding.
As your company evolves, the amount of data you have to deal with immensely increases.
In most cases, an abundance of data is bound to cause human errors.
This can be anything from a simple typo to a mistake in the formula, and while the error might look insignificant, you’ll see how important it is when it changes the whole concept of your dashboard.
Integration is a big part of effective dashboards, by connecting with other apps you can easily multitask and get a lot of work done in one place.
This isn’t the case with Excel since the integration abilities are limited, to say the least.
So why even bother creating a dashboard in Excel when there are business dashboard software and tools that make the process immensely easier?
Take Databox for example.
Creating a dashboard in Databox can literally be done with just a few clicks, and something that you might spend hours on in Excel can be done in a matter of minutes.
As we said, Excel is still regarded as a useful tool when it comes to certain activities such as tracking your budget or staying on top of the sales pipeline.
But why not add some superpowers to it?
We are talking about using Databox’s dashboard software with Excel.
Naturally, you might have some doubts, but reading these benefits could help change your mind.
If you already have Excel spreadsheets to track your budget and overall finances, you can connect them to Databox to create a comprehensive dashboard for each financial goal.
Not only will you save up a lot of valuable time, but you will also gain access to some advanced visualization tools that you can use to showcase specific trends and make sure your audience understands the overall analysis.
Tracking sales pipeline performance, leads, and sales rep efficiency in Excel can only go so far.
These metrics are extremely valuable and not only to your sales team, so why not scale the way you track them if you can?
Databox sales performance reports can help your entire team stay on top of the sales metrics that matter most and make all the dry numbers a lot more understandable.
For the longest time, Excel has been one of the cornerstones when it came to managing inventory data. But while it can get the job done, it doesn’t offer a lot of flexibility.
That’s not the case with Databox.
With our management tool, you can create cross-functional inventory reports and build dashboards from multiple different sources.
Excel automated dashboards are essentially an upgraded version of static dashboards – you can use them to reflect any real-time changes as soon as they occur.
So if you were wondering how to create a dashboard in Excel that updates automatically – the answer is Databox.
Here is how you can do it.
In your Databox account, open up the Data Manager.
Press ‘New Connections’ and select Excel, you will see three different options – Google Drive, One Drive, and Dropbox.
Make sure that the Excel workbook copy you want to connect is already within one of these options and then simply select the specific option.
To create a custom metric, you’ll need to use the Excel Wizard tool.
Select ‘Metrics’ in the left corner and click on ‘Add Metric’.
Next, choose your Excel spreadsheet by clicking on ‘Browse by data source’.
This screen should appear:
Go to the box that has the ‘+’ sign below custom metrics, this will take you to the data verification process.
If everything is the way it should be, just press ‘Continue’.
Time to build your first custom metric.
Firstly, choose the value you want to measure and click ‘Continue’.
You can now customize your metric even further by adding a Dimension (e.g. Region, Industry, and Segment). This option allows you to categorize the metrics in any way you deem fit.
After selecting the date range, press ‘Continue’ to proceed to the final setup screen.
This is the place where you can set up the name of the metric, choose how the data will be showcased, pick a data range, and select the type of visualization you want.
In case you want to create the custom metric through the Databox Query Builder, you can do so by selecting the ‘Switch to Manual Setup’ button in the Wizard setup screen.
Finally, time to build the dashboard.
This step requires using the Dashboard Designer. You can drag, drop, and move any metric you want to include in the dashboard and avoid searching for the numbers you need in a sea of data.
By connecting your Excel sheets to Databox, all the numbers will be accurately showcased and you won’t need to waste time on any type of manual work.
To start the design process, go to ‘Databoards’ on the left side of the navigation bar and click ‘New Databoard’.
This is the place where you will be dropping your crucial Excel metrics.
In here:
You can set and visualize goals here like this:
Also, you can alter the data ranges and compare them to the historical ranges by clicking the arrow right next to the data range.
Databox has a wide variety of visualization options, so choosing the right one for your specific metric will be a piece of cake.
For the longest time, Excel has been one of the best tools for SMB businesses looking to build comprehensive dashboards and make sense of their data.
Even today, there are lots of companies that still integrate Excel and use it for developing different spreadsheets and dashboards.
However, when compared to modern business dashboard tools, we realize that Excel doesn’t cut it anymore.
With limitations like manual data feeding, frequent human errors, lack of integration options, and the fact that creating Excel dashboards can take hours (if not days), it’s simply not worth going through all the hassle anymore.
Luckily, it is still possible to create effortless Excel reporting dashboards – you just need a bit of help from Databox.
While Excel has the ability to manage and analyze large data sets, it doesn’t really do a remarkable job at visualizing that data.
Databox offers an effective solution to this.
By integrating your Excel spreadsheets with Databox, you can create powerful and comprehensive dashboards that your entire team can easily understand and share with other company members.
We have some of the most cutting-edge visualization tools in the industry – using our graphs and charts will make your data come to life and transform dry numbers into compelling stories.
Another benefit of this integration is that you will be able to do much more with your Excel data, which even includes creating your own custom metrics.
Currently, Excel is available only on our Professional and Performer plans and you can test out the integration during our free 15-day trial.
So what are you waiting for? Sign up here for the free trial and take your dashboard reporting in Excel to the next level.
Hey, we’re Databox.Our mission is to help businesses save time and grow faster. Click here to see our platform in action.
Are you maximizing your business potential? Stop guessing and start comparing with companies like yours.
At Databox, we’re obsessed with helping companies more easily monitor, analyze, and report their results. Whether it’s the resources we put into building and maintaining integrations with 100+ popular marketing tools, enabling customizability of charts, dashboards, and reports, or building functionality to make analysis, benchmarking, and forecasting easier, we’re constantly trying to find ways to help our customers save time and deliver better results.
Filip Stojanovic is a content writer who studies Business and Political Sciences. Also, I am a huge tennis enthusiast. Although my dream is to win a Grand Slam, working as a content writer is also interesting.
Get practical strategies that drive consistent growth