In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current data range. The shortcut adds or removes the filter arrows; to choose which records to show, open a column’s drop-down arrow and select filter criteria.
What the Excel filter shortcut does
Ctrl+Shift+L is a toggle: press it once to add filter controls to a suitable data range, and press it again to remove those controls. It does not sort the rows, delete records, or automatically choose values to filter. The arrows appear in the header cells, where you can set the conditions for each column.
Excel generally detects the connected block of data around the active cell. For example, if a worksheet has a continuous table with headers in row 1 and records below, clicking a cell in that block and pressing Ctrl+Shift+L usually places arrows in the header row. You do not normally need to select the entire range first.
For reliable results, use one clear header row and keep the records in a continuous block. A blank row or blank column can make Excel treat part of the data as a separate region. If the selection is ambiguous, select the full range—including its headers—before using the shortcut.
How to apply a filter step by step
- Arrange the data in columns, with a descriptive heading at the top of each column, such as Date, Region, or Status.
- Click any cell within the data block. If the data has blank rows or columns, select the exact range, including the headers.
- Press Ctrl+Shift+L. Confirm that the header cells now show drop-down arrows.
- Click the arrow in the column you want to filter. In the menu, select or clear values, or choose a condition such as a text, number, or date filter.
- Choose OK if the menu presents that button. Excel hides rows that do not match the criteria.
For example, suppose a sales list has columns for Date, Region, and Amount. To see only entries from the West region, open the Region arrow, clear (Select All), select West, and apply the choice. You can then filter Amount as well. Filters on different columns work together, so the displayed rows must meet the Region condition and the Amount condition.
Choosing values and filter conditions
The simplest filter is selecting individual values from a column’s list. This works well for fields such as department, city, or order status. Selecting several values in one column shows rows matching any of those selected values. For example, choosing both Open and Pending in a Status column displays records with either status.
When a column contains many distinct values, use the search box in its filter menu to locate a value rather than scrolling. The search helps find entries in the list; it is not a replacement for choosing the appropriate filter condition. Check the selections before applying the filter, especially if the list is long.
For more specific criteria, use the menus for the relevant data type. Depending on the selected column and Excel’s recognition of its contents, the menu can offer options such as:
- Text Filters: conditions such as Begins With, Contains, or Equals.
- Number Filters: conditions such as Greater Than, Less Than, or Between.
- Date Filters: options for dates or date periods, such as a particular month or year.
These options depend on the data Excel sees. If dates or numbers were imported as text, Excel may show text-oriented choices or compare the entries unexpectedly. Consistent data types make filtering more predictable. Also check for leading or trailing spaces, which can make entries that look identical behave as different values.
Using filters with Excel tables
If you turn a data range into an Excel table using Insert > Table, Excel adds filter arrows to the header row by default. Tables also expand when you add adjacent records and carry formatting and formulas into new rows. That makes a table useful for a list that will grow over time.
You can still use AutoFilter with a regular range; converting everything into a table is not required. If a table already has its filter arrows, you may not need Ctrl+Shift+L to start filtering. Use the arrows in the table headers to set criteria. To show or hide the arrows for a table, click inside it and use the Table Design tab’s Filter Button option, when available in the ribbon layout.
Keep the table’s header row distinct from the records. Avoid placing a title row, blank row, or merged cells inside the data area. Those layouts can interfere with Excel’s ability to recognize the headers and include the intended records.
Clearing criteria without removing the arrows
There is an important difference between clearing a filter and turning AutoFilter off. To show all records while keeping the arrows available, open the filtered column’s menu and choose Clear Filter From followed by that column’s name. To clear filters across the current data range, use Data > Sort & Filter > Clear on the Windows ribbon. The arrows remain, ready for another filter.
Pressing Ctrl+Shift+L toggles the filter controls themselves, so it is not the best first choice when your aim is simply to reveal hidden records. Use the column menu or the Clear command instead. Filtering hides nonmatching rows; it does not remove their contents. Clear the criteria to display the rows again.
That distinction also matters when calculating totals. A regular SUM calculation can include values in rows hidden by a filter. Excel’s SUBTOTAL function is often used when a result should respond to filtered-out rows, but choose the calculation and function number carefully if rows have also been hidden manually.
Common shortcut and filtering problems
- The shortcut does nothing: Click a cell in the data first, then try Ctrl+Shift+L again. If the active cell is outside the data block, Excel may not apply the filter where expected. You can also use Data > Filter.
- Arrows appear on the wrong row: Check whether the range includes a title row above the actual column headings. Select the intended range with its header row and apply the filter again.
- Some records do not get included: Look for blank rows or columns that split the data into separate regions. Select the complete range or reorganize the list so its records form one continuous block.
- A filter choice seems to miss matching entries: Inspect the underlying values for extra spaces, inconsistent spelling, or numbers and dates stored as text. Visually similar cells may not contain identical data.
- Rows appear to be missing: Check every column for an active filter. Several column filters can be active at once, and all their conditions must be satisfied for a row to remain visible.
To inspect the current state, look for a filter indicator on the column arrow and open the menu. If you need the full list again, use the column’s clear option or Data > Sort & Filter > Clear rather than deleting rows or repeatedly switching the filter buttons on and off.
Windows, Mac, and Excel for the web
This article’s main shortcut, Ctrl+Shift+L, is for Excel on Windows. On Excel for Mac, the AutoFilter shortcut is commonly Command+Shift+F; keyboard behavior can vary with Excel version, keyboard layout, or system settings. If that combination is unavailable or behaves differently, use Data > Filter on the ribbon to apply or remove the filter.
Excel for the web runs inside a browser, and browser or operating-system shortcuts can affect which key combinations work. The reliable route is to select the data and use Data > Filter in Excel’s ribbon. The menu may look slightly different from the Windows desktop version, but the basic idea is the same: apply the arrows, then choose criteria from a column menu.
Frequently Asked Questions
What is the shortcut to filter in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to toggle AutoFilter arrows for the current data range. Then open a header’s drop-down arrow to choose what to display.
Does Ctrl+Shift+L sort data alphabetically?
No. It adds or removes filter controls. To sort, use the column menu’s sort options or the commands on the Data tab. Filtering changes which rows are visible; sorting changes their order.
How do I show all rows after filtering?
Open the filtered column’s drop-down menu and choose Clear Filter From that column. To clear all criteria in the range, use Data > Sort & Filter > Clear on Windows. The filter arrows can remain in place.
Can I filter more than one column at a time?
Yes. Apply a condition in each relevant column. Excel displays rows that satisfy the conditions across the filtered columns, so filtering Region to West and Status to Open shows only West records that are also Open.
Why can’t I see the filter arrows?
The active cell may be outside the data, the range may not have a clear header row, or AutoFilter may be off. Click inside the list and press Ctrl+Shift+L, or select the range and choose Data > Filter. Check for blank rows that divide the list.
Related
- Quick Way to Shade Every Other Row in Excel Automatically
- Capitalize First Letter in Excel Quickly
- Quick Search in Excel: Find Data Fast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
- Excel Named Ranges in Formulas: How to Use ThemYes. In Excel for Microsoft 365, type a defined name into a formula anywhere you would otherwise use…
- Excel SUMIF Criteria Examples for Text, Dates, and NumbersExcel’s SUMIF function adds values when one matching condition is true. With categories in A2:A7 and amounts in…
- XLOOKUP Multiple Criteria: Examples and Common FixesTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found").…
- XLOOKUP Excel Example: Search Across Two Excel SheetsIf Orders!A2 contains a product code, and Products!A2:A100 contains product codes with prices in Products!C2:C100, enter =XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$C$2:$C$100,"Not found")…
- Filter Strikethrough in Excel with a Helper ColumnTo filter rows by strikethrough in Excel, add a helper column that checks the formatting of the cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.