Filters let you narrow a big table down to just the rows you care about. Instead of going to Data, then Filter, one shortcut toggles the filter buttons on your headers.
Step by step
- Click any cell inside your data (make sure the top row contains headers).
- Press Ctrl+Shift+L. Filter arrows appear on each header.
- To open a filter menu from the keyboard, select a header cell and press Alt+Down Arrow.
- Press Ctrl+Shift+L again to remove the filters and show all rows.
Tips & troubleshooting
- If filters apply to the wrong range, select the exact range first, then press Ctrl+Shift+L.
- Formatting your data as a Table (Ctrl+T) adds filters automatically, and they grow with your data.
Run the whole filter from the keyboard
Turning the filter on is only the first step. With a header cell selected, Alt+Down Arrow opens that column's filter menu, and every item in the menu has its own key.
- E jumps to the search box. Type part of a value and press Enter to keep only the matching rows.
- Down Arrow moves through the tick list, and Space ticks or unticks the highlighted item.
- S sorts smallest to largest (or A to Z) and O sorts largest to smallest.
- F opens the Text, Number or Date Filters submenu for conditions such as Contains or Greater Than.
- C clears the filter from that one column.
- Alt+A, then C, clears every filter on the sheet but leaves the filter buttons in place. Ctrl+Alt+L reapplies the filter after the data has changed.
Other ways to switch filters on
- Data tab: click Filter in the Sort & Filter group.
- Home tab: click Sort & Filter at the right-hand end, then Filter.
- Format the range as a table with Ctrl+T. Tables get filter buttons automatically and include new rows as you add them, which a plain filtered range does not always do.
How to tell a filter is active
Excel gives three signals. The button on a filtered column changes from an arrow to a funnel, the row numbers down the left turn blue with gaps where rows are hidden, and the status bar at the bottom shows how many records were found. If numbers on a sheet look wrong, check for these first: someone may have left a filter on.
Common problems
- The filter stops part-way down. Excel treats a completely blank row as the end of the list. Delete the blank row, or select the whole range before pressing Ctrl+Shift+L.
- The first row of data has become the header. Your list has no header row, so add one and reapply.
- Only one filter per sheet. A worksheet can hold one filtered range. To filter two lists on the same sheet independently, make each one a table.
- Totals include hidden rows. SUM adds filtered-out rows too. Use SUBTOTAL instead; pressing Alt+= below a filtered column inserts it for you.
On a Mac and in Excel for the web
In Excel for Mac press Cmd+Shift+F or Control+Shift+L. In Excel for the web the Filter button is on the Data tab, in the same place as the desktop app.