Adding to an Excel "Filter" list?

Question

In Excel, how can I add new data to a filtered table and have it automatically append in a different worksheet?

Answer 1

Hello Glenn Hinoki

What version of Excel are you using? M365 or other one-time purchase version?

Has answer provided by AI able to help you solve this issue yet?

If not, I would like to recommend an additional method for you to try:

The Advanced Filter method can work, but it usually needs to be run again when new data is added. If you want the result on another worksheet to update more automatically, you may want to try using the FILTER function instead.

Here is what you can do:

1. Select your source data and press Ctrl + T to convert it into an Excel Table.
2. Go to the worksheet where you want the filtered result to appear.
3. Enter a formula similar to this: =FILTER(Table1, Table1[Status]="Open", "")

Please replace Table1, Status, and Open with your actual table name, column name, and filter condition.

This should create a live filtered result on the other worksheet. When you add new rows to the source table, the filtered result should update automatically, as long as there is enough empty space below and beside the formula for the results to expand.

Feel free reply back to this post if you need further assistance, I'll be happy to help.

Answer 2

Use filtering to control what is visible, then use a second table or advanced filter to append the visible (filtered) records to another sheet.

One supported way is to use Advanced Filter with the “Copy to another location” option:

1. Set up the source data as a proper database range (single header row, no blank rows, empty cells around the database).
2. Set up a Criteria Range that defines what should be filtered (copy the header row to another area and enter the filter conditions under the headers).
3. On the worksheet that contains the source data, use Advanced Filter to copy the filtered records:
   • Ensure AutoFilter is off.
   • Select any cell in the database.
   • Use Advanced Filter and choose:
     • List range: the source table/database.
     • Criteria range: the criteria area created in step 2.
     • Copy to another location: specify a range on the target worksheet.
4. Run the Advanced Filter again whenever new data is added to the source table; the filtered rows will be copied to the specified range on the other worksheet.

This approach uses Advanced Filter’s ability to extract (append) filtered records to another location instead of only hiding rows in place.

---

References:

• Using Advanced Filters in Excel 2010
• Filter data in a range or table in Excel