Track SharePoint List Changes After an Excel Refresh
Question details
Identify changes to a SharePoint list after its connected Excel workbook is refreshed.

- 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.
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.
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.
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.
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.
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.
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.
After reviewing the results, copy the refreshed data and use Paste Special > Values to create a new snapshot before the next refresh.

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.

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.




