Unlock the Power of Data with This Mind-Blowing Excel Dashboard Tutorial!

Data analytics has become an integral part of decision-making in many industries, and being able to visualize data in a user-friendly manner has become increasingly important. This is where Excel Dashboards come in. Excel Dashboards enable you to create real-time data visualizations that help you quickly spot trends, analyze data, and make data-driven decisions. In this tutorial, we’re going to show you how to create a mind-blowing Excel Dashboard, which will help you unlock the power of data!

Setting up Your Data Sheet

Before you can create an Excel Dashboard, you first need to make sure that your data is in order. Start by organizing your data into columns and rows, making sure that you have no blank cells. You can use Excel’s built-in formatting tools or create your own using conditional formatting. Once you have your data sheet set up, you can start creating your dashboard.

Creating Your Excel Dashboard

To create your dashboard, you need to decide which metrics you want to highlight, and how you want to display them. This will depend on the nature of your data and the specific insights you want to glean from it. Here’s a step-by-step guide to creating an Excel Dashboard:

1. Open a new Excel workbook and create a new sheet for your dashboard.
2. On the dashboard sheet, create a table with the metrics you want to highlight. Make sure to include any variables that you want to filter the data by.
3. Use Excel’s PivotTable feature to summarize your data. This will enable you to quickly filter your data and display it in the way that you want.
4. Once you’ve created your PivotTable, you can start creating charts and graphs to display your data. Experiment with different chart types to find the one that best displays your data.
5. Add filters to your charts and graphs to allow the user to quickly filter the data according to their needs. This will enable you to create dynamic dashboards that respond to user input in real-time.
6. Add interactive elements to your dashboard, such as buttons and drop-down menus, to make it more user-friendly.

Tips for Creating an Effective Excel Dashboard

Here are a few tips to help you create an effective Excel Dashboard:

1. Keep it simple – don’t overload your dashboard with too much data. Instead, focus on highlighting the key metrics that are important to your business.
2. Use charts and graphs – they’re an effective way to visually display your data.
3. Add filters – this makes it easy for users to drill down into the data and see specific subsets.
4. Use color effectively – use color to highlight key data points or trends. But be careful not to overload your dashboard with too many colors as it can be distracting.
5. Make it interactive – use buttons and drop-down menus to make the dashboard user-friendly and allow users to interact with the data.

Conclusion

Excel Dashboards are an incredibly powerful tool for visualizing data. Whether you’re analyzing sales figures or tracking website traffic, Excel Dashboards can help you quickly spot trends and make data-driven decisions. By following this tutorial and incorporating the tips we’ve provided, you can create a mind-blowing Excel Dashboard that will help you unlock the power of data!

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.

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

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:

• 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.
• Browse to the file or other data source you want to import and select it. Select Import.
• Depending on the data source type you choose, you’ll see different dialogue boxes to transform the data into Excel spreadsheet format.
• The sheet will populate with all the data from the external file or database.
• 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.
• Select the three dots next to the EDIT link at the bottom of the refresh window, and select Properties.
• 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.
• Repeat the process above in new, separate worksheets until you’ve imported all of the data you want to use in your new dashboard.
• 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.

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.

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

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

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

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.

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

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.

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

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

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

• 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.
• In the Chart Design menu, from the Data group, choose Select Data.
• 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.
• 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.
• 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.
• 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.
• In the Chart Design menu, from the Data group, select Select Data.
• 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.
• 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.
• 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.
• 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.
• Select the textbox and use the Format Shape window on the right to format the text display area in your dashboard.
• 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.
• 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.
• 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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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

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.

• Right-click the outer border of a bar chart and select Format Chart Area.
• Select the Fill icon in the Format Chart Area pane and change the selection to Gradient fill.
• 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.
• 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:

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

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

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.

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

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

These updates happen automatically as long as Excel is open.

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.

Get the Latest Tech News Delivered Every Day