To filter data in Excel, click a cell in your list, choose Data > Filter, then use the arrow in a column heading to select the values or criteria to display. Excel hides rows that do not match; it does not delete them. If you mean a separate filter view for a shared workbook, create a Sheet View first so your filtering does not change what collaborators see.
What You’ll Need
- A workbook in Excel for Microsoft 365 on Windows, with a rectangular list of data and one clearly labeled heading row.
- For a collaborative Sheet View, a workbook saved to OneDrive or SharePoint and opened in a version of Excel that supports Sheet Views. A regular AutoFilter works without a cloud workbook.
- Optional: a backup copy if you will also sort, rearrange, or edit the source data. Filtering itself only hides rows, but other changes can alter the workbook.
Step-by-Step
- Check and prepare the data. Make sure every column has a heading, each row represents one record, and there are no completely blank rows or columns inside the list. For example, a sales list might have headings for Date, Region, Product, and Amount. Keep one type of information in each column: dates in Date, numbers in Amount, and consistent text in Region. Remove merged cells from the data area because they can prevent Excel from recognizing and filtering the range correctly. A blank row above the headings is fine, but avoid a second heading row within the list.
- Choose a regular filter or a separate view. Use a regular filter if you are working alone or if it is acceptable for the visible rows to change for everyone sharing the workbook. To make an isolated view in a shared workbook, first go to View > Sheet View > New. Excel should show an indicator that you are in a temporary view. Use that view for the filtering steps below. If the Sheet View control is unavailable, check that the workbook is in a supported format and saved to OneDrive or SharePoint, then reopen it in a supported Excel version.
- Turn on the filter arrows. Click any cell inside the data list. On the ribbon, choose Data > Filter. Excel adds a drop-down arrow to each heading in the detected range. If the arrows appear on only part of the list, turn the filter off, select the full range including its headings, and choose Data > Filter again. You can also format the range as a table with Home > Format as Table; confirm that My table has headers is selected. Tables include filter arrows by default and usually expand as you add rows beneath them.
- Open the column menu you want to filter. Select the arrow in a heading such as Region. The menu lists distinct values found in that column, along with a search box and a Select All option. Clear Select All, tick the values you want to keep visible, and select OK. For example, select West and Central to see only records from those regions. The checkboxes control which entries remain displayed; a checked value is included, not excluded.
- Use a text, number, or date condition when a checklist is not enough. In the column menu, point to or select Text Filters, Number Filters, or Date Filters, depending on the data type. Choose a condition such as Contains, Greater Than, or Before, enter the value, and confirm. To find amounts above 500, use Number Filters > Greater Than and enter 500. To show dates in a period, use the date filter choices or a custom date condition. Check how dates and decimal values are stored if Excel offers the wrong filter type; values entered as text may not be treated as real dates or numbers.
- Combine filters across columns. Apply a filter to one heading, then open another heading and choose its criteria. Excel keeps rows that satisfy the selected conditions in each filtered column. For instance, choose Region = West and Product = Chair to show only West-region chair records. To filter one column by more than one condition, use its Custom Filter option where available and choose whether the conditions should be joined by And or Or. And requires both conditions; Or accepts either one.
- Review the filtered results. Look at the row numbers and filter icons to see that some rows are hidden and which columns have active criteria. Read a few results against the headings to confirm the filter did what you intended. A filter can show no rows if the conditions are too restrictive or if the values do not match exactly. Remember that totals calculated with ordinary formulas may still include hidden rows; filtering does not automatically make every formula a visible-rows-only calculation.
- Clear or save the view when finished. To remove one column’s condition, open its heading menu and choose Clear Filter From [column name]. To remove all active criteria while leaving the arrows on, choose Data > Sort & Filter > Clear. Choose Data > Filter again to turn the arrows off. In a Sheet View, use View > Sheet View to switch to another view or exit the current one; use the Sheet View controls to keep or rename a view if those options are available in your Excel build. Save the workbook so changes to saved views and other edits are retained.
On Excel for Mac, the core process is the same: use Data > Filter, then select the heading arrows and choose values or conditions. Ribbon wording or the position of Sheet View controls can differ slightly by build. Excel for the web also supports basic filtering and Sheet Views, though some detailed commands may appear in different menus. If you cannot find a control, use the ribbon search box to search for its command name.
Common Mistakes
Filtering only part of the list: If a blank row or column splits the records, Excel may detect only one section. Select the entire range before turning on Filter, or convert the data to a table so the range is easier to manage. Check that the arrows are present on every intended heading.
Thinking hidden rows were deleted: A filter hides records that do not meet the criteria. Clear the filter to display them again. Do not copy only the visible results over the original list unless you mean to replace the source data; work on a copy if you need a separate extract.
Mixing data types in a column: A column that contains real dates alongside date-looking text, or numbers alongside entries such as “unknown,” may offer confusing filters and sort results. Standardize the values and convert text dates or numbers where appropriate. Do not change data blindly: confirm that conversion preserves the intended values.
Using a normal filter in a shared sheet when isolation matters: A standard filter changes the displayed rows in the worksheet, which can affect collaborators viewing that sheet. Use a Sheet View when you need an individual filter arrangement in a supported shared workbook. Sheet Views do not give permission to edit locked cells or change access rights.
Assuming a filter changes formula results automatically: A regular SUM may still include values in hidden rows. If you need a total for visible filtered rows, use a suitable subtotal approach such as the SUBTOTAL function, and verify that its function number and data range match your purpose.
Tips
Give headings short, unique names. Clear labels make the filter menu easier to scan and help prevent selecting the wrong field. Avoid leaving a heading blank; Excel may create a generic name when a range becomes a table, making later references less clear.
Use the search box in a heading menu when a column contains many unique entries. Search narrows the checklist; it does not change the underlying data. After searching, review which checkboxes are selected before choosing OK, especially if you previously applied a different selection.
For a reusable working range, choose Home > Format as Table and select a style. Tables make it easier to keep headings associated with records and generally include new adjacent rows when data is added. Give the table a meaningful name from the Table Design tab if you refer to it in formulas or documentation.
When preparing a report, record the criteria you used, such as “Region is West; Amount is greater than 500.” That makes the result easier to reproduce and helps another person understand why certain rows are not visible. Consider naming saved Sheet Views for their purpose, such as “West region review,” rather than leaving several similarly named views.
Sort and filter are different operations. Filtering hides rows that do not meet conditions; sorting rearranges the rows that remain visible. If you sort a field in a list, Excel normally sorts whole records together when the range is correctly detected. Still, confirm that the full table is selected so names, dates, and amounts do not become separated across rows.
Troubleshooting
No filter arrows appear: Click inside the data and choose Data > Filter. If that does not work, check whether the worksheet is protected, whether you selected a cell inside the intended range, and whether merged cells or blank breaks are interfering. Select the full list and apply the command again. If the file is in an older format, save a copy as an Excel workbook (.xlsx), if appropriate, and retry.
A value you expect is missing from the checklist: Clear any search text in the menu and check for active filters in other columns, since another condition may already be hiding the relevant row. Confirm that the value is actually in the filtered range, not below a blank separator. Watch for leading or trailing spaces and small spelling differences, such as “North” and “North ”, which can appear as separate entries.
The filter shows no results: Reopen each active filter and confirm the selected values and conditions. Clear the filters and apply them one at a time to find the condition that removes all rows. For dates and numbers, verify that the source values are stored as dates and numbers rather than text. Check the boundary too: Greater Than 500 excludes exactly 500, while Greater Than or Equal To 500 includes it.
New rows are not included: If you used a plain range, new entries may have been added outside the filtered area. Turn off the filter, select the expanded range, and turn it back on, or use a table that can expand with adjacent new records. Avoid inserting a blank row between the old list and new records.
Sheet View is missing or seems to affect everyone: Confirm that you created a Sheet View from View > Sheet View > New rather than applying a regular filter. Check that the workbook is saved in OneDrive or SharePoint and that your Excel version supports Sheet Views. If you are editing a local file or using a version without the feature, use a separate copy or worksheet for your own analysis instead of assuming a normal filter is private.
Filtered totals look too large: Check whether the formula includes hidden records. A standard SUM can include values in rows hidden by a filter. If the result should reflect only visible rows, use a formula designed for filtered lists, and test it by changing the filter to make sure the total responds as expected.
Frequently Asked Questions
What is the difference between a filter and a Sheet View?
A filter selects which rows are visible. A Sheet View is a separate display arrangement in supported Excel workbooks, useful when collaborators need different filters or sorts without changing one another’s view. The filter criteria still do not delete or rewrite the records.
Can I filter more than one column at once?
Yes. Apply a condition in each relevant column menu. Excel shows records that meet the conditions across the filtered columns. For example, filtering Region to East and Status to Open shows rows that are both in East and Open.
How do I show all rows again?
Choose Data > Sort & Filter > Clear to remove the active criteria while keeping the filter controls, or choose Data > Filter to turn filtering off. You can also clear an individual condition from that column’s heading menu.
Does filtering delete or move data?
No. A filter hides nonmatching rows in place; it does not delete them. Sorting, by contrast, changes the order of records. Use Clear to restore visibility, and save a separate copy before making structural edits if the original order matters.
Can I filter blank cells?
Usually, yes. Open the column menu and select or clear (Blanks), depending on whether you want blank or nonblank entries. If blanks are not listed as expected, check for cells containing spaces or formulas that return an empty string; those may not behave like truly empty cells.
Why does the same filter affect my coworker?
You may be using the worksheet’s regular AutoFilter rather than a Sheet View. In a shared file, create a view from View > Sheet View > New, then apply your filters there. If Sheet View is not supported or available for the workbook, coordinate before changing shared filters or use a separate copy.
Related
- How to Export an Excel Tab to a New Workbook
- Export Excel List to SharePoint: Step-by-Step Guide
- How to Export an Active Directory User List to Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ABC-XYZ Analysis in Excel: Steps and FormulasABC-XYZ analysis in Excel combines two ways to classify inventory: ABC ranks items by their annual usage value,…
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
- Change Capital to Lowercase in Excel: Formulas and TipsTo change capital letters to lowercase in Excel, use the LOWER function in a blank column: with the…
- Excel Formula for Numbering Rows: Static and DynamicTo number rows in Excel, enter =ROW()-1 in cell A2 when row 1 contains a header, then fill…
- DAX vs Excel Formulas: How They Differ and When to UseDAX and Excel formulas both calculate results, but they work in different places and answer different kinds of…
- Excel SUMIF Contains Number? Match Digits or ValuesYes—Excel’s SUMIF can sum rows where a text entry contains particular digits by using asterisks as wildcards, such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.