Skip to main content

How to Compare Two Excel Sheets Cell by Cell

Sometimes you may need to compare two Excel sheets for difference in data. Manually doing this would be extremely tiring particularly if the tables are large. The easiest way to do this is to use the free Spreadsheet Compare (Excel compare) add-in for Microsoft Excel.

Spreadsheet Compare performs a cell-by-cell comparison of Worksheets within the same or different Workbooks and then highlights the changes on the sheet, or prepares a new sheet report showing all the changes the program has found. The add-in is written in VBA and is compatible with Excel 2000 and through every version till Excel 2010.

After installation, the Spreadsheet Compare function becomes available under the Add-in tab in MS Excel’s ribbon. You will notice two buttons here - Compare and Sync Scroll. Click the Compare button.

spreadsheet-compare

A step-by-step wizard will guide you through the process which is, briefly, as follows: you select the two spreadsheets to be compared, select the start and end row, set sheets to ignore (if any), specify data ranges to ignore (if any) select report type, choose highlight colors and if necessary, stop the program once the first mismatch is detected. If you don’t have any changes to make in the configuration, you can skip all the steps and jump directly to ‘compare’.

After the comparison is done, Spreadsheet Compare highlights the differences found on both sheets and adds a column to show you which columns have been changed.

spreadsheet-compare2

It also generates a detailed comparison report.

spreadsheet-compare3

And a Merged Difference report.

spreadsheet-compare4

The other function of Spreadsheet Compare is to enhances the Synchronous Scrolling feature which was added in Excel 2003, and is incorporated via the ‘Sync Scroll’ feature. Say the spreadsheets you wish to compare are not properly aligned, i.e. one of the sheet has additional rows or missing rows. The Sync Scroll feature allows you to synchronously scroll the two spreadsheets and checked for added or deleted rows. Rows can then be added so that all the matching rows are correctly aligned before performing a comparison.

Spreadsheet Compare should be very useful when working with large Excel sheets, and can potentially save you enormous amount of time.

Comments

  1. There is so much advertizing garbage on this page that is it impossible to find the actual product.

    ReplyDelete
  2. Thanks for the the link to "Spreadsheet Compare". But what about this "Download Manager" ad on the top of this page? When (accidently) clicking on it, nasty stuff gets downloaded and will spread all over the harddrive. Horrible! Kinda worrying that GoogleAdservices will link to such crap.

    ReplyDelete
  3. When I try to run the add in I get a VB error that says "Run time error 459, Can't move the focus....."

    ReplyDelete
  4. I doesn't work on MS Excel 2007. It returns
    "
    Compile error:
    Can't find project or library
    "
    It stops right here
    Private Sub LoadSettings()

    ReplyDelete
  5. It worked really well for me. Thanks for this macro

    ReplyDelete
  6. It stops here in excel 2007 for me
    Private Sub LoadSettings()

    ReplyDelete
  7. Why does this try to connect to the Internet? Do you know antecedents of the author?

    ReplyDelete
  8. Oh this is just awesome! In my office I daily need to compare some spreadsheets. Previously I did this by using excel formulas like "=A1=B1", but this method was too tedious.
    Your add-in will definitely help me. Thanks for sharing such useful information.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Record CPU and Memory Usage Over Time in Windows?

Whenever the computer is lagging or some application is taking too long to respond, we usually fire up task manager and look under the Performance tab or under Processes to check on processor utilization or the amount of free memory available. The task manager is ideal for real-time analysis of CPU and memory utilization. It even displays a short history of CPU utilization in the form of a graph. You get a small time-window, about 30 seconds or so, depending on how large the viewing area is.

How to Schedule Changes to Your Facebook Page Cover Photo

Facebook’s current layout, the so called Timeline, features a prominent, large cover photo that some people are using in a lot of different creative ways. Timeline is also available for Facebook Pages that people can use to promote their website or business or event. Although you can change the cover photo as often as you like, it’s meant to be static – something which you design and leave it for at least a few weeks or months like a redesigned website. However, there are times when you may want to change the cover photo frequently and periodically to match event dates or some special promotion that you are running or plan to run. So, here is how you can do that.

Diagram 101: Different Types of Diagrams and When To Use Them

Diagrams are a great way to visualize information and convey meaning. The problem is that there’s too many different types of diagrams, so it can be hard to know which ones you should use in any given situation. To help you out, we’ve created this diagram that lays out the 7 most common types of diagrams and when they’re best used: