exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

How-to guides

How to filter data by a condition in Excel

Show only the rows that meet a condition, such as region equals East or amount over 500, with the Filter button or the FILTER function.

A filter hides every row that fails a test, so you can read or copy just the rows you care about. Nothing is deleted, and clearing the filter brings everything back.

The Filter button is the quick way to look at part of a table. The FILTER function does the same job in a formula and writes the matching rows somewhere else.

Steps in Excel for Windows

Shortcut: Ctrl+Shift+L

  1. Turn the filter on. Click any cell in the table and press Ctrl+Shift+L, or choose Data, Filter. Drop-down arrows appear in the header cells.
  2. Open the column's drop-down. Click the arrow in the column you want to test, for example Amount.
  3. Choose a filter type. For text columns the menu shows Text Filters, with Equals, Begins With, Contains and more. For numbers it shows Number Filters, with Greater Than, Between, Top 10 and others. For a single value, just tick it in the list.
  4. Enter the condition. Pick Greater Than, type 500, and click OK. Rows that do not qualify are hidden, not deleted, and the row numbers of the rest turn blue.
  5. Add conditions in other columns. Filter a second column and Excel shows only rows that meet both. Filters across columns combine with AND.
  6. Clear the filter. Data, Clear removes the conditions and shows every row again. Alt, A, C does the same. Press Ctrl+Shift+L to remove the drop-downs.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

1. Amount greater than 500

Before
ABC
1OrderRegionAmount
21001East320
31002West640
41003East710
51004North150
61005West520
After
ABC
1OrderRegionAmount
31002West640
41003East710
61005West520

Orders 1002, 1003 and 1005 pass the test, so only those rows stay visible. Notice the row numbers on the left: 3, 4 and 6. The hidden rows 2 and 5 are still in the sheet, just out of sight.

2. Region equals East

Before
ABC
1OrderRegionAmount
21001East320
31002West640
41003East710
51004North150
61005West520
After
ABC
1OrderRegionAmount
21001East320
41003East710

Two orders are in the East, in sheet rows 2 and 4. A filter on text ignores capitals, so "east" would match too.

3. The same condition as a formula

The same condition as a formula. The formula =FILTER(A2:C6,C2:C6>500) in cell E2 returns a block starting with 1002.
ABCDEFG
1OrderRegionAmount
21001East3201002West640
31002West6401003East710
41003East7101005West520
51004North150
61005West520

In Microsoft 365 and Excel 2021, FILTER returns the matching rows to a separate area and leaves the source alone. The formula in E2 spills three rows and three columns.

Common mistakes

  • Thinking filtered rows are gone. They are only hidden. If you copy a filtered range, Excel copies just the visible rows, which is helpful. If you delete rows, you delete only the visible ones.
  • Filtering a range with blank rows. A blank row inside the data stops the automatic range. Excel filters only the block above or below it. Remove blank rows first or select the whole table before turning the filter on.
  • Forgetting a filter is on. A funnel icon on a drop-down and blue row numbers show an active filter. The status bar reads something like 3 of 5 records found. A total that looks too small often means a forgotten filter.
  • Using SUM on a filtered list. SUM adds hidden rows too. To add only what is visible, use SUBTOTAL with 109 as the first argument.
  • Text that looks like a number. When a column of figures is mostly text, Excel offers Text Filters instead of Number Filters. That is a sign the numbers are stored as text, so convert the column before filtering.

On Excel job tests

Prompts may say "show only the orders from the East region", "display orders over $500" or "filter to the last quarter". The first wants a simple filter, the second a Number Filter with Greater Than, the third a Date Filter.

A prompt that says "without using the filter button" or "in a separate area" is asking for FILTER. If the task asks for a total of the visible rows, SUBTOTAL(109, range) is the answer rather than SUM.

  • FILTERReturns the rows of a range that meet a condition, spilling the results into neighboring cells.
  • COUNTIFCounts the cells in a range that meet one condition.
  • SUMIFAdds the values in a range that meet one condition.
  • sort by multiple columns in ExcelOrder a table by one column and then break ties with a second, or a third, using the Sort dialog and Add Level.
  • remove duplicates in ExcelDelete repeated rows with the Remove Duplicates command, choose which columns decide what counts as a repeat, and check the result before you trust it.

Questions

What is the shortcut to turn a filter on and off?
On Windows, Ctrl+Shift+L toggles the filter drop-downs. Alt, A, C clears the conditions without removing the drop-downs.
How do I filter by two conditions on the same column?
Open the Number Filters or Text Filters menu, choose Custom Filter, and enter two conditions joined by And or Or.
Does filtering delete the other rows?
No. The other rows are hidden and come back when the filter is cleared.
How do I total only the filtered rows?
Use =SUBTOTAL(109,range). The 109 tells Excel to sum and skip rows hidden by a filter.

Practice this

The free lesson AutoFilter has checked exercises for this skill in a working grid.

Practice this skill