To enable sorting and filtering in Excel for Microsoft 365 on Windows, click a cell in your data range, then select Data > Filter. Excel adds a drop-down arrow to each header so you can filter records or sort them by that column. For reliable results, first make sure the data is arranged in one continuous range with a clear header row and no blank rows splitting the list.
What You’ll Need
- An Excel workbook containing a list or table of data.
- A header row with a distinct label for each column, such as Date, Customer, or Amount.
- Permission to edit the worksheet. If the sheet is protected, you may need its password or the workbook owner’s help.
You do not need to install an add-in or create a formula. The steps below apply to Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web also provide filters; where menu access differs, it is noted below.
Step-by-Step
- Check and prepare the data. Arrange each record on its own row and each type of information in its own column. Put descriptive, unique labels in the first row of the range. For example, a sales list might have headers for Order Date, Region, Product, and Sales. Remove completely blank rows or columns inside the list, and avoid merged cells in the range. Those gaps and merged cells can make Excel detect only part of the data or prevent a sort. If the worksheet contains unrelated information nearby, leave at least one blank column between it and the list so Excel does not include it by mistake.
- Select a cell in the list. Click any populated cell inside the data rather than selecting one column alone. Excel usually detects the surrounding continuous range automatically. If you want to control exactly what is included, select the full range, including the header row and every record. For example, if the headers are in A1:D1 and records run through row 200, select A1:D200. Include all columns that belong to each record; otherwise, a sort can separate a name from its associated date or amount.
- Turn on the filter arrows. On the ribbon, select Data > Filter. You can also use Home > Sort & Filter > Filter. Small drop-down arrows should appear in the header cells. On Windows,
Ctrl+Shift+Ltoggles AutoFilter on or off when the worksheet is active. On Mac, use Data > Filter if the shortcut differs in your keyboard setup; the ribbon command is the dependable option. In Excel for the web, select Data > Filter in the ribbon. - Filter the records you want to see. Select the arrow beside a header. In its menu, clear (Select All), tick the values to keep, and choose OK. For instance, in a Region column, selecting West displays rows whose Region value is West and hides the other rows without deleting them. The check boxes show the distinct values Excel found in that column. To narrow a long list, use the search box in the menu, type part of a value, select the matching item, and apply the filter. The arrow changes to indicate that the column is filtered.
- Use a text, number, or date condition when needed. Open the relevant header arrow and choose the appropriate filter option, such as Text Filters, Number Filters, or Date Filters. The exact options depend on the data type Excel recognizes. A number column can be filtered to show values greater than a chosen amount; a date column can be narrowed to a period such as last month. Follow the dialog prompts, enter the condition, and confirm. If the menu only shows individual values when you expected a number or date condition, check whether the cells are stored as text.
- Sort by one column. Open a header arrow and choose a sort direction, such as Sort A to Z or Sort Z to A for text, or the corresponding smallest-to-largest or oldest-to-newest option for numbers and dates. You can also select Data > Sort A to Z or Data > Sort Z to A after clicking a cell in the column. Excel should move whole rows together so their fields remain associated. If prompted to expand the selection, choose Expand the selection to sort the related columns with it.
- Sort using more than one column, if required. Click inside the list and select Data > Sort to open the Sort dialog. Check My data has headers if your first row contains labels. Choose the primary column under Sort by, choose what to sort on, and set the order. Select Add Level to add a secondary rule—for example, sort by Region first, then by Customer within each region. Choose OK to apply the sort. The order of levels matters: Excel applies the first level before the next one.
- Clear or remove the filter when finished. To show all records again, open a filtered column’s arrow and choose Clear Filter From that column. To remove every active filter while leaving the arrows available, use Data > Clear. To turn off the arrows entirely, select Data > Filter again. Clearing a filter reveals hidden rows; it does not restore an earlier sort order. If you need the original order later, add a numbered ID or sequence column before sorting.
For a list you will keep updating, consider converting it to an Excel table: select a cell in the range and choose Insert > Table, confirm the range, and ensure My table has headers is selected. Tables include filter arrows by default and generally extend sorting and filtering to new rows added directly below them. This is an alternative way to get filter controls, not a requirement for using the Data tab.
Common Mistakes
- Sorting only one column. Selecting a single column and sorting it by itself can detach values from the records in the other columns. Sort from a cell in the complete range, or select the whole range, and expand the selection if Excel asks.
- Including the header as a record. If Excel sorts the header label among the data, undo immediately and repeat using the Sort dialog with My data has headers checked. Confirm that the header row is included in the selected range.
- Leaving blank rows inside the list. A blank row may cause automatic range detection to stop early. Remove the gap or select the complete range manually before applying the filter or sort.
- Mixing data types in a column. Numbers stored as text, dates entered in inconsistent formats, or extra spaces can produce unexpected filter choices and sort order. Standardize the values and check how Excel recognizes the cells.
- Assuming a filter deletes data. Filtering hides rows that do not match the conditions. It does not remove them. A sort, by contrast, rearranges rows, so save a copy first if the original order matters.
Tips
- Use concise, distinct headers. A label such as Status is more useful than a blank heading or two columns both called Column1.
- Filter one column at a time to understand the results, then add another filter if needed. Filters on different columns work together: a row must meet all active column conditions to remain visible.
- Use the search field in a filter menu for a long list of values. Search helps you find entries, but remember to tick the matching value and apply the menu rather than assuming typing alone filters the range.
- For repeatable, multi-column ordering, use Data > Sort rather than sorting a column by eye. The Sort dialog makes the priority of each level explicit.
- Before a major sort, consider adding a temporary sequence column with the original row order. If necessary, sort by that column later to restore the sequence, then remove it.
- When you are finished reviewing a filtered list, check the status bar and the visible rows, and clear filters before sharing if you want recipients to see the full dataset.
Troubleshooting
No arrows appear after choosing Filter: Check that you clicked inside the intended range and that the worksheet is not protected. A protected sheet may block changes to AutoFilter settings; ask the owner to unprotect it or allow filtering. Also check that you are not editing a cell, viewing a chart sheet, or working in a layout where the command is unavailable. Try selecting the range explicitly and choosing Data > Filter again.
The filter covers only some rows or columns: Look for a blank row or column dividing the data. Clear the gap if appropriate, then select the complete range—including its headers—and reapply Filter. If unrelated data is adjacent, select only the intended range rather than relying on automatic detection.
The sort puts numbers or dates in an odd order: Some entries may be stored as text. Check for left-aligned numbers, apostrophes, spaces, or inconsistent date formats. Convert the values to a consistent number or date format, then sort again. Formatting cells as dates or numbers does not always convert text that was already entered; use Excel’s conversion tools or re-enter a corrected value where needed.
Rows seem to disappear: Open each active filter menu and choose Clear Filter From that column, or select Data > Clear to clear the current filters. Also check for manually hidden rows. A filtered row is still in the worksheet, but it will not appear until the condition is cleared or changed.
The arrows or commands are unavailable: Confirm you can edit the worksheet and that the workbook is not restricted by protection or permissions. If you are collaborating in a shared workbook, ask whether your access level allows changes. In Excel for Mac or the web, use the Data tab’s Filter command rather than relying on a Windows keyboard shortcut.
Frequently Asked Questions
How do I turn on sort and filter for a whole Excel sheet?
Click a cell in the data and choose Data > Filter. Excel adds filter arrows to the detected header row. For the most reliable range, make sure the list has one header row and no blank rows or columns breaking it up. Filtering controls are applied to a range or table, not automatically to every unrelated cell on the worksheet.
Can I sort and filter at the same time?
Yes. Sorting changes the order of the records, while filtering hides records that do not meet selected conditions. You can apply filters in one or more columns and sort the visible data. Clear the filters to show all records again; the sort order remains until you change it.
Why does Excel sort my header row with the data?
Excel may not have recognized that the range has headers, or the header row may not have been included correctly. Undo the sort, select the full range, choose Data > Sort, and check My data has headers. Give every column a clear label to make header detection more reliable.
How do I remove the filter but keep the sorted order?
Choose Data > Clear to remove active filter conditions while leaving the filter controls in place, or clear each column’s filter individually. The sort order stays as it is. If you want to remove the arrows too, choose Data > Filter to toggle them off.
Do Excel tables have sort and filter automatically?
Yes. When you create a table with Insert > Table and confirm that it has headers, Excel adds filter arrows to the table headings. You can sort or filter from those arrows. Tables are useful for lists that grow because new rows added to the table are usually included in its range.
Are the steps different in Excel for Mac or the web?
The core process is similar: select the list and use Data > Filter, then use the header arrows to sort or filter. Ribbon placement and shortcut behavior can vary by platform and keyboard settings, so use the menu command if a Windows shortcut does not apply. Excel for the web uses the Data tab in the browser interface.
Related
- Delete a Hidden Row in Excel: Safe Steps That Work
- How to Delete Gridlines in Excel for a Cleaner Sheet
- How to Delete Grouping in Excel: Rows, Columns & Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UPPER Function: How to Make Text All CapsExcel’s uppercase text function is UPPER. In Microsoft Excel for Microsoft 365, enter =UPPER(A2) to convert the text…
- Excel SUBTOTAL Formula Examples for Filtering and TotalsWith sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70,…
- SUMIF in Excel: Total a Column by One CriterionTo total values in an Excel column when another column meets one condition, use SUMIF. With categories in…
- Excel Formula to Capitalize Names: What PROPER Gets RightTo capitalize names in Excel, enter =PROPER(A2) in a helper column when the name is in A2. PROPER…
- Excel Data Validation List Formula: Examples and FixesFor an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and…
- Concatenate in Excel with the Ampersand (&) OperatorTo concatenate text in Excel, enter an ampersand between the cell references you want to join. With a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.