How to Create A Dashboard In Excel

Pull information in from multiple sheets for the best dashboard

Image of a data dashboard on a computer screen

Stephen Dawson/Upsplash 

Creating a Microsoft Excel dashboard involves pulling information in 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 could use.

The information in this tutorial applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel for Mac.

What Is an Excel Dashboard?

A data dashboard is a tool that helps you visually monitor and analyze data 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 sales staff in your sales department.

Image of people using a dashboard
NicoElNino/Getty Images 

Regardless of how you use a dashboard, the concept is always the same. Background spreadsheets will pull in data from files, services, or API connections to databases and other sources. The main sheet will display 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:

  • Many types of charts
  • Different types of graphs
  • Gauges
  • Maps

There are two types of dashboards you can create. 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. Or you can create a dynamic dashboard people view inside Excel, which 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 Excel spreadsheets from various sources.

Screenshot of importing data into Excel

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.

    Screenshot of selecting a data source in Excel
  2. Browse to the file or other data source you want to import and select it. Then, select Import.

    Screenshot of selecting a data source in Excel
  3. Depending on the data source type you choose, you'll see different dialogue boxes to transform the data into an Excel spreadsheet format.

    Screenshot of importing an external Excel workbook into Excel
  4. When it's done importing, you'll see the sheet populate with all the data from the external file or database.

    Screenshot of data imported into Excel
  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.

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

    Screenshot of selecting refresh properties in Excel
  7. You can 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.

    Screenshot of setting the data refresh interval in Excel

    Refreshing data from external sources is useful, but it can consume CPU time if you make the refresh rate too frequent. It's important to 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. Some data sources can be updated daily and you'll still see appropriate updates in your dashboard.

  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 getting refreshed automatically, it's time to create your real-time Excel dashboard.

The example dashboard below will be based on weather data imported from weather websites from around the internet.

Sometimes, when you import data from external sources, you can't chart the imported data. The fix for this is to create a new spreadsheet and in each cell, type =convert( and select the data from the imported spreadsheet. For the unit parameters, just select the same parameters for before and after. Fill the entire sheet with the same function so all data gets copied to the new sheet and converted to numbers you can use in the various charts you'll create for your dashboard.

  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.

    Screenshot of inserting a bar chart in Excel
  2. In the Chart Design menu, from the Data group, choose Select Data.

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

    Screenshot of selecting chart data range in Excel
  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.

    Screenshot of creating a single data point bar chart in Excel
  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.

    Screenshot of adding bar charts to an Excel dashboard

    Choosing the right data range is important because if you only use the default range, your bar chart scale may be too large for the data you're displaying. This can result in large, 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, and select the 2D Area chart.

    Screenshot of inserting a 2D area chart in Excel
  7. In the Chart Design menu, from the Data group, select Select Data.

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

    Screenshot of selecting a range of data in Excel
  9. Change the title of the chart to match the data you are displaying. Then move the chart to the area of the dash where you'd like to display it.

    Screenshot of inserting a line chart into Excel

    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 dashboards that are neatly designed, and provide as much information to the dashboard user as possible 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, you can 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.

    Screenshot of selecting textbox in Excel
  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.

    Screenshot of linking textbox to cell data in Excel
  12. Select the textbox and use the Format Shape window on the right to format the text display area in your dashboard however you like.

    Screenshot of editing a textbox formatting in Excel
  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.

    Screenshot of selecting data for a pie chart in Excel

    Pie charts compare two or more values. If you're displaying a percentage like relative humidity, you may need to manually crate 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. Then move the chart to the area of the dash where you'd like to display it.

    Screenshot of the pie chart placed in an Excel Dashboard
  15. As you can see, 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 great way to add clarity to your dashboard is by making it clear whether values are good or bad if they're high or low.

You can do this 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.

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

    Screenshot of Format Chart Area in Excel
  2. Select the Fill icon, and change the selection to Gradient fill.

    Screenshot of Gradient fill in Excel
  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 you can see that high relative humidity is set to fade to dark red.

    Screenshot of using gradient fill with colors in Excel
  4. You can 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've created your dashboard, you don't have to worry about doing anything to update the graphics. All of the data in those charts and widgets update as follows.

  • The 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.

You don't have to do anything for all of these updates to happen, so long as the Excel file is open.

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 sale's manager's dashboard should focus on the Key Performance Indicators (KPIs) that are related to sales performance.

Image of a person using a dashboard
 hanieriani/Getty Images

This kind of dashboard shouldn't include information that isn't related to sales performance, otherwise 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 with information.
  • 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 are red, and when they're good they're green.

Most importantly, use creativity to design dashboards that are not only information, but interesting to use.