SolveWithExcel

Excel Challenge #14: Interactive Sales Matrix (Dropdown Filters + Dynamic Formulas)

Difficulty

Intermediate

Estimated Time

40–50 minutes

The Problem

Static dashboards answer fixed questions.
Interactive dashboards let users ask their own questions.

In this challenge, you’ll turn a sales matrix into an interactive report using dropdown filters (Data Validation) and dynamic formulas—without Pivot Tables.

Dataset

Use the same SalesData structure introduced in Challenge #12:

| Date | Region | Product | Sales |

(At least multiple regions, products, and dates.)

Your Task

Part A — Create Filter Controls

On a Dashboard worksheet:

  1. Add a Region selector (dropdown):
  • Values: All, North, South, East, West
  1. Add a Product selector (dropdown):
  • Values: All, Laptop Pro, Tablet Air, Phone Max

Use Data Validation → List.

Part B — Build the Dynamic Matrix

Create a Region × Product matrix that responds to the dropdowns.

Rules:

  • If Region = All, include all regions
  • If Product = All, include all products
  • Otherwise, filter based on selections

Use SUMIFS() with logical conditions.

Part C — Dynamic Totals

Add:

  • Row totals
  • Column totals
  • Grand total

All totals must update based on the selected filters.

Part D — Insight Callouts (Optional)

Add formulas that return:

  • Top Region (based on current filters)
  • Top Product (based on current filters)

Requirements

  • Use Excel formulas only
  • No Pivot Tables
  • Use Data Validation for filters
  • Report must update instantly when selections change

Learning Objectives

By completing this challenge, you will learn how to:

  • Build interactive Excel dashboards using dropdowns
  • Combine Data Validation with SUMIFS
  • Create flexible reports without Pivot Tables
  • Support exploratory analysis in Excel

Why This Matters

This pattern is used in:

  • Self-service dashboards
  • Management reporting
  • Ad-hoc analysis tools
  • Excel-based BI solutions

It is a major step toward fully interactive Excel dashboards.

Next Challenge

Excel Challenge #15: Date-Filtered Sales Summary (Start/End Date Controls)

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.