How to Clear an A to Z Filter in Excel and Show All Rows

Formulas & functions · Updated November 20, 2026

To show all rows hidden by a filter in Excel for Microsoft 365 on Windows, open the filtered column’s drop-down arrow and choose Clear Filter From [Column]. To remove filter criteria from the whole sheet, go to Data > Sort & Filter > Clear. Note that A-to-Z sorting and filtering are different: clearing a filter reveals rows, but it does not necessarily return sorted data to its original order.

What You’ll Need

  • An Excel workbook with the filtered list or table open.
  • Permission to edit the workbook. If it is read-only or protected, you may need the owner to allow changes before you can clear or adjust its filters.
  • The name of the column, if you want to clear one column’s filter without changing other filters.

You do not need a formula or add-in. If the workbook contains important data, it is sensible to save a copy before changing its sort or filter settings, especially if you are unsure how the list was arranged originally.

Step-by-Step

  1. Open the worksheet and identify the list. Click a cell inside the filtered range or Excel table. Look across the header row for the column with a funnel icon or a sort arrow. A funnel usually indicates that a filter is applied; an A-to-Z or Z-to-A arrow indicates a sort order. A header can show sorting even when no rows are hidden by a filter.
  2. Open the relevant column menu. Select the drop-down arrow in the header of the column that has the filter. In a table, the arrows are part of the table’s header row. In a regular range, they appear when AutoFilter is enabled. If you do not see the arrows, click the list and choose Data > Sort & Filter > Filter to turn on the filter controls. Turning the controls on does not itself choose a filter condition.
  3. Clear the filter from just that column. In the drop-down menu, select Clear Filter From [Column], where Excel fills in the column name. The wording may appear with slightly different capitalization. Excel removes that column’s criteria and displays the rows it had hidden. Any filters on other columns remain in effect, so the worksheet may still show only part of the list.
  4. Clear filters from the whole worksheet when needed. If you want to display every row affected by filters on the current sheet, select a cell in the data and choose Data > Sort & Filter > Clear. This clears the active filter criteria so the rows are shown again. Check the list afterward: if a separate filter is applied to another range, or rows are hidden manually, you may need to address that separately.
  5. Check whether the list is still sorted A to Z. Clearing a filter does not mean the original row order is restored. If the values remain alphabetized, the list was sorted, or was sorted as well as filtered. Look for the sort arrow in the header and decide whether you only needed to show all rows or also need to undo the sort.
  6. Undo a recent sort if it is the last change. If you sorted the list just now and have not made later changes you need to keep, press Ctrl+Z on Windows. This may reverse the sort and restore the previous order. On Mac, use Command+Z. Undo is not a reliable way to recover the original order if the sort is no longer the latest undoable action, or if the workbook has been closed and reopened.
  7. Confirm that all expected records are visible. Scroll through the list and check its last rows, or compare the visible records with a known total. If records are still missing, look for filters on other columns, manually hidden rows, grouped or collapsed rows, and data outside the filtered range. Save the workbook once the display is correct.

To remove the filter arrows too: click inside the filtered range, then choose Data > Sort & Filter > Filter to turn AutoFilter off. This hides the drop-down controls; it is different from clearing filter criteria. For an Excel table, the header controls are part of the table design, so turning off a worksheet filter may not work the same way as it does for a regular range. You can still clear a table’s criteria from its column menus.

Mac and web: In Excel for Mac, use the column’s filter menu to clear its criteria, or look for Data > Clear on the Data ribbon to clear sheet-level filter criteria. The ribbon arrangement can differ slightly by version. Excel for the web also provides filter menus in the column headers; use the clear-filter option there. If the wording or placement differs, use the column menu rather than relying on the Windows ribbon layout.

Common Mistakes

  • Confusing a sort with a filter. A-to-Z sorts values into alphabetical order; it does not, by itself, hide rows. A filter limits which records are displayed. If every record is visible but alphabetized, clearing a filter will not put the list back in its earlier sequence.
  • Clearing only one column when several are filtered. Clearing the menu for one column removes that column’s condition, not necessarily the conditions on the rest of the sheet. Use the sheet-level Clear command when the goal is to remove all active filter criteria on that worksheet.
  • Turning off the arrows and expecting that to clear a sort. The Filter button controls whether filter drop-downs are displayed for a range. It is not a dependable way to reverse an A-to-Z sort or recover an earlier row order.
  • Selecting just part of a list. If headers or records fall outside the range Excel recognizes, a filter can affect only part of the data. Keep each record in a continuous range with one header row, and avoid blank rows or columns inside the list.
  • Assuming hidden rows are filtered. A row hidden with the Hide command, by grouping, or by a collapsed outline may remain hidden after filter criteria are cleared. Check the row numbers and outline controls if the list still appears incomplete.
  • Sorting one column without its related data. If Excel asks whether to expand the selection while sorting, choose to expand it so each row’s related values move together. Sorting a single column alone can mismatch names, dates, or other fields across records.

Tips

  • Use the funnel as a clue. A funnel in a header is a useful sign that a filter condition is active. A sort arrow without the funnel generally points to a sorted column rather than a filtered one. Icons can look slightly different across Excel versions, so open the menu to confirm.
  • Clear one condition when you want to preserve the rest. For example, if a sales list is filtered to show one region and one quarter, clear only the quarter filter to see all quarters for that region. This is more precise than clearing every condition on the sheet.
  • Keep a source-order column for important lists. If you may need to return to the original sequence after sorting, add an index column before sorting, with values such as 1, 2, 3, and so on. Sorting by that column later can restore the sequence, provided the index stays attached to each record.
  • Turn the range into a table when appropriate. Select a cell in the data and choose Insert > Table, confirming that the data has headers if it does. Tables make it easier to work with a consistent header row and filter each field. Avoid including unrelated notes or totals in the table unless they belong in the records.
  • Use Undo promptly for an accidental sort. Ctrl+Z on Windows or Command+Z on Mac is most useful immediately after the sort. If you have made other edits since, review what Undo would reverse before using it, because it may undo those later edits first.
  • Save a known-good copy before reorganizing shared data. A filter changes what is displayed, while a sort changes the row order. In a shared or important workbook, preserving a copy or a stable index column can prevent confusion if someone later needs the previous sequence.

Troubleshooting

The Clear Filter option is missing. Open the header menu and check whether any filter condition is actually active. If the list is simply sorted A to Z, Excel may not offer a clear-filter command for that column. If filter arrows are missing, click in the list and enable them with Data > Sort & Filter > Filter. Also make sure you are opening the header menu, not a cell’s right-click menu.

Some rows are still missing after clearing. Clear filter criteria in other columns as well, or use Data > Sort & Filter > Clear for the current sheet. Then inspect row numbers for hidden rows and check whether an outline is collapsed. If the data is in separate filtered ranges, clearing one range will not necessarily change the other. Unprotect the sheet only if you have authorization and the password or other required access.

The records are visible but remain in alphabetical order. That is expected if the list was sorted. Use Undo if the sort is still the latest action. Otherwise, sort by a saved original-order or ID column if one exists. Without a preserved sequence, a backup, or an available version history, Excel cannot determine the exact order the rows had before a sort.

The Clear command seems to affect the wrong data. First click inside the intended list and check that Excel has recognized its full range and header row. If the worksheet contains multiple independent lists, make sure you are working in the right one. Save a copy before making further changes, then clear the criteria from the specific column menu rather than changing filters elsewhere.

The worksheet will not let you change the filter. The sheet may be protected, the file may be read-only, or you may lack editing permission. Check the workbook’s status and ask its owner for access if needed. Do not remove protection or overwrite a shared file unless you are allowed to do so.

Frequently Asked Questions

Does clearing an A-to-Z filter put the rows back in their original order?
Not usually. Clearing a filter shows rows excluded by the criteria. A-to-Z is a sort operation, and the original order is recoverable only if you can undo the sort or sort by a saved original-order field or another reliable source.

How do I clear one column’s filter without removing the others?
Open that column’s header drop-down and choose Clear Filter From [Column]. Avoid the sheet-level Clear command if you want to preserve other columns’ filter criteria.

How can I tell whether a column is filtered or just sorted?
A funnel icon generally indicates an active filter, while an A-to-Z or Z-to-A arrow indicates a sort direction. Open the header menu to inspect its selections, and check whether any records are actually hidden.

Will clearing a filter delete any data?
No. Clearing filter criteria changes which rows are displayed; it does not delete the records. Sorting changes their order, so it is still wise to save a copy before reorganizing important data.

Can I clear filters on every worksheet at once?
The Data tab’s Clear command applies to the current worksheet, not automatically to every sheet in the workbook. Visit each worksheet that has filtered data and clear its criteria there.

What if the list is still incomplete after I clear all filters?
Check for manually hidden rows, collapsed groups, a separate filtered range, or a filter that applies to only part of the data. Also verify that the expected records are inside the range or table you are checking.

Related

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.