Often, you may find yourself wanting to compare two lists in a spreadsheet. With a simple formula, you can do exactly that. Let’s learn how to compare lists in Microsoft Excel.
How to Compare Lists in Excel
Consider two lists of names. You want to be able to quickly see which names appear in both lists, and which names do not. Of course, you could manually analyze the lists, but that gets difficult if one or both is very lengthy. Fortunately, Excel’s Conditional Formatting feature will quickly run the analysis and return an easy-to-understand visual comparison.
To get started, go ahead and select your data range. Do that by clicking and dragging your cursor across the cells. Here, that range is D1:E5.
Next, go to the Home tab on Excel’s ribbon. Just right of center, you’ll see the Conditional Formatting button with a dropdown arrow. Click the dropdown, and then hover over Highlight Cells Rules. The option you’ll want to select is at the bottom: Duplicate Values.
Click here, and you’ll see Excel launch the New Formatting Rule menu. Here, you have several stylistic choices to make. By default, Excel is set to Format Only Unique or Duplicate Values, with Duplicate values in the selected range. Since you’re seeking names that appear in both lists – therefore being duplicates – you can leave these settings alone.
The Format With dropdown lets you choose the color formats. Red is the default, but you can change it to Green Fill with Dark Green Text, for example.
When you’re finished, click OK. All duplicate values will be highlighted in green, with the text changed to a dark green. This makes it instantly obvious at a glance which names occur in both lists.
As you can see, Conditional Formatting is a great way to easily capture duplicate values in an easy-to-use visual. It only takes a few clicks. Give it a try next time you need to compare lists in Excel.
Lock Cells in Excel