How to use Excel’s conditional formatting to compare lists

Whether you’re comparing one list or multiple lists, conditional formatting in Excel can get the job done.

istock-844437488.jpg

Photo: iStock / NicoElNino

Comparing lists of common values ​​or frequencies is a task that often contains many variables. You can compare values ​​in the same list or you may want to compare one list to another. Then there is the duplicate definition. You will find many solutions if you search the internet, but you will not find a one-size-fits-all solution. You must know your data and implement the appropriate solution.

In this article, we will use conditional formatting to compare lists and identify duplicates. First, we’ll apply the built-in frequency rule to compare items in a single list; Then we’ll use it to compare two lists. Next, we’ll use a custom conditional formatting rule to find duplicates when the inline rule doesn’t fit.

I’m using Excel 2016 (for desktop) on Windows 10, but these rules are available on older ribbon versions. You can work with your own data or download the .xls and annotation file. xlsx. The browser version supports existing conditional formatting rules and you can even apply built-in rules. However, you cannot apply custom rules in the browser.

built-in referee

You can use a conditional formatting formula to compare data, but sometimes the inline rules can get the job done. You won’t need expert knowledge, but you should understand how the feature works to avoid frustration. For clarity, we will first look at how the inline rule compares to the items in a single list, using the simple sheet shown at Figure A As follows:

  1. Select the first list, B1: B12. (I repeated the last item on purpose.)
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. Choose Cell Highlight Rules and then select Duplicate Values ​​in the following list (Figure A).
  4. In the resulting dialog, select the appropriate format and click OK. as you can in Figure B, this inline rule highlights duplicates in a file same Column because we selected one column.

Figure A

excelcomparecompilation1a.jpg
This inline iterative rule compares items in a single list.

Figure B

excelcomparecompilation1b.jpg
Inline rules highlight duplicates in the same column.

Now let’s use the same built-in rule to compare the list in columns B to the list in column C. To do this, select B2:C12 and follow the same steps as above. Figure C Shows results. This rule applies discrimination if the item appears more than once – anywhere. This may include duplicates in the same column or items that occur more than once across both columns.

Figure C

excelcomparecompilation1c.jpg
The repeating rule highlights any element that occurs more than once in the specified range.

See: 10 Excel time savers you might not know (TechRepublic)

custom rules

Both menus appear in Figure A Similar, but there are subtle differences. Some items are unique to both lists. In addition, sometimes the element in column B differs from the corresponding element in column C. In this section, we will use a custom conditional formatting rule to specify which elements differ from one column to another. You can think of these items as mismatches. We have already seen that the inline rule evaluates all values ​​in any position, which is not what we want.

Conditional formatting can quickly determine the differences between two lists – from column to column – using an expression in the form:

= COUNTIF (Other listAndSelected menu) = 0

Now, let’s apply this rule to column B as follows:

  1. Select the list in column B, B2: B12.
  2. Click the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the drop-down list.
  3. Choose the Use a formula option to select the cells to format.
  4. Enters = COUNTIF (C2: C2, B2) = 0 in the formula control.
  5. Click Format, click the Fill tab, choose a color, and then click OK. figure d Shows form and judgment.
  6. Click OK to return to the sheet described in Figure E.

figure d

excelcomparecompilation1d.jpg
This rule will highlight items in column B that do not match the corresponding items in column C.

Figure E

excelcomparecompilation1e.jpg
The highlighted items in column B do not match the corresponding value in column C.

This rule does not highlight duplicates. Any value in column B that contains a value Not The corresponding cell in column C is easy to identify thanks to the contrasting fill color of the cell. It does not highlight the values ​​that occur in both columns as the inline rule did. To apply the rule to column B instead, you can use the rule =COUNTIF(B2:B2,C2)=0 after specifying C2:C11.

This rule works with values ​​as well as text entries. Rolls don’t have to match in size either. For example, the rule highlights the last value, Mishi Kobe Niku because the corresponding cell in column C is empty. In addition, slight differences are important. For example, if you remove the apostrophe character in the bob tag, the items no longer match. On the other hand, the comparison is not case sensitive. If the format does not feature an element as you would expect, compare the two. There may be an extra space character.

Next, we’ll use the same technique with a slightly different reference to compare two lists; If an element in column B does not appear anywhere in column C, then this element was highlighted in column B. Earlier, we marked the unmatched elements from column to column; We will now highlight the mismatched items in both columns.

In this example, we’ll highlight the items in column B that don’t appear in column C, as follows:

  1. B2 highlight: B12.
  2. Click the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the drop-down list.
  3. Choose the Use a formula option to select the cells to format.
  4. Enters = COUNTIF ($C$2: $C$12, $B2) = 0 in the formula control.
  5. Click Format, click the Fill tab, choose a color, and then click OK. shape and Shows form and judgment.
  6. Click OK to return to the sheet described in shape g.

shape and

excelcomparecompilation1f.jpg
This rule will highlight items in column B that don’t occur anywhere in column C.

shape g

excelcomparecompilation1g.jpg
It is easy to determine which value in column B does not occur in column C thanks to the contrasting fill color of the cell.

You see, there are three items in column B that don’t appear in column C. But what about column C? To select items in column C that do not occur in column B, repeat the above steps. However, in Step 1, select C2: C12 and in Step 4, enter the formula = COUNTIF ($B$2: $B$12, $C2) = 0; Choose another color in step 5, if you prefer. as you see in shape h, four items in column C, including the space at C12, do not appear in column B.

shape h

excelcomparecompilation1h.jpg
Two similar rules highlight items that do not appear anywhere in the other list (column).

keep following

In a later article, we will continue to solve more complex duplicate search problems.

Send me your question about Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

See also: