To extract matching rows in Excel, filter your source data, select the visible results, copy them, and paste them into a new worksheet. For results that update automatically when the source changes, use Excel 365’s FILTER function instead. Before starting, make sure your data has a header row and that you know which column or condition determines which rows to keep.
What You’ll Need
- An Excel workbook containing the rows you want to extract, preferably with one header row and consistent columns.
- A clear selection rule, such as a status of Open, a particular region, or dates within a period.
- A blank worksheet in the same workbook for the extracted rows. A blank destination area also works, but a separate sheet keeps the results easier to manage.
Step-by-Step
- Check and prepare the source data. Open the workbook and go to the worksheet containing the rows. Make sure each column has a descriptive header, such as Date, Region, or Status. Remove completely blank rows or columns from the middle of the data, and check that each record uses the same column layout. A blank row can cause Excel to treat part of the data as a separate block. If the data already has filters or hidden rows, note that before you begin.
- Create a destination worksheet. Click the plus sign beside the sheet tabs to add a blank worksheet. Double-click its tab and give it a useful name, such as Open Orders or West Region. Naming the sheet after the extraction rule makes it easier to find later. Keep the source worksheet intact; the filter-and-copy method below does not remove or move records from the original.
- Turn on filters for the source range. Return to the source worksheet and click a cell inside the data. On the ribbon, select Data > Filter. Excel should place a drop-down arrow beside each header. If the arrows do not cover all columns, select the complete data range, including headers, and choose Data > Filter again. You can also press Ctrl+Shift+L on Windows to toggle filters; on Mac, use Command+Shift+F.
- Apply the condition that identifies the rows. Click the drop-down arrow in the relevant column. For a simple category, clear Select All and tick the value you want, such as Open. For a numeric or date condition, choose an appropriate option such as Number Filters or Date Filters, then set the condition and confirm it. Excel hides rows that do not match; it does not delete them. Check the visible records to make sure the filter is doing what you intend.
- Select the filtered data, including its headers. Select the header row and the displayed records across all columns you need. If the filtered data is a large contiguous range, click in it and use Ctrl+A on Windows to select the data region; check the selection before copying, since Ctrl+A can select a larger table or worksheet on a second press. To avoid copying hidden rows in a filtered selection, use Home > Find & Select > Go To Special > Visible cells only, then choose OK. This is especially helpful if the worksheet also contains rows hidden manually.
- Copy and paste the extracted rows. Press Ctrl+C on Windows, or Command+C on Mac. Go to the destination sheet, click cell A1, and press Ctrl+V or Command+V. Pasting at A1 preserves the header row above the extracted records. If you already placed headers in the destination, click A2 instead. Check that every expected column and visible row was pasted and that the first row is the header, not a data record.
- Choose whether to keep the copied formatting. A normal paste generally brings over cell formatting as well as values. If you want plain values, use Home > Paste > Paste Values, or right-click the destination and choose the values-only paste option. If number or date formats matter, inspect the pasted results; values-only pasting may not preserve the source display format. You can adjust column widths on the destination sheet by selecting the columns and using Home > Format > AutoFit Column Width.
- Clear the source filter when finished. Return to the source worksheet and select Data > Clear to show all records again, or open the filtered column’s drop-down and choose Clear Filter From. Clearing the filter changes what is displayed, not the original data. Keep the destination sheet as a snapshot: later changes to the source will not update rows you copied and pasted.
- Use a formula if the extracted list should update. This option is for Excel for Microsoft 365 and Excel 2021 or later. For example, assume the source sheet is named Data, headers are in Data!A1:D1, records are in Data!A2:D101, and the status is in column C. Copy the headers to A1:D1 on the destination sheet. With the records in Data!A2:D101, their statuses in Data!C2:C101, and the destination formula entered in A2, use:
=FILTER(Data!A2:D101,Data!C2:C101="Open","No matching rows"). The formula spills matching records into the cells below and to the right. In an illustrative source where only rows 2 and 5 have Open in column C, the result is those two records, in the same order. Change Open to the value you need. Leave the spill area empty, because existing content can prevent the results from appearing. Unlike a pasted copy, this formula result updates when matching source values change.
Common Mistakes
Copying the wrong columns: Selecting only the filtered condition column produces a list of values rather than complete records. Before copying, check that the selection starts at the first needed column and ends at the last one. If the source has extra notes or calculation columns, decide whether they belong in the extract.
Including the wrong header or omitting it: If you copy only data rows, the destination may be hard to interpret later. If you paste headers over a row that already has headers, you can end up with duplicate headings. Confirm whether you selected the source header and whether the destination is A1 or A2 before pasting.
Assuming a copied extract stays connected: A copy-and-paste result is a snapshot, not a live link. It will not refresh when the source changes. Use FILTER for a live list, or repeat the filter-and-copy process when you need a refreshed snapshot.
Filtering inconsistently formatted values: A column may contain values that look the same but are stored differently, such as dates stored as text or names with extra spaces. A filter can then separate entries unexpectedly. Check the source values and make their formats consistent before extracting.
Overwriting existing destination data: Pasting into a populated sheet can replace cells without making the result obvious. Create a new blank sheet or verify that the destination range is clear first. This matters especially when a FILTER formula needs to spill across several rows and columns.
Tips
For recurring work, convert the source range into an Excel table before filtering. Click in the range and choose Insert > Table, confirm that My table has headers is selected, and choose OK. Tables make headers and data easier to work with as records are added. The filter-and-copy steps still apply; check that the table includes all of the intended columns and rows.
Use a specific sheet name that records the rule and, if useful, the date of the snapshot, such as Open Orders – Nov 3. If you create a new extract each week, this makes it less likely that someone will confuse an older copy with current data. For a live formula result, label the destination clearly so users know it depends on the source sheet.
When extracting by more than one condition, decide whether rows must meet all conditions or any condition. For example, “Status is Open and Region is West” is different from “Status is Open or Region is West.” With the manual method, filter each relevant column; filters on separate columns normally narrow the displayed rows to those meeting both selections. If you need an OR rule, make sure your method includes every intended match rather than accidentally excluding one group.
Before sharing the workbook, review the destination for hidden rows, unexpected blanks, and missing columns. If the extract contains sensitive or confidential information, remember that hiding or filtering rows on the source sheet does not remove that information from the workbook. Share only the data that the recipient should receive.
Troubleshooting
The filter arrows do not appear on every column. Click inside the source data and inspect its edges. If a blank row or column separates part of the range, Excel may have applied the filter to only one section. Select the full rectangular range, including the header row, and choose Data > Filter. Remove unnecessary gaps within the data first.
Some matching records are missing. Open the filter menu and check whether more than one value or condition is selected. Also inspect the source for leading or trailing spaces, inconsistent spelling, and dates or numbers stored as text. If the rule is a date range, verify the start and end dates and check that the column contains real Excel dates. Clear the filter and reapply it after correcting inconsistent entries.
Hidden rows appear in the copied result, or expected rows are absent. Confirm that the filter is active and that you selected the filtered data range rather than an adjacent range. Use Home > Find & Select > Go To Special > Visible cells only before copying when you need to exclude manually hidden rows too. If the selection includes only visible cells, copy it again and paste into a cleared destination area.
The FILTER formula shows #SPILL!. Excel cannot place the full result because one or more cells in the output area are occupied, merged, or otherwise blocking the spill. Clear the cells below and to the right of the formula, and unmerge any cells in that area. Also make sure the formula is not inside an Excel table, where dynamic array results cannot spill in the usual way.
The formula returns No matching rows or an error. Check that the source and criteria ranges have the same number of rows, and confirm that the condition exactly matches the source values. In the example formula, the source range has 100 rows and the status range also has 100. Make sure the sheet is named Data, or update the sheet reference to match your workbook. FILTER is available in Microsoft 365 and Excel 2021 or later; in older versions, use the filter-and-copy method.
Frequently Asked Questions
Can I extract rows based on more than one column?
Yes. With the manual method, filter each column in turn; filters on different columns combine to show rows matching all selected conditions. For conditions where either one value or another should qualify, carefully select both values in the relevant filter menu or build a formula that represents the intended rule.
Will the new sheet update when I edit the source?
Not if you copied and pasted the rows; that creates a separate snapshot. A FILTER formula can update automatically as source values change, provided the formula’s source range includes the relevant cells and the output area remains clear.
Can I extract rows to a different workbook?
Yes. Copy the filtered, visible rows and paste them into a blank sheet in the other workbook. If you use a formula instead, references to another workbook may depend on that workbook remaining accessible, so a pasted copy is often simpler for a portable snapshot.
How do I extract rows that contain a word anywhere in a cell?
Open the filter menu for the text column and use Text Filters > Contains, then enter the word or phrase and apply the filter. Review the results for spelling variations or extra spaces, which can affect whether a cell matches.
Can I extract rows without changing the source data?
Yes. Filtering only changes which rows are displayed, and copying does not remove records. Clear the filter afterward to show the full source list. Avoid cut-and-paste if you want to keep the original rows in place.
Why does my FILTER formula show a different number of rows after an edit?
The result is recalculated when source data or the criteria change, so rows may be added or removed from the displayed extract. Check the source values and criterion, and confirm that the formula’s ranges include newly added records. Using a table-based source can make a recurring formula easier to maintain as the data grows.
Related
- Enter Serial Numbers in Excel: Manual and Automatic Methods
- How to Enable Word Wrap in Excel and Adjust Cell Text
- How to Enable Wrap Text in Excel and Adjust Row Height
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel HP Data Analysis: How to Analyze Data on an HP PCYes—Microsoft Excel can analyze data on an HP computer; HP is the computer manufacturer, while the analysis features…
- Full Page of Labels in Word: Create and Print a SheetTo create a full page of labels in Microsoft Word, open Mailings > Labels, choose the label brand…
- Excel Data Validation List AutoFill: What Works?Excel can autocomplete some data-validation dropdown lists in current Microsoft 365 versions, but the feature is not available…
- Out of Office in Outlook on the Web: Setup and TipsTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- Actual vs Forecast in Excel: Compare ResultsActual vs forecast in Excel means comparing what happened with what was expected. Put the forecast in one…
- How to Not Copy Hidden Rows in Excel Without ErrorsTo avoid copying hidden rows in Excel, select the range you need, choose Home > Find & Select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.