How to Create a Dashboard in Excel

Creating a Microsoft Excel dashboard involves pulling information from multiple sources so you can view that data in one place. Excel offers a variety of ways to do this, which makes it one of the most powerful dashboard reporting tools you can use.

What Is an Excel Dashboard?

A data dashboard is a tool that helps you visually monitor and analyze the metrics that are most important to you. You might use a dashboard to monitor your business performance, the efficiency of a manufacturing process, or the performance of staff in your sales department.

Regardless of how you use a dashboard, the concept is always the same: Background spreadsheets pull in data from files, services, or API connections to databases and other sources. The main sheet displays data from those multiple sheets in one place, where you can review it all at a glance.

Typical elements of a data dashboard in Excel include:

• Charts
• Graphs
• Gauges
• Maps

You can create two types of dashboards. For dashboard reporting, you can create a static dashboard from data in other sheets that you can send to someone in a Word or PowerPoint report. A dynamic dashboard is one people can view inside Excel, and it updates whenever the data on the other sheets are updated.

Bringing Data Into an Excel Dashboard

The first phase of creating an Excel dashboard is importing data into spreadsheets from various sources.

Potential sources to import data into Excel include:

• Other Excel workbook files
• Text, CSV, XML, or JSON files
• SQL database
• Microsoft Access
• Azure Data Explorer
• Facebook and other web pages
• Any other database that supports ODBC or OLEDB
• Web sources (any website that contains tables of data)

With so many potential data sources, the possibility of what data you can bring into Excel to create useful dashboards is unlimited.

To bring in a data source:

1. Open an empty Excel worksheet. Select the Data menu, and in the Get Data drop-down menu, select the data type you want, and then choose the data source.

2. Browse to the file or other data source you want to import and select it. Select Import.

3. Depending on the data source type you choose, you'll see different dialogue boxes to transform the data into Excel spreadsheet format.

4. The sheet will populate with all the data from the external file or database.

5. To refresh the data so that it regularly uploads any changes made in the external data source, select the Refresh icon on the right side of the Queries & Connections pane.

6. Select the three dots next to the EDIT link at the bottom of the refresh window, and select Properties.

7. Configure data to refresh from the data source at regular intervals by setting the Refresh every xx minutes to whatever interval you would like to update the data.

   Refreshing data from external sources is useful, but it can consume CPU time if you make the refresh rate too frequent. Choose a refresh rate that keeps the data updated about as frequently as it gets changed at the source, but not so frequently that you're only copying the same data each time.

8. Repeat the process above in new, separate worksheets until you've imported all of the data you want to use in your new dashboard.

9. Finally, create a new worksheet, place it as the first worksheet in the workbook, and rename it Dashboard.

How to Create an Excel Dashboard

Now that you have all of the data you need in your Excel workbook, and all of that data is refreshing automatically, it's time to create your real-time Excel dashboard.

The example dashboard below will use weather data from websites from around the internet.

1. Create a Bar Chart to display a single point of data. For example, to display current relative humidity (from 0 to 100 percent), you'd create a bar chart with 0 percent as the lowest point and 100 percent as the highest point. First, select the Insert menu, and then select the 2D Clustered Column bar chart.

2. In the Chart Design menu, from the Data group, choose Select Data.

3. In the Select Data Source window that pops up, click the Chart data range field, and then select the cell in the data spreadsheet that you'd like to display with this bar graph.

4. Change the title of the chart to match the data you are displaying. Update the axis bounds to be from 0 to 100 percent. Then move the chart to the area of the dash where you'd like to display it.

5. Repeat the same steps above to create bar charts for any other single data points you want to chart. Make the axis range the minimum and maximum for those measurements. For example, a good barometric pressure range would be 28 to 32.

   Choosing the right data range is important because if you only use the default, the scale may be too large for the data, leaving mostly blank bar charts. Instead, keep the minimum and maximum end of the axis scale only slightly lower and higher than the extreme possible values of your data.

6. Create a Line Chart to display a trend of data. For example, to display a history of local temperatures for your local area, you'd create a line chart covering the last number of days of data you can import from the weather website table. First, select the Insert menu select the 2D Area chart.

7. In the Chart Design menu, from the Data group, select Select Data.

8. In the Select Data Source window that pops up, click the Chart data range field, and then select the cells in the data spreadsheet you'd like to display with this line chart.

9. Change the title of the chart to match the data you're displaying and move the chart to the area of the dash where you'd like to display it.

   Charts are very flexible when placing them in a dashboard. You can change the location as well as the size and shape of the chart widget. Use this flexibility to design organized dashboards that provide as much information to the user in the smallest amount of space.

10. Create a Textbox to display string data from sheets you've imported. For example, to see weather warning updates on your dashboard, link textbox content to a cell in the imported data sheet. To do this, select the Insert menu, select Text, and then select the Textbox.

11. Place the mouse cursor in the formula field, type = and then select the cell in the imported data table that contains the string data you want to display.

12. Select the textbox and use the Format Shape window on the right to format the text display area in your dashboard.

13. You can also compare two data points in your imported data sheets using pie charts. For example, you may want to display relative humidity in the form of a pie chart. First, select the data you want to display, and in the Insert menu, select the 2D Pie chart.

    Pie charts compare two or more values. If you're displaying a percentage like relative humidity, you may need to manually create another cell that subtracts that value from 100% for the second value to compare it to. This will result in a pie chart that displays the percentage value as a portion of the total possible 100 percent.

14. Change the title of the chart to match the data you are displaying, and then move the chart to the area of the dash where you'd like to display it.

15. Adding various data charting types, you can create a useful dashboard that displays all types of data in one convenient dashboard.

Add Visual Appeal and Context With Color

Another way to add clarity to your dashboard is by giving your bar charts a gradient fill that portrays a warning color like red for regions of the data that may not be good.

For example, if you want to show that a relative humidity of over 75% is uncomfortable, you can change the gradient fill of the single bar chart accordingly. Here's how.

1. Right-click the outer border of a bar chart and select Format Chart Area.

2. Select the Fill icon in the Format Chart Area pane and change the selection to Gradient fill.

3. Select each level icon along the gradient fill line and change the color and darkness to suit how 'good' or 'bad' that level is. In this example, high relative humidity fades to dark red.

4. Repeat this process for each chart where adding color context to the chart makes sense for that data point.

How Excel Dashboards Update Automatically

Once you create a dashboard, you don't have to do anything to update the graphics. All of the data in those charts and widgets update as follows:

• Sheets with imported data refresh at the date that you set when you first created the data import.
• Any additional sheets that you created to fix or reformat the data from the imported sheets will update with the new data in those sheets.
• Each widget in your dashboard automatically updates to display the new data inside those updated sheets for the ranges you selected when you created those charts.

How to Use Excel Dashboards

Creating dashboards in Excel can be useful for many different reasons. However, it's important to create them based on a specific need, rather than trying to build one dashboard that does everything.

For example, if you're a sales manager and you're interested in monitoring the performance of your sales team, then the sales manager's dashboard should focus on the Key Performance Indicators (KPIs) related to sales performance.

This kind of dashboard shouldn't include information that isn't related to sales performance or else the dashboard could get too cluttered. A cluttered dashboard makes it more difficult to see the data relationships and patterns that matter.

Other considerations when building dashboards:

• Use the right charts for the right data.
• Don't use too many colors throughout the dashboard.
• Lay out the dashboard with similar data and chart types in common blocks.
• Make sure each chart displays simple labels and aren't too cluttered.
• Organize widgets in hierarchy of importance, with the most important information at the upper-left of the dashboard.
• Use conditional formatting to make sure when numbers are bad they're red, and when they're good they're green.

Most importantly, use creativity to design dashboards that are informative and interesting to use.