Are you new to Microsoft Excel and finding it hard to compare lists within the program? No worries, we’ve got you covered. This guide will explore a few great tech tips for comparing lists in Excel with multiple methods.
Compare Lists in Excel Tech Tips Method 1: Highlight Unique Cells
Using Excel’s unique “highlight” function, you may quickly compare two lists in your spreadsheet. This feature highlights items in one list that are not present in the other.
To utilize the procedure:
- Open your spreadsheet and select the lists you wish to compare.
- Click the “Home” section on Excel’s ribbon at the top while your lists are highlighted.
- Click “Conditional Formatting”, Highlight Cells Rules, then “Duplicate Values” in the “Styles” section of the “Home” tab.
- Click “Duplicate” and then “Unique” in the “Duplicate Values” box.
- Next, pick how you want to emphasize the missing elements from the second drop-down option.
- Finally, select “Custom Format” from the menu to customize your formatting. Click “OK” to save your modifications to your lists.
Excel will indicate the items in your lists that are missing. For example, those items missing from the second list will be marked in your first list, and so on.
Compare Lists in Excel Tech Tips Method 2: Use the Equal Sign Operator
This is another solution for comparing lists in Excel. In the next column, just after the first two columns, you must name a new column called “Status.” For example, =A2=B2 is the formula to enter cell C2. This formula compares the values of cells A2 and B2. If both cell values are the same, the outcome will be “TRUE” or “FALSE.”
Drag the formula to cell C9 to calculate the remaining values. The outcome will be “TRUE” or “FALSE” whenever you have the same values in common rows.
Compare Lists in Excel Tech Tips Method 3: Use Conditional Formatting
This is a more advanced method for comparing lists in Excel. Use a formula with conditional formatting if you only want to identify missing items in a certain list. To begin, pick all rows from your first list in your spreadsheet. Then, select the text box in the top-left corner, write “FirstList,” and hit Enter. This gives your range of cells a unique name, allowing you to refer to them all by the same name.
After choosing all of the rows in your second list, you’ll need to give it a unique name. Then, select the text box in the top-left corner, write “SecondList,” and hit Enter.
By clicking the text box in the top-left corner and selecting “FirstList,” you may select all rows of your first list. Next, click the “Home” tab on Excel’s ribbon at the top, then “Conditional Formatting”, then select the “New Rule” button.
You can define how your missing items will be highlighted in the “New Formatting Rule” section. Select “Use a Formula to Determine Which Cells to Format” from the “Select a Rule Type” portion of this box.
Type the following in the “Format Values Where This Formula is True” box:
“=COUNTIF(SecondList,A1)=0”
Select the “Format” option and decide how you want the missing items in your list to be formatted. Then click “OK” to preserve your changes. Returning to the spreadsheet, Excel has highlighted the items missing from the first list. And that’s it, you’re done.
Comparing lists in Excel really isn’t all that difficult!
Contact TCI Technologies
Contact TCI TechnologiesAt TCI we have a bunch of tech tips we’re ready to share. If you need help, contact TCI Technologies today!
