Function Problems

How to Automatically Copy Excel Data to Another Sheet Based on Criteria

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to automatically extract and copy specific rows of data from one Excel sheet to another when a specific condition (such as 'Y' in a Report column) is met, while ensuring the formulas expand automatically for new data.

Product: Excel
Device & OS: not provided
Scenario: Filtering and extracting specific data records to a separate summary sheet based on conditional criteria.
Observed behavior: The user wants data to flow automatically into the new sheet as new rows are added, and needs to resolve an issue where entered formulas display as plain text instead of calculating.

Before you start

Ensure your source data is organized in a clear tabular format with distinct column headers, and verify that your target sheet has enough empty space to accommodate the extracted data without overlapping existing content.

Solution 1Recommended

Use the FILTER Function and Convert Data to an Excel Table

Converting your data into an Excel Table and using the FILTER function is the most efficient way to automatically extract records and ensure results expand dynamically.

The FILTER function is a powerful dynamic array formula that extracts matching records based on your criteria. By pairing it with an Excel Table, you ensure that any newly added rows are automatically evaluated and included in the destination sheet without having to adjust the formula range.

Convert source data to a Table

Select your entire source data range and press Ctrl+T on your keyboard. Ensure 'My table has headers' is checked and click OK. This creates a dynamic table (e.g., Table1).

Navigate to the destination sheet

Click on the tab of the sheet where you want the filtered data to appear and select the top-left cell where the extracted data should start.

Enter the FILTER formula

Type the formula: =FILTER(Table1, Table1[Report]="Y", "No records found"). Make sure to adjust 'Table1' and 'Report' to match your actual table and column names.

Apply the formula

Press Enter. The matching data will automatically spill into the adjacent cells. When you add new rows with 'Y' to the original table, this destination sheet will update automatically.

Dynamic Array Compatibility: The FILTER function requires Excel 365, Excel 2021, or a modern spreadsheet alternative like WPS Office. If you are using an older version, you may need to use advanced filtering or a complex INDEX/MATCH array formula.

Solution 2

Resolve Formulas Displaying as Plain Text

If your formula is not calculating and instead shows the actual formula text in the cell, it is likely due to the cell's number formatting being set incorrectly.

Powerful Spreadsheet Tool

Easily Filter and Automate Data with WPS Spreadsheet

WPS Spreadsheet provides powerful dynamic array functions like FILTER and robust table management tools, allowing you to automatically copy and filter data across sheets with seamless efficiency.

1. Open your data file: Launch WPS Office and open your spreadsheet document.
2. Format data as Table: Select your dataset and press Ctrl+T to create a dynamic table that auto-expands.
3. Apply the FILTER formula: In your destination sheet, use the =FILTER() function referencing your table and specific criteria.
4. Ensure General formatting: Keep your cell formatting set to 'General' to ensure all formulas calculate instantly.

100% compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Built-in dynamic array functions to easily filter, sort, and extract data automatically.Quickly convert data to dynamic tables for effortless automatic fill-down features.Lightweight, fast, and free to use for your everyday data processing and office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER formula returning a #CALC! error?

This error usually occurs when the FILTER function finds no matching records based on your criteria. You can avoid this by providing a fallback value in the third argument of the formula, such as =FILTER(range, criteria, "No matches").

Can I copy data to another sheet based on multiple criteria?

Yes, you can use the multiplication operator (*) for AND logic, or the addition operator (+) for OR logic within the FILTER function. For example: =FILTER(Table1, (Table1[Report]="Y") * (Table1[Status]="Done")).

How do I make a standard formula fill down automatically in a column?

To make standard formulas (like VLOOKUP or IF) fill down automatically, convert your data range into a Table (Ctrl+T). When you enter a formula in a new column within the table, it will automatically populate down to the last row.

Why does my formula result not update when I add new rows?

If your source data is not formatted as a Table, the formula range remains static. Convert your source data to a Table so that the references expand dynamically, or manually adjust your formula to reference entire columns (e.g., A:A), though full column references may impact spreadsheet performance.

Excel & Data Problems