Power Query Problems

How to Copy Excel Data to a New Sheet Without Rows Marked Exit

Olivia Miller Oct 1, 2026 868 views

Question details

The user needs a method to duplicate an Excel attendance list to a new monthly sheet while automatically omitting rows where students are labeled as 'exit'.

How to Copy Excel Data to a New Sheet Without Rows Marked Exit

Product: Excel
Device & OS: not provided
Scenario: Creating a new monthly attendance sheet by carrying over previous data but removing inactive or obsolete entries.
Observed behavior: The user seeks an automated or efficient way to transfer the data to the next month's sheet without manually identifying and deleting rows marked 'exit'.

Before you start

Ensure your monthly attendance data is formatted as an official Excel Table (press Ctrl+T) so that Power Query can seamlessly detect, import, and refresh the dataset.

Solution 1Recommended

Use Power Query to Append and Filter Data

Power Query is the most robust method to automate data transfer between sheets while applying specific text exclusions. It prevents manual errors and can be easily refreshed.

By loading your table into Power Query, you can instruct Excel to automatically ignore rows containing the word 'exit'. When a new month begins, you can simply refresh the query to generate the updated list.

Load data into Power Query

Select any cell inside your attendance table, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Filter out the 'exit' status

Locate the column containing the student status. Click the drop-down arrow in the column header, uncheck the box next to 'exit' (or navigate to Text Filters > Does Not Equal > type 'exit'), and click OK.

Load the filtered data to a new sheet

Click the 'Close & Load' drop-down on the Home tab, then select 'Close & Load To...'. Choose 'Table' and 'New Worksheet' to output the cleaned data into a new sheet for the next month.

Use Power Query to Append and Filter Data

Automating Future Months: Whenever source data changes, you do not need to repeat these steps. Just right-click the new filtered table and select 'Refresh' to update the list immediately.

Solution 2

Use the FILTER Function for Dynamic Extraction

If you are using a modern version of Excel or WPS Office, the dynamic array FILTER function can pull over data instantly without opening the Power Query editor.

Filter Data Easily with WPS Office

Effortlessly Manage Monthly Attendance Sheets in WPS Spreadsheet

WPS Spreadsheet features robust data handling tools, including dynamic array functions like FILTER and advanced data sorting, making it incredibly easy to manage attendance lists without complex setups.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing attendance workbook.
2. Prepare the new month's sheet: Click the '+' icon at the bottom to add a new worksheet for the upcoming month.
3. Apply the FILTER function: In the new sheet, type =FILTER() referencing your main attendance table and set the criteria to exclude 'exit'.
4. Organize your layout: Hit Enter to populate the data instantly, then apply quick formatting tools to keep your new sheet clean and professional.

Instantly extract specific rows to new sheets using dynamic array formulas.Fully compatible with Microsoft Excel (.xlsx and .xls) file formats.Lightweight architecture ensures large data sets and queries process smoothly.Free to use with a familiar, tabbed interface to easily navigate between monthly sheets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I exclude multiple different statuses like 'exit' and 'graduated' at the same time?

Yes. In Power Query, simply uncheck both 'exit' and 'graduated' in the filter dropdown. If you are using the FILTER function, you can multiply conditions for AND logic, like this: =FILTER(A1:D100, (C1:C100<>"exit") * (C1:C100<>"graduated")).

Will deleting a student in the original sheet automatically remove them from the new sheet?

If you used the FILTER function, the removal is instant. If you used Power Query, the new sheet will not update until you right-click the data table and select 'Refresh', or set the query properties to refresh automatically upon opening the file.

How do I keep the original formatting when copying data to the new sheet?

Power Query outputs raw data into a standard Table style, which overrides custom cell colors. To restore specific visual styles, you can apply a custom Table Design or use Format Painter. If exact visual duplication is required, copying the entire sheet and then filtering/deleting rows manually or via VBA might be necessary.

Excel & Data Problems