---
title: "Build a self-filtering Excel dashboard with FILTER, SORT, and structured table references, no PivotTable required"
date: 2026-08-04T00:00:00Z
updated: 2026-07-30T09:40:02Z
tags: ["Excel", "Dynamic Arrays", "Dashboards", "Getting Started", "Structured Tables"]
canonical: https://bianca.codes/blog/build-a-self-filtering-excel-dashboard-no-pivottable-required/
---

# Build a self-filtering Excel dashboard with FILTER, SORT, and structured table references, no PivotTable required

_No refresh button, no PivotTable, just a dropdown and a formula that already knows your table changed._

The deliverable: a dashboard block that reshows the right rows the moment your source table changes, no refresh button, no right-click "Refresh," no PivotTable at all. You type a name into a dropdown, and the list below it re-sorts and re-filters itself. Add a new row to the source data and the dashboard already knows about it.

You need Excel with dynamic arrays (Microsoft 365, or Excel 2021+) and one range turned into a proper structured Table with Ctrl+T. If your "table" is still just a range with headers and borders, stop here and fix that first, because everything below depends on it.

## Why this approach

A PivotTable does something similar, but it's a snapshot. It doesn't know your data changed until you tell it to refresh, and half the people who inherit your workbook never will. FILTER and SORT stacked over a structured Table reference are different: they recalculate the instant the source changes, the same way any other formula does, because they _are_ formulas. No refresh button to forget, no "why is this dashboard showing last week's numbers" conversation three weeks from now.

The structured Table reference is the part people skip, and it's the part that actually matters. `Tasks[Status]` isn't just a tidier way to write `C2:C500`. It's a reference to a column that grows and shrinks with the table, so a formula built on it doesn't need updating when someone adds row 501. Build the same dashboard over a hardcoded range and you've built something that quietly breaks the first time the table outgrows it.

## Step 1: Turn your range into a real Table

Select your data (headers included) and press Ctrl+T. Name it something meaningful in the Table Design tab, not "Table1". This build uses a table named `Tasks` with columns Task, Owner, Status, DueDate, and Priority.

This step is the one people are tempted to skip because the range already "looks like" a table. It isn't one until Excel treats it as an object. Everything downstream depends on referencing `Tasks[Owner]` instead of `C2:C500`.

## Step 2: Build the owner dropdown from the table itself

```vba
=SORT(UNIQUE(Tasks[Owner]))
```

Put this in a helper cell and point a data validation list at the spill range (name the cell, then use the named range as the list source, or reference the spill with `#`). The dropdown now lists whoever is currently in the Owner column, in alphabetical order, with no manual maintenance. Add a new team member to the table and they show up in the dropdown next time someone opens it.

## Step 3: Filter and sort the dashboard block

Say the dropdown cell is `$B$1`.

```vba
=SORT(
    FILTER(Tasks, (Tasks[Status]<>"Done") * (Tasks[Owner]=$B$1), "Nothing open for this person"),
    4,   -- sort by column 4 of the returned array (DueDate)
    1    -- ascending, soonest due date first
)
```

Read it right to left. FILTER scans the table row by row, keeps the rows where status isn't "Done" and owner matches the dropdown, and returns them as an array. SORT takes that array and orders it by column 4, which is DueDate _within the array FILTER returned_, not within the original table. Get that column index wrong and you'll sort by the wrong field, quietly, with no error to tell you.

The `*` between the two conditions is doing AND logic - both have to be TRUE for a row to survive. Swap it for `+` if you ever need OR logic instead.

## Step 4: Add a live KPI that doesn't depend on the filtered array

```vba
=COUNTIFS(Tasks[Status], "<>Done", Tasks[Owner], $B$1)
```

This gives you an "open tasks" count for a KPI cell without referencing the FILTER output at all, which matters more than it sounds like it should. If the filtered list is empty, COUNTIFS still returns a clean 0. Reference the FILTER array's row count instead and an empty result (which returns the "Nothing open for this person" text, not an array) breaks the count formula in a way that's annoying to debug at 4pm on a Friday.

## Step 5: Build the second view, overdue and unsorted-by-owner

A second block, this time filtering across everyone rather than by dropdown:

```vba
=SORT(
    FILTER(Tasks, (Tasks[DueDate]<TODAY()) * (Tasks[Status]<>"Done"), "Nothing overdue"),
    5,   -- sort by Priority, the 5th column FILTER returned
    -1   -- descending, highest priority first
)
```

Same mechanism, different filter condition. This is the part that makes the dashboard feel alive rather than just personalised: one block answers "what's mine," the other answers "what's actually late," and neither one needs a separate PivotTable or a manual refresh to stay honest.

## Common mistakes

### **Referencing the range instead of the Table.**

`FILTER(A2:A500, ...)` looks identical to `FILTER(Tasks[Task], ...)` right up until row 501 gets added. The hardcoded range silently excludes it. There's no error, no warning, just a dashboard that's slowly, invisibly wrong. If you ever catch yourself typing a cell range instead of `Tasks[ColumnName]` in this build, that's the bug already happening.

### **Column indices in SORT counting from the wrong array.**

SORT's column argument counts columns in whatever FILTER handed it, not in the original table. If FILTER returns all five columns of Tasks, column 4 is DueDate. If you later change FILTER to return only three columns, column 4 doesn't exist anymore and SORT throws a `#VALUE!` that has nothing obviously to do with the change you just made.

### **A static dropdown list instead of the dynamic one from Step 2.**

Typing a fixed list into Data Validation feels faster the first time and turns into a maintenance job by the third new hire. The whole point of building the dropdown from `SORT(UNIQUE(Tasks[Owner]))` is that it never needs touching again.

## Frequently Asked Questions

### **Does this work if my table lives on a different sheet from the dashboard?**

Yes. Structured references work across sheets the same way normal cell references do - `Tasks[Owner]` resolves correctly whether the table is on the same tab as the FILTER formula or three tabs away.

### **What happens when someone deletes a row from the Tasks table?**

The FILTER and SORT formulas recalculate automatically, the same as any formula referencing changed cells. You don't need to do anything. This is the entire point of building it this way instead of with a PivotTable.

### **Do I need Microsoft 365 for this, or does Excel 2019 work?**

You need dynamic arrays, which shipped in Excel 2021 and every Microsoft 365 subscription. Excel 2019 and earlier don't have FILTER, SORT, or UNIQUE as worksheet functions at all - you'd be back to helper columns and COUNTIFS gymnastics, which is exactly what this build is replacing.

### **Can I skip the dropdown and just show everyone's tasks at once, grouped by owner?**

Not cleanly with FILTER and SORT alone - grouping by owner into separate visual sections is a PivotTable's actual strength. This build is for a personalised or role-specific view; if you genuinely need every owner's list on one page grouped by owner, that's the PivotTable's job, not this one's.

### **Does FILTER slow down on a table with tens of thousands of rows?**

Noticeably, if the filter condition passes most of the rows through. On a table with a tight condition (few rows survive, like "assigned to me and not done"), it stays fast. If you're filtering a near-full table repeatedly, the SORT step is usually the expensive part, not FILTER.

### **What if two people need different dashboard views from the same workbook?**

Duplicate the dashboard block per person, each pointed at its own dropdown cell, or give each person their own sheet with the dropdown pre-set to their name. The underlying Tasks table stays single-sourced either way - you're only duplicating the two formulas, not the data.

## Where to go from here

If you want the mechanism underneath this - what actually changed in Excel's calculation engine to make stacking FILTER inside SORT possible, and where spill ranges break down - I've covered that already in [Dynamic arrays: FILTER, SORT, UNIQUE - what changed](/blog/dynamic-arrays-filter-sort-unique-what-changed/).

The structured table reference itself, `Tasks[Owner]` versus a plain range, deserves its own explanation of why it works the way it does. That's a separate Magic Monday post landing later this month.