How to Compare Two Columns in Excel

There are many ways for comparing two columns on Excel. You can manually go through them, or a formula with conditional formatting can be used. In case the columns are separated into different worksheets, then you can see them next to each other. You can follow these ways to compare two columns in Excel.

Steps for Compaing Two Columns in Microsoft Excel

•    You need to bookmark the cell on the upper side, which is titled as the A1 given in the column located in the Sheet1.

•    Now, you have to start adding this formula  to the column “A1 cell: ‘=IF(Sheet1!A1<> Sheet2!A1, “Sheet1:”&Sheet1!A1&” vs. Sheet2:”&Sheet2!A1, “”)’

•    Pull the formula on the downward direction to each column for the cells which you wish to compare two columns in Excel.

•    In the result, the cells which are not original will be bookmarked in both of the columns that you are comparing with each other.

•    The comparison you have made will be bookmarked as Sheet1 vs Sheet2.

•    The differences will be included in the Difference1 section.

Steps for Comparing Two Columns for Differences in Microsoft Excel

•    Go to a fresh sheet in Microsoft Excel.

•    Bookmark the cell in the given columns which you want to compare.

•    You need to start adding  ‘=IF(Sheet1!A1<> Sheet2!A1, “Sheet1:”&Sheet1!A1&” vs Sheet2:”&Sheet2!A1, “”)’

•    Pull the formula to the downward direction on the webpage to get multiple cells which include in the columns being compared.

•    You need to bookmark the differences like Sheet1 vs Sheet 2, Difference1, etc. into the cell wherein you have made the comparison.

Steps for Comparing Two Sheets in Microsoft Excel

•    Go to the sheet in which you want the duplicates highlighted.

•    After that, choose the cell which is on the top of the sheet in Microsoft Excel.

•    Click on the Control + Shift +End keys together.

•    Press on the Home option and after that, choose the Conditional Formatting option.

•    Choose the New Rule option.

•    Enter ‘=A1<>Sheet2!A1’ in the prompt box.

•    Now, start comparing A1 Sheet1 with the A1 Sheet 2.

•    Choose the format which you want to show.

•    After that, press on the OK button.

Steps for Comparing Two Workbooks in Microsoft Excel

•    Press on the View Side By Side option.

•    Choose the View option.

•    Choose the Arrange All option.

•    After that, choose the Vertical option so that you can easily compare two workbooks in Microsoft Excel

Steps for Comparing Conditional Formatting in Microsoft Excel

•    Go to the Excel sheet, which includes the data you wish to format.

•    Bookmark the cells, which includes the data you wish to format.

•    Choose the Home menu and after that, press on the Conditional Formatting option.

•    Choose a Rule Set option.

•    Make a new rule set by pressing on the New Rule option.

•    Now, you have to type the parameters which should be higher than a hundred.

•    Choose the format on which you wish to show the results.

•    After that, choose the OK option.

Edward Lewis  is a Microsoft Office expert and has been working in the technical industry since 2002. As a technical expert, Edward has written technical blogs, manuals, white papers, and reviews for many websites such as office.com/setup.

Source : How to Compare Two Columns in Excel