How To Compare 3 Sets Of Data In Excel

1
Highlight the commencement jail cell of a bare column. When comparison two columns in a worksheet, you'll be outputting your results onto a blank column. Brand sure you are starting on the aforementioned row as the ii columns you're comparing.
- For instance, if the 2 columns you want to compare start on A2 and B2, highlight C2.
two
Blazon the comparison formula for the start row. Type the following formula, which will compare A2 and B2. Change the prison cell values if your columns beginning on different cells:
- =IF(A2=B2,"Friction match","No friction match")
Advertisement
3

Double-click the Fill box in the lesser corner of the cell. This will apply the formula to the rest of the cells in the column, automatically adjusting the values to match.
four

Look for Match and No match . These will indicate whether the contents of the ii cells had matching data. This will work for strings, dates, numbers, and times. Annotation that case is non taken into consideration ("Ruby" and "reddish" will match).^[1]

Advert

1

Open the first workbook you want to compare. You can utilize the View Next feature in Excel to view two dissimilar Excel files on the screen at the same time. This has the added do good of scrolling both sheets at one time.
2

Open the second workbook. You should now have two instances of Excel open on your figurer.
iii

Click the View tab on either window.
4

Click View Next . You'll find this in the Window section of the ribbon. Both workbooks will appear in on the screen, oriented horizontally.
5

Click Accommodate All to change the orientation.
6

Click Vertical and then OK . The workbooks will change then that 1 is on the left and the other is on the right.
7
Scroll in one window to coil in both. When Side by Side is enabled, scrolling volition be synchronized betwixt both windows. This will allow you to easily look for differences as you scroll through the spreadsheets.
- You lot tin can disable this feature by clicking the Synchronous Scrolling button in the View tab.
Ad

1

Open the workbook containing the two sheets you want to compare. To apply this comparison formula, both sheets must be in the same workbook file.
two

Click the + button to create a new blank sheet. Yous'll see this at the bottom of the screen to the right of your open sheets.
iii

Place your cursor in prison cell A1 on the new sheet.
four
Enter the comparing formula. Type or copy the post-obit formula into A1 on your new sheet:
- =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
5

Click and drag the Make full box in the corner of the cell.
6

Elevate the Make full box downwards. Drag information technology downward as far downward as the commencement 2 sheets become. For example, if your spreadsheets go down to Row 27, drag the Fill box downwards to that row.
7

Elevate the Make full box correct. After dragging it down, drag information technology to the right to comprehend the original sheets. For example, if your spreadsheets go to Cavalcade Q, drag the Fill box to that column.
8
Look for differences in the cells that don't match. After dragging the Fill up box beyond the new sheet, you lot'll see cells make full wherever differences between the sheets were found. The prison cell will display the value of the cell in the commencement 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 volition display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.^[two]
Advertising

Add New Question

Question

How practice I find indistinguishable values in a row?

To notice duplicate values only apply the Data --> Remove Duplicates characteristic and follow the prompts on the screen.
Question

How do I subtract two rows?

Yous have to select the 2 rows {shift} so right click on the at present highlighted rows. A box volition pop upward with a listing of actions. Y'all should click on 'delete rows'.

Ask a Question

200 characters left

Include your e-mail address to get a bulletin when this question is answered.

Submit

Thanks for submitting a tip for review!

About This Article

Commodity Summary X

1. Highlight the start cell in a blank cavalcade.
2. Type " =IF(A2=B2,"Match","No friction match")".
3. Drag the formula down to populate all cells in the cavalcade.
four. Look for "Match" or "No match" in the results.

Did this summary assist you?

Thanks to all authors for creating a page that has been read 1,795,850 times.

Is this article up to appointment?