Filtering Data - Focus on relevant records
Use AutoFilter to show open invoices above a threshold while keeping other records in the source and understanding visible totals.
By the end, you can
- Apply filters on more than one source field.
- Distinguish hidden rows from deleted records.
- Inspect the scope of totals after filtering.
Before you start: Use a rectangular source with a header row. Steps use Windows desktop Excel.
In this lesson
A view of the source, not its deletion
AutoFilter temporarily hides source rows that do not meet selected criteria. Collections can focus on open invoices worth at least ₹1,000 without deleting paid or smaller invoices. Filters on different columns work together, so a record must meet the active rules. Clearing them restores the hidden source rows.
Before: a complete invoice source
I101| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Status | Amount INR |
| 2 | I101 | Open | 1200 |
| 3 | I102 | Paid | 800 |
| 4 | I103 | Open | 2500 |
| 5 | I104 | Open | 300 |
| 6 | I105 | Paid | 1500 |
Apply status and amount filters
- Click inside A1:C6 and choose Data > Filter, or press Ctrl+Shift+L in Windows desktop Excel.
- Open the Status header arrow, clear Select All, tick Open, and confirm.
- Open the Amount INR header arrow and choose Number Filters > Greater Than Or Equal To. Enter 1000 and confirm.
- Inspect I101 and I103 as the two visible qualifying records. Other source rows are hidden, not removed.
- To restore all records, choose Data > Clear in Sort & Filter, or clear each column's filter. Reapply after changing data if the view needs updating.
I101| A | B | C | D | |
|---|---|---|---|---|
| 1 | Original worksheet row | Invoice | Status | Amount INR |
| 2 | 2 | I101 | Open | 1200 |
| 3 | 4 | I103 | Open | 2500 |
Check the view and its totals
- The visible qualifying amounts total 3700; all source amounts still total 6300. Ordinary SUM can include filtered-out source values, so choose an appropriate visible-row aggregate such as SUBTOTAL when needed.
- Use text, number, or date filters according to the actual field type. Seeing Text Filters on an amount field is a clue to inspect imported text amounts.
- A filter matches existing records. It does not correct statuses, deduplicate customers, or rank rows unless a separate operation is applied.
- The FILTER formula builds a separate returned array, while AutoFilter changes which rows of the original range are visible.
Common mistakes and troubleshooting
The custom workspace does not recreate native AutoFilter header menus. Its FILTER lesson links to a compatible formula task instead; no native-filter challenge is invented here.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.