fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

28 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/

  16. PereComelles says:

    If you have Excel 2013 or newer, you can use "Spreadsheet Compare" to compare two workbooks:

    http://www.sysadmit.com/2015/10/excel-comparar-ficheros.html

  17. Maxim says:

    Chandoo,

    I have two Excel 2016 files and want to compare them, but "View Side by Side" button, "Synchronous Scrolling" and "Reset Window Position" are greyed and I cannot access them. I have enabled "Inquire" comm add-in, I have enabled and disabled other add-ins, but no luck.
    Unfortunately "Spreadsheet Compare 2016" tool is not working.

    On MS (MonstruouSoft) site, on "View Side by Side" tips, they say "In Microsoft Excel 2013 and Excel 2016, workbooks that you open in Excel are no longer displayed as multiple workbook windows inside a single Excel window. Instead, they are shown as separate Excel windows."

    Is there a way to return to ol' good "Side by Side" behaviour?
    Thx, I'm really desperate.

    • Hui... says:

      @Maxim
      Open the two files
      Goto the View Tab
      Select View Side by Side
      Select Synchronous Scrolling

      Both View Side by Side and Synchronous Scrolling should now have a Darker Grey background
      Scroll away

      • Maxim says:

        Hui,

        What I am saying is that I cannot access "View Side by Side" because they are greyed out and found no option to turn this on.
        When I'm logged on another PC with Office 2007 I am able to do that, but not in Office365/Office 2016.
        I need help on how to turn on this command, directly or through a macro.

        Thx and regards,
        Maxim

        • Hui... says:

          Maxim

          Office 365 isn't Office 2016
          Office 365 is a web based Office
          Office 2016 is a standalone version

          The View Side by Side and scrolling works on Office 2016 as that is what I use day to day
          I will test 365 tomorrow, but it may not be implemented yet

  18. Me3 says:

    {=AND(EXACT(Sheet2!A1:D1,sheet1!A1:D1))}

  19. rohit kulkarni says:

    Hi,

    I need to compare two large size files having similar headers with one common factor. I am looking out for automating the compare process through some macros or likewise as it is going to be a repeated activity. can you guide me how it can be done.

  20. john 82 says:

    You can compare 2 Excel sheets with highlighting the differences (update, new, removed) cells and rows without using any formula using Dose for Excel Add-In:

    https://www.zbrainsoft.com/Compare-Sheets.html

Leave a Reply