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

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

What is the Excel Toolbar?

The Data Dashboard is a tool that helps you visually track and analyze the data metrics that matter most to you. You can use the dashboard to track the performance of your business, the performance of your manufacturing process, or the performance of your sales force in your sales team.

Image of people using dashboard
NicoElNino/Getty Images

No matter how you use the toolbar, the concept is always the same. Background spreadsheets retrieve data from files, services, or API connections to databases and other sources. The master sheet will display data from these multiple sheets in one place where you can view it all at a glance.

Typical data panel elements in Excel include:

  • Many types of charts
  • Various types of graphs
  • Measuring instruments
  • Cards

There are two types of dashboards that you can create. To create dashboard reports, you can create static dashboard from other sheet data that you can send to someone in a Word or PowerPoint report. Or you can create in excel dynamic view of the toolbar which is updated whenever data in other sheets is updated.

Entering data into the Excel Control Panel

The first step in creating an Excel dashboard is to import data into Excel spreadsheets from various sources.

Screenshot of importing data into Excel

Potential sources for importing 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 containing data tables)

With so many potential data sources, the possibilities of what data you can bring into Excel to create useful dashboards are endless.

To enter a data source:

  1. Open a blank Excel sheet. Select menu » Data» and in drop-down menu » To get data» select the desired data type, and then select a data source.

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

    Screenshot of selecting data source in Excel
  3. Depending on the selected data source type, you will see different dialog boxes for converting data to Excel spreadsheet format.

    Screenshot of importing an external Excel workbook into Excel
  4. When the import is complete, you will see that the sheet is populated 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 downloads any changes made to the external data source, select icon » Update» on the right side panels » Requests and Connections» .

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

    Screenshot of selecting update properties in Excel
  7. You can set the refresh of data from the data source at regular intervals by setting the parameter Refresh every xx minutes to whatever interval you want to update.

    Screenshot of setting data refresh interval in Excel

    Refreshing data from external sources is useful but can consume processor time if you make the refresh rate too fast. It’s important to choose a refresh rate that updates the data about as often as it changes at the source, but not so often that you copy the same data every time. Some data sources may be updated daily and you will still see the relevant updates in your dashboard.

  8. Repeat the above process on new separate worksheets until you have imported all the data you want to use in the new dashboard.

  9. Finally, create a new sheet, place it as the first sheet in a book and rename it to dashboard .

How to Create an Excel Control Panel

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

The example toolbar below will be based on weather data imported from weather websites from all over the web.

Sometimes when you import data from external sources, you cannot display imported data. The fix for this is to create a new spreadsheet and in each cell type = convert( and select data from the imported spreadsheet. For block parameters, simply select the same parameters for before and after. Fill the whole sheet with the same This function allows you to copy all the data to a new sheet and convert it to numbers that can be used in various charts that you create for your dashboard.

  1. Create bar chart to display one data point. For example, to display the current relative humidity (from 0 to 100 percent), you would create a bar graph with 0 percent as the lowest point and 100 percent as the highest point. First choose menu » Insert» and then select bar chart » 2D clustered column «.

    Screenshot of pasting a bar chart in Excel
  2. AT menu » Chart Design» in group » Data » select « Select data» .

  3. AT opened window » Selecting a data source» click in field » Chart data range» and then select the cell in the data spreadsheet you want to display on this histogram .

    Screenshot of selecting chart data range in Excel
  4. Change the title of the chart to match the data being displayed. Update the axis bounds to be between 0 and 100 percent. Then move the chart to the dash area where you want to display it.

    Screenshot of creating a histogram with one data point in Excel
  5. Repeat the same steps as above to create histograms for any other data points you wish to plot. Make the axis range the minimum and maximum for these measurements. For example, a good barometric pressure range would be 28 to 32.

    Screenshot of adding bar charts to the Excel toolbar

    Choosing the Right data range important because if you only use the default range, your histogram scale might be too large for the data being displayed. This can lead to large, mostly empty histograms. Instead, keep the minimum and maximum scale ends of the axis only slightly lower and higher than the maximum possible values ​​of your data.

  6. Create line chart to display the data trend. For example, to display the local temperature history for your region, you would create line chart, covering the last number of days of data that you can import from the website’s weather table. First select the «Insert» menu and select diagram 2D regions .

    Screenshot of inserting a 2D area chart in Excel
  7. AT menu » Chart Design» in group » Data » select « Select data» .

  8. AT opened window » Selecting a data source» click in field » Chart data range» and then select the cells in the data spreadsheet that you want to display with this line chart.

    Screenshot of data range selection in Excel
  9. Change the title of the chart to match the data being displayed. Then move the chart to the dash area where you want to display it.

    Screenshot of inserting a line chart in Excel

    Charts are very flexible when placed on the dashboard. You can change the location, as well as the size and shape of the chart widget. Use this flexibility to create neatly designed dashboards and provide as much information as possible to the dashboard user in the smallest amount of space.

  10. Create text field to display string data from sheets that you imported. For example, to see updates to weather alerts in the toolbar, you can link the contents of a text box to a cell in an imported data sheet. To do this, select menu » Insert» select » Text», then select » Text field» .

    Screenshot of text box selection in Excel
  11. Place the mouse cursor in the formula field, enter =, and then select the cell in the imported data table that contains the row data you want to display.

    Screenshot: Associating a text box with cell data in Excel
  12. Select text field and use window » Shape Format » on the right to format the text display area of ​​the control panel however you like.

    Screenshot of editing text box formatting in Excel
  13. you also can compare two data points in imported tables data using pie charts . For example, you can display relative humidity as a pie chart. First select the data you want to display and in menu » Insert» select two-dimensional circular diagram.

    Screenshot of data selection for pie chart in Excel

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

  14. Change the title of the chart to match the data being displayed. Then move the chart to the dash area where you want to display it.

    Screenshot of a pie chart placed on the Excel toolbar
  15. As you can see, by adding different types of data charts, you can create a useful toolbar that displays all types of data in one convenient toolbar.

Add visual appeal and context with color

Another great way to bring clarity to your panel is to make it clear if the values ​​are good or bad if they are high or low.

You can do this by giving the histogram a gradient fill that displays a warning color like red for areas of the data that might not be very good.

For example, if you want to show that a relative humidity of over 75% is inconvenient, you can change the gradient fill of the single histogram accordingly.

  1. Right-click on the outer edge of the histogram and select » Chart Area Format» .

    Screenshot of format chart area in Excel
  2. Select the «Fill» icon and change the selection to » Gradient Fill » .

    Gradient fill screenshot in Excel
  3. Select each level icon along the gradient fill line and change the color and darkness according to how «good» or «bad» that level is. In this example, you can see that High Relative Humidity is set to dark red.

    Screenshot 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 Updates Automatically

Once you’ve created your dashboard, you don’t have to worry about any graphics refresh steps. All data in these charts and widgets is updated as follows.

  • Sheets with imported data are updated on the date specified when the data import was first created.
  • Any additional sheets you create to correct or reformat data from imported sheets will be updated with the new data in those sheets.
  • Each dashboard widget automatically updates to display new data in these updated sheets for the ranges you selected when you created these charts.

You don’t have to do anything to make all these updates happen as 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 create one control panel that does everything.

For example, if you are a sales manager and you are interested in monitoring the performance of your sales team, then the sales manager dashboard should focus on key performance indicators (KPIs) related to sales performance.

Image of a person using a dashboard
Hanieriani/Getty Images

This kind of dashboard should not contain information that is not related to sales performance, otherwise the dashboard might become too cluttered. A cluttered dashboard makes it harder to see significant relationships and data patterns.

Other considerations when creating dashboards:

Most importantly, use creativity to design dashboards that are not only informative, but also fun to use.

Похожие записи