How to Build a Simple Dynamic Dashboard with FILTER, SORT and XLOOKUP

In this tutorial we will build a small but useful dashboard: you choose a region from a drop‑down and Excel updates a table and chart automatically.

We will use three modern tools together: FILTER for subsetting data, SORT for ordering it and XLOOKUP for pulling a few summary metrics into neat KPI cards.

Step 1: Prepare and name your source data

Start with a table called Sales with columns Date, Region, Product, Amount, formatted as an Excel Table via Insert > Table.

Having the data in a Table means the spilled formulas will automatically expand when you add new rows.

Step 2: Create a region selector

Pick a cell for the user’s selection, for example H2 and label it “Region”.
Then create a dynamic list of regions in another column using UNIQUE:

=SORT(UNIQUE(Sales[Region]))

Use this spilled list as the source for a data‑validation drop‑down linked to H2, so the user can pick any region.

Step 3: Filter the data for the selected region

In an empty area (for example starting in J4) create the main filtered table:

=SORT(
  FILTER(Sales, Sales[Region]=$H$2, "No data for this region"),
  1, 1
)

This formula returns only the rows where Region matches the selection in H2 and sorts them by Date in ascending order.

Because FILTER and SORT are dynamic array functions, changing the drop‑down instantly refreshes all the rows and keeps them nicely ordered.

Step 4: Add simple KPI cards using XLOOKUP

Above your filtered table, you can show a few quick KPIs, such as:
total sales, last sale amount and first sale date.

For total sales in the selected region you can simply use SUM on the Amount column of the filtered output:

=SUM(INDEX(J4#,0,4))

Here J4# refers to the spilled array, and column 4 is the Amount column, so the formula always sums the visible rows.

To pull the last sale amount you can combine INDEX with COUNTA, or use XLOOKUP on Date to grab the latest row.
A clear approach with XLOOKUP is:

=XLOOKUP(
  MAX(INDEX(J4#,0,1)),        
  INDEX(J4#,0,1),             
  INDEX(J4#,0,4)              
)

This looks up the maximum date in the filtered table and returns the matching Amount.

Step 5: Insert a chart that follows the filtered data

Select a few columns of the spilled output, for example Dates and Amount, then insert a line or column chart in the usual way.

As long as the chart’s series references the spilled range (for example =Sheet1!J4# for the data), the chart will automatically resize and change whenever the user picks a different region.

Step 6: Tidy up the layout

Once everything works, hide any helper columns, format the KPI numbers add a simple title like “Sales by Region” and maybe a color that changes with the selected region.

The result is a light‑weight dashboard that relies entirely on formulas, updates instantly and is easy to understand when someone else opens the file.