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:
ChartsGraphsGaugesMaps
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:
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.
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.
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.
