Learn how to create a dashboard in Excel from the ground up. Our free training might be just what you need! We will show you, how to build from a clean white spreadsheet to an efficient report. We’ll give you a complete dashboard framework using Microsoft Excel. Have no time for a deep dive into the details? Jump to the download section! Shake usual tutorials up a little bit!
Above all, we will share all tips, tricks, and secrets. This free tutorial is perfect for basic and advanced users. Furthermore, we will answer the most frequently asked questions. We not only going to learn about dashboards but will detail every relevant step that connects with the main workflow. You’ll understand the anatomy of a dashboard quickly.
Not everyone is born an Excel genius. We weren’t born like that either. We are also not geniuses. However, we have learned a lot over the years. For that reason, we’ve created this step-by-step tutorial. From now everyone can learn the basics of dashboards. Of course, we can feel fortunate because we do the things we like.
Learn and play with these downloadable dashboards! You will get eye-catching solutions. It’s time to build something new, something special. That’s the point.
In this in-depth article we’ll help you to deepen their knowledge in Excel.
What is a Dashboard?
What are dashboards? For sure that they do not only represent visual data! When you look at a dashboard, it is evident that you not only interested in real-time performances.
However, dashboards provide information that poses our questions. How are things going? How will you explain to your boss the causes of increased profit? A well-structured dashboard will give suitable answers to these key questions, and much more!
Practically our goal is to clean, organize, manage and display the large data collected by the company. Further standpoint is to show the classified data in an easily understandable format. Under the hood, there are complex data. You can dig further down in them by a drill-down method. It happens quickly!
Probably we can call the dashboard the central brain or engine of the company or department. Showing key performance indicators in real time is necessary. We have to keep them always up to date. We don’t have time to look for reasons for weaker of exceptional results for long days.
General rules and guidelines for effective Dashboard design
Before you start to create an excel dashboard one of the most critical tasks is dashboard design. It’s not as easy as we might think. We want to help you with the planning phase also. Finally, here are the ground rules. Keep in your mind these dashboard fundamentals!
Start with users, not the data. The best way to start is to understand the objectives and motivations of end-users. If you can realize this for sure, you will create a dashboard in Excel that is most useful for them.
What is this all mean in practice? Go and talk to the users! Keep calm! Sit down. Try to understand the scope. Create a dashboard that is not in constant need of updates! Because of this, you can dramatically cut development costs.
Don’t flood the user with unwanted information. You should seek that the dashboard is useful for them. You can even create such custom views. Custom views and filtering data enable to display relevant information only for that user.
Provide an overview and allow users to check the details. A well thought out, and well-planned dashboard should be like a quality newspaper. On the front page provides a clear overview of the key information and leading news there are within. If one wants to look at information in detail must know where to navigate. Allow users to see the essential information. Drill-down into the details if they’re going to find out more!
Use visualizations and create a clean, uncluttered dashboard. Charting possibilities in Excel are almost endless. That’s where data visualization comes into play. Use the traditional chart types if you want, however, based on our experiences most useful to use a gauge, bullet, and variance charts also.
Think about what is going to be most impressive at telling our story. Dynamic charts are better than pie charts. Avoid over the top visualizations and graphics. Exploding pie charts and stacked bar charts are bad ideas too. Remove unnecessary components from the dashboard. They distract the attention from the essence.
Create a clean structure and easy navigation. Create a detailed and well thought out menu structure. Your goal is that the users be able to orientate easily about the information most relevant to them. There are several Excel tricks exist for making professional menus. Try to organize the indicators into the appropriate hierarchy. So, the users will always know which level they are on in the structure.
Provide help and guidance where necessary. Most ideal if the dashboard doesn’t need any additional explanation. However, what if a new colleague arrives at the company and right away has to overtake the tasks of an experienced business analyst. What if you seldom use an indicator?
You will see that in these and like cases contextual help is helpful. We’ll show you the logical link between contextual help and dashboard in our step-by-step tutorial.
Plan the Dashboard layout and create proper draft
It would be best if you never started building a dashboard without planning. If you make mistakes in the planning phase, you can only correct them with disproportional effort later. Your dashboard and indicators are useless if it doesn’t get appropriately communicated.
You have many possibilities to create a proper layout. Some like to plan just in Word or PowerPoint. Some prefer the classical methods, takes out a sheet of paper and sketch the ideas.
We are using Excel to create a ready-to-use chart and form control elements with shapes. It’s easy to create the initial layout design of your report or dashboard. Now you get to see how your draft version could potentially look in an Excel spreadsheet. It helps you organize each section. You can decide on what types of dashboard elements (charts, form controls) you’ll need in the planning phase.
Here is the layout of our dashboard:
Good to know about shapes that they are flexible and reusable. You can change the color of the treemap chart for example. Furthermore, you can freely customize the text in the titles and change the background color of the boxes.
Dashboard design helps you customize and fine-tune the color and titles to your requirements. So, you can come up with a proper draft before you start with implementation.
We prepared the layout using chart and form control mockups. Now the dashboard layout is ready. We’ll step forward.
Connect to a CSV file using Power Query – External Data Sources
The most common case is that you don’t store to be processed data in Excel. You can import it from an external data source. Let’s see an example of data import in Excel. Use Excel’s Get & Transform function (with the help of Power Query) to connect to a comma separated values (.csv) file.
Using external data sources seems to be a rational decision when raw data is not stored in Excel.
Using Power Query editor, we can get and transform our data easily. Our data is stored in a CSV file. To import data from a file, do the following steps.
Click on the Data tab on the ribbon, choose ‘Get Data’ > ‘From Text / CSV’ > ‘From File’ > ‘From Text/CSV’
In the Import Data dialog box, locate the file that you want to query. Click Import.
If you are working with a CSV file, Power Query will automatically detect column delimiters. Power Query automatically uses the first row as the column names and changes each column data type.
If you want to edit your data ‘on the fly’ click to edit. A Power Query Editor will appear.
Finally, click Close and Load. Excel puts the external data range in the location that you specify. Our data transformed into a table. Why it’s so important? We’ll answer this question in the next chapter.
Convert tabular format into excel table – Internal Data Sources
In this phase, you don’t have cleaned input, but you already have data on a worksheet. What will be the next step?
First, you must check if the necessary information is in a tabular format. Tabular format means that every data, data point lives in one cell, for example, the name of the city, address or phone number. If the data is in a tabular format, you should convert it into an Excel table. Using Excel tables provides us with many advantages.
Use Excel Table to avoid trouble. For example, if the scenario changes further down the line and you need more data than you can only expand the table and will not lose the formulas. A very useful tool indeed to use!
To convert tabular data into an Excel table, follow these simple steps: Select the data then on the Insert Tab choose Table or press Ctrl + T.
If our data table has headers we need to check the box ‘My Table has headers.’ In case your data doesn’t have headers, Excel will automatically insert headers into the first row.
Explanation: What kind of benefits do you get by using an Excel table when creating a dashboard? Instead of the cell references for example ‘A1: B340’, use the names, in this case. It is a simpler and more transparent to refer to a range like ‘Products’ instead of writing in the long cell reference. When we convert our tabular data into Excel Table, the data will look as shown below. Data filters are enabled on the header.
Prepare, Sort, Consolidate and Clean Raw Data
After the data is in Excel, you can start the data cleansing process. There are many awesome cleansing tricks, although we often forget about this obvious method. Some effective ways to clean data in Excel:
- Sorting data to see extremes and peaks
- Remove duplicates
- Use the FIND() and REPLACE() functions
- Change Text to Lower – Upper – Proper Case
- Remove unnecessary characters in Excel
- Check the Type of Data in a Cell
- Convert Numbers Stored as Text into Numbers
- Remove extra spaces and eliminate blank cells
- Split text into columns
- Use the CONCATENATE() function
We recommend to you our free excel add-in for cleaning and sorting raw data. The time-consuming procedure will be faster and easier.
If the data is on more than one Excel workbook or worksheet than we perform consolidation.
Create the ideal Workbook and Worksheet structure
How can you create a consistent workbook structure? What is this mean exactly? Open a workbook and create three sheets. Let us see the parts of the workbook structure:
The first one is the dashboard. The worksheet contains the final data visualization. The name of the second one is data. Also, the third one is called calculation. This partition seems logical. From the raw data throughout Excel formulas and functions, you reach the result, your dashboard.
How to make the Dashboard interactive?
If you wish to make a dynamic and user-friendly dashboard, there is a spectacular solution at your disposal. In this chapter, we’ll show you the tools we very often use.
We’ll use Excel Worksheet Form Controls for a specific task. Excel provides us with many possibilities to make a user interface. Add and use the Developer tab on the ribbon!
There are many elements of the drop-down menu. You can choose from the list. Using Form Controls, you make your own dashboards interactive.
In this example, we’ll use the drop-down list.
Tip: Here is the complete list of Form Controls that are available in Excel:
- Combo Box
- Check Boxes
- Spin Button
- List Box
- Group Box.
Analyze data and build the dashboard
We took you through the procedure that converts unprocessed data into a data structure capable of creating a dashboard.
Ask yourself the following questions: Do you have to display all the data at once? Is it necessary to remove some data? From now on you can use Excel formulas and various methods that help us move forward.
This list at first seems scary and long. If you look through the additional articles of the blog related to dashboards, everything will be much easier. To make a good dashboard, you rather need creativity than the knowledge of all the formulas.
We’ll use these functions and tools to build the dashboard in Excel:
- NAME MANAGER
How to create an Excel Dashboard – Step by step Tutorial
Our goal is to provide an overview of a company’s annual sales trend using interactive visual controls in Excel. In this tutorial, we will show you how to create an interactive dashboard step by step. We’ll use form controls and formulas. Dynamic chart enables us to measure sales performance by months during a given period.
We’ll use flat UI colors for a dashboard. If we implement the style correctly, then the result will be easily understandable than if we prefer our favorite color schemes. Grey and white are the most boring colors, so we should use them as background colors. Everyone will focus on the key numbers.
The result looks like:
We separate the dashboard layout into three parts. As always, we prefer the grid layout!
The left section of the dashboard shows the monthly sales performance. The line chart provided a review of sales achievements in a monthly breakdown of one year. It’s important to show the minimum and maximum values on the line chart and mark them using callouts.
The top-right area uses a drop-down list to display the results of the chosen period.
The bottom-right part is a comparison area for the top and bottom customers.
Now let us see how to build the dashboard in Excel!
Step 1: The initial sales data set looks as shown below. Insert a new column and apply the EOMONTH() function. For example, we can convert the ’01-May-2017’ string to ‘May-2017’ without any trouble.
Step 2: In general, named ranges make your life easier as a developer. Select the cell or range! In this dashboard example, we will use both. Go to the Formulas tab! Select the Defined Names group, click Define Name. Enter a Name (AllSales) for the range. Let us see the results in the picture below!
Create further named ranges for the necessary calculations! Here is the list of the names:
Step 3: Create a custom combination chart to display the sales trend and highlight the highest and lowest value! We’ll plot the sales chart based on column ‘I ‘(sales). Use NA() formula as the second parameter of IF() formula.
In this example, we need to create an #N/A for the unnecessary cells. It’s a nice trick if we can show only one value of the series. The high and low series contains only a single value. So we’ll display only the maximum and minimum values on the line chart.
Select the data source, range “H4: K14.” Add the date column for the horizontal axis labels and use the sales, high and low column for the legend entries. Choose the chart type and axis for your data series. Select “Line with markers” for the highlighted data points and use a line chart for the main sales trend. Click OK.
Step 4: Format the data points: we’ll highlight the highest data point using green, and we’ll apply red for the lowest point of the line chart. On the Format Data Series pane choose a marker style and select the fill color. Repeat these steps for the lowest data point too.
We’ll apply data callouts to display the highest and lowest sales values in the given period. Right click on the data point then ‘Add Data Labels’ and ‘Callouts.’
Remove the unnecessary elements from the chart; title, legends, border, and background.
Step 5: Insert a drop-down list to select months from a menu; click Data Validation on the Data Tab. A new window appears, select the Allow box, then choose List.
Click in the Source box and type “=ListMonths.” It is a named range on the calc Sheet, and it contains the name of months.
Step 6: Design the top-bottom comparison area! We’ll create progress doughnut charts to measure the individual sales performance / monthly sales ratio to a goal. We love this chart type because it’s very easy to understand.
This the best chart type if we want to display a progress bar with the percentage of completion on a target. We need only two cells and one simple formula to create a progress doughnut chart.
Insert a new doughnut chart first. On the calculation sheet in the cell P8 type the following formula “=MaxValSold / MonthSales.”
In the cell P9 (reminder value) enter “=100-P8.”
Select the range “P8: P9” and insert a doughnut chart.
Move the chart to the main dashboard sheet and fill the ‘complete’ section using green and apply a dark blue color for the reminder. Repeat these steps! Remove the chart title, legends, and the fill color of the background. Change the doughnut hole size if you want.
Step 7: Add a Text Box for the Percentage Value. Insert a text box and move it into the center of the doughnut chart. Select the text box and click in the Formula bar.
Now assign a linked cell or a range. We’ll increase the font size to keep these numbers on focus.
Step 8: You can improve the dashboard’s visual quality using icons. Choose the Insert tab on the ribbon. You can find the Icons section in the Illustrations group. This feature available in subscription versions of Office 365.
Step 9: Choose your preferred icon and put it into the worksheet. There’s a sorted gallery available. Furthermore, they’re grouped into types to make them easily searchable. Because we’ll use an icon in the background, you should to decrease the transparency and send it to back.
Finally, our dashboard is ready, but we must now move forward.
There are several ways in Excel to provide extra support to developers and users. Contextual help is a space-saving solution. However, it’s not just about more free space on the dashboard.
It’s also supporting targeted help to users within the context of what they’re working on.
Save the workbook as ‘xlsm’ format. Our dashboard is ready to use!
Frequently Asked Questions regarding Dashboards
Before you start creating the dashboard using Excel, you have to contemplate on a few things. Like, who will use it? Where will data come from? What will we need exactly? Let’s examine these core premises in detail!
What is the reason you creating the dashboard? Maybe one of the business procedures have a weakness? Would you like to prove or disprove a hypothesis? Is it going to serve a short term or long-term projects? A well-made dashboard will provide the answer to all the questions!
Who is the dashboard for? A co-worker, a branch manager, an IT manager or a stakeholder has a different need for information. The result must be useful for all levels. Let us think this through carefully.
What kind of data will you work with? The most commonly used types are an integer, real, categorical, time-series, geospatial. Let it be any of these we must clean it.
What would you like to communicate with your dashboard? Would we want to search the effectiveness between two procedures and portray that?
Would we like to compare the costs of the two projects? Alternatively, would we want to know what the breakdown cost is between two branches?
How many KPIs represent the company’s efficiency comprehensively? Focus on business goals! Only show KPIs that represent values. With a preliminary survey, you have to ascertain these. In most cases, you don’t have resources of money, HR capacity and time to deal with indicators that are not relevant.
What kind of data connectors shall we use? You have to know already in the planning phase what type of tool we’ll use to import data into the dashboard. If you work in Excel, the best solution is the Power Query and Power Bl. These tools are perfect for handling millions of rows in a blink of an eye. However, you can use the standard ODBC connector and the SQL DB.
Are there compatibility issues within the company? IT professionals have to assure that everyone involved uses the same version of Excel. If you build this into the planning phase, you can avoid a lot of frustrating problems later.
How often does your dashboard need to be updated? Should you make decisions based on real-time information? Is it enough in the regular daily, weekly, monthly, quarterly or yearly breakdown? You can create the initial structure of the dashboard based on the answer.
What format do you publish the dashboard? Do you send flat Excel tables to the users or maybe you put the result on SharePoint? Perhaps you need to embed some charts into a PowerPoint presentation? You have to consider access issues also. Accessibility levels are different for a manager and the owner.
May I use a multi-page dashboard? In this case, you should create easy navigation to your dashboard. Insert shape based buttons and links to keep the structure.
Differences between Dashboards and Reports
The biggest mistake we can make is to use report and dashboard as the synonyms of each other! No, they are not at all alike! It is time to clear up the differences. A report is primarily a text or table-based tool that supports the work of co-workers within an organization or a company. Seldom contains visual elements. We make it randomly, but usually, we distribute them by regular scheduling (daily, weekly, or monthly).
The report can be a more pages layout of the task makes it necessary. In summary, the report comprised of background data. Dashboards are the opposite of reports. Its primary objective is to portray business information on one screen that is crucial for making important decisions. It is not to introduce details.
If we want to know where the data comes from we can find out from the reports. The correctly chosen key performance indicators easily decidable if things are on the right course or not. So, which one should I choose and when?
The ideal case is when we have a dashboard that only shows the essentials. If we would like to get into the details and would like to look behind the scenes than the perfect tool is the reports. Based on a dashboard a business decision can be made immediately, while the report supplies the background information.
If we would like to utilize both to the max, we recommend creating and publishing them in pairs. Whoever wants to see the essence, looks at the dashboard, and is one wants to know the source of the data can read through the longer reports.
Dashboard toolbox for interactivity
Let us see how to make your excel dashboard more user-friendly and interactive!
Drop-down list is a space-saving solution that is a great value when we create one-page dashboards in Excel.
Conditional formatting is the best choice if you want to highlight cells based on any condition or rule. Of course, you can use other methods besides colors. You can achieve spectacular results using icons, bars, shapes, color scales, indicators, and ratings.
Free excel heat maps help us to visualize geographical information. If you need a risk map or a product comparison tool, we recommend using a treemap chart.
Create a hyperlink rollover effect for your dashboard! we need a simple macro to change the series based on where the user pointed the mouse. The hyperlink cells have a formula which uses hyperlink and the rollover action built-in macro.
Conditionally formatted dynamic shapes can improve the visual quality of your dashboard!
Data Entry using userform and VBA: Manually input data always carries the possibility of error. Instead, use userform and write a short macro for it! With some VBA support, we can create a user-friendly panel that is easy to customize.
Pivot tables are the most potent weapon in Excel when we are working with large data sets. It is easy to use with only a few clicks we can summarize data, and we can view the report in any chosen structure.
Data Validation: We might want to restrict what users can write in a 1-1 cell. Why is this good for us? Just imagine that ten users in 10 different workbooks write phone numbers. If we do not restrict the format of the phone numbers with the help of data validation when summarizing the workbooks, there might be mistakes. For example, we found not allowed characters that we have to correct manually. If we set a rule that users must use that restricted format. Otherwise, they get an error message.
Named ranges: We like this a lot for in a matter of seconds we can call a range with any given name. Just highlight a data range, and in the name box we write the chosen name: for example: ‘sales’. Form this point we can manage the formulas efficiently.
Dynamic Charts: These days to use dynamic charts is essential if we want to create interactive charts to refresh upon the user’s choice use this solution automatically.
Shapes: With the use of vector objects we can create unique buttons and menus. Most often applied when navigating between worksheets. We must not forget that functionality is first when creating a dashboard in Excel!
Dashboard Widgets: We can call them also individual charts but more exact the chart combination term. Make your dashboard unique!
How to choose the right chart type for your dashboard?
Let’s see some useful tip regarding the charts. In the last years excel charting has been radically changed. For example, the column chart is not able to visualize complex information. We will examine what kind of graphs we should use in the function of a given project.
Compare Values: Their mutual characteristic is that they merely show high or low values. Recommended types for excel charting are: column, mekko, bar, line and bullet charts. Like data visualization? Check how the radial bar chart work.
Composition: How can we portray the different sales achievements in different regions? The most appropriate charts are Pie, Stacked Bar, Mekko, Stacked Column, Area and Waterfall charts.
Analyzing Trends: If we would like to portray the achievement of an examined data set (for example product) in a given period than using the following charts: Line, Dual-Axis Line, and Column.
Relationships between variables: With the use of Plot, Bubble and Line charts we can show how one variable relates to the others.
Sales Process Tracking: Need to track the sales process or the conversion rate? We recommend using the sales funnel chart.
Show the differences between budget and actual values: the best choice using the variance charts.
Performance measurement and KPI tracking: Use gauge charts if you want to see how far you are from reaching a goal. It displays a single value.
Dashboard Tools for Excel – Free Chart Add-in
We have great news! Install and check our add-in to create the famous chart in seconds.
Free dashboard software can help you track the right data and build your first KPI Dashboard using Excel.
Download the free add-in!
When more power is needed, or you are in a hurry check our data visualization tools for Excel.
Download Free Dashboards
In this chapter, we introduce the frequently used excel dashboard templates! Check our free examples!
First, we have to say a few words about this article for it is a little bit different from the usual tutorial. We don’t only talk about dashboards theoretically, but we also create them. Also, they are free for you to download.
Let’s see a little selection of the best ones! Scroll through the dashboard examples listed below and choose a template to download for free. All dashboards are compatible with Microsoft Excel 2010, Excel 2013 and Excel 2016 / Office 365.
Call Center Dashboard
Reduce the time to answer and abandon rate and increase the first call resolution. Using this dashboard, you can analyze your weekly call center metrics. For example branch performance, time to answer and first call resolution. Our business spreadsheet allows you to compile complex data at various levels. From overall call center performance to departments and individual agents.
When we talk about sales than we can approach the task from a lot of different directions. The primary objective is that we have to track all the activities and procedures that can influence sales. We can make simple forecasts with the help of sales-forecast charts. Use more dynamic dashboards that demonstrate the development of key performance indicators.
Keep an eye on the operation of the HR department to gain an advantage by early recognizing surfacing problems. You can keep their impacts on the business on a healthy level. Highly emphasized are the following: hiring and onboarding, employee programs and expenditure.
So, we can keep their impacts on the business on a healthy status. Let us see the main measures: Salary, Bonus, Overtime and Sick Days.
Customer Service Dashboard
We like this topic! Using our spreadsheets, you can analyze the main performance indicators of customer service. Measuring customer satisfaction, call duration or resolved calls? No problem!
Dynamic VBA Dashboard
What is VBA good for? How can we construct a user-friendly interface with it that increase user experience? Dynamic charts make users life more comfortable. We will show you the essential things!
Would you like to understand how to create a dynamic chart? It is a vital component of the dashboard. Insert two command buttons to create the start and stop controls of the animation. Use a simple macro and assign them to two Command buttons. The static chart will be dynamic!
SEO Analytics Dashboard
Every website owner feels that it is essential to get an overview of the operation of the site based on different indicators. Is it possible to display the KPIs and trends on a single dashboard? Absolutely.
Clap your eyes on the SEO analytics! You can track the number of unique visitors, new visits, bounce rate and more with an SEO analytics dashboard.
Project Management Dashboard
Critical tasks for project managers: project tracking, monitoring risk, shaping a work breakdown structure. Other essential elements: issue tracking and Gantt chart. Probably we already have the framework! There are many diverse tools at our disposal to support this goal. In Excel, we have modeled a software development project.
Below you can find some free project management dashboards.
Data is useless without the ability to visualize it! Let us see our answer from the general question. I’m an Excel newbie: how to develop an Excel dashboard?
First and foremost for you is to learn. If you already feel comfortable in the fields of basic formulas and form controls that have a go at it.
Are you sure of the basics? Try to learn the more advanced formulas. You can make you dashboard dynamic! Above all, we recommend that you go step by step and success will follow.
Simplicity is the ultimate sophistication. We hope that you enjoyed our article. We’ll update our dashboards frequently!