Keep Deleted Excel Sheet Records in a Master History
Question details
Create a master sheet that retains records after they are removed from an active Excel worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has an active sheet for current records and needs a separate master history for records that may later be deleted.
- Observed behavior
- The master sheet should retain previously recorded values even when the corresponding rows no longer exist on the active sheet.
Add a unique ID column to the active sheet and make sure each record has a distinct ID. Decide whether the master sheet should keep only the first recorded version of each ID or separate versions of changed records.
Append active-sheet records to a master history before deleting them
Use this manual method to preserve records without relying on formulas that link to the active sheet.
A formula linked to the active sheet does not create a permanent history. Copy and paste values into the master sheet before removing records from the active sheet.
This method records new IDs; it does not automatically capture later edits. To retain multiple versions, append each changed version as a separate row and distinguish it with a timestamp.
Add a worksheet named Master. Copy the active sheet's column headers into it, including the unique ID column, and add a Logged At column.
On the active sheet, add a helper column. If IDs are in column A on both sheets, enter =COUNTIF(Master!$A:$A,A2) in the first data row and fill it down. A result of 0 identifies an ID not yet in Master.
Select the rows with a helper result of 0, copy their record fields without the helper column, and use Paste Special > Values in the first empty rows of Master. For nonadjacent rows, copy and append them separately.
In the Logged At column for each appended row, enter the date and time. If you use =NOW(), copy the results and use Paste Special > Values so the timestamps do not change.
Check that the IDs and values appear in Master, then delete the corresponding rows from the active sheet. Repeat the append process before future deletions.

Build a master record history in WPS Spreadsheets
Use WPS Spreadsheets to append active-sheet records as values to a master sheet before deleting them, keeping their IDs and timestamps for reference.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Set up the history sheet: Add a sheet named Master, copy the active sheet's headers into it, and add a Logged At column. Ensure every active record has a unique ID.
- 3. Check and append records: Compare the IDs against Master, then copy new records and use Paste Special > Values in the first empty master rows.
- 4. Log and verify: Enter a date and time in Logged At for each appended row. Check the copied records in Master before deleting their rows from the active sheet.

Frequently Asked Questions
Will a formula link keep a record after its source row is deleted?
No. A formula link reflects the source rather than creating an independent history. Copy the record and use Paste Special > Values on the master sheet before deleting it.
How can I avoid adding the same record twice?
Give each record a unique ID and check it against the ID column on Master. For example, if both ID columns are in column A, use =COUNTIF(Master!$A:$A,A2) on the active sheet; 0 means the ID is not yet in Master.
Can I keep a history of changes to the same record?
Yes. Append each version as a separate row on Master and add a fixed timestamp or version number. Checking only for new IDs will not capture subsequent edits.
Can I recover records deleted before I created the master sheet?
The master sheet cannot capture a record that was already deleted. If the workbook has version history, open File > Info > Version History in Excel and check an earlier version.




