Data Import & Export

Track SharePoint List Changes After an Excel Refresh

Camila MilosovichCamila Milosovich Nov 22, 2026 868 views

Question details

Identify changes to a SharePoint list after its connected Excel workbook is refreshed.

Track SharePoint List Changes After an Excel Refresh
Product
Microsoft Excel and SharePoint
Device & OS
not provided
Scenario
An Excel workbook imports a SharePoint list through an IQY web query and needs a way to compare data between refreshes.
Observed behavior
Excel Track Changes cannot be enabled because of the workbook's XML maps and table requirements.
Before you start

Identify a unique list item ID and the columns you want to compare. The formula example requires a version of Excel that supports XLOOKUP; adjust the column names and references to match your workbook.

Solution 1Recommended

Compare a pre-refresh snapshot with the refreshed list

Use a separate snapshot and comparison formulas to find net changes without enabling Excel Track Changes.

This example compares an ID and a Status column. Repeat the status comparison for each additional column you need to track. It shows differences between snapshots, not every change made between refreshes.

1
Create a snapshot

Before refreshing, copy the imported list to a new worksheet and use Paste Special > Values. Include the ID and tracked columns, then select the copied range and choose Insert > Table. On Table Design, name the snapshot table Before.

2
Refresh the list

Select the connected list and choose Data > Refresh All. For this example, name its worksheet Current and its table Current, with ID in column A and Status in column B. Adjust the references below if your layout differs.

3
Find added and modified rows

On a separate comparison sheet, enter =IF(COUNTIF(Before[ID],Current!A2)=0,"Added",IF(Current!B2<>XLOOKUP(Current!A2,Before[ID],Before[Status]),"Modified","Unchanged")) and fill it down through the refreshed rows. This compares Status only; add checks for other tracked columns as needed.

4
Find deleted rows

On the snapshot sheet, add a column beside the Before table and enter =IF(COUNTIF(Current[ID],A2)=0,"Deleted","Present"), assuming the snapshot ID is in A2. Fill the formula down through the snapshot rows.

5
Prepare for the next comparison

After reviewing the results, copy the refreshed data and use Paste Special > Values to create a new snapshot before the next refresh.

Compare a pre-refresh snapshot with the refreshed list
When to ask an administrator: For a complete audit trail or help with a complex IQY and XML map setup, consult your SharePoint administrator or an IT Pro community. A snapshot comparison cannot reveal changes that were reversed before the refresh.
Free Microsoft Office alternative

Compare Spreadsheet Data with WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheet snapshots. Its Excel format compatibility makes it easier to move existing files into WPS; it does not fix Excel's SharePoint query or Track Changes restrictions.

Review and compare spreadsheet snapshots in WPS SpreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should I use the SharePoint item ID for comparison?

The item ID provides a stable way to match rows even if their order changes after refresh. Include the ID in both the snapshot and the refreshed data.

Can the comparison show every edit made between refreshes?

No. It shows the net difference between the snapshot and the refreshed data. Ask your SharePoint administrator about list version history or auditing if you need a record of intermediate edits.

How do I check more than one column for changes?

Add comparisons for each tracked column to the formula on the comparison sheet, using the item ID to look up the corresponding values in the Before table.

What if my version of Excel does not support XLOOKUP?

Use another lookup method, such as INDEX and MATCH, to retrieve the snapshot value by ID, or ask an Excel specialist to help build a comparison suitable for your version.