Office Dễ Hiểu

How to use FILTER in Excel

Build a dynamic task list from a status selected in another cell.

FILTER returns the rows that satisfy a condition. Use the status in E2 to filter the source table A2:C4.

Visual walkthrough

ExcelE5FILTER

Before

Three source rows. E2 contains In progress.

After

E5:G6 contains HS01 and HS03.

Check the result

Change E2 to Done. Only HS02 should remain.

Try it here

Change the selection to see the result and matching data rows.

=FILTER(A2:C4,C2:C4=E2,"No records")
CodeTaskStatus
HS01Submit documentsIn progress
HS02Send minutesDone
HS03ReconcileIn progress
ResultRecords HS01 and HS03
Code (A)Task (B)Status (C)
HS01Submit documentsIn progress
HS02Send minutesDone
HS03ReconcileIn progress

If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.

Step-by-step instructions

  1. Enter the sample task table in A1:C4.
  2. Type In progress in E2 and enter the formula below in E5.
    =FILTER(A2:C4,C2:C4=E2,"No records")
  3. Keep E5:G6 clear so the results can spill into adjacent cells. HS01 and HS03 should appear.
  4. Change E2 to Done to show only HS02. A status with no matches should show No records.

Frequently asked questions

What does #SPILL! mean?

Excel cannot place the array results. Check for existing values, merged cells or another obstruction in the expected output area.

Reference: Microsoft Support