To extract matching rows in Excel, turn on filtering with Data > Filter, apply your criteria, then copy the visible results to a new sheet or range. For a list that updates automatically, use the FILTER function in Microsoft 365 or Excel 2021 and later. The steps below cover both methods, as well as extracting a list of unique values.
What You’ll Need
- An Excel workbook with a clearly labelled header row and data arranged in columns. Avoid blank rows or columns within the data.
- A filter condition, such as a region, status, date range, or number threshold.
- A blank destination worksheet or enough empty space for the extracted results. A separate sheet is usually easier to manage.
- For formula-based results, Microsoft 365 or Excel 2021 or later. The FILTER and UNIQUE functions are not available in some older Excel versions.
Step-by-Step
- Check and prepare the source data. Make sure each column has one header, such as Region, Rep, and Sales, and that each row represents one record. Remove completely blank rows from the middle of the data, because they can cause Excel to treat part of the list as a separate range. If you have notes or totals below the records, keep them outside the data block.
- Turn on the column filters. Click a cell inside your data. On the ribbon, choose Data > Filter. Excel adds a drop-down arrow to each header. If the arrows appear in the wrong place or only cover some columns, undo the filter and select the full data range, including the header row, before choosing Data > Filter again.
- Apply the condition you want to extract. Open the arrow on the relevant header. Select or clear the checkboxes, or choose a text, number, or date filter from the menu. For example, open Region and select only West. Click OK. Excel hides rows that do not match; it does not delete them.
- Decide which columns belong in the output. If you need complete records, include every column in the filtered data. If you only need a list of names or IDs, select just that column’s header and visible results. Keeping the header makes the extracted list understandable when you share or revisit it.
- Select the filtered range carefully. Drag from the first cell you want to copy to the last, including the header if needed. A selection may contain hidden rows between visible records, so do not assume that selecting a range automatically means Excel will copy only what you can see. With the range selected in Windows, press Alt+; to select visible cells only. You can also use Home > Find & Select > Go To Special > Visible cells only, then click OK. On Mac, use the Go To Special menu path if the shortcut differs in your Excel version.
- Copy the visible cells. Press Ctrl+C on Windows or Command+C on Mac. A moving border appears around the copied selection. If you copied a range with several columns, keep the same column structure in the destination so the records remain easy to read.
- Paste the extracted results. Click the first cell of an empty area or create a new worksheet with the plus button beside the sheet tabs. Press Ctrl+V on Windows or Command+V on Mac. Check the pasted rows and headers before using the new list. If you want fixed results rather than formulas or linked content, use Home > Paste > Values when that option is available in the paste menu.
- Clear the source filter when you are finished. To show all source records again, use the filtered header arrow and choose Clear Filter, or choose Data > Clear to clear filters across the current range. Clearing the filter reveals rows; it does not erase the separate copy you pasted.
- Use a formula if the extracted list should update. For a small example, assume A1:C7 contains headers Region, Rep, and Sales, with records in rows 2 through 7. In an empty cell, enter
=FILTER(A2:C7,A2:A7="West","No matches"). The result is the three West records: Ben (150), Cara (90), and Eli (175). The formula spills into adjacent cells, so keep that area empty. Change the word West in the formula to another region, or refer to a cell containing the desired region. - Return just one copy of each matching value, if needed. With the same example data and Rep names in B2:B7, enter
=UNIQUE(FILTER(B2:B7,A2:A7="West",""))in an empty cell. It returns Ben, Cara, and Eli, once each. FILTER and UNIQUE work in Microsoft 365 and Excel 2021 or later; in older versions, apply the regular filter and copy the visible cells instead.
The copy-and-paste method creates a snapshot: later changes to the original data do not automatically refresh the extracted copy. The formula method is a live result, so it recalculates when the source values change. Choose based on whether you need a one-time export or a list that stays in sync.
Common Mistakes
- Copying hidden rows along with visible ones. A filtered range includes rows that are hidden by the filter. Select visible cells only before copying, especially if the destination must contain matching records and nothing else.
- Filtering only part of the table. If a column is left outside the filter range, its values may no longer line up with the records being extracted. Start by clicking within the contiguous data block, and check that every header has a filter arrow.
- Overwriting existing information. Pasting a multi-column selection can replace cells to the right and below the destination cell. Paste into a blank sheet or check the destination area first.
- Expecting a copied list to refresh. A pasted result is generally a separate copy, not a connection to the source. Use FILTER when the output needs to update as source data changes.
- Confusing distinct values with filtered records. A filter narrows records; it does not automatically remove repeated entries from a copied column. Use UNIQUE with FILTER for a distinct formula result, or use Excel’s Remove Duplicates command only on a separate copy if you need a one-time result.
- Including totals or unrelated rows. A summary row can be copied as if it were a record, or interfere with a formula range. Keep totals outside the data list and make sure your selected range ends at the last actual record.
Tips
- Convert the source range to a table. Click in the data and choose Insert > Table, then confirm that My table has headers is selected. Tables provide filter arrows and expand as you add records, reducing the chance that new rows are missed. Check formulas and ranges if you later add data beyond the original example range.
- Write down the criteria used. If you are sending an extracted list to someone else, include a short note such as “Region = West” and the date you made the copy. That makes the result easier to verify later.
- Use a helper cell for changing criteria. Put a region name in E1, for example, and use that cell in place of the typed condition in your FILTER formula. This lets you change the criterion without editing the formula each time.
- Keep formula spill space clear. Dynamic-array formulas place results in nearby cells automatically. Leave the expected output area empty; a value or merged cell in the way can stop the result from spilling.
- Preserve a clean original. For a one-off cleanup or export, make the extracted list on another sheet rather than deleting or rearranging rows in the source. That makes it easier to check your work and start over.
Troubleshooting
The extracted list contains rows that were filtered out. Return to the source and select the same range again. Choose Home > Find & Select > Go To Special > Visible cells only before copying, then paste into a fresh destination. This is particularly useful when the selection spans multiple filtered rows.
No filter arrows appear, or some columns have none. Click inside the data and choose Data > Filter. If the range is split by a blank row or column, remove the break or select the complete range, including all headers, before applying the filter. Also check that the worksheet is not protected in a way that prevents filtering.
The FILTER formula returns “No matches” or an empty result. Check spelling, spaces, and the exact values in the source column. “West” and “West ” (with a trailing space) may not match. Confirm that the condition range and data range start and end on the same rows. If you are using a cell as the condition, verify that the cell is not blank or formatted with an unexpected value.
Excel shows #SPILL! for a formula. Clear cells in the area where the result should appear, unmerge any cells in that area, and move the formula if necessary. A dynamic result cannot spill into occupied cells. If the formula is inside an Excel table, place it outside the table because spilled array formulas cannot spill within table columns.
The formula name is not recognized. FILTER and UNIQUE require Microsoft 365 or Excel 2021 and later. In an older release, use the header drop-down to filter the source, then copy the visible cells. If Excel expects semicolons instead of commas because of your regional settings, use the separator required by your installation.
The pasted list has unexpected formatting or formulas. Use the paste options after copying and choose Values to keep the displayed results without carrying over formulas or source formatting. If columns do not line up, copy the header and data using a consistent rectangular range, or paste into a new worksheet.
Frequently Asked Questions
Does filtering delete the rows that do not match?
No. Excel hides nonmatching rows temporarily. You can reveal them by clearing the filter. Copying visible cells to another location makes a separate extracted list, but it does not remove the original records.
How do I extract only the values shown in a filter?
Apply the filter, select the relevant column, choose Visible cells only, and copy it to a blank destination. If you want each value only once and have Microsoft 365 or Excel 2021 or later, use UNIQUE with FILTER.
Can I extract filtered rows without using a formula?
Yes. Use Data > Filter, choose your criteria, select visible cells only, then copy and paste. This is often the simplest choice for a one-time list or for Excel versions without dynamic-array functions.
Will a copied filtered list update when I edit the source?
No. A normal copy-and-paste result is a snapshot. Use a FILTER formula if you need results to recalculate as the source data changes; keep the formula’s output area clear.
Can I extract more than one condition?
Yes. In the regular filter menu, set criteria on more than one column; Excel displays rows that meet the selected conditions. With FILTER, combine conditions in the include argument. For example, multiplication between two logical tests means both conditions must be true, but check that the ranges cover the same rows.
Does the same process work in Excel for Mac or the web?
The main approach is similar: apply filters, select the visible results, and copy them. Ribbon layout and keyboard shortcuts can vary by platform and version, so use the visible-cells menu path if a Windows shortcut does not work. FILTER and UNIQUE are available only in supported versions of Excel.
Related
- Excel Audit Tools: How to Enable and Use Them
- How to Enable an Audit Trail in Excel: Track Changes
- Freeze Panes in Excel: How to Edit Rows and Columns
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Average Dates in ExcelTo average dates in Excel, use =AVERAGE(A2:A10) when the cells A2:A10 contain valid Excel dates. Format the result…
- How to Schedule an Email in Outlook Web App for LaterTo schedule an email in Outlook on the web, compose your message, select the arrow beside Send, choose…
- How to Add Footnotes in Microsoft Word, Step by StepTo add a footnote in Microsoft Word, place the cursor where the reference belongs, then select References >…
- How to Double Space in Word for Mac: Steps and FixesTo double-space text in Word for Mac, select it and choose Home > Line and Paragraph Spacing >…
- Convert an Excel File to a Google Doc: Step by StepTo turn an Excel file into a Google Doc, upload it to Google Drive, open it in Google…
- How to Remove a Filter in Google SheetsTo remove a standard filter in Google Sheets, open the sheet in a web browser, select the tab…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.