In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current data range. On a Mac, the usual shortcut is Command+Shift+F. Excel adds filter arrows to the header row; click an arrow to choose which records to show.
What the shortcut does
The shortcut toggles Excel’s AutoFilter feature. When you use it in a list or table, Excel displays a small dropdown arrow in each column heading. Those arrows open options for filtering and sorting that column. A filter changes which rows are visible; it does not delete the rows that do not match.
For example, imagine a list with the columns Region, Product, and Sales. Turn on AutoFilter, open the Region arrow, and select West. Excel hides the rows for other regions and leaves the West records visible. The row numbers at the left may skip, which indicates that rows are hidden by the filter rather than removed.
The shortcut is a toggle, not a command that only switches filtering on. Press it again to turn AutoFilter off. If your goal is to keep the arrows but show every record, clear the filter criteria instead of toggling AutoFilter off.
How to apply it to a range
For a straightforward list, click any cell inside the data and press Ctrl+Shift+L. Excel usually detects the connected data region and places arrows on its header row. You can also select the full range, including the headers, before using the shortcut. The header row should contain a distinct label for each column, such as Date, Customer, or Status.
- Check that the list has one header row and that each column has a heading.
- Click a cell in the list, or select the whole range including its headings.
- Press
Ctrl+Shift+Lon Windows, orCommand+Shift+Fon Mac. - Click a header arrow and choose a text, number, date, or other filter option.
If the list is separated by blank rows or columns, Excel may detect only part of it. Select the intended range explicitly in that case. A fully blank row within a list can also make Excel treat the data below it as a separate block, so remove accidental breaks or select the complete range before applying the filter.
Filtering versus clearing a filter
Each column’s dropdown lets you control that column’s criteria. For example, filtering a Status column to show Open and Pending records hides other statuses. Filtering a second column as well narrows the visible results further: the rows must satisfy the active filters together. This is useful for finding, say, open orders in one region.
To show every value in a column again without removing the dropdown arrows, open that column’s dropdown and choose Clear Filter From followed by the column name. To clear filters across the range, use Data > Sort & Filter > Clear in Excel for Windows. The command clears filtering criteria; it does not remove the data. To remove the dropdown arrows themselves, use Ctrl+Shift+L again.
Filtering and sorting are different operations. A filter hides records that do not meet a condition, while sorting changes the order of records. Clearing a filter does not necessarily undo a sort, so check the order of the rows separately if the list no longer appears in its original sequence.
Using the shortcut with Excel tables
Excel tables are often the best choice for regularly updated lists. Select a cell in the data and press Ctrl+T to create a table, confirm the range, and indicate whether it has headers. A table normally displays filter arrows in its header row automatically. When the table is extended with new rows, its formatting and filtering structure can extend with it.
The table feature is separate from the shortcut for a plain range. If a table already has its header arrows, there may be no need to press the filter shortcut. Table-specific display options are available on the Table Design tab, including the Filter Button setting. If arrows are missing from a table, check that setting and confirm the table has a header row.
A table also helps avoid a common range-selection problem: when you add records directly beneath it, Excel can include them as part of the table. For a one-time report or a carefully defined range, AutoFilter on a normal range may be enough; for a list that grows over time, a table is often more convenient.
Filtering by text, numbers, and dates
The menu options depend on the type of values in a column. Text columns offer choices such as selecting particular labels or using text conditions. Number columns can be filtered by conditions such as greater than, less than, or between. Date columns offer date-related groupings and conditions. If a column mixes data types, Excel may show a less consistent set of options.
For instance, in a Sales column, choose the dropdown, then Number Filters and a condition such as Greater Than, and enter 1000 to display records above that amount. In a date column, a date filter can help isolate a period without manually selecting every individual date. Menu labels can vary slightly with the type of data and Excel version, but the filter arrow is the starting point.
You can also select or clear individual values in the dropdown list. This is handy for categories such as Department, where choosing Finance and Support may be simpler than creating a custom condition. If the list is long, use the search box in the dropdown when it is available to find a specific value.
When the shortcut does not work
If Ctrl+Shift+L appears to do nothing, check the range and the active Excel window first. The current cell should be in the data, or the intended range should be selected. If the worksheet is protected, some filtering actions may be restricted; protection settings determine whether users can use AutoFilter on a protected sheet.
- No arrows appear: Confirm that you selected the actual data range and that the first row contains headings. Try selecting the range manually.
- Only part of the list gets arrows: Look for blank rows or columns that divide the data, then select the complete range before applying the filter.
- The arrows disappear: The shortcut toggles AutoFilter. Press it again to restore the arrows, then clear criteria separately if needed.
- Some expected records are missing: Check each column’s active filter. Several filters can be applied at once, so a restriction in another column may also hide a row.
- The filter menu groups values strangely: Check for inconsistent entries, such as numbers stored as text or dates mixed with text.
Also make sure you are using the shortcut in Excel rather than another application that may capture keyboard input. In Excel for the web, shortcuts can depend on the browser and keyboard settings; if the shortcut is intercepted or unavailable, use the ribbon command Data > Filter.
Shortcut compared with the ribbon
The keyboard shortcut is fastest when you already know the range is correct. The ribbon is easier to discover and gives you a visible command: choose Data > Filter to apply or remove filter arrows. The ribbon is also useful if you are working on a keyboard where the shortcut differs or is difficult to enter.
Use the dropdown arrow itself for the actual filtering choices. The shortcut does not open a filter menu or choose a condition; it only switches AutoFilter on or off. Once the arrows are visible, you can use the mouse to select criteria. With a cell in a filtered column, Alt+Down Arrow on Windows opens that column’s filter dropdown, which can be useful when navigating without a mouse.
Practical tips for reliable results
A well-structured list makes filtering more predictable. Keep one type of information in each column, use a single header row, and avoid merged cells in the data area. Do not add explanatory notes or totals inside the middle of the list; place them above or below it. If a total row is part of an Excel table, use the table’s total-row feature rather than inserting an unrelated row into the records.
Before filtering a large list, consider whether other people need to see all records. A filter can make data look missing because hidden rows are not obvious at first glance. Look for the filter indicators in the headings and clear the criteria when you are finished. If you copy visible results, remember that filtering hides rows but leaves them in the workbook; take care to copy only the visible results if that is what you intend.
Filtering is also not a security measure. It does not protect hidden records from someone who can access the workbook. Use worksheet or workbook protection only for the limited editing controls it provides, and use appropriate access controls when data should not be shared.
Frequently Asked Questions
What is the shortcut to add a filter in Excel?
On Excel for Windows, press Ctrl+Shift+L with a cell in the data range selected. On Mac, the usual shortcut is Command+Shift+F.
How do I remove a filter in Excel without deleting rows?
To show all records but keep the arrows, clear the criteria from the column dropdown or use Data > Sort & Filter > Clear. No records are deleted by clearing a filter.
Does Ctrl+Shift+L sort the data?
No. It turns AutoFilter on or off. Sorting is a separate command, available through a column’s dropdown or the sorting controls on the Data tab.
Why are the filter arrows missing?
The range may not be selected correctly, the headings may be missing, or the filter may be switched off. Select a cell in the list and try the shortcut again, or select the whole range and choose Data > Filter.
Can I filter an Excel table with the same shortcut?
Tables normally show filter arrows automatically. Use the arrows in the table header to filter; if the buttons are hidden, check the Filter Button option on the Table Design tab.
Does filtering delete the rows that do not match?
No. Filtering temporarily hides nonmatching rows. Clear the filter to display them again; use a delete command only if you truly intend to remove records.
Related
- Randomize Numbers in Excel Without Duplicates
- Rank in Excel: How to Rank Values Largest to Smallest
- Randomize Names in Excel Without Repeats: Easy Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Extract Text in Excel With Formulas and ExamplesTo extract text in Excel, use functions such as LEFT, RIGHT, MID, and FIND to return the characters…
- Excel Percentage Change Formula: Examples and Common ErrorsTo calculate percentage change in Excel, subtract the old value from the new value, divide by the old…
- COUNTIF in Excel for Text: Practical Examples and TipsYes. In Excel, use COUNTIF to count cells that match text: with status values in A2:A20, count cells…
- Days Between Dates in Excel: TODAY Formula and ExamplesTo calculate the number of days from a date in Excel to today, subtract the date cell from…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
- Excel Year Quarter Formula: Get Year and Quarter from DatesWith a valid Excel date in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.