Summary of "Excel Filter Basics (for quick data analysis)"

Excel Filter Basics — Key Ideas

Filters let you quickly hide rows in large tables so you can focus on a subset of data without deleting anything.
Multiple ways to apply and clear filters: keyboard shortcuts, ribbon commands, and right-click options.
Filters can be applied by exact value, search/wildcards, text conditions (contains), color, and date (built-in ranges or custom ranges).
You can copy only visible (filtered) rows and paste them elsewhere.
The worksheet status bar shows aggregate values (sum, count, average) for selected visible cells. Standard SUM() includes hidden rows, so use SUBTOTAL or AGGREGATE to compute totals that ignore filtered rows.
Converting a range to an Excel Table (Ctrl+T) adds built-in filtering plus benefits like a Total Row, structured references, automatic expansion, and automatic updates to charts and pivot tables.
Advanced Filter (a different feature) is on the Data tab and offers more advanced filtering options (covered separately).

Click anywhere inside the data range and press Ctrl+Shift+L (toggle).
Or: Home tab → Sort & Filter → Filter.
Or (quick right-click option): Right-click a cell → Filter → Filter by Selected Cells’ Value.

Press Ctrl+Shift+L again to remove the filter icons.
To clear filters while keeping the filter UI: Home → Sort & Filter → Clear.
Alternate method: press Ctrl+Shift+L twice — once to remove all filters, again to reapply a fresh (cleared) filter setup.

Click the filter arrow on a column header → check/uncheck items to select only the values you want → OK.

* is the wildcard character:
- shirt* — values starting with “shirt”
- *shirt — values ending with “shirt” (e.g., “blue shirt”)
- *shirt* — values containing “shirt”
Typing text with wildcards in the search box will auto-select matching items in the checklist.

Column header → Text Filters → Contains → enter text (e.g., “site”) → OK.
Use Custom Filter to combine conditions with OR:
- Column header → Text Filters → Custom Filter → set Condition 1 (e.g., contains “blue”) OR Condition 2 (e.g., contains “white”) → OK.

Click the column’s filter arrow → Filter by Color → choose the cell/font/fill color used.

Click the filter arrow on a date column → choose built-in date filters (e.g., This Month, Last Month, Year-to-Date).
For a specific range: choose Between → opens Custom AutoFilter with date pickers → set From and To dates → OK.

Select the filtered area (Ctrl+A while the filtered table area is active) → Ctrl+C → go to a new sheet → Ctrl+V. Only visible rows are copied.

Select visible cells; Excel’s status bar shows Sum/Count/Average of the selection.

Example: =SUBTOTAL(9, range) where 9 specifies SUM. SUBTOTAL excludes filtered-out rows.
AGGREGATE offers additional options and functions that can ignore hidden rows and errors.

Table benefits:

Built-in filter arrows on headers.
Optional Total Row (Table Design → Total Row). You can choose a function per column (Sum, Count, etc.) — totals respond to filters.
Structured references and automatic expansion of formulas when new rows are added.
Charts and PivotTables referencing the table will update automatically as the table grows.

Filtered-out rows are hidden, not deleted — row numbers show blue when some rows are hidden.

The status bar aggregate is a quick check, but formulas like SUM(range) still include hidden rows; use SUBTOTAL or AGGREGATE to ignore filtered rows.
Using Excel Tables is recommended for ongoing datasets because of dynamic ranges and better integration with charts and PivotTables.
Wildcards (*) are supported in the filter search box; for text filters you can also use the built-in “Contains” or other operators.

Advanced Filter is a separate feature located on the Data tab (not on Home). It provides more complex filtering capabilities and will be covered separately.