Microsoft Excel is extremely versatile and this leads to it being the most popular spreadsheet software in the industry today.
This widespread use of Excel is more often than not likely to lead to situations where you want to find the difference between certain tables, columns or entire spreadsheets. In this article, we walk you through just how to do that.
We assume you have a basic working knowledge of Excel.If not, we do recommend you try out this Excel course that’s been made really easy or alternatively this Excel crash course for beginners. Once you’re done, read on to learn about some of the most commonly used functions to compare and retrieve data in Excel.
Use Conditional Formatting to Differentiate and Highlight Values
Say you have two lists of data (List 1 and List2) in your excel worksheet. You want to compare them to find out whether they have some common data, or highlight the different data sets. How would you do it? Here’s one way you can use to compare two lists and identify the different or duplicate data.
- Select the two columns that you want to compare
- In the Home tab, click “Conditional Formatting”. You will get a drop-down menu.
- Click on “Highlight Cell Rules,” and then go straight to “More Rules” and click on it.
- Here you will find many options. Depending upon your preference choose one of them. For instance, say you want to highlight all the data that’s different in the both the columns. First select “Format only unique or duplicate values.“, then, choose “Format only unique”. Alternatively, if you want to format only duplicate values, select “Duplicate values”.
- Now Click the “Format” button and set the font, border and fill options to highlight the cells you want.
- Click “OK” to confirm the changes.
Using IF function to differentiate between values of two lists
If you have two columns containing numerical values, you can use the If() function to find whether the value of one column is greater than the other, less, equal to or not equal to. Just follow these simple steps
- Lets say you have the list values in A1 and B1. Now place your cursor in C1.
- Enter the function, “=if()”. Figure how you want to compare the values in A1 to B1 – equal, greater, lesser etc. IF () has three parts. The first part is the conditional part, the second is the cell value if the condition is met and the third part is the cell value if the condition is not met. Here are the formulas that you can use.
- =IF(A3<>B3,”true”,”false”) (not equal to)
You can use one of the functions at a time.
- Enter the formula and press the “Enter” Key.
- Now you can either copy and paste the formula into C2, C3 and so on. Or you can hold the handler of your cursor in C1 and drag it down.