In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn the filter buttons on or off. The shortcut adds or removes AutoFilter controls; it does not choose filter criteria or hide rows by itself.
The shortcut and what it does
AutoFilter is Excel’s built-in way to show drop-down buttons in a table’s header row. Those buttons let you display only rows that match chosen values or conditions. Pressing Ctrl+Shift+L toggles those buttons for the current data range: press once to turn filtering on, and press it again to turn it off.
For example, if a worksheet has columns named Region, Salesperson, and Amount, the shortcut adds a drop-down at the top of each column. The rows remain visible until you open a drop-down and select a filter. Turning AutoFilter off removes the controls and clears the active filter display, so rows hidden by a filter become visible again. It does not delete the underlying records.
Excel generally uses the current cell’s surrounding data region to identify the range. Click a cell inside a well-formed list before using the shortcut. If the list has blank rows or columns that divide it into separate blocks, Excel may identify only part of the data; selecting the full range first can make the intended scope clearer.
Turn on filters step by step
For a typical list, you can enable filters and apply one without using the ribbon:
- Make sure the first row of the list contains column headings, such as Date, Category, and Amount.
- Click a cell within the list. If Excel might not detect the complete range, select the header row and all the data rows instead.
- Press Ctrl+Shift+L. Filter arrows should appear in the headers.
- Click a header arrow, choose the values or conditions you want, and select OK if the menu presents that button.
You can also use the ribbon: choose Data > Filter. This is a useful alternative if you forget the shortcut, use a keyboard layout where the key combination is awkward, or need to confirm that you are working with Excel’s Filter command. The same ribbon command can turn AutoFilter off.
Apply one filter or combine several
Click a filter arrow to see the options for that column. For a text column, the list usually includes distinct values and a search box. Clear Select All, select the value or values to keep, and confirm. In a Region column, for example, selecting West displays rows for West while temporarily hiding rows for other regions.
Excel offers more than a checklist. Depending on the data type, the menu includes options such as Text Filters, Number Filters, or Date Filters. You can use a condition such as “begins with” for a text field, or choose a numeric comparison such as “greater than” for amounts. Date filters can group or select dates by periods such as months or years. The available choices depend on the values in the column and the version of Excel.
Filters in different columns work together. If you filter Region to West and Amount to values greater than 500, Excel displays rows that meet both conditions. Within a single column, selecting several checklist values generally means show rows matching any selected value—for example, West or Central. This distinction is helpful when a result seems narrower than expected: criteria across columns are combined, while multiple selected values in one checklist are alternatives.
Use the search box in a filter menu when a column contains many distinct entries. Searching narrows the menu’s checklist; it does not change the source data. Check that the desired values are selected before confirming, especially if you previously changed the selection or used a search term.
Prepare the data so the filter covers the right rows
A reliable filter starts with a single header row and a consistent block of records. Each column should have a distinct heading, and each row should represent one record. Avoid blank rows inside the list, merged cells in the header, and multiple unrelated tables directly beside each other. These patterns can make the boundaries unclear or make filtering difficult.
Look for details that affect how Excel groups values. A column that mixes numbers with text, or dates with text that only looks like dates, may not offer the expected filter choices. Extra spaces can also make entries that look identical behave as separate values. If a filter checklist shows near-duplicates, inspect the source cells and standardize the data rather than assuming the filter is broken.
For a range that you will sort or filter regularly, converting it to an Excel table can make the structure easier to manage. Select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Tables typically show filter buttons in their header row and expand as you add adjacent records. A table is not required to use AutoFilter, though; the shortcut also works with an ordinary contiguous range.
Filtering is different from sorting
Filtering and sorting are often found together in the same header menu, but they do different jobs. A filter hides records that do not meet the selected criteria. Sorting changes the order of the records that remain visible, such as arranging dates from oldest to newest. Neither operation normally removes the source records.
Suppose a sales list contains 200 rows and you filter the Region column to West. The other regions are hidden from view, while the West records remain in the worksheet. Sorting the visible Amount values from largest to smallest then changes their display order; it does not add a second condition or restore the hidden regions. If you need all records back, clear the filter rather than sorting again.
Another useful distinction is between AutoFilter and advanced data analysis. AutoFilter is intended for interactively narrowing a list. It is not a formula, and it does not create a separate extracted list of matching records. If you need a live formula-generated result elsewhere in the workbook, Microsoft 365 and Excel 2021 include the FILTER function, which is different from the header drop-down filtering controlled by this shortcut.
Clear a filter without removing the buttons
If you want to see all rows again but expect to filter the list later, clear the criteria rather than turning AutoFilter off. Open the arrow for the filtered column and choose Clear Filter From followed by the column name. This restores the rows for that column while leaving the header buttons available. If more than one column is filtered, clear each one as needed.
You can clear filters across the current range from the ribbon using Data > Sort & Filter > Clear. The Filter button and the Clear command are not interchangeable: Filter toggles the drop-down controls, while Clear removes filtering criteria so the records show again. To remove the controls entirely, use Ctrl+Shift+L or choose Data > Filter.
When a filter is active, Excel commonly marks the filtered column’s drop-down with a filter icon rather than only the standard arrow. That visual cue helps identify why some rows are missing. A worksheet can have filters available without having any criteria applied, so the presence of arrows alone does not mean records are hidden.
Common shortcut and filter problems
- Nothing happens: Confirm that the Excel workbook has focus and that you pressed all three keys together. Some keyboard layouts require a different key position for the bracket keys or may treat a modifier differently. Use Data > Filter to check whether the ribbon command works.
- Only part of the list gets arrows: Select the entire intended range, including headings, and apply Filter again. Check for empty rows or columns splitting the data into separate regions.
- The first record seems to be treated as a heading: Excel expects a header row. If the list has no headings, add descriptive column names before filtering; otherwise the top data row may be interpreted as labels.
- Some expected values are missing: Another column may still have an active criterion, or the values may differ because of spaces, inconsistent spelling, or mixed data types. Use Data > Sort & Filter > Clear to clear all criteria, then inspect the relevant column.
- Rows seem gone: Filtering hides nonmatching rows; it does not normally delete them. Clear the filter from its drop-down or use the ribbon Clear command to show the full list again.
Be cautious when copying or editing a filtered list. Hidden rows remain part of the worksheet, and operations on a selection can have effects beyond what is currently visible depending on the action. Before deleting records or making a large change, clear filters or verify the selected range and the visible-row status.
Windows, Mac, and Excel for the web
The primary shortcut here is for Excel on Windows: Ctrl+Shift+L. In Excel for Mac, the commonly used equivalent is Command+Shift+F. Keyboard behavior can vary with the keyboard layout and Excel build, so the dependable ribbon route on either desktop platform is Data > Filter.
Excel for the web has a browser-based interface, and browser shortcuts or keyboard settings can affect some combinations. If the Windows shortcut does not work in the web app, choose Data > Filter on the ribbon. The same practical steps apply: select a cell in the list, enable the filter controls, and choose criteria from a column header.
Excel tables add one small complication: their headers commonly have filter buttons already. If you are working with a table and the buttons do not respond as expected to the shortcut, use the table-specific ribbon controls or the Data tab rather than assuming the list has been converted to a different kind of filter. The key point is to distinguish showing the controls from choosing which records to display.
Frequently Asked Questions
What is the shortcut to add a filter in Excel?
On Windows, click a cell in the data and press Ctrl+Shift+L. Excel adds filter drop-downs to the detected range; select criteria from a header menu to hide nonmatching rows.
Does Ctrl+Shift+L filter the data automatically?
No. It turns AutoFilter controls on or off. You still need to open a column’s drop-down and choose values or conditions before Excel hides rows.
How do I remove a filter but keep the drop-down arrows?
Open the filtered column’s menu and choose Clear Filter From that column, or use Data > Sort & Filter > Clear to clear criteria. Do not toggle Filter off if you want to keep using the arrows.
What is the Excel filter shortcut on a Mac?
The commonly used Mac shortcut is Command+Shift+F. If it is unavailable in your setup, use Data > Filter from the Excel ribbon.
Why does the shortcut add arrows to only some columns?
Excel may have detected only one contiguous block because a blank row or column separates part of the list. Select the complete range, including its headers, and choose Data > Filter or press the shortcut again.
Does filtering delete hidden rows?
No. A filter hides rows that do not match the selected criteria; the records remain in the worksheet. Clear the filter to display them again.
Related
- How to Unlock Microsoft Excel: Sheets, Files, and Editing
- Unlock Scroll Lock in Excel With a Keyboard Shortcut
- How to Unhide a Sheet in Excel With a Shortcut
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
- Excel Sheet Formula List: Essential Functions and ExamplesAn Excel sheet formula list is a reference to formulas and functions you can use to calculate, summarize,…
- Excel Test If Two Cells Are Equal: Formulas and FixesTo test whether two cells are equal in Excel, enter =A1=B1 in another cell, replacing A1 and B1…
- VLOOKUP Examples for Multiple Sheets in ExcelTo look up a value on another worksheet, use VLOOKUP with that sheet’s range, such as =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE). To…
- Excel Insert Row and Copy Formulas From Above EasilyTo insert a row and copy formulas from above in Excel for Microsoft 365 on Windows, right-click the…
- Excel Conditional Formatting: Nested IFs for Dates?Yes. Excel conditional formatting can use a nested IF formula, but the formula must ultimately return TRUE or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.