To filter information in Excel, select a cell in your data, choose Data > Filter, then use the arrow in a column heading to choose which records to show. Filtering hides rows that do not match your choices; it does not delete them. You can narrow a list by text, numbers, dates, colors, or criteria in more than one column, making a large worksheet easier to inspect.
What You’ll Need
- An Excel worksheet with related information arranged in rows and columns, such as a customer list, inventory log, or sales report.
- A single heading row with a clear label for each column, for example Date, Region, Product, and Amount.
- Microsoft Excel for Microsoft 365 on Windows. The main steps also work in current Excel for Mac and Excel for the web, although some shortcuts and ribbon details can differ.
Before filtering, check that each row represents one record and each column contains one kind of information. For example, keep dates in a Date column and amounts in an Amount column rather than combining several kinds of details in one cell. If the data is important, save the workbook before making structural changes.
Step-by-Step
- Check the layout of your data. Put the column headings in one row above the records. Remove completely blank rows or columns from the middle of the list, since gaps can stop Excel from recognizing the whole range. Avoid merged cells in the heading row and keep each record together on one row. A blank row above the list is fine; the important point is that the data itself is a continuous block.
- Select a cell inside the list. Click any populated cell in the data rather than selecting only the column you plan to filter. Excel will usually detect the surrounding range automatically. If the worksheet contains adjacent blocks of data that should not be filtered together, select the exact range first, including its headings and all intended records.
- Turn on the filter controls. On the Windows ribbon, choose Data > Filter. You can also use Home > Sort & Filter > Filter. Small drop-down arrows should appear beside the column headings. On Mac, use Data > Filter; Excel for the web also provides Filter on the Data tab. In Windows desktop Excel, Ctrl+Shift+L toggles the filter controls on or off. Keyboard shortcuts can differ on Mac, so use the ribbon if that shortcut does not work.
- Open the menu for the column you want to filter. Click the arrow beside a heading, such as Region or Status. The menu contains a list of values currently found in that column, along with a search box and commands for filtering or sorting. The choices are based on the values in the selected data range, so a value that does not occur in the range will not appear.
- Choose specific values from the list. To show only selected entries, clear (Select All), tick the values you want, then click OK. For example, in a Region column, selecting North and West shows records from those two regions and hides the others. Use the search box in the menu to find a value in a long list, then select the matching entry. Search narrows the menu’s value list; it does not change the worksheet data.
- Use a condition when selecting individual values is not practical. In a text column, open the arrow and choose Text Filters, then select a condition such as Contains, Begins With, or Equals. In a numeric column, choose Number Filters and select a condition such as Greater Than, Between, or Top 10. Enter the requested text or number in the dialog and confirm. For example, a condition of Greater Than 100 in an Amount column displays rows whose amounts exceed 100.
- Filter dates using the date menu. Open the arrow in a column containing real Excel dates and choose Date Filters. Depending on the data, you can select a period such as This Month or Last Year, or use a condition such as Before, After, or Between. In the filter menu, dates may appear grouped by year, month, and day; expand or collapse the groups to select the level you need. If Excel treats dates as text, these date options may not appear or may not work as expected.
- Combine criteria from different columns. Apply a filter in one column, then open another column’s arrow and make a second selection. Excel displays only rows that meet both filters. For instance, filter Region to West and Status to Open to see open records from the West only. To show either of two values in the same column, select both values in that column’s list. Criteria in different columns are combined, while selected values within one column can provide alternatives.
- Check the result and clear it when finished. Look at the row numbers and filter icons to see that some rows are hidden and which columns have active filters. To remove one column’s condition, open its arrow and choose Clear Filter From followed by the column name. To clear all conditions while keeping the arrows, choose Data > Clear. To remove the arrows entirely, choose Data > Filter again. Clearing a filter reveals the hidden records; it does not restore a particular prior selection if you have changed it.
For a list you filter often, consider converting the range into an Excel table. Select a cell in the data and choose Insert > Table, confirm that the range is correct, and make sure My table has headers is selected if it does. Tables include filter arrows in their headings and usually extend their range as you add records. This is useful for recurring reports, but check that the table includes every intended row before relying on it.
Common Mistakes
- Filtering only part of the list. If the range was selected incorrectly or a blank row split the data, related records may remain visible or be excluded. Clear the filter, remove gaps within the list, and apply Filter again from a cell in the correct data block.
- Assuming hidden rows were deleted. A filter only hides records that do not match. Clear the filter to display them again. Do not delete rows just because they disappeared from view.
- Using inconsistent data types. A column that mixes numbers with text versions of numbers, or real dates with date-looking text, can produce confusing filter choices. Standardize the values in the source data before filtering, and inspect cells that do not behave like their neighbors.
- Forgetting an earlier filter. A second column’s filter works alongside the first. If the result is unexpectedly small or empty, inspect every heading with a filter icon and clear conditions you no longer need.
- Confusing filtering with sorting. Filtering hides rows that do not meet criteria. Sorting changes the order of visible records. If you want every record to remain on screen but arranged by date or amount, use the sort commands instead.
- Expecting a selection to update itself in every situation. If you add or change records while a filter is active, the displayed results may need refreshing. Use Data > Reapply to apply the current filter conditions again.
Tips
- Use descriptive, distinct headings. Names such as Order Date and Delivery Date are easier to identify than repeated headings such as Date. Clear headings also make it less likely that you will filter the wrong field.
- Filter one question at a time. Start with a broad condition, check the result, and then add another column’s condition. This makes it easier to identify which criterion removed a record you expected to see.
- Use the menu search for long lists. In a column with hundreds of distinct customer or product names, searching the filter list is faster than scrolling. Clear the search or reopen the menu when you need to select other values.
- Use color filtering only when color has meaning. If cells are filled with colors or use font colors, the filter menu may offer Filter by Color. Choose the relevant color to show matching rows. Color is a visual formatting choice, so do not rely on it as the only record of a category if the workbook is shared or colors may change.
- Keep a copy of the original data. Filtering is non-destructive, but later edits or deletions can affect the records themselves. For a one-off analysis, duplicate the worksheet or save a copy before making broad changes.
- Check the status bar and row numbers. Filtered-out rows are hidden, and the visible row numbers are not necessarily consecutive. This is normal and can help confirm that the list is being filtered rather than rearranged.
Troubleshooting
No arrows appear after choosing Filter: Click inside the data and choose Data > Filter again. Confirm that the worksheet is not protected in a way that prevents filtering, and check whether you selected a range with no recognizable heading row. If the commands appear unavailable, check the workbook’s editing or protection status.
Some records are missing from the filter range: Clear the filters, inspect for blank rows or columns that divide the list, and click inside the relevant block before turning Filter on. If necessary, select the complete range manually, including the heading row, and apply the filter again. A table’s boundary can be checked by selecting a cell in it and looking at the table design options.
A value you expect is not listed: First clear the filter in that column and any filters in other columns. Then look for extra spaces, spelling differences, or values stored in a different format. A cell containing “North ” with a trailing space may not behave like “North.” If new records were added after filtering, choose Data > Reapply and verify that those records are inside the filtered range.
Date or number filters behave like text filters: Excel may be reading the entries as text because the cells were imported or formatted inconsistently. Check several cells, correct the underlying values to actual dates or numbers, and apply the filter again. Changing a cell’s appearance with number formatting alone does not always convert text into a true numeric value.
The sheet looks empty after applying a condition: Open each active filter menu and clear it, then apply the conditions again one at a time. Verify the spelling, date range, decimal values, and comparison operator. Multiple column filters must all be satisfied, so one restrictive condition can hide every row even when another filter is correct.
Frequently Asked Questions
Does filtering delete information in Excel?
No. A filter hides rows that do not match the selected criteria. Use Data > Clear or clear a particular column’s filter to display the hidden rows again. Deleting visible rows is a separate action and can permanently remove data.
Can I apply more than one filter at once?
Yes. Apply conditions in multiple columns to show only records meeting all those conditions. For alternatives in a single column, select multiple values from that column’s checklist, or use its filter condition options where appropriate.
Can I filter by partial text?
Yes. Open the arrow for a text column, choose Text Filters > Contains, and enter the part of the text to match. You can also use the filter menu’s search box to locate entries in its value list.
How do I show every row again?
Choose Data > Clear to clear the active filter conditions while keeping the filter arrows. Or open a particular column’s arrow and choose Clear Filter From to clear only that column’s condition.
Will filtering change the order of my records?
Filtering by itself hides rows and leaves the order of the remaining records unchanged. If you choose a sort command from a filter menu, Excel also changes the order. Use Clear to remove filters, and sort only when reordering is what you intend.
Can I filter an Excel table?
Yes. Tables have filter controls in their header row. Use the same column menus to select values or conditions. When you add records directly below a table, Excel generally expands the table, but verify that new records are included if they do not appear in the filter results.
Related
- How to Enter a Negative Number in Excel Correctly
- Enter Serial Numbers in Excel: Manual and Automatic Methods
- How to Enable Word Wrap in Excel and Adjust Cell Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Filter by Text Color in Excel: A Step-by-Step GuideTo filter by text color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Calculation for Hours Worked: Formulas and TipsFor a shift with start time in A2, end time in B2, and unpaid break duration in C2,…
- Excel Group Dates by Month in a Filter: Steps & FixesTo group dates by month in an Excel filter, make sure the column contains real Excel dates, then…
- Excel INDIRECT Function: How to Use It with Cell ReferencesTo make Excel interpret text as a cell reference, use INDIRECT. With “B3” in A1 and 42 in…
- Excel Formula Between Two Numbers: ExamplesTo check whether a value in A2 is between a lower limit in B2 and an upper limit…
- Data Validation List Formula: Excel Examples and SetupIn Excel, a data validation list formula goes in Data > Data Validation > Settings > Allow: List…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.