'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Compare Monthly Excel Data When Names Change
Question details
The user needs to identify monthly changes in Excel datasets, such as new or removed personnel, altered dates, and updated locations, while avoiding the pitfalls of inaccurate conditional formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking monthly updates and changes in staff lists, course end dates, and work locations.
- Observed behavior
- Identifying added, removed, or changed records across two different monthly workbooks accurately without layout discrepancies.
Ensure both the previous and current month's Excel files are saved locally on your device and have identical column headers before starting the comparison.
Use the Excel Inquire Add-in to Compare Workbooks
The Inquire add-in is a built-in Excel tool available in specific versions designed to compare two workbooks and highlight structural or data changes.
The Inquire add-in provides a detailed cell-by-cell comparison of two workbooks, which is highly effective when names change or new rows are inserted. It avoids the misalignment issues common with basic conditional formatting.
Note that this feature is typically only available in Office Professional Plus and Microsoft 365 Apps for enterprise editions.
Go to File > Options > Add-Ins. In the Manage drop-down list, select COM Add-ins and click Go. Check the box for Inquire and click OK.
Open the Excel file containing the previous month's data and the file containing the current month's data.
Navigate to the newly added Inquire tab on the ribbon and click Compare Files. Select your previous month's workbook in the 'Compare' dropdown and the current month's workbook in the 'To' dropdown.
Click OK. Excel will generate a color-coded grid highlighting the changed values, added rows, and removed records, allowing you to easily export the results.
Compare Sheets Using Lookup Formulas
Use lookup formulas to compare a backup of the previous sheet with the current data to spot additions or removals based on unique identifiers.
Compare Monthly Spreadsheets Seamlessly in WPS Office
WPS Spreadsheet offers powerful built-in formulas, side-by-side viewing, and conditional formatting tools that make tracking monthly data changes straightforward and accurate.
- 1. Install WPS Office: Download and install WPS Office Free on your computer.
- 2. Open Both Datasets: Open both the previous and current month's datasets in WPS Spreadsheet.
- 3. View Side by Side: Go to the 'View' tab and select 'View Side by Side' to visually align and manually compare the two workbooks.
- 4. Apply Duplicate Highlighting: To quickly find records that exist in only one sheet, paste the data into one worksheet, select the data range, and navigate to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Frequently Asked Questions
Why is my conditional formatting highlighting wrong cells when comparing names?
This usually happens when rows have been sorted differently or new rows are inserted, causing direct cell-by-cell conditional formatting to misalign. Using lookup formulas like VLOOKUP or XLOOKUP based on a unique ID is much more accurate.
Is the Excel Inquire add-in available in all versions of Excel?
No, the Inquire add-in is typically only available in Office Professional Plus editions and Microsoft 365 Apps for enterprise. It is not included in standard Home or Student versions.
How can I compare data if I don't have the Inquire add-in?
You can use a combination of VLOOKUP, INDEX/MATCH formulas, or Excel's Power Query feature to merge and compare datasets based on unique identifiers to find additions, deletions, and modifications.
