Analytics

Create an Excel Dashboard from Scratch in 8 Steps (or Just 3 with Databox)

Want to learn how to create a comprehensive Excel dashboard? This guide will teach you everything you need to know.

Filip Stojanovic Filip Stojanovic on June 7, 2022 (last modified on November 9, 2022) • 21 minute read

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.

setup-free-dashboard-databox-dsss

What is an Excel Dashboard?

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.

4 Considerations to Have in Mind before Building an Excel Dashboard

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.

The Purpose of the 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.

The Most Important KPIs

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.

The Audience

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

The Format

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.

How to Create an Excel Dashboard from Scratch? 8 Steps

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.

  1. Create a Proper Layout for Your Excel Dashboard
  2. Import Data into Excel
  3. Create a Table with Raw Data
  4. Clean and Filter the Data
  5. Analyze Your Data
  6. Build the Excel Dashboard
  7. Create a Dashboard Scorecard
  8. Customize with Macros, Colors, and More

1. Create a Proper Layout for Your Excel Dashboard

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:

  • Data – Used for storing tables of raw data
  • Dashboard tab – This is the sheet that includes the main dashboard. Most commonly, it’s a single-page view that displays data-based insights.
  • Calculation – The worksheet used for making calculations.
Excel dashboard tab

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:

Excel dashboard layout example

2. Import Data into Excel

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.

How to import data into Excel

In this picture, we chose the Text/CSV option to create the starting dataset.

3. Create a Table with Raw Data

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.

4. Clean and Filter the Data

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:

  • Filter the data by separating ‘Extremes’ and ‘Peaks’
  • Make sure there aren’t any duplicates
  • Remove data that you think is irrelevant
  • Use lower, upper, and proper case text
  • Eliminate the leading and trailing spaces

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’.

How to filter data in Excel
Source: Excelkid.com

5. Analyze Your Data

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:

  • Pivot tables
  • Data validation
  • Auto-shapes
  • Charts
  • Conditional formatting
  • Macros

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.

6. Build the Excel Dashboard

The final stop – building the Excel dashboard!

Let’s go through the features you’ll need.

How to Add a Gantt Chart

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.

How to Add a Gantt Chart in Excel

Lastly, connect the bar chart to columns like Project Name, Start Date, and Duration.

This is what a Gantt chart looks like:

How to Create and Format Charts

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.

Creating a column chart in excel

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’.

How to properly format the X-axis in Excel
Source: Excelkid.com

For formatting, you should click on the ‘Design’ tab.

Here you can add a title by following Add Chart Element > Chart Title > Above Chart.

Excel design tab

That’s it! Here is an example of what the chart should look like:

How to Insert Pivot Tables

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’.

How to insert Pivot tables in Excel- step 1

Click the last icon in the ‘Table/Range’ field and select the entire data table.

How to insert Pivot tables in Excel- step 2

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.

How to insert Pivot tables in Excel- step 3

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.

7. Create a Dashboard Scorecard

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.

How to create a dashboard scorecard in Excel
Source: Excelkid.com

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:

Creating a scorecard in Excel

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.

8. Customize with Macros, Colors, and More

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.

How to Customize Chart Colors and Font

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.

How to Customize Chart Colors and Font

How to Create a Dynamic Excel Chart

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.

Example of a dynamic chart in excel
Source: Excelkid.com

Excel Dashboards Do’s and Don’ts

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:

  • Keep things simple – Remember, keeping your dashboard simple and understandable is much more important than making it look ‘nice’. The 3D effects, additional shapes, or any other over-the-top elements might seem tempting, but they will often have a counter-effect on the readers.
  • Use alerts for important data – Alerts are a great way to highlight specific parts of the dashboard and remind your readers of the most important information.
  • Add different tabs – To organize your dashboard, we recommend using different tabs for showcasing different things.
  • Freeze panes are useful – For larger excel data dashboards, you can use freeze panes to make navigation easier. This feature basically freezes the rows so the audience can see the entire dashboard in one view, without having to scroll through it.

Now, let’s check out the don’ts:

  • Going overboard with colors – While you might think that a wide range of colors will impress the readers, you should be careful not to go overboard with it. A dashboard should include mostly ‘neutral’ colors so the focus stays on the information, bright colors should only be used when you want to highlight something really important.
  • Including too much data – We already mentioned this at the beginning, you should filter out the most important data and avoid including unnecessary information in the dashboard. By providing your readers with a crowded dashboard, you will only end up confusing them, which means you won’t be able to convey your main message.
setup-free-dashboard-databox-dsss

Excel Dashboard Limitations

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.

Manual Data Feeding

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.

Human Errors Are Common

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.

Limited Integrations

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.

Benefits of Using Excel with Databox

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.

Financial Reporting

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.

Sales Performance 

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.

Inventory Management

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.

How to Create a Dashboard in Excel that Updates Automatically? Use Databox

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.

Import your Excel workbook into Databox 

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.

Import your Excel workbook into Databox

Create custom metrics 

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:

Create custom metrics 1

Go to the box that has the ‘+’ sign below custom metrics, this will take you to the data verification process.

Create custom metrics 2

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’.

Create custom metrics  3

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.

Adding finishing touches

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.

Build a dashboard 

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.

How to build an Excel dashboard in Databox

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.

setup-free-dashboard-databox-dsss

Create Effortless Excel Reporting Dashboards with Databox

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.  

About the author
Filip Stojanovic
Filip Stojanovic 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.
You may also like...
Read more

How to Benchmark Your Website’s Performance: A Detailed Metrics Breakdown

A full guide on the most important metrics you should benchmark to understand how your website’s performance measures up with the competition.

Analytics   |  Nov 16

Read more

The 16 Most Useful Google Analytics Reports (Recommended By Experts)

With so many metrics to track and dimensions to filter them by, what are the most useful reports to run in Google Analytics? Nearly 50 marketers weigh in.

Analytics   |  Nov 14

Read more

Google Analytics 4 Custom Dimensions 101 Guide

Having trouble learning the ropes around custom dimensions in GA4? This article will walk you through everything you need to know.

Analytics   |  Nov 10