How to Compare Two Excel Workbooks and Create a Redline
Question details
The user is looking for ways to compare an older and newer version of an Excel workbook to create a redlined document that highlights changed values and formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Identifying specific data and format modifications between two versions of a spreadsheet when standard comparison tools fail to output a traditional text redline.
- Observed behavior
- Built-in tools like Spreadsheet Compare do not automatically generate a traditional redlined text format, which results in formatting and alignment problems when attempting workarounds like exporting the sheets to Word.
Ensure both the original and revised workbooks are saved with distinct file names and backed up. If your spreadsheets contain heavy formatting, note that cross-application workarounds may affect cell alignment.
Use a Comparison Workbook or Add-in Tool
Utilize Excel's built-in comparison features to visually highlight differences in values, formulas, and formatting without manually scanning the document.
Launch Excel and open both the original workbook and the revised workbook you wish to compare.
Click on the 'Inquire' tab on the top ribbon. If you do not see this tab, you may need to enable the Inquire COM Add-in via File > Options > Add-ins.
Click on the 'Compare Files' button within the Inquire tab. Select your older workbook as the 'Compare' file and the newer one as the 'To' file.
Review the generated comparison workbook. It will display a color-coded grid highlighting changed values, structural modifications, and formatting differences.
Export Worksheets to Word for Redline Comparison
If a traditional text redline is strictly required, copy the spreadsheet data into Microsoft Word to leverage its Track Changes and Document Compare features.
Compare Raw Data using CSV or Text Files
Strip away problematic spreadsheet formatting by saving the workbooks as comma-delimited files, making it easier to spot exact text and value changes.
Compare Workbooks Effortlessly in WPS Office
WPS Spreadsheet provides intuitive tools to view and compare multiple workbooks side by side, making it easy to identify data modifications and formatting changes. Enjoy a lightweight, highly compatible alternative for all your spreadsheet tasks.
- 1. Install WPS Office: Download and open WPS Office, then launch the WPS Spreadsheet module.
- 2. Open Both Files: Open both the original and the modified workbooks you wish to compare.
- 3. Access the View Tab: Navigate to the 'View' tab on the main ribbon interface.
- 4. Enable Side by Side View: Click 'View Side by Side' to arrange both workbooks on your screen horizontally or vertically.
- 5. Scroll Synchronously: Ensure 'Synchronous Scrolling' is toggled on to scroll through both files simultaneously, allowing you to manually spot and highlight changes.
Frequently Asked Questions
Why doesn't Spreadsheet Compare create a traditional redline?
Spreadsheet Compare highlights structural, formula, and value changes through cell color-coding rather than text strikethroughs (redlines). This is because spreadsheets manage data in a mathematical grid framework, which behaves entirely differently than standard word processing text.
Can I use conditional formatting to compare two Excel sheets?
Yes. You can copy the new data onto a new sheet in the original workbook, highlight the range, and apply Conditional Formatting using a custom formula (for example, =A1<>Sheet2!A1) to highlight cells that differ between the two sheets.
How do I fix formatting issues when pasting Excel tables into Word?
When pasting into Word, click the Paste Options icon that appears and select 'Keep Source Formatting' to retain the look. If you only need to compare the raw text for a redline, pasting as 'Keep Text Only' will lose the grid alignment but makes tracking text differences much cleaner.
Is there a third-party tool specifically for Excel redlining?
Yes, several third-party add-ins and software solutions specialize in Excel document comparison. These external tools offer detailed audit trails and traditional redline outputs that built-in native spreadsheet tools might lack.
