• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Comparing Lists in Excel

May 21, 2021 by Andrew Childress
Comparing lists Excel

You may find yourself wanting to compare two lists in a spreadsheet. Here's how to compare lists in Excel with a simple formula.

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. 

Comparing lists in Excel

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.

Compare lists Excel

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.

Comparing lists Excel

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.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Lock cells in Microsoft ExcelLock Cells in Excel
Next Post:Average in ExcelAverage in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top