You can think of these items as mismatched. In this section we’ll use a custom conditional formatting rule to spot the items that are different from one column to the other. In addition, sometimes the item in column B differs from the corresponding item in column C. The two lists shown in Figure A are similar, but there are subtle differences. See: 10 Excel time-savers you might not know about (TechRepublic) Custom rules Figure C The duplicate rule highlights any item that occurs more than once in the selected range. That might include duplicates in the same column or items that occur more than once across both columns. This rule applies highlighting if the item appears more than once–anywhere. To do so, select B2:C12 and follow the same steps as above. Now let’s use the same built-in rule to compare the list in columns B to the list in column C. Figure B The built-in rules highlights duplicates in the same column. As you can in Figure B, this built-in rule highlighted duplicates in the same column because we selected a single column.įigure A This built-in duplicate rule compares items in a single list.
I’m using Excel 2016 (desktop) on a Windows 10 system, but these rules are available in older ribbon versions.
How to create a fun Fly In effect in PowerPoint
Microsoft Office: Get a lifetime license and second laptop screen for one price Next, we’ll use a custom conditional formatting rule to find duplicates when the built-in rule isn’t adequate. First, we’ll apply the built-in duplicates rule to compare items in a single list then we’ll use it to compare two lists.
In this article, we’ll use conditional formatting to compare lists and spot duplicates. You must know your data and apply an appropriate solution. You’ll find many solutions if you search the internet, but you’ll find no one-size-fits-all solution. Then there’s the definition of duplicate. You can compare values in the same list or you might want to compare one list to another. Whether you're comparing a single list or several, Excel's conditional formatting can get the job done.Ĭomparing lists for common values, or duplicates is a task that often has many variables. How to use Excel’s conditional formatting to compare lists