In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current data range. Once the filter arrows appear in the header row, click an arrow to choose which rows to show; the shortcut itself does not select filter criteria.
What the Excel filter shortcut does
AutoFilter adds a drop-down arrow to each column heading in a list of data. Those menus let you show rows that match selected values or conditions, while temporarily hiding rows that do not match. For example, a sales list with columns for Date, Region, and Amount can be filtered to show only the West region or only amounts above a chosen threshold.
Ctrl+Shift+L is a toggle. Press it once to apply filter controls to the relevant range, and press it again to switch AutoFilter off. It is not a shortcut for opening a particular column’s menu, selecting a value, or clearing just one column’s criteria. Use the arrow in that column’s header for those actions.
Filtering does not delete the rows that are hidden. The underlying records remain in the worksheet, and you can restore them by changing or clearing the filter. This matters when you copy, calculate, or review filtered data: make sure you understand which rows are currently visible before acting on them.
How to turn filters on with the keyboard
- Make sure your list has a single header row, with a clear label for each column.
- Click a cell inside the data, or select the full range including its header row.
- Press Ctrl+Shift+L. Filter arrows should appear in the headings.
- Click the arrow for the column you want to filter, then choose values or a condition.
Excel usually identifies the surrounding contiguous data range when you start from a cell in the list. This is convenient when the dataset is laid out as one block, but blank rows or blank columns can make Excel interpret the boundaries differently than you expect. If the arrows appear on only part of the list, undo the toggle if needed, select the entire intended range including the headings, and press the shortcut again.
For a mouse-based alternative in Windows, choose Data > Filter on the ribbon. The command applies or removes AutoFilter in much the same way. If keyboard access is preferable, the shortcut avoids navigating to the ribbon, but the ribbon command is useful when a shortcut is unavailable or unclear.
How to choose values and filter results
After turning on AutoFilter, open a column’s drop-down menu. The menu normally includes a search box and a list of distinct values found in that column. Clear the selection for all values, select the values you want to keep, and confirm the choice. The displayed list then contains only rows matching that selection.
For numeric, date, and text columns, the menu also offers condition-based options. Depending on the data type, you can filter for items such as numbers greater than a specified amount, dates in a period, or text that begins with particular characters. For example, in an Amount column, a number filter can show sales greater than 500; in a City column, a text filter can find names beginning with “San.” The available choices depend on the contents of the column.
Filters on separate columns work together: a row must satisfy the active conditions in each filtered column to remain visible. Within one column, selecting several values generally means show rows matching any of those selected values. For instance, choosing East and West in Region, then choosing a quarter in Date, displays records from either selected region that also fall within the selected date period.
To remove one column’s condition while leaving other filters in place, open that column’s menu and choose its clear-filter option. To clear the filtering criteria across the range but keep the filter arrows, use Data > Sort & Filter > Clear. To remove the AutoFilter controls themselves, press Ctrl+Shift+L again. Clearing criteria and turning the controls off are different actions.
Using filters with Excel tables
Excel tables are structured data ranges created with Insert > Table or the Ctrl+T shortcut. Tables normally include filter arrows in their header row automatically, so you may not need Ctrl+Shift+L to begin filtering a table. Click a table header’s arrow to filter it as you would a regular AutoFilter range.
Tables are useful for lists that grow over time. When you add a row directly below a table, Excel can expand the table to include it, helping keep related formatting and features together. Table column headings also make it easier to identify fields, and the filter controls stay associated with the table. If the arrows are missing, check whether the table has a header row and whether its filter buttons have been hidden.
To show or hide a table’s filter buttons, select a cell in the table and look for the contextual Table Design tab. In the Table Style Options group, use Filter Button. Tab and group placement can vary slightly with ribbon layout or window size. Unlike a plain range, a table has its own contextual controls, so use the table setting if the standard AutoFilter toggle does not produce the result you expect.
Windows, Mac, and Excel for the web
The main answer here is for Excel for Microsoft 365 on Windows: Ctrl+Shift+L. If your keyboard has an Fn layer or a nonstandard layout, the keys may be interpreted differently; the Data tab’s Filter command is a dependable alternative.
In Excel for Mac, the commonly listed shortcut for applying or removing a filter is Command+Shift+F. Keyboard behavior can depend on the Mac keyboard and Excel version, so use the Data > Filter command if the combination does not work. On a Mac, Command is the key marked ⌘.
Excel for the web runs in a browser, where some keyboard combinations may be intercepted by the browser or differ according to the keyboard configuration. Try Ctrl+Shift+L, or use Data > Filter from the Excel ribbon. If the workbook is open in a browser and the command appears unavailable, check that the sheet is editable and that you have selected a cell in the relevant data.
Common filter problems and fixes
- No arrows appear: Click inside the data, include the header row in your selection, and try Ctrl+Shift+L again. Confirm that the worksheet is not protected in a way that prevents filtering.
- Only part of the list gets arrows: Look for blank rows or columns splitting the data into separate blocks. Select the full intended range before applying the filter.
- Some expected values are missing: Another column may already have an active filter, so matching rows are hidden. Clear all criteria with Data > Sort & Filter > Clear and inspect the list again.
- A number or date filter behaves oddly: Check that the column contains consistent data types. Numbers stored as text, mixed date formats, or extra spaces can affect how Excel groups and filters entries.
- The shortcut changes something else: Confirm that Excel has focus and that you are using the Windows combination. On a Mac or in a browser, try the ribbon command instead.
A common misconception is that a filtered-out row has been removed. It is hidden from the current view, not erased. Another point to remember is that filters do not sort data: they limit which records are displayed, while sorting changes their order. If a filtered result looks incomplete, check active filters in every column before assuming the source records are gone.
Filter versus sort and other ways to narrow a list
Filtering is best when you want to temporarily focus on records that meet conditions, such as open orders, one department, or a date range. Sorting is best when you want the records rearranged, for example from newest to oldest or from highest amount to lowest. The two operations can be combined: sort the visible results after filtering, or filter a range that has already been sorted.
For a one-time review, AutoFilter is quick and reversible. For a reusable report, a PivotTable may be more appropriate because it can summarize data by category and provide report filters or slicers. For a separate formula-driven result list, Microsoft 365 also includes the FILTER function, but that is a worksheet formula rather than the AutoFilter control; it returns matching data in another area and does not hide source rows. Choose based on whether you want to inspect the original list, summarize it, or create a separate output.
If the goal is to filter only a small group of records, selecting a specific range before applying AutoFilter gives more control than relying on Excel to detect the data boundaries. If the list will expand frequently, converting it to a table can make the range easier to manage. Either way, keep one clear header row and avoid empty separator rows within the dataset.
Frequently Asked Questions
What is the shortcut to add or remove filters in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L. It toggles AutoFilter for the selected data range; it does not choose which values to display.
How do I filter a column without using the mouse?
Turn on AutoFilter, move focus to a header cell, and open its filter menu using the keyboard. Exact navigation can vary by Excel version and keyboard layout; the menu can also be opened by clicking the header arrow. Use its search, checkboxes, or condition options to set the filter.
How do I clear a filter but keep the arrows?
Use Data > Sort & Filter > Clear to remove filter criteria while leaving AutoFilter controls in place. To clear only one column, use that column’s drop-down menu and its clear-filter command.
Why does Ctrl+Shift+L not work?
Check that the worksheet is active, Excel has keyboard focus, and the intended data range is selected. The shortcut described here is for Windows desktop Excel; on Mac, try Command+Shift+F, and in any version use Data > Filter as an alternative.
Does filtering delete hidden rows?
No. Filtering hides rows that do not match the active conditions; it does not delete their contents. Clear the filter or show all selected values to display them again.
Related
- Power Query Background Refresh in Excel: How to Disable It
- Excel Cleanup Taking Too Long? Make Cleanup Faster
- Excel Percentage Number Format: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
- How to Find XLOOKUP in Excel and Use It in a FormulaTo find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup &…
- How to Sort Dates Correctly in Excel Without ErrorsTo sort dates correctly in Excel, make sure Excel recognizes the entries as real dates, then select a…
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when…
- Align Right in Excel: Shortcut, Menu, and Mac KeysIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, H, A, R—one…
- How VLOOKUP Works Between Two Excel SheetsTo look up a value from one Excel sheet in a table on another, use VLOOKUP with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.