📊 Excel Comparator

User Guide - FileForge Suite

📋 Overview

Excel Comparator is a professional tool for comparing Excel spreadsheets, identifying differences, and generating detailed comparison reports. Perfect for version control, data validation, financial reconciliation, and audit trails.

Key Features

Side-by-Side Comparison - View both files simultaneously with synchronized scrolling
Smart Field Mapping - Automatic fuzzy matching with color-coded match quality (exact, fuzzy, manual, unmatched)
Color-Coded Differences - Instantly identify changes, additions, deletions, matched rows, and duplicate keys
3-Step Configuration - Guided wizard for sheet selection, header row preview, and primary key with uniqueness validation
Auto-Detect Primary Key - Automatically finds the best unique identifier column
Numeric Normalization - Treats "100" (text) and 100 (number) as equal to prevent false differences
Duplicate Key Detection - Identifies and marks duplicate primary keys in both files
Built-in File Viewer - Preview file contents without needing Excel installed
Detailed Export - Generate multi-sheet Excel reports with comparison, raw data, and summary
Result Caching - Smart caching avoids re-comparing when settings haven't changed

🚀 Getting Started

Quick Start in 5 Steps

Step 1: Load your "old" (baseline) Excel file using Browse or drag & drop

Step 2: Load your "new" (comparison) Excel file

Step 3: Click Configure to set sheet, headers, and primary key

Step 4: Click Compare to run the comparison

Step 5: Review results and export if needed

First Time User? Start with two simple Excel files with matching column headers. Once you're comfortable, explore advanced features like field mapping and custom primary keys!

🖥️ Interface Guide

Main Areas

Area	Purpose
File Selection (Top)	Browse, View, or drag & drop Old File (left) and New File (right). Status indicators show configuration progress (gray → red → green).
Configuration	3-step wizard: Sheet selection with row/column counts → Header row with live data preview → Primary key with uniqueness validation and auto-detect
Action Buttons	Configure, Map Fields, Compare (Preview), Export (Save & Format), Reset, View, Browse Output, Open Output
Comparison View	Side-by-side tables showing old and new data with color coding
Difference Log	Detailed list of all changes with before/after values
Progress Bar	Shows comparison progress and status messages

Comparison View Modes

Side-by-Side Mode (Default)

Shows old file on left, new file on right. Perfect for reviewing changes in context.

Single View Mode

Shows only changed rows in a unified view. Great for focusing on differences only.

⚙️ Comparison Modes

Basic Comparison

Compares all columns with matching names automatically. Best for files with identical structures.

Example:

Old File: Employee_v1.xlsx with columns: ID, Name, Salary, Department

New File: Employee_v2.xlsx with columns: ID, Name, Salary, Department

Result: Automatically compares all 4 columns

Field Mapping Comparison

Manually map columns when names differ between files. Essential for comparing files with renamed or reordered columns.

Example:

Old File columns: EmpID, Full Name, Annual Salary

New File columns: EmployeeID, Name, Salary

Solution: Use Map Fields to match:

EmpID → EmployeeID
Full Name → Name
Annual Salary → Salary

When comparing files with different structures, always use Map Fields first! This ensures accurate comparison even when column names don't match.

🔗 Field Mapping

When to Use Field Mapping

Column names changed between versions
Columns reordered in new file
Need to compare specific columns only
Files have different numbers of columns

How to Map Fields

After loading both files, click Map Fields button

Fields are automatically grouped by match quality: Exact Matches, Fuzzy Matches, Manual Mappings, No Matches

Review color-coded cards: Green ✓ (exact), Yellow ⚠ (fuzzy), Red ✗ (no match), Blue (manual)

Use quick actions: Auto-Match All, Accept Exact, or Clear Uncertain

Use the search box to find specific fields in large datasets

Adjust mappings as needed and click OK

Important: Primary key column must be included in mapping! Without it, rows cannot be matched between files.

Field Mapping Dialog

Element	Purpose
Match Quality Badge	Color-coded indicator showing match confidence (exact, fuzzy, manual, unmatched)
Include (Checkbox)	Check to include this mapping in comparison
Old Field	Column name from old file (read-only)
New Field (Dropdown)	Select matching column from new file
Statistics Summary	Shows breakdown of exact, fuzzy, manual, and unmatched fields

🎨 Color Coding

Excel Comparator uses color coding to quickly identify different types of changes:

Yellow: Modified rows (data changed)

Light Blue: Same rows (no changes)

Red: Deleted rows (in old, not in new)

Green: New rows (in new, not in old)

Purple: Duplicate primary key rows (flagged in exported raw data sheets)

Cell-Level Highlighting

Within modified rows (yellow), individual changed cells are highlighted to pinpoint exact differences.

Example:

Row with ID=123 exists in both files, but Salary changed from $50,000 to $55,000

Result: Entire row highlighted yellow, Salary cell gets additional emphasis

📤 Export Options

What Gets Exported

The exported Excel file contains four sheets:

Sheet	Contents
Summary	Statistics: total rows, changes, additions, deletions, matches, duplicate key counts, comparison settings used
Old File Data	Complete raw data from old file with duplicate primary keys highlighted in purple
New File Data	Complete raw data from new file with duplicate primary keys highlighted in purple
Comparison	Side-by-side view of all matched rows with full color coding

Export Features

Preserved Formatting - All colors and highlighting retained
Bold Headers - Column headers are bold for clarity
Auto-Width Columns - Columns automatically sized to fit content
Frozen Headers - Header row frozen for easy scrolling
Cell-Level Changes - Individual changed cells highlighted within rows

Export files are standard Excel format (.xlsx) and can be opened in Excel, Google Sheets, or any compatible application!

🔄 Common Workflows

Workflow 1: Version Control (Same Structure)

Load yesterday's version as Old File

Load today's version as New File

Click Configure (use same settings for both)

Click Compare

Review yellow (modified) rows to see what changed

Export for documentation/audit trail

Workflow 2: Data Migration Validation

Load source system export as Old File

Load destination system export as New File

Configure with primary key (e.g., Record ID)

Map Fields if column names differ

Compare to validate migration

Check for red (lost records) or unexpected changes

Workflow 3: Financial Reconciliation

Load expected/budget data as Old File

Load actual/current data as New File

Configure with transaction ID as primary key

Compare to find discrepancies

Review Difference Log for before/after values

Export for accounting records

Workflow 4: Multi-Sheet Comparison

Load first file

Configure Old File for Sheet1

Load second file

Configure New File for Sheet1

Compare and export

Repeat with Sheet2, Sheet3, etc.

💡 Tips & Best Practices

Primary Key Selection

Use unique identifiers - Employee ID, Transaction ID, Record ID
Avoid duplicates - Primary key values should be unique
Same column in both files - Must exist in old and new
Don't use names - Names can change, IDs don't

If primary key values aren't unique, rows may be incorrectly matched or skipped!

Header Row Configuration

Header row is typically row 1
If your data has multiple header rows, use the row with actual column names
Must specify same header row pattern for both files
Merged cells in headers may cause issues - avoid if possible

File Preparation

Remove empty rows/columns - Clean data compares better
Consistent data types - Don't mix numbers and text in same column
Standardize formats - Dates, currency, phone numbers
Trim whitespace - Leading/trailing spaces can cause false differences

Performance Tips

Large files (50,000+ rows) may take a minute to compare
Close other applications to free up memory
Remove unnecessary columns before comparison
Use field mapping to compare only relevant columns

Interpreting Results

Lots of yellow rows? Data has been updated - review changes
Lots of red rows? Data deleted - verify intentional
Lots of green rows? New data added - confirm expected
All blue rows? Files are identical - no changes

Common Mistakes to Avoid

Wrong primary key: Using non-unique columns (like "Department") causes incorrect matches. Always use IDs!

Forgot to configure: Comparison won't work without setting sheet, headers, and primary key for both files.

Mixed data types: Enable the Numeric Normalization checkbox to treat "100" (text) and 100 (number) as equal. This is on by default and prevents most false differences from mixed types.

🔧 Troubleshooting

Q: "Primary key not found" error appears

A: The primary key column name doesn't exist in one or both files. Check spelling and case sensitivity. The column name must match exactly.

Q: All rows show as different even though data looks the same

A: Data types likely differ (text vs numbers, or date formatting). Excel treats "100" (text) as different from 100 (number). Standardize formats in source files.

Q: Some rows aren't showing up in comparison

A: Primary key values might be duplicated or missing. Each row must have a unique primary key value, and it must exist in both files to be matched.

Q: Colors aren't showing in exported file

A: Ensure you're opening the file in Excel or a compatible application. Some viewers don't support cell formatting.

Q: Comparison is very slow

A: Large files take time. Reduce columns using field mapping to include only what's needed. Close other applications to free memory.

Q: "No differences found" but I know there are changes

A: Check that you've selected the correct sheets in Configure. If comparing wrong sheets, no differences will be found.

Q: Can't map fields - dropdown is empty

A: Ensure New File is loaded and configured. Field mapping requires both files to be loaded first.

Q: Headers aren't aligned in side-by-side view

A: Verify header row numbers are set correctly in Configure. Both files should have headers on the same row number.

⌨️ Keyboard Shortcuts

Action	Shortcut
Reset Comparison	Ctrl + R
Return to Main Menu	Ctrl + M
Exit Application	Alt + F4