A dashboard is a tool for presenting data in clear, concise, graphical representation. A running dashboard is one which excel updates automatically when new data arrives. Excel does not come with the ability to create dashboards; this must be done by using third party add-ins or macros. A few features of dashboards are drill down functionality, resource monitoring, and updating the dashboards with changes in data. The great thing about dashboards is that anyone can make them, even if you don’t know any computer programming.
In this article we shall be looking ate ways to to create an excel dashboard.
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 create a dashboard to track the Turnover rate, your goal is to show how many employees leave a company in the given period.
We have said many times that data is useless without the ability to visualize it.
Let us see the basics! The core of every Excel dashboard is a one-page layout. Why? Keep it in mind: a CEO doesn’t always interested in the details.
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!
1. Create a layout for your dashboard
The planning phase does not substitute an exact draft but complements it. Therefore, the sketch will not describe how the framework should work! 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 the effect of the dashboard UI mockup. Before we start working with shapes, we should know what kind of result we want to see at the end. What kind of conditions the final dashboard 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 Tab: the main dashboard Worksheet
- Calculation: make the calculations on this Worksheet
First, the department has decided on the type of grid dashboard layout they will use. Then pick a color scheme and font type and assign it to the report. Finally, 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 a dashboard in Excel!
2. Get your data into Excel
To create a dashboard, you need to choose data sources. If the data is present in Excel, you are lucky and 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.
3. Clean Raw Data
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? First, 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 makes life easier. Just sort the data by growing or decreasing order. Why? 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.
4. Use an Excel Table and Filter Data
You don’t have cleaned input in this phase, 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 point lives in one cell, for example, the name of the city, address, or phone number. If it is in a tabular format, you should convert it into an Excel table and select the data range.
On the Insert Tab, choose Table or press Ctrl + T.
In this case, we don’t have headers. Excel will automatically insert headers into the first row. Use Excel Table to avoid trouble. If you need more data, you can only expand the table and not lose the formulas. A handy tool indeed to use!
5. 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.
Ask yourself:
- 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. However, to make a useful dashboard, you need creativity rather than knowing all the formulas. So 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 face missing chart data points and unknown formula errors. A little bit scary situation! How to figure out what went wrong? Do not panic! Excel grants great auditing tools that can help you find and fix Workbook or Worksheet issues.
Use Microsoft Excel Inquiry to visualize 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. First, you’ll get a detailed overview of the workbook and its structure, including formulas, cells, ranges, and warnings. Then, find the errors, fix them, finally recheck the given Worksheet.
So, before we start creating a chart, you have to validate the data.
If you want to analyze your data quickly, use the Quick Analysis Tool.
6. 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 proper chart.
Strike a balance between great looking dashboard and function.
Pick the right chart. It is critical.
Let’s see some useful charting tips! First, 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, Panel Chart, and Bullet charts. Don’t forget to check how the radial bar chart work.
- Composition: How can you display different sales results in different regions? The most fitting charts are Pie, Stacked Bar, Mekko, Stacked Column chart, Area, and Waterfall charts. We prefer the heat map also.
- Analyzing Trends: To analyze the result of a data set in a given period, use the following charts: Line, Dual-Axis Line, and Column charts. If you want to create a quick forecast in Excel, check this example.
- 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 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. For example, 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 impact of sparklines: you can position the chart near its data source.
- Dynamic charts are essential if we want to create interactive charts to refresh the chart based on the user’s choice.
- Shapes or conditional formatting shapes: Using 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. Unfortunately, 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 charts – for displaying key performance indicators.
Learn more about chart templates.
Tip: Just a few words about the pie charts. Pie charts are the most overused graphs in Excel. It’s one of the worst ways to present data. In other words, if you want to create better dashboards, get rid of the pie charts!
We love working with data visualization! First, learn how to build a radial bar chart in Excel. Okay, let us select the data for the chart!
7. Select the data and build your chart
We have cleaned and grouped data in this phase, 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 the 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%. Next, select the ‘Calculation’ tab. 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. Next, open the ‘Insert Chart’ dialog to create a custom combo chart to 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.
8. Improve your charts
Now you have a chart that’s fit your data. It looks great, but you can improve it! First, clean up the chart to remove the background, title, and borders from the chart area. Next, 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. Next, go to the formula bar and press “=.” Next, select the actual value and click enter. Once Text Box is linked with actual value, format the text box.
Repeat the process for the other data! For example, a typical Excel dashboard contains various charts to display data. Next, repeat the chart insertion and data validation steps for other essential metrics, like the 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.
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.
9. Create a Dashboard Scorecard
Your dashboard is almost ready. You need only a few components to create a scorecard:
- Label,
- 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 to place the components and click on the ‘merge cells’ button. Now, link the label name from the ‘Data’ sheet. If you change the name of the value on the ‘data’ sheet, the widget label will reflect. 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. Then, navigate to the ‘Dashboard’ sheet and apply the ‘Paste Special’ option. Next, choose the ‘Paste as linked Picture’ option. Working with linked pictures is easy.
Check the steps in the picture below:
We want to add dynamic text to the main sheet to indicate the changes in key metrics. You link a text to the object you have inserted into the main excel dashboard from now on. Then, 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.
Additional Info
1. Get your data into Excel
For you to be able to create your dashboard in Excel, you have to import your data first. You can either copy and paste the data directly or use an external app to pass the data in real-time.
Then, set up an Excel table with the raw data on another tab. With an Excel table, you will be able to name your rows, columns, and cells however you like. This makes referencing easier later on.
2. Set up your dashboard tab
On this next step, you need to structure your workbook and place the dashboard as the first tab for easy access.
Following the instructions on the first step, you could end up with about three tabs all in all.
Here’s how it looks like:
This makes your Excel file look clean and neat.
3. Analyze your data
One of the things you have to consider is the purpose of your dashboard. What do you want to highlight or show on your dashboard?
It’s important since you have to make an analysis of your data to reflect your purpose.
For that, you will need to use different Excel features and functions such as:
This may seem intimidating at first since you’re looking at a list of features and functions. However, you have to remember that whatever tool you will be using depends on your purpose.
Figure out the purpose of your dashboard first and see what tools (features and functions) you need to use to analyze the data.
4. Create the dashboard
This step could be summarized by simply inserting the charts and visuals you need on your dashboard.
As complicated as it sounds, the final result is a spreadsheet with different visuals that would tell a story about your data and fulfill the purpose of your dashboard.
Like this:
Consider using the new charts in Excel that was released as part of Excel 2016. You could also use a Gantt chart if there’s a timeline involved. Or maybe you can use a chart as simple as a bar graph.
Don’t forget to add customization to your visuals to reflect your brand. You can always change the chart colors, fonts, and even a little bit of Macro to add flair to your dashboard.
That’s it! All you have to do now is to continue refining your dashboard especially when you need to add new data into it.
Conclusion
Dashboards are an excellent way to communicate multiple data points in a visually attractive format. For example, you can create a dashboard that tracks the status of projects, how many leads were closed this quarter, the budget for the next quarter, and more. You can include charts, graphs of percentages, or just plain numbers on your dashboard.