Others

How to Highlight Monthly Changes in Excel When Names Change

Rana Garcia Sep 28, 2026 869 views

Question details

The user needs a reliable way to identify and highlight changes (such as added, removed, or modified records) between monthly Excel reports when relying solely on employee names is inaccurate.

How to Highlight Monthly Changes in Excel When Names Change

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing monthly employee reports to track updates like course end dates or work locations.
Observed behavior: Conditional formatting based just on names fails or yields inaccurate results when employees are renamed, added, or removed.

Before you start

Ensure you have a saved, unchanged copy of the previous month's data to compare against the current month's worksheet, and try to use unique Employee IDs instead of just names.

Solution 1Recommended

Use the Excel Inquire Add-in to Compare Workbooks

The Inquire add-in is a powerful built-in tool in Excel designed specifically to compare two workbooks and highlight changes, additions, and deletions cell-by-cell.

This tool provides a comprehensive visual breakdown of what has changed between two versions of a workbook. Note that the Inquire add-in is only available in specific Office Professional Plus editions and Microsoft 365 enterprise versions.

Enable the Inquire add-in

Go to File > Options > Add-Ins. In the Manage drop-down list at the bottom, select 'COM Add-ins' and click 'Go'. Check the box for 'Inquire' and click OK.

Open both workbooks

Open both the previous month's workbook and the current month's workbook in Excel.

Run the comparison

Navigate to the newly added 'Inquire' tab on the ribbon and click on 'Compare Files'. Choose the previous month's file in the 'Compare' box and the current month's file in the 'To' box, then click OK to generate a detailed report of changes.

Use the Excel Inquire Add-in to Compare Workbooks

Compatibility Check: If you do not see the Inquire add-in in your COM Add-ins list, your version of Office may not support it. In this case, use formula-based comparison methods.

Solution 2

Compare Records Using Power Query

Power Query allows you to merge two tables from different months based on a unique identifier to easily spot changed, added, or removed records.

Solution 3

Use Lookup Formulas and Conditional Formatting

If you prefer working directly in the grid, use VLOOKUP or XLOOKUP with Conditional Formatting to cross-reference unique IDs.

Compare Data Easily in WPS Spreadsheet

Highlight Data Changes Across Sheets with WPS Office

Track employee record changes accurately between months using WPS Spreadsheet's robust formula engine and conditional formatting tools, ensuring no data update goes unnoticed.

1. Open your monthly records: Launch WPS Spreadsheet and open the workbook containing both your previous and current month's data.
2. Set up a status column: Add a new column titled 'Status' in the current month's sheet to track changes.
3. Apply a lookup formula: Enter a VLOOKUP or XLOOKUP formula using the unique Employee ID to fetch the previous month's data into the current sheet.
4. Compare the values: Use a simple IF statement to compare the fetched value with the current value (e.g., `=IF(CurrentCell=OldCell, "No Change", "Changed")`).
5. Highlight the differences: Select the column, click 'Conditional Formatting' under the Home tab, and set a rule to highlight cells containing the text 'Changed' in red.

Completely free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) formatsSupports advanced lookup formulas like XLOOKUP and VLOOKUPIntuitive Conditional Formatting interface for quick data highlighting

microsoft office alternative - wps office

Frequently Asked Questions

Why is conditional formatting based only on employee names unreliable?

Relying strictly on employee names can cause formatting rules to break or highlight incorrectly if an employee gets married and changes their last name, if there is a typo, or if two employees share the exact same name. Using a unique Employee ID prevents these false positives.

How do I turn on the Inquire add-in in Excel?

Go to File > Options > Add-Ins. In the Manage drop-down list at the bottom of the window, select 'COM Add-ins' and click Go. Check the box next to 'Inquire' and click OK. The Inquire tab will then appear on your ribbon.

Can I compare lists if employees were deleted in the current month?

Yes. If you are using formulas, apply your VLOOKUP or XLOOKUP from the previous month's sheet pointing to the current month. If the formula returns an error or a 'Not Found' message, it indicates that the employee record was removed in the current month.

Office Quick Solutions