How to Compare Data in Excel Files and Find Differences - Bump Files like a Pro!

Have you ever been asked to “bump those files together” or “just bump that file against this one?” If you've felt overwhelmed by the thought of finding differences in Excel files, you’re not alone! Comparing data can seem daunting, but it doesn’t have to be. In this tutorial, I’ll walk you through three real-life examples that show how to tackle common data comparison challenges in Excel. You’ll learn how to identify missing data using XLOOKUP, pinpoint changes in reports using IF Formulas and Conditional Formatting, and detect duplicates with Excel’s built-in Highlight Duplicates feature.

By the end, you’ll be equipped with actionable techniques to make data comparisons a breeze. You can always watch the video tutorial for a detailed demonstration:

How to compare data in Excel files and find differences between Excel worksheets! Have you heard people say, "Just bump that file against this one" or "Bump those files together?" Well, there are many ways we can do this in Excel. In this video, I'll show you three real-life examples, including: How to find missing data between two Excel files using XLOOKUP; How to find differences between two Excel reports using IF Formulas and Conditional Formatting; and How to detect duplicates in your data using Excel's built-in highlight duplicates feature.

Example 1: Finding Missing Data Between Two Files with XLOOKUP

1. Set Up Your Data

• Open the two Excel files: one with work assignments and another with your employee list.

• Identify the column containing the unique identifier (e.g., Employee ID).

2. Calculate Total Records

• Highlight the Employee ID column in each file.

• Look at the count in the Excel status bar to compare totals and determine if records are missing.

3. Add a New Column

• Insert a new column titled “Missing Work Assignment” in your employee list file.

• Apply a distinct color to the header to keep it organized.

4. Use XLOOKUP to Find Missing Data

• Enter the formula:
  =XLOOKUP(A2, 'WorkAssignments'!B:B, 'WorkAssignments'!C:C, "#N/A")

  NOTE: This formula’s cell references align with the spreadsheet used in the video tutorial

  • A2: Employee ID in the employee list.

  • 'WorkAssignments'!B:B: Employee ID column in the work assignments file.

  • 'WorkAssignments'!C:C: The value to return if a match is found.

  • "#N/A": The result if no match is found.

• Drag the formula down the column to apply it to all rows.

5. Filter Missing Records

• Use the filter feature to display rows with “#N/A.”

• These represent employees without work assignments.

Example 2: Identifying Changes Between Two Reports with IF Formulas and Conditional Formatting

1. Compare Data with IF Formulas

• Insert a new column in your newer report and paste the salary data from the older report.

• Add another column for comparison and use this formula:
  =IF(F2=G2,"No Change","Changed")

  • F2: Salary in the newer report.

  • G2: Salary from the older report.

• Copy the formula down to check all records.

2. Filter for Changes

• Apply a filter to show rows labeled “Changed.”

• These represent employees with salary adjustments.

3. Use Conditional Formatting for Dynamic Comparisons

• Highlight the salary column in the new report.

• Go to Home > Conditional Formatting > New Rule.

• Choose “Use a formula to determine which cells to format” and enter:
  =A2<>Sheet2!A2

  • A2: Salary in the newer report.

  • Sheet2!A2: Corresponding salary in the older report.

• Remove absolute references (dollar signs) to make the range dynamic.

• Set a highlight color for mismatched cells.

4. Filter by Highlight Color

• Use the filter tool to display only the highlighted cells, revealing changes.

Example 3: Detecting Duplicates in Your Data

1. Prepare Your Data

• Open the file containing job codes.

• Sort the column to group similar values together.

2. Create a New Column for Cleaned Codes

• Insert a new column titled “New Job Code.”

• Use the formula:
  =RIGHT(A2,5)

  • This removes any letters and extracts the last five digits.

• Drag the formula down to apply it to all rows.

3. Highlight Duplicates

• Copy the cleaned column and paste it as values using Ctrl+Shift+V.

• Highlight the column, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

• Choose your preferred highlight color.

4. Filter Duplicates

• Apply a filter and select the highlight color to see duplicates.

Wrapping Up

These techniques—XLOOKUP, IF formulas, conditional formatting, and duplicate detection—are powerful tools for comparing Excel files. Whether you’re managing HR data, tracking changes in reports, or cleaning up duplicates, these methods will save time and improve accuracy.

For a full walkthrough of these steps, watch my YouTube video tutorial here. Don’t forget to subscribe for more tips, and visit my website, SharonSmithHR.com, for additional resources and templates.