Filtering Data

Filtering hides rows that do not match. The data is entirely intact and returns when the filter is cleared — which is what makes it safe. But SUM and AVERAGE include hidden rows, because they operate on the range, not on what happens to be visible.

=SUBTOTAL(109, E2:E900) is the fix: it sums only visible rows and updates as the filter changes. The 109 specifies sum-ignoring-hidden-rows. There is a parallel code for each function — 101 for average, 103 for count. Converting the range to an Excel Table gives you a Total Row that uses SUBTOTAL automatically.

When a worksheet has a lot of rows, it’s hard (and honestly pointless) to scroll forever hunting for what you need. Filters let you temporarily hide rows that don’t match your criteria—without deleting anything.

Filters are perfect for finding items by category, narrowing to specific dates, or showing only records that meet a numeric requirement (like loan amounts over $100).

Filtering works best when your data has a clear header row (column names like ID#, Type, Item Description, etc.). Excel uses these headers to label each filter dropdown.

To enable filtering, go to the Data tab and click the Filter button. Excel adds a dropdown arrow to each header cell.

Once filters are on, every column header gets a dropdown arrow you can click to filter that column.

You can also access Filter from the Home tab under Sort & Filter (different path, same tool).

The simplest filter is the checkbox list. You open the filter dropdown, uncheck everything, then check only what you want to see.

Example: Filter the Type column to show only Laptop and Projector.

First, uncheck Select All so you can start from a clean slate.

Then check only the values you want and click OK. Excel hides everything else.

Filters are cumulative—meaning each new filter further narrows your results. This is how you go from “a lot of rows” to “exactly what I need.”

Example: If you already filtered Type to Laptop + Projector, you can ALSO filter Checked Out to show only items checked out in August.

After applying that second filter, you’ll see a smaller subset that matches BOTH criteria.

When you’re done with a filter, don’t panic-scroll. Just clear it.

Open the dropdown for the filtered column and choose Clear Filter From [Column Name]. That restores the hidden rows for that column’s filter.

If you want to remove ALL filters from the sheet at once, click Filter again on the Data tab to toggle filtering off.

Search is the fastest way to filter when you’re looking for one brand, keyword, or repeated term.

Open the filter dropdown for the column you want (like Equipment Detail), then type into the search box. Excel narrows the checkbox list automatically as you type.

Example: Type saris to show only Saris items.

When checkboxes and search aren’t specific enough, use Text Filters. These let you filter based on rules like Begins With, Ends With, Contains, or Does Not Contain.

Example: Exclude items that contain the word laptop.

This opens a Custom AutoFilter box where you type the word or phrase and confirm.

Number Filters are used for columns with numeric values like ID numbers, quantities, prices, or loan amounts.

You can filter values greater than, less than, between two numbers, top 10, above average, and more.

Example: Show only records with ID numbers between 3000 and 6000.

Date filters are built for time-based data. You can filter by year, month, or use built-in ranges like Last Week, Next Month, or Between two dates.

Example: Filter Checked Out to show only items checked out between two specific dates.