In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for a selected data range. The shortcut adds filter arrows to the column headings; it does not open a dropdown or delete any rows.
The Excel filter shortcut and what it does
AutoFilter lets you show only the rows that match criteria you choose, while temporarily hiding the other rows. When you press Ctrl+Shift+L with a cell in a suitable data range selected, Excel identifies the range and puts a dropdown arrow in each heading cell. Press the same shortcut again to turn those filter controls off.
The shortcut is a toggle, so the result depends on the current state. If arrows are absent, it usually adds them. If they are already present on the selected range, it usually removes them. It is not the shortcut for opening a filter menu: to open a heading’s dropdown, select that heading cell and press Alt+Down Arrow.
Filtering is nondestructive. Rows that do not meet the current criteria are hidden from view, not erased. Clear the criteria to show them again, or turn filtering off to remove the filter controls. This differs from deleting rows, which changes the data itself.
How AutoFilter works
Each arrow belongs to a column heading. Its dropdown contains options appropriate to the values in that column, such as a list of text or number values, a search box, and commands for filtering or sorting. For a date column, Excel can provide date-based choices; for a numeric column, it can offer comparisons such as greater than or between.
You can filter more than one column at a time. For example, a sales list could be filtered to show only rows where Region is West and Status is Open. A row remains visible only if it satisfies the active filters across the columns. Within a single column, selecting several listed values generally shows rows matching any of those selected values.
Excel indicates that a filter is active with a changed dropdown icon, and the row numbers at the left may skip because some rows are hidden. The status bar can also report how many records are visible when you select data. These signs help distinguish a filtered list from a list where rows were simply removed or moved.
Before using the shortcut
For the best results, keep the data in a simple rectangular list: one heading row followed by records, with one type of information in each column. Avoid blank heading cells, merged cells in the heading row, and completely blank rows or columns inside the list. Those features can make it harder for Excel to detect the intended range.
To apply filters:
- Click a cell inside the list, or select the full range you want to filter.
- Check that the first row contains a distinct heading for each column.
- Press Ctrl+Shift+L.
- Choose a heading arrow and select the values or conditions you want to display.
If you select only one cell, Excel generally tries to detect the surrounding data region. If the list is separated by blank rows or columns, the detected region may stop at that gap. Selecting the exact range first can help when you want to include a particular block or when the worksheet contains several nearby lists.
Filtering a list in practice
Imagine a worksheet with the headings Date, Region, Product, and Units. To see only products from the West region, turn on AutoFilter, open the arrow under Region, clear the selection for all values, and select West. Excel hides the rows for other regions. To narrow the results further, use the Product arrow as well; the visible rows must now meet both column filters.
For a number-based example, a Units column can be filtered to show values greater than a threshold. Open the Units dropdown and choose the number filter option that matches the condition, then enter the value. For dates, use the date filter choices or select particular dates from the checklist. The precise menu wording can vary with the data type and Excel build, but the controls are in the relevant column’s dropdown.
To restore the full list without removing the arrows, open the relevant dropdown and choose its clear-filter option. If several columns have active criteria, clear each one or use the command to clear filters from the Data tab. The arrows remain available for another filter. Press Ctrl+Shift+L only when you intend to toggle the AutoFilter controls themselves.
Turn filters off, or keep them available
Press Ctrl+Shift+L again to remove the AutoFilter dropdown arrows from an ordinary filtered range. This removes the filter interface; it is not the same as clearing each criterion first. The rows become visible again when AutoFilter is switched off, but it is good practice to confirm the full list is displayed before sharing or copying results.
If you want to keep the arrows but see every record, clear the filters instead of toggling AutoFilter off. This is useful when you expect to filter the list again soon. If the goal is to copy only the currently visible results, take care: filtered-out records remain in the worksheet. Verify the visible selection and the destination before pasting or sharing it.
There is a related distinction for Excel Tables. A Table created with Insert > Table or Ctrl+T normally has filter arrows in its header row by default. Table headers have their own controls, so if the arrows do not behave as expected, check that the active cell is in the Table and look at the Table Design options. The shortcut is most straightforward for a regular range; do not assume that every Table control is identical to toggling AutoFilter on a plain range.
Windows, Mac, and Excel for the web
For Excel for Microsoft 365 on Windows, use Ctrl+Shift+L. On Excel for Mac, the corresponding shortcut is commonly Command+Shift+F; some Mac versions also recognize Control+Shift+L. Keyboard mappings can depend on the Excel version and system, so the ribbon command is a dependable alternative if the key combination does not work.
On Windows, you can use Data > Filter to apply or remove the filter controls. You can also find the command at Home > Sort & Filter > Filter. On Mac, use the Filter command on the Data tab. Excel for the web has a similar Filter command on its ribbon, though browser and keyboard settings can affect shortcuts; use the ribbon if Ctrl+Shift+L is intercepted or unavailable.
On a laptop, you generally still press the Ctrl, Shift, and L keys together; the function-key layer is not involved. If a keyboard layout or another application intercepts the shortcut, the Data tab command provides the same practical route without requiring you to guess a different key combination.
Common problems and fixes
- No arrows appear: Click inside the intended data region and try again. Confirm that the range has a heading row and is not split by blank rows or columns. Selecting the range explicitly may help Excel identify it.
- The wrong cells get arrows: Undo is not always the simplest correction. Turn AutoFilter off, select the intended range carefully, and apply it again. Check for adjacent data that Excel may have included in its detected region.
- The shortcut opens no menu: That is expected. Ctrl+Shift+L turns filter controls on or off. Use Alt+Down Arrow while the heading cell is selected to open its dropdown.
- Some records seem missing: Look for active filters in other columns, not just the one you were using. Clear the filters to restore all records. Also check whether rows are hidden for another reason, such as manual row hiding.
- Filter choices look incomplete: A filter list reflects the values Excel detects in the relevant column and can be affected by the selected range or data layout. Check for blank rows, inconsistent headings, and records outside the range, then reapply the filter to the complete list.
One common misconception is that filtering changes the original data or permanently removes records. It normally only changes which rows are displayed. Still, if you are preparing a report or sending a workbook to someone else, clear filters or make the filtered state obvious so readers do not mistake a partial view for the complete dataset.
Filter shortcut compared with sorting and Tables
Filtering and sorting are related but different. A filter hides records that do not match a condition. Sorting changes the order of records, such as arranging dates from newest to oldest, while leaving the records in the list. Both actions can be accessed from the column dropdowns, but Ctrl+Shift+L specifically toggles AutoFilter; it does not sort the data.
For a list you update regularly, converting the range to an Excel Table can be more convenient than repeatedly managing a plain range. Tables provide structured headers, expand as you add records, and include filter dropdowns by default. A Table is not required to filter data, however. For a one-off list or a carefully selected range, AutoFilter is often enough.
Use the option that matches the job: choose AutoFilter for quick filtering of a range, a Table for an ongoing dataset with built-in structure, and sorting when you want to reorder records rather than hide them. Whichever method you use, make sure the heading row is clear and keep each field in its own column for reliable filtering.
Frequently Asked Questions
What is the shortcut key for putting a filter in Excel?
In Excel for Microsoft 365 on Windows, press Ctrl+Shift+L. It adds AutoFilter dropdown arrows to the detected data range, or removes them if they are already on.
How do I open the filter dropdown with a keyboard?
Select a cell in the column heading that has a filter arrow, then press Alt+Down Arrow on Windows. Ctrl+Shift+L toggles the arrows; it does not open the dropdown.
How do I clear a filter without removing the arrows?
Open the dropdown for the filtered column and choose the clear-filter command. If several columns are filtered, clear each active filter or use the clear command on the Data tab. The dropdown arrows stay in place.
Does Ctrl+Shift+L delete filtered-out rows?
No. Applying a filter hides rows that do not match the selected criteria. It does not delete them. Clear the filter or turn AutoFilter off to show the records again.
What is the filter shortcut on a Mac?
Command+Shift+F is commonly used in Excel for Mac, and some versions also support Control+Shift+L. If neither works in your setup, use the Filter command on the Data tab.
Related
- Power Query: Get Excel Files From a Folder in Excel
- Power Query Excel Number Format: What You Can Control
- Power Query: Append Excel Files Into One Table
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Power Query Filter Rows: Practical Steps and ExamplesTo filter rows in Excel Power Query, open a query in the Power Query Editor, select the filter…
- Filter Excel Table by Criteria: A Practical Excel GuideTo filter an Excel table by criteria, open the drop-down arrow in the column header, choose a value…
- Filter in Excel With Blank Rows: Keep Records TogetherTo filter data that contains blank rows, select the entire data range—including the blank rows—and choose Data >…
- Copy the Exact Same Formula in Excel Without Changing ItTo copy the exact same formula text in Excel, select the formula in the formula bar, copy it,…
- Dependent Dropdown in Excel Using INDIRECT: Setup GuideYes. Create a named range for each parent choice, then use INDIRECT in the child dropdown’s Data Validation…
- Excel Day of Week Formula: Return a Name or NumberIf the date is in cell A2, use =WEEKDAY(A2,2) to return its weekday number, with Monday as 1…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.