Compare 2 Excel Files using View side by side Mode [Quick Tip]

Posted on April 16th, 2012 in Excel Howtos - 18 comments

Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.

Compare 2 files or sheets of data in Excel - How to

Example:

Lets say we have 2 files – this_month.xls and last_month.xls both with our employee productivity data. We want to see both of them together to understand which employees did better in both months. Like this:

How to Compare 2 files in Excel using View Side by Side mode - Demo

To do this:

  1. Open both files
  2. Go to View ribbon
  3. Turn on “View side by side” mode.
  4. If promoted, select the other file.

But how to compare data in 2 different sheets in same file?

Sometimes, we may have 2 sheets, say this_month & last_month in the workbook and we want to compare both. Like this:

How to Compare 2 sheets in Excel using New Window & View Side by Side mode

In such cases:

  1. Open the file
  2. Go to View ribbon
  3. Click on new window. This opens the same file in one more Excel window.
  4. Enable View side by side mode.
  5. Select sheet 1 in first window, sheet 2 in second window.
  6. Compare and be merry!

Do you compare files like this?

Nowadays, I rarely use this mode of comparison as I never have such data. But back when I was working as a Business Analyst, I used to compare 2 files (or sheets) using this method. It is quick and answers any ad-hoc questions.

What about you? Do you use view side by side mode & compare files? Share your experience & tips using comments.

More on Comparison using Excel

If you deal with similar data & compare it often, read these articles to learn more:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Compare 2 Excel Files using View side by side Mode [Quick Tip]”

  1. Bobcat says:

    I have a spreadsheet that is 445 lines long and A-AI wide (35 columns). The easiest method I have found is to highlight all of the new line with green, import and then sort on column A twice using custom sort. I sort with 2 conditions; 1-alpha column A, 2-color green (on top) column A. Then I painstakingly compare the 2 rows.

  2. Doug Bailey says:

    When I have two files to compare like this, I add a column called “Source” and put some identifier like “File A” in that column in the first file and “File B” as the source in the second file. I copy the columns I want to compare from File A (along with the “Source” column) and create a new file. Then I copy the corresponding columns from File B and append it at the end of my new file.

    Now you can just do a pivot table with the comparison value as the row label, “Source” as the column label and see at a glance which values are in each file. If you need to compare some numeric value in both files, you can use that as the summed value in the pivot table. Finally, if you reverse the sign of the values in one of the files, the pivot table row total will show you the difference between the values in each file.

    Sounds complicated, but once you do it a couple of times you can do it pretty quickly. It makes the comparison process simple and works great for reconciliation.

  3. Kiev says:

    It is kind of plus to get the idea straight away. Easy to understand and also to apply for, although Pivot table or Excel formula can do the same job. Thank you.

  4. Pynn says:

    If it is a large grid of numerical data, I will often create a new sheet, copy the headers and row title as applicable then subtract the two values. From there use conditional formatting to detect significant variance.

    For period over period financial data series, charts are my preferred method of comparison.

    For list/or category comparison i will use countif (presence) or vlookup (value).

    I rarely use side by side compare, even though it is theoretically faster, i find myself double and triple checking.

  5. Mike says:

    What software do you use to record GIF in such good quality?

  6. Uwe says:

    One of the easiest ways if you have a similar structure is to do like Chandoo I guess, but one of the best to not give up or skip any important changes in information is to use the vlookup function (if you always have the same data and structure).
    If the dataset cannot be found, it will be shown directly. This makes it easier I guess, if you want to be on the safe side.

  7. Steve says:

    I have two screens and have found that the only way to view another file on the second screen is to open excel twice. Anyone know of a more elegant solution?

    • Erich says:

      for me the better way is to open Excel just once, make it “windowed” or “Restore” (not fullscreen) and enlarge the window over both screens. Then arrange your file-windows side by side. (I have some Macros assigned to buttons that do the resize of the app-window and the file-windows).

      With just one Excel open you have less problems with copy&paste-actions…

  8. Prasanna Venkatesh says:

    Normally i use vlookup to compare different sheets havin common data

  9. Rebekah says:

    I copy and paste my Excel worksheets to be compared into separate Word documents, then use Word’s merge and compare function to compare the two. It quickly shows you all differences between the worksheets via “change tracking”. Quick, easy, cheap.

  10. Marisa says:

    The best way to compare two Excel workbooks is to use DiffEngineX.
    It is free to use for the first 30 days. You can downloaded it from
     
    http://www.florencesoft.com/excel-differences-download.html
     
    Some instructions on how to use it can be found at
    http://www.florencesoft.com/better-viewing-of-excel-differences.html
    http://www.florencesoft.com/diffenginex-guide.html
     
     
     

  11. ad says:

    not really. If your tables have thousands of lines, and you want to compare them because you have versioning problems, for example, the easiest way I found was:
    – save each list/table/file as flat text (CSV)
    – open the documents in a code text editor like ViM, Notepad++, anything like that
    – run a “compare documents” command. That way you dont have to go hunting which lines are different yourself.

  12. Chris says:

    One way that I frequently use, and it is really simple, is conditional formatting. This assumes that you are attempting to verify that two sets of data actually look identical or not. 
    If you are comparing 2 sheets in the same workbook, go ahead and select the second sheet. Highlight the data cells. 
    Click on conditional formatting near the top right on the home menu. 
    Click “new rule”
    Select the option “Use a formula to determine which cells to format”
    formula is something like: =A1<>Sheet1!A1 
    which assumes that A1 is the top left corner of the data you selected. 
    Pick a format, i usually do a yellow fill with red font, but whatever works for you. 
    Click ok and if any cells are different in the second sheet from those in the first, they will appear with the formatting you selected. 
    Note: you may need to sort both sheets so that they would be in the same order. If one sheet has more or less rows than the other, then probably everything from that row discrepancy onward would be formatted with the conditional format you set, so this is primarily only good to verify if there were any changes between two sheets. 
     

  13. Denice Juma says:

    Thanks Chandoo for the hint, it came in handy in tracing discrepancies in different worksheets

  14. Zebla says:

    When I compare some complex Excel workbooks, I use Excel Diff.
    It seems to have free trial version for 30 days.
    This software can download here.

    http://www.suntrap-systems.com/ExcelDiff/

    And I found other software here.
    http://www.tucows.com/search.html?search_scope=win&tree_id=689&cost=&tag=&search_terms=Excel+Diff&search_type=soft

  15. Vijay says:

    Visit my blogs for text files and excel files data comparison details. No VLOOKUPS and No Formulaes. Just input the data in input files provided and run the program and get the output

    http://datadecoder.blogspot.in/
    http://datacompare.blogspot.in/
    http://exceljoins.blogspot.in/

Leave a Reply