How to Create Interactive Dashboards in Microsoft Excel (and Why You Should)

In addition to organizing data, basic table layouts, and performing calculations, Microsoft Excel can also serve as a robust tool for creating engaging, interactive dashboards. After all, static tables with endless rows of numbers can sometimes be overwhelming and may fail to display and convey important insights. This is where interactive dashboards come in, giving you a dynamic way to explore data, identify trends, and make informed decisions.

What is an interactive dashboard in Microsoft Excel?

Before we go through the step-by-step guide on how to create an interactive dashboard in Microsoft Excel, let's briefly talk about An interactive dashboard contains a combination of charts, graphs, tables, and figures that help you visualize your complex data in an easy-to-read format.

An interactive dashboard not only provides an interface for quick data interpretation but also leaves a good impression on your stakeholders, professors, investors or potential customers. Such a one-page dashboard goes beyond a collection of charts and tables and provides important information and valuable insights to speed up your decision-making process.

For example, if you're a sales manager and want to track your team's performance across different regions, product categories, and time periods, you can create an interactive dashboard with charts and graphs, pivot tables, slicers, and conditional formatting. After you've completed your dashboard, you can quickly check, for example, whether the quarterly sales target was met or exceeded and get other important metrics.

With interactive dashboards in Excel, the possibilities are endless.

How to create an interactive dashboard in Microsoft Excel

Now that you have a basic idea of ​​interactive dashboards in Microsoft Excel, let's see them in action. Before you begin, clearly define the purpose and use case for your dashboard. With this clarity, you can quickly follow the steps and complete the setup task efficiently.

Insert data and create a pivot table (or several!)

First, generate the raw data set you want to use to create an interactive dashboard in Excel. You must first integrate the data into an Excel spreadsheet. in table format with headingsthen from there we can create the pivot tables that summarize the data based on the categories of your choice. As an example below we will take a table with product sales information and I will demonstrate how to use pivot tables to extract and visualize information such as the units sold per month, the sales made by salespeople, etc.

1. Create or open a corresponding table in Microsoft Excel.
2. Select your data and then click Insert > Tableto put everything into a nice table format with headings.

   

3. Check the box next to My table has headings (and of course make sure that this is the case), and then click OK.

   

4. If you want, you can format your table using the top menu to give it a different look (a blue theme is used by default).

   

5. Now it's time to create a pivot table (or several) from your table so you can analyze your data and find patterns in it. To do this, go to Insert > PivotTable and select From table/range.

   

6. Give it a suitable name, select New worksheet as location and click OK.

   

For this example, we are creating three charts, so we need three pivot tables. Instead of repeating the same steps as above to create three new pivot tables from scratch, you can simply CTRL (or option on Mac) and drag the new worksheet to the right to copy it. Repeat this process to create three PivotTable sheets. Rename the sheets for better organization using the right-click menu.

Creating charts with pivot tables

Now that our three new pivot tables are ready, it's time to create charts for the dashboard based on the information they contain.

1. Once your pivot table is ready, a pivot table box will appear in the sidebar. Select the fields you want to include in the report (which correspond to the headings in your original table).
2. Let us choose Month And Units sold.

   

3. Go to Insert > Charts and select the type of chart you want to insert. Let's add a pie chart.

   

4. You can select a chart and click the style icon to change its appearance. You can also click the + icon and tweak the chart elements.

   

For example, you can add or hide a chart title, change its position, change the positioning of the data labels, and even change the position of the legend. Overall, you have enough customization options to get the result you want in Excel.

Once you've completed your first pivot table, switch to another sheet, select your pivot table, and insert the relevant fields to create another chart. Here, we've added a column chart.

Repeat the same for the third sheet, select the fields you want and add a chart. We have added an area chart here. The steps are identical to those described in the section above.

Create a central dashboard

Once your PivotTable charts are ready, it's time to combine them into a single sheet.

1. Click on the + icon at the bottom of the file to insert a new sheet. You can right-click it to rename it dashboard.
2. Copy the pivot charts from the other sheets and paste them into the dashboard.
3. You can rearrange them as you wish and create a consistent look.

   

Pro Tip: Add Slicer to your Dashboard

You can also add filters in the form of slicers to an Excel dashboard. Power users can interact with the dashboard and filter relevant data in a snap. Here we add a slider for the sales team so that we can check the sales performance of each rep with a single click.

1. Select any pivot chart on your dashboard. Go to the PivotChart analysis.
2. Choose Insert slicer.

   

3. Click the checkbox next to Sales Representative and met OK.

   

4. Place the slicer in a specific location. From now on, your slicer will only be connected to the selected chart. We need to make sure it is also connected to the data in the other pivot charts in order to slice the data across the board and, in this case, capture all the metrics for the same employee.
5. Right-click on All-purpose slicer and select Reporting connections.

   

6. Select the other related pivot tables and click OK.

   

7. Now you can click on any sales rep’s name and check their relevant metrics in real time.

If we click on Jim, the dashboard will show how many units he sold monthly and how much profit and revenue he made across all the categories visualized on your dashboard.

Reasons for creating interactive dashboards with pivot tables

Still not sure about using an interactive dashboard in Excel? Here are some of the key benefits of creating dynamic dashboards in Microsoft Excel.

• Better data exploration: Slicing and dynamic filtering allow you to review hand-picked data based on specific criteria for the metrics you want to see.
• Improved decision-making process: It provides real-time insights and enables decision makers to respond quickly to ever-changing circumstances.
• Increased productivity: It increases efficiency and gives analysts valuable time.
• Cost-effective: Thanks to this functionality integrated into Excel, you do not have to rely on other software programs to generate such interactive dashboards, but can easily create and manage them yourself.

Make data-driven decisions

From simple charts and graphs to advanced pivot tables, you have plenty of options to create a personalized dashboard in Excel. Start with the tricks above to transform your simple tables into dynamic dashboards that will give you and your team a deeper understanding of those endless numbers in rows and columns.

Creating a spreadsheet from scratch can be a time-consuming affair, but if you're looking to tackle more projects, here are the best free Excel templates that will help you create more useful worksheets in no time.