How to Get Out of Filter Mode in Excel and Show All Rows

To get out of filter mode in Excel and show every row, select a cell in the filtered data and choose Data > Sort & Filter > Clear. This removes the active filter criteria but leaves the filter arrows available. To remove the arrows too, choose Data > Sort & Filter > Filter, or press Ctrl+Shift+L on Windows. First make sure you are working in the right worksheet and, if the file matters, save a copy before changing its layout.

What You’ll Need

An open Excel workbook with the worksheet that contains the filtered list.
Permission to edit the worksheet. A protected sheet may prevent you from changing filter settings.
A moment to decide whether you want to show all records while keeping the dropdown arrows, or remove the arrows as well. These are separate actions in Excel.

Step-by-Step

Go to the worksheet with the hidden rows. Click its sheet tab at the bottom of the Excel window. If the workbook has several sheets, check that you are on the sheet where the rows appear to be missing. Filters apply to a particular list or table on a worksheet; changing a different sheet will not affect them.
Click inside the filtered list or table. Choose a cell within the data, not an unrelated blank cell elsewhere. Look for dropdown arrows in the header row. A funnel symbol on an arrow, or a changed arrow icon, commonly indicates that a filter is active in that column. Some matching rows may still be visible, so the presence of data does not mean all records are showing.
Clear all active filters while keeping the arrows. On the Windows ribbon, select Data > Sort & Filter > Clear. Excel removes the filter conditions from the current filtered range or table and displays the rows again. The header dropdowns remain, so you can apply a new filter later. This is usually the best choice when you simply want to see the full list.
Clear a filter from just one column, if that is all you need. Open the dropdown in the column that has a filter, then choose Clear Filter From [column name]. The wording includes the column’s heading. This removes that column’s condition but leaves any filters in other columns in place. If rows are still missing afterward, another column may also be filtering the list.
Use the dropdown to show all values in one column. Open that column’s filter menu and choose Select All, then click OK if the menu has an OK button. Check that search text or other selections have not left only some items selected. This is useful when you want to preserve other column filters but remove the restriction from one field.
Turn off the filter arrows entirely, if desired. Click inside the list or table and choose Data > Sort & Filter > Filter. The arrows disappear and Excel displays the rows. On Windows, Ctrl+Shift+L also toggles AutoFilter on or off for the selected data range. Because this shortcut toggles the setting, check the result before pressing it again; a second press can turn filtering back on.
Check the result before continuing. Scroll through the list and confirm that the records you expected are visible. If you chose Clear, the arrows should still be present, but the active filter conditions should be removed. If you turned Filter off, the arrows should be gone. Save the workbook if you want to keep the changed view for the next time it is opened.

In Excel for Mac, the same distinction applies: use the Data tab’s filter controls to clear criteria or turn filtering off. Ribbon group names and placement can vary with the window size and Excel version, so expand the ribbon or look for the Filter and Clear controls on the Data tab. The Windows shortcut Ctrl+Shift+L is not the shortcut to rely on for a Mac; use the ribbon if you are unsure which shortcut your version supports. In Excel for the web, select the range or table and use the Filter controls on the Data tab; some ribbon options may be arranged differently from the desktop app.

Common Mistakes

Removing the arrows when you only wanted to see all rows. Choosing Filter toggles the filter feature off, while Clear removes the filter criteria and leaves filtering enabled. If you expect to filter again soon, use Clear.
Clearing only one column when several columns are filtered. A list can have conditions in multiple headers at the same time. Clearing one condition may reveal more rows without restoring the entire list. Use the main Clear command to remove all criteria from the current filtered list.
Assuming hidden rows are always caused by a filter. Rows can also be hidden manually, grouped, or hidden by a different feature. If Clear does not reveal them, inspect the row numbers and the troubleshooting section below.
Pressing the filter shortcut repeatedly without checking. Ctrl+Shift+L is a toggle, not a one-way “show all” command. It can switch filtering back on after you have turned it off.
Selecting a cell outside the list. Excel may apply a filter to a different block of data, or the relevant controls may not act on the list you meant to change. Click within the affected range or table first.

Tips

Choose the least disruptive option. For a quick look at every record, clear the conditions rather than turning off filtering. This keeps the table’s or range’s filter controls ready to use.
Look for a funnel, not just an arrow. In a filtered header, the dropdown may show a funnel icon. Open it to see the selected items or conditions and identify which field is restricting the rows.
Use the column-specific command when preserving other conditions matters. For example, if you want to remove a date restriction but keep a status restriction, clear the date column’s filter rather than using the overall Clear command.
Make the header row easy to identify. Filters work best when the data has one clear header row and no completely blank rows or columns splitting the list. For a formatted Excel table, use a cell inside the table before changing its filter settings.
Remember that clearing a filter does not rearrange the data deliberately. It restores the rows that meet no filter restriction; it does not undo a sort. If the records are in an unexpected order, use the sort controls separately.

Troubleshooting

Clear is unavailable or does nothing. Click a cell inside the filtered range or table and check the Data tab again. If the sheet is protected, go to Review > Unprotect Sheet if you have permission and the password, then try again. Do not try to bypass protection; ask the workbook owner for access if needed.

Some rows remain missing after you clear. Open each header dropdown and look for another active filter. If all filters appear cleared, check whether the row numbers skip—for example, row 12 is followed by row 18. Select the affected row numbers, right-click, and choose Unhide. If rows are grouped, click the outline level controls or the plus sign beside the row headings to expand the group. A manually hidden row is not restored by clearing a filter.

The dropdown arrows disappeared, but the data still looks incomplete. Turning off Filter does not necessarily unhide rows that were manually hidden or collapsed in a group. Select the whole sheet using the triangle at the top-left corner between the row and column headings, then right-click a row number and choose Unhide. Use this carefully: it may reveal rows that were intentionally hidden for presentation.

The Filter command seems to affect the wrong cells. Excel decides which contiguous range to filter based on the selected cell. Remove the unintended filter if necessary, make sure the data has a single header row without blank separator rows, then click inside the intended list and apply Filter again. If nearby data blocks touch, consider converting the intended list to a table with Insert > Table, after confirming the correct range in the dialog.

You cannot edit the filter in a shared or web workbook. Another user’s changes, workbook permissions, or a protected view may limit what you can do. Check whether the file is read-only, whether editing is enabled, and whether you have permission to change it. If a change is not yours to make, ask the owner rather than saving a modified copy over the original.

Frequently Asked Questions

What is the fastest way to show all rows in Excel?
Click inside the filtered list and choose Data > Sort & Filter > Clear. That removes active criteria while keeping the dropdown arrows. On Windows, the Filter shortcut is a toggle and is not the clearest way to remove criteria.

Does clearing a filter delete any data?
No. Clearing a filter changes which existing rows are displayed; it does not delete the filtered-out records. They become visible again unless they are also hidden manually, grouped, or excluded by another filter.

How do I remove filter arrows but keep the rows visible?
Click inside the filtered range or table and choose Data > Sort & Filter > Clear first, then choose Data > Sort & Filter > Filter to turn off the arrows. Clearing first helps ensure all filter criteria are removed before you disable the controls.

Can I clear a filter from only one column?
Yes. Open that column’s dropdown and choose Clear Filter From [column name]. Filters in other columns remain active, so use the overall Clear command if you want every condition removed.

Why are some rows still hidden after I turn off Filter?
Those rows may have been hidden manually, collapsed in an outline group, or affected by another feature. Look for skipped row numbers, select the affected rows and choose Unhide, or expand the outline group. Turning off AutoFilter does not reverse those separate actions.

Will clearing a filter undo a sort?
No. A sort changes the order of records, while a filter controls which records are shown. Clearing filter criteria makes filtered-out rows visible but keeps the current order. If necessary, sort the list again using the appropriate column and order.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.