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.
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.
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:
-
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.
-
Find the file or other data source you want to import and select it. Then select Import .
-
Depending on the selected data source type, you will see different dialog boxes for converting data to Excel spreadsheet format.
-
When the import is complete, you will see that the sheet is populated with all the data from the external file or database.
-
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» .
-
Select three dots next to link EDIT at the bottom of the update window and select Properties .
-
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.
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.
-
Repeat the above process on new separate worksheets until you have imported all the data you want to use in the new dashboard.
-
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.
-
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 «.
-
AT menu » Chart Design» in group » Data » select « Select data» .
-
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 .
-
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.
-
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.
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.
-
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 .
-
AT menu » Chart Design» in group » Data » select « Select data» .
-
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.
-
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.
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.
-
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» .
-
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.
-
Select text field and use window » Shape Format » on the right to format the text display area of the control panel however you like.
-
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.
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.
-
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.
-
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.
-
Right-click on the outer edge of the histogram and select » Chart Area Format» .
-
Select the «Fill» icon and change the selection to » Gradient Fill » .
-
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.
-
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.
