Data analysis can help you improve your processes with efficient reporting methods. But sometimes, you need more than just numbers in a spreadsheet to make your point. So, why not use data visualization tools like interactive dashboards?

They can help report campaigns, increase conversions, analytics, and more. In this article, we'll walk through how to create an interactive dashboard with an example and also provide tips for designing beautiful dashboards in Excel.

1. Organize Your Source Data and Create a Layout

Persons looking at charts

A well-designed dashboard is a powerful reporting tool that can help you visualize and communicate your data. The first step is ensuring that your data is free of errors and properly organized into rows and columns with appropriate headers.

You can use dashboards in any industry, but they're especially helpful in finance, sales, and marketing.

When designing a dashboard, you should remember that it should be easy for end users to navigate. The next step is to decide the kind of layout based on the information that needs to be displayed.

For our example, let's create a dashboard that shows the following information on one sheet:

  • Cells A3 - T5: Project Status
  • Cells A7-T16: Overview and Project Financials.
  • Cells A15- U25: Project Progress and Heat map.

Create a new Excel worksheet labeled "Dashboard". This is where you'll build up your dashboard by adding elements. Using text styles and fonts that align with your or your brand's voice, tone, and personality is also a good idea.

2. Build PivotTables to Organize Your Data

A pivot table makes it easy to organize your data in Excel into groups. You can use the tool to count, sum up, multiply values, and perform multiple actions across those groups.

To demonstrate how these interactive dashboards actually work, we'll create an interactive "Freelance Projects" dashboard as an example.

You'll need to arrange your data in a tabular format. To do this, use the keyboard shortcut CTRL + T and select the data. For this purpose, our source data will look somewhat like this:

Source dats in Excel
  1. Once done, open a new worksheet and label it as PivotTable.
  2. Within the worksheet, we'll create a Pivot table by going to the Insert option on the ribbon. Select the data range for your table.
  3. Now, it's time to choose the fields for your Pivot table.
    Arranging data in pivot tables
  4. Following our example above, select the fields—Task, Manager name, Project start and end dates, Duration, Days to completion, Progress, etc. for your row labels.
  5. For the column labels, assign budgeted and actual values
  6. Once the data shows up in your worksheet, arrange the same in a tabular format. To do this, go to the layout section in Design > Report Layout, and select Show in Tabular Form from the drop-down options.
  7. Ungroup the data by right-clicking on the data fields. You'll also need to turn off the sub-totals. To do so, head over to the Design Tab > Subtotals > Do Not Show Subtotals. Now, your PivotTable would look like this:
    Arranging data in a PivotTable
  8. To choose a PivotTable color, go to the Design Tab > PivotTable Styles.
  9. Turn off collapsible headers by going to PivotTable Analyze > Show (extreme right corner). Turn off the +/- Buttons.
  10. To organize your data further, go to Design > Report Layout > Repeat All Item Labels.

3. Apply Appropriate Formulae

It's crucial to ensure that you're using the correct formulae, or else your data will be inaccurate and misleading.

For our example, let's start by counting the number of projects across different categories—started, work-in-progress, or completed. To do this, we'll need to use the COUNTIF and COUNTIFS functions.

  1. Create a new "Calculations" worksheet for working with formulae and charts while building your dashboard.
  2. Enter the status of projects ("Work-in-progress", "Active Projects Remaining", "Completed Before Due Date", and "Completed") in consecutive rows, as shown below.
  3. Next, select the Progress (%) column in your Pivot Table. The total number of tasks works out to be 32.
  4. Using the COUNTIF(S) function(s), you can calculate the number of projects categorized under the subheadings as follows.
  5. Depending on the nature of projects you're using filters for, make sure to include the appropriate data columns in your Pivot Table worksheet—for active projects, it makes sense to use the Progress column, whereas for canceled projects, use the Cancelled column.
    Calculating the count of projects using the COUNTIFs function

4. Use Visual Elements, Charts, and Graphs

With the help of the appropriate formulae and visual formatting, you can create meaningful charts or graphs that help you better understand your data. In this example, let's try to add a chart for completed projects; follow the steps listed below:

  1. In your Calculations sheet, look at cell B7 as shown above.
  2. To plot your chart, start by selecting cells D7 and E7.
    Project counts in Excel using COUNTIFs
  3. Next, go to Insert > All Charts > Sunburst or Donut chart.
  4. Once the chart comes up, click on the plus (+) symbol on the right to access formatting options. Choose Chart Elements as you wish to.
  5. Click on different parts of the chart to bring up formatting options such as Format data series, plot area, etc. Using these; you can easily customize the colors, titles, borders, and other attributes for your chart.
  6. Once this is done, you can copy and paste the chart onto the top of your dashboard and align it accordingly.
    Creating a project dashboard in Excel

For the other rows, select the desired data in the Calculations sheet and repeat the process above for any other charts or graphs you wish to include.

5. Add Interactive Settings and Tabs

You can easily add elements such as heatmaps, icons, slicers, and scroll bars to your dashboard to make it more interesting, informative, and engaging for your audience. Let's take a closer look at some of them.

Insert Slicers for Interactivity

Slicers are short tabs that allow you to filter the data on your dashboard in real-time. Here's how to add them to your dashboard:

  1. Select the cells that contain your data.
  2. Navigate to the Insert tab > Filters > Slicer.
  3. Choose the type of slicer you want from the drop-down menu, then click OK.
  4. To align them horizontally, click on the Buttons section in a separate Slicer tab. You can also leave them as is, depending on how you want your dashboard to look.

For our dashboard, we'll add slicers for the headings—Managers and Projects. Here's how to do this:

Inserting slicers in Excel
  1. Select the data in your Calculations sheet and select these options from the drop-down menu.
  2. Next, you'll need to link slicers to your Pivot tables. To do this, right-click on the slicer, and click Report Connections.
  3. In the dialog box that appears, select all the PivotTables and Pivot charts and click OK.

Add Scroll Bars

Scroll bars allow you to view updated data, which is helpful if you're looking at something that's changing over time.

  1. To add a scroll bar, go to the Developer tab. Keep in mind that this option isn't enabled by default.
  2. To activate the Developer tab, go to File > Options > Customize Ribbon.
  3. Under the Main tab, check the Developer box.
  4. To insert the scroll bar, go to the Developer tab > Insert > Form controls. Hover the mouse over the options and select the Scroll bar.
  5. Drag it and place it in the row or column of your choice in your Dashboard

Add a Quick (Project) Overview Tab

To get a quick view of project details, you can import the data from the Pivot Table into your dashboard. We'll be using the INDEX function, as shown below. Our data array consists of cells B8 through K40 in the Pivot Table Tab.

To bring up the data from our pivot table, we'll input the function:

=INDEX('Pivot Tables'!$B$8:$K$40,'Pivot Tables'!$B$6 + ROWS(Dashboard!$A$13:F17)-1,COLUMNS(Dashboard!$A$13:F17))

Now, our dashboard looks something like this:

Project overview tab in Excel

Apply Conditional Formatting

Now that we've created a quick project overview tab, you might want to highlight project update percentages. You can easily use conditional formatting to apply color scales or data bars to do this. This will allow you to see at a glance which projects are completed and which ones need more work.

Here, we've used a three-color scale to highlight the progress of individual projects, as shown below:

Using conditional formatting in Excel

Add Heatmaps to View Values Between Dates

Heatmaps are visualization tools that can be useful to highlight patterns within your data. For example, you could use a heatmap to show the number of times visitors clicked on different parts of your webpage.

First, you'll need to create a table with the data you want to see mapped out. In our example, we've calculated the project count between two dates using the COUNTIFS function, as shown below.

Heatmaps in Excel with conditional formstting

You can use conditional formatting to apply rules to different parts of your table. The completed dashboard would look like this:

Interactive dashboard in Excel

Interactive Excel Dashboards: Analyze and Communicate Key Metrics

Excel dashboards are a great way to visualize data, and we hope this guide can help you create one specific to your needs. Interactive and dynamic, these useful tools can aid decision-makers in a quick and easy analysis of relevant data.

With just a dash or two of creativity, Excel spreadsheets can go far beyond the norm, and you can create something truly engaging and presentable.