Download Article
Download Article
This wikiHow teaches how to compare different sets of data in Excel, from two columns in the same spreadsheet to two different Excel files.
Steps
-
Highlight the first cell of a blank column. When comparing two columns in a worksheet, you'll be outputting your results onto a blank column. Make sure you are starting on the same row as the two columns you're comparing.
- For example, if the two columns you want to compare start on A2 and B2, highlight C2.
-
Type the comparison formula for the first row. Type the following formula, which will compare A2 and B2. Change the cell values if your columns start on different cells:
- =IF(A2=B2,"Match","No match")
Advertisement -
Double-click the Fill box in the bottom corner of the cell. This will apply the formula to the rest of the cells in the column, automatically adjusting the values to match.
-
Look for Match and No match . These will indicate whether the contents of the two cells had matching data. This will work for strings, dates, numbers, and times. Note that case is not taken into consideration ("RED" and "red" will match). [1] X Research source
Advertisement
-
Open the first workbook you want to compare. You can use the View Side by Side feature in Excel to view two different Excel files on the screen at the same time. This has the added benefit of scrolling both sheets at once.
-
Open the second workbook. You should now have two instances of Excel open on your computer.
-
Click the View tab on either window.
-
Click View Side by Side . You'll find this in the Window section of the ribbon. Both workbooks will appear in on the screen, oriented horizontally.
-
Click Arrange All to change the orientation.
-
Click Vertical and then OK . The workbooks will change so that one is on the left and the other is on the right.
-
Scroll in one window to scroll in both. When Side by Side is enabled, scrolling will be synchronized between both windows. This will allow you to easily look for differences as you scroll through the spreadsheets.
- You can disable this feature by clicking the Synchronous Scrolling button in the View tab.
Advertisement
-
Open the workbook containing the two sheets you want to compare. To use this comparison formula, both sheets must be in the same workbook file.
-
Click the + button to create a new blank sheet. You'll see this at the bottom of the screen to the right of your open sheets.
-
Place your cursor in cell A1 on the new sheet.
-
Enter the comparison formula. Type or copy the following formula into A1 on your new sheet:
- =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
-
Click and drag the Fill box in the corner of the cell.
-
Drag the Fill box down. Drag it down as far down as the first two sheets go. For example, if your spreadsheets go down to Row 27, drag the Fill box down to that row.
-
Drag the Fill box right. After dragging it down, drag it to the right to cover the original sheets. For example, if your spreadsheets go to Column Q, drag the Fill box to that column.
-
Look for differences in the cells that don't match. After dragging the Fill box across the new sheet, you'll see cells fill wherever differences between the sheets were found. The cell will display the value of the cell in the first sheet and the value of the same cell in the second sheet.
- For example, A1 in Sheet1 is "Apples," and A1 in Sheet2 is "Oranges." A1 in Sheet3 will display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula. [2] X Research source
Advertisement
Community Q&A
Search
-
QuestionHow do I find duplicate values in a row?Community AnswerTo find duplicate values simply use the Data --> Remove Duplicates feature and follow the prompts on the screen.
-
QuestionHow do I subtract two rows?Community AnswerYou have to select the 2 rows {shift} and then right click on the now highlighted rows. A box will pop up with a list of actions. You should click on 'delete rows'.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Tips
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
References
About This Article
Article Summary
X
1. Highlight the first cell in a blank column.
2. Type " =IF(A2=B2,"Match","No match")".
3. Drag the formula down to populate all cells in the column.
4. Look for "Match" or "No match" in the results.
Did this summary help you?
Thanks to all authors for creating a page that has been read 1,864,907 times.
Reader Success Stories
- "Needed to compare a list of employee data with sheets containing different elements, but one sheet was 3 employees off. This quickie compare helped identify those that were in one sheet and not another. The 'why' was a different matter!" ..." more
Advertisement
"Needed to compare a list of employee data with sheets containing different elements, but one sheet was 3 employees off. This quickie compare helped identify those that were in one sheet and not another. The 'why' was a different matter!"
..."
more
"I have spreadsheets with huge amounts of text edited by multiple people, so being able to compare spreadsheets this easily is a HUGE help to me in order to compile the latest and greatest versions."
..."
more
"This article helped me to compare construction documents much easier by being able to identify missing pages, and summary differences between revisions. "
..."
more
"Wow, thanks for the valuable information. I searched for the solution for over 2 hours and this gave me a simple solution."
..."
more
"I needed a way to compare two Excel files side by side. This article was very instrumental and visually easy to follow."
..."
more
