How to Filter Data in Excel (Ctrl+Shift+L)

To filter data in Excel, click a cell in your table and press Ctrl+Shift+L (Mac: ⌘⇧F), or choose Data > Filter. An arrow button appears in each header cell, and you use it to show only the rows you need, such as one department or amounts over $1,000. Nothing is deleted. This guide covers the clicks, the conditions, totals that respect the filter, and the FILTER function.

The examples use a list of 10 expense claims in A1:D11, with the columns Employee, Department, Region and Amount.

How to filter data in Excel#

First, check that your data has a header row: a top row with a name for each column. Then turn on the filter buttons:

1. Click any cell inside the data.
2. Press Ctrl+Shift+L. On a Mac, press ⌘⇧F, or ⌃⇧L (Control, not Command).
3. Or click Data > Filter, or Home > Sort & Filter > Filter.

A small arrow button appears in every header cell. Press the same shortcut again to remove the buttons and clear every filter at once.

Excel guesses the range from the cell you click. A blank row in the middle stops it, so the rows below the gap aren't filtered. Remove blank rows first, or select the whole range before you press the shortcut. If your data is an Excel table (Ctrl+T, Mac: ⌘T), the buttons are already on.

Filter by one or several values#

The value list is the quickest filter. To show only the Sales department:

1. Click the arrow in the Department header.
2. Untick (Select All). This clears every box at once.
3. Tick Sales.
4. Click OK.

To show Sales and Marketing, open the same arrow again and tick Marketing too. Rows that match any ticked value show. In a long list, type in the Search box to find a value quickly.

Filter by number, text or date conditions#

For a condition instead of a list of values, use the condition menus. Their names depend on the column type:

To show amounts over $1,000: click the arrow in Amount, choose Number Filters > Greater Than, type 1000 (no $ or comma), and click OK. For two conditions in one column, choose Custom Filter and join them with And or Or, such as "greater than 500 and less than 1000". If you used colors to mark cells, Filter by Color shows only those rows; our guide to conditional formatting in Excel shows how to color them by rule.

Filter more than one column#

You can filter several columns at once, and a row shows only if it passes every filter. Keep Sales and Marketing in Department, and add Amount greater than 1,000. Four rows are left:

So filters on different columns work like AND, and ticked values within one column work like OR.

How to tell a filter is on, and how to clear it#

A filtered table gives three signs:

• A funnel icon on the arrow of each filtered column.
• Blue row numbers that skip the hidden rows.
• A message in the status bar at the bottom, like "4 of 10 records found".

To clear one column, open its arrow and choose Clear Filter From "Amount". To clear all of them, choose Data > Clear, or press Ctrl+Shift+L (Mac: ⌘⇧F) twice: once to remove the buttons, once to turn them back on.

Reapply, sort and copy filtered data#

Three habits save time once a filter is on:

• Reapply after editing. A filter doesn't re-check rows when you change their values. If you edit an amount so it no longer matches, the row stays visible until you choose Data > Reapply (Windows shortcut: Ctrl+Alt+L).
• Sort from the same menu. Every filter arrow also has Sort A to Z and Sort Smallest to Largest, so you can filter to Sales and then sort the claims by amount without leaving the menu.
• Copy only what you see. When you select a filtered range and copy it, Excel copies only the visible rows. Paste it into a new sheet to send a manager just the rows they asked for.

Deleting is different: if you select visible rows and delete them, only those rows go, but they are gone for good. Undo with Ctrl+Z (Mac: ⌘Z) if you didn't mean it.

Total only the visible rows with SUBTOTAL#

A normal SUM adds hidden rows too. With the filter above on, =SUM(D2:D11) still shows $10,820, the total of all 10 claims. To add only the rows you can see, use SUBTOTAL with 109:

=SUBTOTAL(109,D2:D11)

Now the result is $5,980, the four visible claims. Change the filter and it updates. =SUBTOTAL(103,A2:A11) counts the visible rows. Put these formulas above or below the data, not in rows that might be hidden. Our SUBTOTAL function guide explains every function number.

The FILTER function: a formula alternative#

The FILTER function returns the matching rows in a new place, instead of hiding rows in the original. It needs Excel 2021 or Microsoft 365:

=FILTER(A2:D11,B2:B11="Sales")

This lists every Sales claim, and the results spill into the cells below and to the right. It updates as soon as the data changes, with no clicks. To combine conditions, multiply them: =FILTER(A2:D11,(C2:C11="North")*(D2:D11<1000)) returns the North claims under $1,000. See the FILTER function guide, and FILTER function vs AutoFilter for when to use each.

Filtering is one of the everyday Excel skills for admin jobs. If your list sits on another tab, you can still total it from a summary sheet; see how to reference another sheet.

Practise it#

Filters are quick to learn by doing. In ExcelReady's filter a table lesson, you turn on the filter buttons with the shortcut, filter by one and several departments, add a number condition, total and count the visible rows with SUBTOTAL, clear everything, and then answer a manager's question with two filters, all in a live spreadsheet. Practise filtering data in the lesson.