This article shows how to compare different data sets using Microsoft Excel, starting with information contained in two different columns, but belonging to the same sheet, up to the comparison of two distinct workbooks.
Steps
Method 1 of 3: Compare Two Columns
Step 1. Select the first cell of an empty column
When comparing data stored in two columns of an Excel sheet, the result of the comparison must be entered in a third column. Make sure you start from the same row from which the data of the two columns to be analyzed starts.
For example, if the data inside the two columns to be compared starts from cells A2 and B2, we will have to select cell C2
Step 2. Type the formula to compare within the chosen cell
To compare the data in cells A2 and B2, type the following formula (if the data in your Excel sheet are stored in columns and rows other than those in the example, remember to make the necessary changes):
= IF (A2 = B2, "Same values", "Different values")
Step 3. Select the fill handle, located in the lower right corner of the highlighted cell, with a double click of the mouse
In this way, the formula inserted in it will be applied to the rest of the cells of the same column and the reference values will be changed automatically to fit the dataset to be compared.
Step 4. Look at the results of the analysis
Look for the words Equal Values and Different Values within the column used as the output. These two indications indicate whether the data contained in the two compared cells coincide or not. This simple formula works for different types of data: strings, dates, numbers and times. Note that, in this case, words written with uppercase or lowercase characters are not taken into account, so the strings "RED" and "red" will be equal to each other.
Method 2 of 3: Compare Two Work Folders Visually
Step 1. Open the first workbook you want to compare
To visually compare two different Excel files, you can use the side-by-side view mode. In this way, you have the additional advantage of being able to consult the contents of both files at the same time.
Step 2. Open the second workbook
At this point, you should be able to see two instances (two windows) of Excel on your computer screen.
Step 3. Go to the View tab of one of the two program windows
Step 4. Click the Tile icon
It is located within the "Window" group of the Excel ribbon. Both files will be displayed on the screen, side by side horizontally.
Step 5. To change the orientation of the windows, press the Arrange All button
Step 6. Choose the Vertical option, then press the OK button
The orientation of the two displayed windows will be changed so that they appear side by side vertically: one arranged on the left side of the screen, the other on the right side.
Step 7. Scroll through the contents of a window to be able to examine the data of both at the same time
When the side-by-side view mode is active, the scrolling of the data present in all the Excel windows displayed on the screen is synchronized. This makes it very easy to visually identify any differences between the different worksheets displayed.
To disable this feature, simply press the "Synchronous Scroll" button located within the "View" tab of the Excel ribbon
Method 3 of 3: Compare Two Sheets for Differences
Step 1. Open the Excel workbook which contains the sheets to be processed
To use the formula shown in this section of the article, both Excel sheets must be stored within the same workbook, i.e. within the same file.
Step 2. Press the + button to create a new blank sheet
It is located at the bottom right of the Excel sheets currently displayed on the screen.
Step 3. Place the cursor on cell A1 of the new sheet you just created
Step 4. Type the formula to compare
Manually enter or paste the following formula into cell A1 of the newly created Excel sheet:
= IF (Sheet1! A1 Sheet2! A1, "Contents Sheet_1:" & Sheet1! A1 & "and Contents Sheet_2:" & Sheet2! A1, "")
Step 5. Select the fill handle in the lower right corner of the cell where you entered the formula without releasing the mouse button
Step 6. Move the mouse cursor down
Continue dragging the fill handle down to where you want it. For example, if your worksheet has valid data up to row 27, you need to drag the fill handle down to that row.
Step 7. Move the mouse cursor to the right
After reaching the final row of the dataset to be analyzed, move the mouse cursor to the right to understand all the information present within the original sheets. For example, if the dataset to compare extends to the Q column of both sheets, drag the fill handle to that position.
Step 8. Visually search the cells where the comparison result is shown
This will only happen if the comparative information does not match. After you have extended the formula to the entire area of the new worksheet that matches those in which the data in the two original sheets is present, you will see the comparison results appear only in the cells whose starting information does not match. Inside these cells the value contained in the same cell of the first sheet will be shown, together with that of the same cell of the second.
