Filtering and sorting are two of the most used data tools in Excel. Filtering hides rows that don’t meet your criteria so you can focus on specific records, while sorting rearranges rows by a chosen column (A to Z, smallest to largest, etc.). This guide walks you through both features step by step, including how to combine them, avoid common errors, and fix issues when things don’t work as expected. No prior Excel knowledge is needed beyond opening a workbook.
What You’ll Need
- A computer with Microsoft Excel (2010 or later; the steps are similar in Excel for Microsoft 365, Excel for Mac, and Google Sheets).
- A worksheet with data arranged in columns, ideally with a header row (e.g., Name, Date, Amount).
- Your data should be in a contiguous block—no completely blank rows or columns in the middle—so Excel can detect the range correctly.
Step-by-Step
- Select your data range. Click any single cell inside your data. If your data has a header row, include it in the selection. Excel usually auto-detects the range when you click a cell, but if you want to be explicit, drag to select all cells including headers.
- Turn on filtering. Go to the Data tab on the ribbon. In the Sort & Filter group, click the Filter button (it looks like a funnel). You’ll see small dropdown arrows appear in each header cell.
- Apply a basic filter. Click the dropdown arrow in the column you want to filter. A panel appears. You can:
- Uncheck Select All to clear all checkboxes, then check only the values you want to see (e.g., only “East” region).
- Use the search box at the top to quickly find a value.
- For numeric or date columns, choose Number Filters or Date Filters to set conditions like “Greater Than” or “Between”.
Click OK to apply. Rows that don’t match are hidden, but not deleted.
- Clear a filter. To remove a filter from a single column, click its dropdown and choose Clear Filter From “Column Name”. To clear all filters, click the Clear button in the Sort & Filter group on the Data tab.
- Sort data. Click the dropdown arrow in the column you want to sort by. Choose Sort A to Z (ascending) or Sort Z to A (descending) for text; for numbers, it will say Smallest to Largest or Largest to Smallest. For dates, it says Oldest to Newest or Newest to Oldest.
- Sort by multiple columns. If you need to sort by more than one column (e.g., first by Region, then by Sales), click the Sort button in the Sort & Filter group. In the dialog box, add levels: choose the first column, then click Add Level to add the second, and so on. Set the order for each. Click OK.
- Sort with a custom list. For a non-alphabetical order (like High, Medium, Low), use the Sort dialog. Under Order, choose Custom List, then select or create a list (e.g., High, Medium, Low) and click OK.
- Combine filter and sort. You can sort while a filter is active—the sort only applies to the visible rows. For example, filter to show only “East” region, then sort by Sales descending. The hidden rows stay hidden during the sort.
- Turn off filtering. When done, click the Filter button again to remove the dropdown arrows. Your data returns to its original order (unless you sorted it).
Common Mistakes
- Not including the header row. If you select data without headers, Excel treats the first row as data, so headers get sorted or filtered incorrectly.
- Blank rows or columns. A completely blank row or column in the middle of your data can break the range detection. Excel may filter only part of your data, leaving some rows visible when they shouldn’t be.
- Sorting only one column. If you select a single column and sort, Excel may ask if you want to expand the selection. If you choose “Continue with current selection,” only that column gets reordered, scrambling your rows.
- Merged cells. Filtering and sorting with merged cells can cause unpredictable behavior. Unmerge before applying filters or sorts.
- Forgetting that filtering hides rows, not deletes. Some users panic thinking data is gone. It’s just hidden—clear the filter to bring it back.
Tips
- Use keyboard shortcuts: Ctrl+Shift+L toggles filtering on and off. Alt+A+S opens the Sort dialog.
- Filter by color: If you’ve manually colored cells or fonts, the dropdown includes “Filter by Color” options, letting you filter by fill or font color.
- Use the status bar: When you filter, the status bar at the bottom shows “X of Y records found,” giving you a quick count.
- Copy filtered data: If you want to copy only visible rows, select the range, press Ctrl+G (Go To), click Special, choose Visible cells only, then copy. This avoids copying hidden rows.
- Turn your data into a Table: Select your data and press Ctrl+T. Tables have built-in filter arrows, and sorting/filtering is more robust—plus new rows automatically get included.
- Save a custom view: After setting up filters and sorts, go to View > Custom Views to save the layout so you can reapply it later.
Troubleshooting
- Filter arrows are missing: Ensure you’re on the Data tab and the Filter button is highlighted. If not, click it. If you’re using a Table, the arrows are always there.
- Sorting scrambles my data: You likely sorted only one column. Undo (Ctrl+Z) and use the Sort dialog with the whole range selected. Always ensure the “My data has headers” checkbox is ticked in the Sort dialog.
- Filter shows no results: Check for hidden rows or that your criteria are too restrictive. Clear the filter and try again with broader conditions.
- Numbers sort as text: If numbers appear as text (e.g., “10” before “2”), convert them to numbers. Select the column, use the warning icon that appears, and choose “Convert to Number.” Or use Text to Columns.
- Dates don’t filter correctly: Ensure your dates are real dates, not text. Select the column and change the format to Date. If they’re text, use the DATEVALUE function to convert.
- Filter not working on a new row: If you add a row below your data, it may not be included. Convert your data to a Table (Ctrl+T) so new rows are automatically part of the filter range.
Frequently Asked Questions
Q: How do I filter for blank cells?
Click the dropdown arrow, uncheck all values, then check the “(Blanks)” option at the bottom. This shows only rows where that column is empty.
Q: Can I filter by multiple columns at once?
Yes. Apply a filter on one column, then click the dropdown on another column and set its filter. Both filters apply simultaneously—only rows meeting both criteria are shown.
Q: How do I sort by row instead of column?
In the Sort dialog, click Options and choose “Sort left to right.” This lets you sort horizontally by a specific row.
Q: Does sorting affect my formulas?
Sorting rearranges rows, so formulas that reference cells will update to the new positions. However, if a formula references a specific cell (e.g., =A1), it will still point to that cell, which may now contain different data. Use absolute references or structured references in Tables to avoid issues.
Q: How do I remove duplicate rows after filtering?
Use the Data tab > Remove Duplicates. It works on the entire range, not just visible rows. If you want to remove duplicates from filtered data, copy the visible rows to a new sheet first.
Q: Why does my filter not show all unique values?
If you have a large dataset, Excel may show a limited list. Click the dropdown and type the value in the search box to find it. Also, ensure there are no hidden rows or that the range is correct.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Enable Privacy Indicators on Chromebook
- How to Calculate Taxes on Overtime Pay
- VPN Not Working on iPhone? Fixes That Actually Work
- Hide & Unhide Apps on Samsung Phones
- Fix DNS Server Unavailable Error in Windows 11
- Fix iOS 17 Keyboard Not Working Properly