Can you think of a time when you had to compare two lists in Excel and you just sat and stared at your screen until everything just started to blur? Numbers and letters simply begin to float across the Excel workbook page and you’d rather punch the screen than continue to stare at those endless columns.
Manually comparing columns in Excel takes extreme focus, inordinate amounts of time, and is notoriously known to cause a headache or two. Understanding how to have Excel do the work for you will make your life easier, more productive, and infinitely less frustrating.
The Equals Operator
When you have to perform a row by row comparison to make sure that each column matches, there are various functions and conditional formatting options which will allow you to check your work. The Equals Operator will allow you to compare two columns, row by row, and will display your results as True or False.
- Once you have entered your complete lists into Column A and Column B of your Excel Workbook page, use Column C to insert your formula.
- If your columns have unique headers, make sure to only insert the formula in the row adjacent to the cells you wish to start comparing.
- Enter =A2=B2 into Column C. If your lists do not contain headers, your formula may read as =A1=B1.
- Click on the green square on the bottom right of the cell to drag down the function to all remaining rows. Immediately, you will see Column C populate with True or False depending on whether the row by row comparison matches or not.
The If Function
What if you would like your row by row comparison to read its results as Match or Mismatch? For very long lists, Match/Mismatch may be easier to follow versus True/False. The If Function allows you to label your results as Match or Mismatch.
- Insert =IF(A2=B2,”Match”,”Mismatch”) into Column C, adjacent to the rows you want to compare.
- Drag down the cell to complete the function for all remaining rows.
- Match or Mismatch will populate in Column C to help you easily identify which rows match and which do not.
The If Function can also be used if you need your row by row comparison to help you identify case sensitive differences. Simply insert =IF(EXACT(A2,B2),”Match”,”Mismatch”) into Column C. Drag down as needed to complete the function for all rows.
The Go To Special Function
If you want to just quickly do a row by row comparison to compare two cells in Excel, you can use the Go To Special function. This will provide a glimpse of how much data matches or doesn’t match between your two columns.
- Select the data set you wish to compare.
- Click the Home button and then choose the Find & Select icon located towards the left of the Styles toolbar.
- Select Go To Special and a dialogue box will appear. Click Row Differences and press okay.
- The matching data cells across the column’s rows will be white and the cells that don’t match will be light gray.
Highlighting Via Conditional Formatting
What if you don’t want the results of your row by row comparison displayed in Column C? You can choose to have your mismatches highlighted instead. This highlighting option also allows you to not simply perform your data comparison row by row. Instead, it searches your entire set for matches and mismatches. This is particularly helpful if you want to distinguish between differences that may not be in the same row.
- Select all the data you wish to compare.
- Click the Home button and then select Conditional Formatting from the Styles toolbar at the top of the page.
- Select Highlight Cell Rules and then Duplicate Values.
- A pop-up box will appear, allowing you to choose whether you would like to highlight duplicate cells or unique cells. Make your selection depending on whether you want to see the matching cells or the non-matching cells highlighted.
- In the same dialogue box, choose your preferred highlighting format from the different color and styles options. Select Custom Format if none of the available options speak to you.
The VLOOKUP Function
Sometimes you may need to discern whether a data point from Column A is also present in Column B. To do so, you can use the VLOOKUP formula.
- Insert =VLOOKUP(A2, $B$2:$B$5,1,0) into Column C.
- It is important to understand the composition of this formula so that you can make adjustments as needed depending on your data. The A2 refers to the values in cell A2. The $B$2:$B$5 is telling the formula to compare the value in cell A2 to the values in cells B2 to B5. Depending on how many rows Column B contains, you can alter your formula to include all your data cells. The 1 refers to the position of the column you wish to search in reference to the original subject list. Since this formula is comparing Column B to Column A, it is only one column away. Lastly, the .0 means that you are looking for exact matches.
- With an understanding of this formula, you can adjust it as needed to suit your particular data set. Once you have inserted your customized Lookup Function into Column C, drag down to repeat the function across all rows. Your results will populate in Column C and exact matches will appear as-is and mismatches will read as #N/A.
- Sometimes the #N/A results can be confusing, particularly if you may be sharing your Excel list with someone not too familiar with this type of function. You might want Column C to display “Mismatch” to make it easier to read. To do so, insert =IFERROR(VLOOKUP(A2, $B$2:$B$5,1,0),”Mismatch”) as your function in Column C. Now, Column C will either display the matching data or, if it doesn’t match, it will read as Mismatch.
- Remember to adjust your formula as needed based on your exact field of data.
As is evidenced above, there are a plethora of options when it comes to comparing two columns in Excel. The one you choose will largely depend on the complexity of your list and how you wish to see your results displayed. The reason so many options exist is because every Excel user may have differing needs. Some people prefer a Column C to easily distinguish between matches and mismatches. Others find the highlighting method much clearer and easier to implement. No matter your desired choice, at Geeker.co we will provide you with the expertise necessary to understand and manipulate your data as needed.