To filter and sort data in Excel, select a cell in your list, choose Data > Filter to show dropdowns, and use a column’s dropdown to choose what to display. To reorder records, use the dropdown’s sort command or go to Data > Sort for multiple sorting levels. First make sure your data has one header row and that each record stays in a single row.
Filtering temporarily hides rows that do not meet your criteria; it does not remove them. Sorting changes the order of rows, so a safe sort must include every column belonging to each record. The steps below are for Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook containing a rectangular list of related records, such as orders, contacts, or inventory. Each row should represent one record and each column one type of information.
- A single row of distinct column headings, such as Date, Customer, and Amount. Avoid blank rows or columns inside the list and merged cells in the data area.
- Permission to edit the workbook. If the sheet is protected or the file is read-only, you may need the owner to enable editing before you can sort or filter.
You do not need to convert the list to a table, but doing so can make the range easier to manage. To create one, click inside the data and choose Insert > Table. Confirm the selected range and check My table has headers if the first row contains headings. Tables typically extend their filter controls when you add records directly below them.
Step-by-Step
- Prepare and inspect the list. Click a data cell and look across the list before turning on filtering. Check that every column has a heading, there are no completely blank rows splitting the records, and each row contains one complete record. A blank cell within a column is usually fine; a blank row across the whole list can make Excel treat the data above and below as separate blocks. If the list has a title or notes above it, keep those outside the header row and data range.
- Select the data range if needed. If your list is a continuous block, clicking one of its cells is usually enough for Excel to detect the range. For more control, drag to select the full rectangle, including the headings. Do not select just one column when you intend to sort records: that can put names, dates, or amounts out of alignment with the rest of each row. If you use a table, click anywhere in the table instead.
- Turn on filter controls. Choose Data > Filter on the ribbon. Dropdown arrows appear beside the headings. If Excel did not identify your header row correctly, undo the filter, select the intended range including its headings, and turn Filter on again. You can also use the Filter command on the Data tab for a table; its heading dropdowns are normally present as part of the table.
- Sort one column. Open the dropdown beside a heading. Choose Sort A to Z or Sort Z to A for text, Sort Smallest to Largest or Sort Largest to Smallest for numbers, or the appropriate oldest-to-newest or newest-to-oldest choice for dates. Excel sorts the associated rows with the selected column when it recognizes the complete list. After sorting, check a few records across multiple columns to confirm their values still belong together.
- Use the Sort dialog for more control. Click inside the list and choose Data > Sort. In the dialog, confirm that My data has headers is selected when the first row contains headings. Choose the column in Sort by, then select what to sort on, such as cell values, cell color, font color, or a conditional-formatting icon, and choose the order. Use Add Level to define a secondary sort. For example, sort by Department A to Z, then add a level for Start Date oldest to newest. Excel applies the first level, then orders ties using the next level.
- Filter to show matching rows. Open a heading dropdown and use the checklist to select the values you want to keep visible. Clear Select All, then check the desired values, and choose OK. For a long list of distinct values, type part of a value in the dropdown’s search box to narrow the checklist. Search helps you find items; you still need to select the relevant result and apply the filter.
- Apply a number, text, or date condition. In a column dropdown, point to or choose Number Filters, Text Filters, or Date Filters, depending on the data. Options can include equals, greater than, begins with, or a date period. Select the condition, enter the value or date, and confirm. For example, a Number Filter can show values greater than a threshold, while a Date Filter can show records from a particular month. Available choices depend on the values Excel recognizes in that column.
- Combine filters and review the result. Apply a filter to one column, then use another heading dropdown to add a second condition. Excel displays rows that meet the chosen criteria in every filtered column, so this narrows the results. Look for the filter indicator on the headings and the row numbers, which may skip hidden rows. When finished, save the workbook if you want to keep the current view, or clear the filters if you want the full list visible.
- Clear or turn off filtering. To remove a condition from one column, open its dropdown and choose Clear Filter From followed by the column name. To clear all criteria while leaving dropdown arrows available, choose Data > Sort & Filter > Clear. To remove the arrows, choose Data > Filter again. Clearing a filter reveals hidden records; it does not restore a previous sort order. If you need the original order, keep a copy of the data or add an index column before sorting.
On Excel for Mac, the main commands are also on the Data tab, though the ribbon layout and some dialog labels can differ slightly by release. Excel for the web provides sorting and filtering from the Data tab and heading dropdowns as well; some advanced sort options may be arranged differently or unavailable compared with the Windows desktop app.
Common Mistakes
- Sorting only one column. If Excel asks whether to expand the selection, choose Expand the selection to keep each row intact. Choosing to continue with the current selection can scramble which names, dates, and values belong together.
- Including an extra title row as a heading. A report title above the actual column names can confuse the detected range. Select the real headings and records, or convert only that range to a table.
- Mixing data types in a column. Numbers stored as text may not sort in numeric order, and dates stored as text may not behave like dates. Check that values use consistent formats and that imported entries are recognized as the intended type.
- Assuming a filter deletes records. Filtered-out rows are hidden from view, not erased. Do not copy or report only the visible results without checking whether that is what the task requires.
- Using a blank row to separate sections. A blank row may cause Excel to detect only part of the list. Keep one continuous dataset, or select the exact intended range before sorting or filtering.
Tips
- Use clear, unique headings. Short labels such as Region or Order Date make dropdowns easier to understand. Avoid duplicate or empty headings, particularly in a table.
- Make a backup before a major reorganization. Sorting can be undone immediately with Undo, but a long sequence of edits may make recovery inconvenient. A copy or an original-order index provides a reliable reference.
- Use a table for lists that grow. Tables include filter controls and usually extend formatting and formulas when new rows are added. To make a table, choose Insert > Table and confirm the header option.
- Choose a custom sort for grouped results. A multi-level sort is useful for grouping by team, then ordering each team by date or amount. Put the grouping field first and the within-group field second; use Add Level to set the order explicitly.
- Check the complete filtered range. A filter may have been applied to only part of a list if there was a blank row, an intervening column, or a manually selected range. Confirm that every intended heading has a dropdown and that the visible results include the expected records.
- Preserve the original sequence when it matters. Before sorting, add a numbered ID or sequence column to the records. You can later sort by that column to return to the starting order, provided the index stays attached to each record.
Troubleshooting
Filter arrows do not appear. Click inside the data and choose Data > Filter. If nothing happens, check whether the sheet is protected, whether the workbook is in a mode that restricts editing, or whether a selection outside the list is active. Select the intended data range, including the header row, and try again. Also check for merged heading cells, which can interfere with sorting and filtering.
Some records are missing from the filter or sort. Look for a fully blank row or column that separates part of the list from the rest. Select the entire intended range and reapply the command, or place the data in a table. Check that the records are within the selected range and that there are no hidden rows you are overlooking. When filtering by a value, clear the filter and inspect the source entries for extra spaces, inconsistent spelling, or different data types.
Numbers or dates appear in an unexpected order. Excel may be treating some entries as text. Compare how the values align, inspect their cell formats, and correct the underlying entries so the column uses a consistent data type. Changing the displayed number format alone may not convert text into a real number or date. If the values came from an import, use an appropriate conversion method or re-import them with the correct data type, then sort again.
Rows no longer match after sorting. Stop editing and use Undo immediately if it is available. Then repeat the sort by clicking in the list, choosing Data > Sort, and expanding the selection when Excel prompts you. Confirm that the entire dataset is included and that My data has headers is set correctly. If Undo is no longer available, restore a backup or compare with an original copy; do not try to repair row alignment by guessing.
A filter seems stuck or the expected rows do not return. Open each filtered column’s dropdown and clear its condition, since several filters can be active at once. Use Data > Sort & Filter > Clear to clear all active criteria without removing the dropdown arrows. If records remain absent, check for manually hidden rows or a filter applied to a different range.
Frequently Asked Questions
Can I filter and sort at the same time?
Yes. Apply a filter to show the records you need, then sort a column to arrange the visible results. Filtering controls which rows are shown, while sorting controls their order. Clear the criteria when you want to see all records again.
Does sorting change the data permanently?
Sorting changes the row order in the workbook, but it does not change the cell values themselves. Use Undo right after a sort to reverse it, or sort by a saved index column to restore an earlier sequence. Save a copy first if the original order is important.
How do I sort by two or more columns?
Click in the data and select Data > Sort. Choose the primary column and order, select Add Level, and configure each additional column. The top level has priority; later levels sort records that tie on the earlier level.
Can I filter for blank cells?
Yes. Open the relevant column dropdown and look for (Blanks) in the checklist. Select it to show rows with blank entries in that column. If the option is missing or results seem wrong, check whether the cells contain spaces or formulas returning an empty string rather than being truly blank.
How do I show every row again without removing the filter arrows?
Choose Data > Sort & Filter > Clear to clear active filter criteria. The dropdowns remain available so you can apply different criteria. Choosing Data > Filter toggles the dropdown controls themselves on or off.
Related
- How to Enable Merge and Center in Excel: Quick Fixes
- Forecast Sheet in Excel: Enable It and Build a Forecast
- Distinct Count in Excel PivotTables: How to Enable It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Scenario Manager Examples for What-If AnalysisExcel Scenario Manager examples include comparing low, expected, and high sales forecasts, testing different staffing plans, or checking…
- Excel Weekdays Between Dates: Formulas, Holidays, and TipsTo count weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the…
- Monthly Excel Budget Spreadsheet: Setup and FormulasTo make a monthly Excel budget spreadsheet, list spending categories in column A, planned amounts in column B,…
- Calendar Day Formula in Excel: Dates, Weekends & MoreFor the day number within a date, use =DAY(A2), where A2 contains an Excel date; for example, January…
- Excel Next Number in Sequence: Formulas and TipsWith the first number in A2, enter =A2+1 in A3 to calculate the next number in a sequence…
- Excel XLOOKUP Between Dates: Find a Date RangeTo find the value tied to the most recent start date on or before a date, use XLOOKUP…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.