How to Link Excel Dropdowns to Filter Entire Dashboards Dynamically

Image by Editor
 

Interactive dashboards in Excel allow users to analyze data easily without modifying formulas or charts manually. One powerful way to enhance user interactivity is to link a dropdown to dynamically filter dashboard elements.

In this tutorial, we will show how to link Excel dropdowns to filter entire dashboards dynamically.

Let’s use sample sales data to build a sales dashboard with dropdown filters that automatically update the entire dashboard based on dropdown selections.

Step 1: Create an Excel Table

• Enter the sample data into a new Excel worksheet.
• Select all data, including headers.
• Go to the Insert tab >> select Table.
• Check My table has headers and click OK.

• Go to the Table Design tab >> name your table SalesData.

Step 2: Create a Dashboard Layout

• Create a new sheet named Dashboard.
• Add a title at the top: Sales Dashboard.
• Add labels for your filters.
  • Region:
  • Category:

Step 3: Create Basic Dropdown Filters

Create dropdown lists from the sales table. Always insert unique values in a dropdown list.

Create Region Dropdown:

• Select cell B2 (next to the “Region:” label).
• Go to Data tab >> select Data Validation.
• Under Allow >> select List.
• In Source, enter the following formula and click OK.

=UNIQUE(SalesData[Region])

Note: If UNIQUE doesn’t work, manually list regions in another column and refer to that range.

• Use the UNIQUE formula in another sheet to list all regions and categories.

Formula:

=UNIQUE(SalesData[Region])

=UNIQUE(SalesData[Category])

• Select cell B3 (next to the “Region:” label).
• Go to Data tab >> select Data Validation.

• Under Allow >> select List.
• In Source, refer to the cell range: SalesData!$I$2:$I$5
• Click OK.

Create Category Dropdown:

• Select cell B3 (next to the “Category:” label).
• Go to Data tab >> select Data Validation.
• Under Allow >> select List.
• In Source, refer to the cell range: SalesData!$J$2:$J$4
• Click OK.

Step 4: Add an “All” Option to Each Dropdown

To allow users to view all data, we’ll add an “All” option:

• In a blank area of the SalesData sheet, insert All in K1.                      `.
• In cell K2, insert the following formula.

Formula:

=UNIQUE(SalesData[Region])

• In a blank area of the SalesData sheet, insert All in L1.                      `.
• In cell L2, insert the following formula.

Formula:

=UNIQUE(SalesData[Category])

Create Named Range:

• Go to the Formulas tab >> select Name Manager >> click New.
• Name this range RegionList.
• In Refers to:  enter:
• Click OK.
• Do the same for the category and name it CategoryList.

• Change your Region dropdown Source to: =RegionList

• Change your Category dropdown Source to: =CategoryList

Step 5: Create Filtered Named Ranges

Now we’ll create formulas that filter the data based on dropdown selections.

• Go to the Formulas tab >> select Name Manager >> click New.
• Name it FilteredData.
• In Refers to: insert the following formula.
• Click OK.

=LET(
  regionFilter, IF(OR(Dashboard!$B$2="All", ISBLANK(Dashboard!$B$2)), TRUE, SalesData[Region]=Dashboard!$B$2),
  categoryFilter, IF(OR(Dashboard!$B$3="All", ISBLANK(Dashboard!$B$3)), TRUE, SalesData[Category]=Dashboard!$B$3),
  combinedFilter, IF(AND(Dashboard!$B$2="All", Dashboard!$B$3="All"), 
                     TRUE*ROW(SalesData[Region]), 
                     regionFilter * categoryFilter),
  filteredResult, FILTER(SalesData, combinedFilter),
  headers, CHOOSE({1,2,3,4,5,6,7}, "ID", "Region", "Category", "Product", "Sales", "Quantity", "Profit"),
  VSTACK(headers, filteredResult)
)

Step 6: Add Summary Statistics

Add some key metrics that will update automatically.

Insert KPI:

• Total Sales:

Formula:

=SUM(INDEX(FilteredData,,5))

• Total Profit:

Formula:

=SUM(INDEX(FilteredData,,7))

• Items Sold:

Formula:

=SUM(INDEX(FilteredData,,6))

• Format the sales and profit cells as currency.

Step 7: Create a Dynamic Chart

Let’s add a chart that updates based on your filter selections:

To display a filtered table:

• Select cell A6 on your Dashboard.
• Insert the following formula.

Formula:

=FilteredData

Sales by Region:

• Select the data from your FilteredData display.
• Go to the Insert tab >> from Charts group >> select Column Chart.
• Move the chart to a convenient location on your dashboard.

Step 8: Format Your Dashboard

Make your dashboard look professional:

• Add borders around your summary statistics.
• Use consistent colors and fonts.
• Add a title to your chart.
• Resize columns for better readability.

Step 9: Test Interactivity

Now, select a value from a dropdown:

• Region: North.
• Category: Electronics.

Now, the entire dashboard will update dynamically.

• The FilteredData named range recalculates instantly.
• All connected formulas update automatically.
• The table of filtered data refreshes.
• The chart updates to show only the filtered data.

Troubleshooting Tips

• #SPILL! error: Make sure no cells are blocking where your dynamic arrays need to expand.
• Dropdowns not working: Verify your table name and column references.
• Charts not updating: Make sure your chart is based on the filtered data.

Conclusion

By following the above steps, you can create a dynamic dashboard that filters with simple dropdown selections. This makes the data easier to analyze without complex Excel skills. The Named Range also plays a crucial role in making the dropdown filter dynamic. As you become more comfortable with these techniques, you can add more filters, charts, and features to create increasingly powerful dashboards.