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.
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:
To do this:
- Open both files
- Go to View ribbon
- Turn on “View side by side” mode.
- 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:
In such cases:
- Open the file
- Go to View ribbon
- Click on new window. This opens the same file in one more Excel window.
- Enable View side by side mode.
- Select sheet 1 in first window, sheet 2 in second window.
- 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:
28 Responses to “Compare 2 Excel Files using View side by side Mode [Quick Tip]”
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.
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.
That's exactly what I'm doing. After the comparison, I just filter on the variance and work on them.
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.
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.
What software do you use to record GIF in such good quality?
@Mike... I use Camtasia Recorder to produce these animated GIFs. Excellent software I must say. Visit http://chandoo.org/wp/about/what-we-use/ to know more about Camtasia & other tools I use.
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.
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?
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...
Normally i use vlookup to compare different sheets havin common data
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.
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
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.
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.
Thanks Chandoo for the hint, it came in handy in tracing discrepancies in different worksheets
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
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/
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
you are god
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.
@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
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
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
{=AND(EXACT(Sheet2!A1:D1,sheet1!A1:D1))}
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.
Hey Rohit,
Here's an automated online Compare Tool that may help you out: https://automatethatshit.com/lab/compare-spreadsheets
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