Learn how to create a dashboard in Excel from the ground up. This step-by-step tutorial might be just what you need! Above all, we will show you how to build from a clean white spreadsheet to a dynamic dashboard.
It’s not a usual tutorial. You not only going to learn about dashboards but will detail every step that connects with the main workflow. You’ll get free dashboard tools, training materials, and a complete dashboard framework in Microsoft Excel!
Okay. Now you have to decide how to go on. A question of time. This is an in-depth tutorial: we are really happy if you read on. But if you are in a hurry get ready to use excel dashboards now!
We’ll show you how to import and filter data, prepare and clean data, and how to choose the right chart for your dashboard. Spot the differences between dashboards and reports! You’ll get useful tips about how to build and modify your charts as well. Finally, you’ll learn how to improve your Excel dashboard.
An easy learning curve, isn’t it? First of all, creating a dashboard in Excel is not rocket science.
- What is an Excel dashboard? Differences from Reports
- How to create an Excel Dashboard
- Best practices for creating visually effective Excel Dashboards
- Excel Dashboards Do’s and Don’ts
- Different Kinds of Dashboards – Examples
What is an Excel Dashboard? Differences from Reports
As first, you should know what an Excel Dashboard is.
Excel dashboard is a useful decision-making tool that contains graphs, charts, tables, and other visually-enhanced features. A dashboard provides interactive form controls, dynamic charts, and widgets to quickly summarize data and show KPIs.
It is time to clear up the differences between dashboards and reports.
- The report can be a more pages layout of the task that makes it necessary. In summary, the report comprised of background data. Above all, a report is a text or table-based tool. It supports the work of employees within an organization or a company. Seldom contains visual parts. Usually, you share them by regular scheduling (daily, weekly, or monthly).
- Dashboards are the opposite of reports. Its main goal is to display the key performance indicators on one-page that is crucial for making important decisions. It is not to show details by default, but in some cases, you are using the drill-down method. All dashboards should answer a question.
The biggest mistake you can make is to use reports and dashboards as the synonyms of each other! No, they are not at all alike!
Which one should I choose? If you want to know where the data comes from, you can find out from the reports. The correctly chosen KPI easily decidable if things are on the right course or not. So, which one should you choose and when?
The ideal case is when you have a dashboard that only shows the essentials. If you want to get into the details and look behind the scenes, reports are yours. Based on a dashboard, a decision can be made now, while the report supplies the background information.
If you 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 if one wants to know the source of the data can read through the longer reports.
How to create an Excel Dashboard?
Before you start creating dashboards, keep in mind your main objective.
Focus on the essence!
What does it mean in practice? If you build a dashboard to track HR activities, your goal is to show the monthly data on your main charts. Build a scorecard to compare the selected month and the past month, too.
Tip: if you are creating a dashboard to track the Turnover rate, your goal is to show how many employees leaving a company in the given period.
Before building an Excel Dashboard: Questions and Guidelines
Before you take a deep dive, wait a moment, and spend time on planning and researching phase. Let’s see a few questions to ask yourself before you start building your dashboard.
The goal is to show the main metrics using a large data collected by the company. Furthermore, we have to provide the data in a clear format. You have to aim for a few things. Like, who will use it? Where will data come from? What will we need exactly? Let’s check these core premises in detail!
Maybe one of the business procedures have a weakness? Would you like to prove or disprove a hypothesis? Decide what you want to show: a short term or long-term projects? A good dashboard will serve the answer to all the questions!
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.
The most used types are integer, real, categorical, time-series, geospatial. Let it be any of these you must clean it.
Would you want to search the relationship between two procedures and show that? Or would you want to know what the breakdown cost is between two branches?
Focus on business goals! Only show KPIs that represent values. With a 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.
Everybody knows that data is useless without the ability to visualize it.
Let us see the basics! The core of every Excel dashboard is one-page-layout. Why? Keep it in mind: a CEO doesn’t always interested in the details.
Create a layout for your dashboard
The planning phase does not substitute an exact blueprint but complements it. The wireframe will not describe how the framework should work!
What is a dashboard layout? A dashboard layout describes the final design for the given department. Build a wireframe before you invest time and money to finalize the dashboard. It is a lot less spent time and cost to create a layout than rebuild the structure after it is already completed.
You can use paper and pencil, but we prefer Microsoft Excel to create mockups. You should know about the mockups that they have simple, grouped shapes.
Tip: Let us review together with the effect of the dashboard UI prototyping. Before we start working with shapes, we should know what kind of result we want to see at the end. What kind of requirements the final presentation has to answer? In the figure below, we are showing a layout.
How can you create a logical workbook structure? What is this mean? Open an Excel workbook and create three sheets.
Let us see the parts of the workbook structure: Mostly, you use three worksheets for dashboards.
- Data: you can store the raw data tables here
- Dashboard: the main Excel dashboard Worksheet
- Calculation: make the calculations on this Worksheet
After the department has decided on the type of grid dashboard layout they will use, then the color scheme and font type can be assigned to the report. Make a template that contains the following style, color codes, and font types. You can prevent most of the issues if you work with structured data and data tables.
Your wireframe and structure are ready. Let’s start creating the Excel dashboard!
7 steps to create a dashboard in Excel:
- Add data to the ‘Data’ sheet.
- Calculate the variance between the selected month and the previous month.
- Link the data from the ‘Data’ sheet to the ‘Dashboard’ sheet.
- Create a line chart or sparklines for displaying trends.
- Link the calculated values from the ‘Calculation’ sheet to the ‘Dashboard’ sheet.
- Choose a great looking chart to show your KPIs.
- Improve the dashboard!
Get your data into Excel
To create a dashboard, you need to choose data sources. If the data present in Excel, you are lucky, and you can jump to the next step. If not, you have to use external data sources.
Go to the Data tab and pick one of the import options. It’s easy to import data into an Excel workbook. In the example, you are using a CSV file to create the initial dataset for our dashboard.
Clean Raw Data
Okay, our raw data is in Excel. Now you can start the data cleansing process. There are many tricks to clean and consolidate data.
- Sort data to see extremes and peaks
- Remove duplicates to avoid errors
- Change the text to lower, upper or proper case
- Remove leading and trailing spaces
How to remove leading and trailing spaces from raw data? Go to the Formula bar and apply the TRIM function. Now copy the formula down. If you are using data cleansing tools, the step will be faster and easier.
Tip: Apply simple sorting in Excel to find errors! Sorting just makes life easier. Just sort the data growing or decreasing order. Why? If you are using sorted data, you’ll find the peaks in a range (highest and lowest data). To do that right-click on the first cell and select the ‘Sort Largest to Smallest’ option from the context menu.
Use an Excel Table and Filter Data
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 required information is in a tabular format. The 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 it is in a tabular format, you should convert it into an Excel table.
If you want to convert tabular data into an Excel table, select the range which contains data.
On the Insert Tab, choose Table or press Ctrl + T.
In this case, we doesn’t have headers, Excel will automatically insert headers into the first row. Use Excel Table to avoid trouble. If you need more data than you can only expand the table and will not lose the formulas. A very useful tool indeed to use!
Analyze, Organize, Validate and Audit your data
You took you through the method that converts raw data into a structure capable of creating a dashboard.
- 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. To make a useful dashboard, you rather need creativity than the knowledge of all the formulas. You’ll use these functions and tools to build the Dashboard in Excel: IF, SUMIF, COUNTIF, ROW, NAME MANAGER.
Sometimes nothing works well in Excel; you are facing missing data points on the charts and unknown formula errors. A little bit scary situation! How to figure out what went wrong? Do not panic! Excel grants useful auditing tools that can help you find and fix Workbook or Worksheet issues.
Use Microsoft Excel Inquiry to get a visual model of which cells in your Worksheet contribute to a formula error. This step should cut down the time spent on the usual validation procedures.
The Excel Workbook Analysis function creates an interactive report with a single click. You’ll get a detailed overview of the workbook and its structure, include formulas, cells, ranges, and warnings. Find the errors, fix them, finally recheck the given Worksheet.
So, before we start creating a chart, you have to validate the data.
Choose the right chart type for your Excel dashboard
Now you have an organized, cleaned, and error-free data set, it’s time to choose the right chart.
Strike a balance between great looking dashboard and function.
Pick the right chart, it is critical.
Let’s see some useful charting tips! You can choose what kind of graphs is the best for different goals.
- Compare Values: Their characteristic is that they merely show high or low values. Recommended types for charting are a column, mekko, bar, line, and bullet charts. Furthermore, check how the radial bar chart work.
- Composition: How can you display different sales achievements in different regions? The most appropriate charts are Pie, Stacked Bar, Mekko, Stacked Column chart, Area, and Waterfall charts.
- Analyzing Trends: To analyze the result of an examined data set in a given period, use the following charts: Line, Dual-Axis Line, and Column charts.
- Relationships between variables: use Plot, Bubble, and Line charts.
- Sales Process Tracking: Need to track the sales process or the conversion rate? Use the sales funnel chart.
- Show the differences between budget and actual values: use variance charts.
- Performance measurement: Use gauge charts if you want to see how far you are from reaching a goal. It displays a single value.
- Use traffic signals as a status for the actual value. If the green traffic light is active that the value is great, the red light indicates the weak values.
- Sparklines are a tiny graph in a worksheet cell that offers a visual representation of your data set. Use sparklines to show trends in a series of values. Another useful thing: you can highlight maximum and minimum values easily. So, the greatest impacts of sparklines: you can position the chart near its data source.
- Dynamic Charts: These days, to use dynamic charts is essential if we want to create interactive charts to refresh upon the user’s choice to use this solution automatically.
- Organizational Chart help us to show the company structure.
- Shapes or conditional formatting shapes: With the use of vector objects, we can create unique buttons and menus. Most often applied when navigating between worksheets. Good charts and vector elements – when used properly – can boost your dashboard. The opposite is also true: when used wrong, it’ll break things.
Your goal is to show the % of job seekers who accepted a job offer each month on a chart. In this tutorial, you’ll use custom combination charts using doughnut charts – progress circle chart – for displaying key performance indicators.
Tip: Just a few words about the pie charts. Pie charts are the most overused graphs in Excel and it’s the worst way to present data. In other words, if you want to create better dashboards, get rid of pie charts!
We love working with data visualization! Learn how to build a radial bar chart in Excel. Okay, let us select the data for the chart!
Select the data and build your chart
In this phase, we have cleaned and grouped data, and we’ve just picked the chart or graph for the data. It’s time to select the data! As you learned before, the combo chart requires two doughnut charts and a simple formula.
Select the ‘Calculation’ tab (which contains filtered data and calculated fields). Highlight the range of what you want to display. In the example, you are using two values to show Acceptance Ratio.
The actual value comes from the Data tab. After that, then calculate the reminder value using this simple formula. In this case, 75%. Select the ‘Calculation’ tab. The cell E23 will show the actual value. The second cell, E24, contains a simple formula and displays the remainder value to 100%.
Make sure that the value in the source cell is in percentage format! Okay, now we are selecting the ‘Actual Value’ and ‘Reminder Value’ data. To create a custom combo chart, open the ‘Insert Chart’ dialog to see a preview and choose different chart types. Furthermore, you can move the data series to the secondary axis.
Select the inserted chart and press Control + C to duplicate the chart.
Improve your chart
Now you have a chart that’s fit your data. It looks great, but you can improve it! Clean up the Chart! Remove the background, title, and borders from the chart area. Select the reminder value section of the outer ring.
Right-click, then choose Format Data Point. Use the ‘No fill’ option. Let’s see the inner ring. Select the actual value section. Apply the ‘No fill’ option. Adjust the doughnut hole size if you want. Insert a Text Box and remove the background and border.
Link the actual value to the text box.
To do that, select Text Box. Go to the formula bar and press “=.” Select the actual value and click enter. Once Text Box is linked with actual value, format the text box.
Tip: If you are uncertain about which types of charts are good for you, don’t hesitate to choose ‘Recommended Charts.’ In this case, you will get a custom set that Excel thinks will fit best with your data.
Repeat the process for the other data! A typical Excel dashboard contains various charts to display data. Repeat the chart insertion and data validation steps for other important metrics, like Training effectiveness and 90-day quit rate.
Keep your source data in the Data tab and do not remove or hide it. If further calculations are necessary, use the Calculation Worksheet. If you want to replace the source data, use the Calculation sheet, not the Data Worksheet.
Create a Dashboard Scorecard
Your dashboard is almost ready. You need only a few components to create a scorecard:
- Actual value,
- Annual trendline,
- Variance (between the selected and the last month)
Because you need a little bit more space, merge the cells. Select the cells that you want to place the components and click on the ‘merge cells’ button. Link the label name from the ‘data’ sheet. From now if you change the name of the value on the ‘data’ sheet, the widget label will be reflected. Now link the data from the ‘data’ sheet to a ‘dashboard’ sheet.
Select the cell, go to the formula tab. Enter an equal sign and select the value on the ‘data’ sheet. Creating an annual trendline is easy. Select yearly data on the ‘data’ sheet and insert a line chart. To highlight the variance between the selected and the past month, use a little trick. Go to the ‘Calculation’ sheet and create a helper table.
Create three new conditional formatting rules.
Select the cell which contains variance and copy it. Navigate to the ‘Dashboard’ sheet and apply the ‘Paste Special’ option. Choose the ‘Paste as linked Picture’ option. Working with linked pictures is easy.
Check the steps in the picture below:
We want to add a dynamic text to the main sheet to indicate the changes in key metrics. From now on, you link a text to the object you have inserted into the main excel dashboard. If you change the value on the source sheet, the target cell will show the refreshed value. What a nice feature! You can apply this trick for textboxes or charts, like sparklines.
Best practices for creating visually effective Excel Dashboards
- Drop-down list is a space-saving solution that is a great value when you create one-page dashboards. You can use data validation to control the type of data or the values that users type into a cell. To build the list of options is to type them on a worksheet. You can do this method on the sheet that will have the drop-down menus, or on a different worksheet.
- Conditional formatting is the right 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 splendid results using icons, bars, shapes, color scales, indicators, and ratings.
- Data Validation: Restrict what users can write in a single cell. Just imagine that ten users in 10 Excel workbooks write phone numbers. If you do not restrict the format of the phone numbers with the help of data validation when summing up the spreadsheets, there might be mistakes.
- Data Entry using userform and VBA: A manual data input data always carries error. Instead, use the userform and write a short macro for it. You can create a user-friendly form that is easy to customize. Active report parts like form controls or pivot table slicers suggest playing with the chart.
- 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 drill-down the data into any chosen structure.
- Named ranges: You can call selected cells with any given name. Highlight a range that contains data. In the name box, write the chosen name, for example: ‘sales.’ Form this point, we can manage the formulas efficiently.
Improve your Excel Dashboard
In this section, you’ll learn how to improve your Excel dashboard using interactive help. Discover how to create a ribbon navigation menu for your dashboard.
Do not panic, you will need only basic Excel knowledge. Select the ‘Shapes’ option and insert a rounded rectangle and modify the fill or background color. Enter the text. Double click on the shape, and you can enter your text.
Create four shapes. Now place the text in the box. Holding the Ctrl button, you can select and group the objects. Right-click and select Group. Click in the Name box. Go to Insert tab and add a question mark icon. Click Alt + F11 and insert a module and paste the VBA code. Clicking on the question mark icon, you can show/ hides the text. The last step is to assign the macro to the question mark icon. Right-click on the object and select the ‘Assign Macro’ option. In the appearing window, chose the macro and, with a click, link it to the icon.
A simple menu enables you to interact with your Worksheets using buttons or icons.
Frequently asked questions about Dashboards
- May I use a multi-page dashboard? In this case, you should create easy navigation. Insert shape-based buttons and links to keep the structure.
- What kind of data connectors shall you use? You have to know already in the planning phase what type of tool you’ll use to import data into the dashboard. If you work in Excel, the solution is the Power Query and Power Bl. These tools are great for handling millions of rows in a blink of an eye. However, you can use the ODBC link or SQL DB.
- Are there compatibility issues within the company? IT pros have to ensure that everyone involved uses the same version of Excel. If you build this into the planning phase, you can avoid problems later.
- 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 slide? You have to review access issues also. Accessibility levels are different for a manager and the owner.
- 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? Outline a dashboard structure!
Excel Dashboards Do’s and Don’ts
As first, take a look at some or best practices! There are several ways to boost your Excel dashboard.
You need to know the user’s requirements. It is under these conditions, and these conditions only will be the dashboard useful. 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 answers to these questions, and much more! It can decrease the timeframe and the costs of development.
- Start with users, not the data. As first, try to understand the goal of end-users. If you can realize this for sure, you will create a dashboard that is most useful for them. What is this all mean in practice? Go and talk to the users and try to understand the scope. Try to build a dashboard that is not in constant need of updates! Because of this, you can 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. Filter data and display the relevant information only, as well.
- 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 the data in detail must know where to navigate. Allow users to see the essence.
- Use visualization and create a clean Excel dashboard. Charting prospects are almost endless. That’s where data visualization comes into play. Use simple charts if you want; most useful to use a gauge, bullet, and variance charts also.
- Improve Excel Dashboard UI and UX: Build a menu and control your dashboard from the ribbon! Add a dependent help using tooltips to improve user experience.
- Use grids and consistent color schemes. This means that the information flow between departments provided by the use of already defined structures or schemes.
To-do list if you are using large data tables:
- Freeze Top Row: Keep the first row of the table visible while you scroll down! Use this feature if you want to keep the information always on the top — like table headers with column names.
- Enable Horizontal Scroll: Use this function if you have large data sets, and you have the main data in the first column. Go to the View Tab and choose the ‘Freeze first column.’
- Apply row styles: Frequently, we lose the focus when browsing large tables. Use table-style formatting to keep our eyes on the main content.
- Use the GROUP and UNGROUP function to drill-down into details.
Common Pitfalls with Excel Dashboards
Now let’s see the most common mistakes.
- Using too many colors: I don’t tell you often enough about the importance of colors. Do you know the game, “Where is Waldo?” It’s a nice game for kids! But please don’t follow this method if you want to create a stunning dashboard. Try to minimize the number of applied colors and use flat color schemes. Here is a really bad example. Keep the visual content as simple as possible.
- Cluttering the screen with useless design: Hey, what you want to see? A clean dashboard or a traffic jam? Get rid of borders and frames!
- Using pie charts. Keep in mind: when all charts is in focus, nothing stands out. All of the data displayed on a dashboard is important, but not all data are equally important.
Different Kinds of Dashboards – Examples
You can create various types of dashboards for all purposes. Save time and money using these excel dashboards below! Let us see our latest picks:
Above all, let’s take a look at the most used dashboard types and download the files:
Social media dashboard: Get a quick overview of your social media channel’s performance using a social media dashboard. You can include metrics like unique views, Engagement, Watch Time, Subscribers. It’s easy to control your strategy using real-time analytics.
You need only a few steps to use this dashboard. As first, pull your data to the ‘Data’ sheet and select or insert your key metrics. After that, on the ‘Calc’ sheet, change the formatting rules if you want. Finally, select the given month from a list. That is all.
Human resource dashboard: Measure the activities of the company using an HR Dashboard. Set up metrics that show whether given goals have been met, like turnover, recruiting, and retention. You can check all activities using a one-page dashboard.
Sales tracking dashboard: Turn activities into actionable and easily editable reports to refine your sales process. The sales tracking dashboard contains a review of sales activities during an exact time frame to spot trends. You can compare actual versus targeted performance.
Are you tired of boring graphs in Excel? Check how the sales funnel works.
Financial Dashboard: Keep your eyes on the most vital metrics! As first, create a sketch. After that, pull the data from different data sources. Finally, build a great dashboard to view data on a single Worksheet. It is easy to use and tells the data-driven story of a company based on the update of a drop-down list.
Project management dashboard: Show the risk, shape a work breakdown structure! Or track issues using a project management dashboard. Turn your raw data into easy-to-read graphics and display the actual status without sharing the details of the project.
Business intelligence dashboard: BI dashboards help you to track core performance metrics in real-time. You can use PowerBI and Microsoft Excel for this purpose!
Conclusion: Everything wants Dashboards
This guide gives you a lot of stuff you can do on your Excel dashboards.
The truth is that creating a dashboard in Excel is more than these 10 steps. If you already feel comfortable in the basics of Microsoft Excel Dashboards, then have a go at it.
- Use clearly defined goals.
- Learn all about Excel formulas.
- Build custom charts and be a power user.
You should have to go step by step, and success will follow.
Sometimes you are in a hurry. Also, if you want to build a great Excel dashboard in minutes, check out our chart add-in. We hope that you enjoyed our article! Good luck, and stay tuned.
Additional resources and downloads: