Version History Issues

Keep Deleted Excel Sheet Records in a Master History

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

Create a master sheet that retains records after they are removed from an active Excel worksheet.

Keep Deleted Excel Sheet Records in a Master History
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the master sheet

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.

2
Check for existing IDs

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.

3
Append new records

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.

4
Add timestamps

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.

5
Verify before deleting

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.

Append active-sheet records to a master history before deleting them
This is not automatic: Records deleted before they are copied to Master will not be captured by this process. For automatic logging, a custom VBA macro or Office Script can append records, but it must run before deletion.
Preserve worksheet records

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 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. 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.
Keep a separate history of records removed from the active sheetUse unique IDs to check whether records are already in the master sheetOpen and save Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.