Science & Tech

How to Compare Two Lists in Microsoft Excel

Microsoft Excel logo on a green background.

Microsoft Excel affords two completely different strategies that will help you examine two lists and spotlight the lacking gadgets in each. You possibly can select to focus on the lacking gadgets in each lists or in a single one. We’ll present you the way.

RELATED: Easy methods to Discover the Proportion of Distinction Between Values in Excel

The Fast Means: Spotlight Distinctive Cells to Evaluate Lists

A fast option to examine two lists in your spreadsheet is to use Excel’s distinctive spotlight function. This function highlights the gadgets in a listing that aren’t discovered within the different listing. This manner you realize precisely what gadgets are lacking out of your lists.

To make use of the strategy, first, choose the lists you wish to examine in your spreadsheet.

Select both lists.

Whereas your lists are highlighted, in Excel’s ribbon on the high, click on the “Home” tab.

Select "Home" at the top.

On the “Home” tab, within the “Styles” part, click on Conditional Formatting > Spotlight Cells Guidelines > Duplicate Values.

Choose Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Within the “Duplicate Values” field, click on “Duplicate” and select “Unique.” Click on the second drop-down menu and select the way you want to spotlight the lacking gadgets. To specify your individual formatting, select “Custom Format” from the menu.

Then apply your adjustments to your lists by clicking “OK.”

Highlight unique cells in Excel.

Excel will spotlight the lacking gadgets in your lists. For instance, in your first listing, you’ll have these gadgets highlighted which are lacking from the second listing, and so forth.

Two lists compared in Excel.

And that’s it.

The Components Means: Use Conditional Formatting to Evaluate Lists

The above technique highlights gadgets in each your lists. When you’d solely like to focus on lacking gadgets in a selected listing, then use a components with conditional formatting as defined beneath.

RELATED: Easy methods to Use Conditional Formatting to Discover Duplicate Information in Excel

First, in your spreadsheet, choose all rows of your first listing. Then, within the top-left nook, click on the textual content field, sort FirstList, and press Enter. This assigns a novel identify to your vary of cells, which helps you to consult with all these cells utilizing a single identify.

Assign "FirstList" to the first list.

Assign a novel identify to your second listing by first choosing all rows of your listing. Then, within the top-left nook, click on the textual content field, sort SecondList, and press Enter.

Assign "SecondList" to the second list.

Choose all rows of your first listing by clicking the textual content field within the top-left nook and selecting “FirstList.”

Choose "FirstList."

In Excel’s ribbon on the high, click on the “Home” tab and select Conditional Formatting > New Rule.

Select Conditional Formatting > New Rule.

On the “New Formatting Rule” window, you’ll specify how your lacking gadgets shall be highlighted. On this window, from the “Select a Rule Type” part, select “Use a Formula to Determine Which Cells to Format.”

Within the “Format Values Where This Formula is True” field, sort the next:

=COUNTIF(SecondList,A1)=0

Choose the “Format” button and specify the way you’d prefer to format the lacking gadgets in your listing. Then, save your adjustments by clicking “OK.”

Format cells to compare lists in Excel.

Again on the spreadsheet, Excel has highlighted the gadgets in your first listing which are lacking from the second listing. Your job is finished.

Two lists compared in Excel.

And that’s the way you rapidly carry out a comparability of two completely different lists in your Excel spreadsheets. Very helpful!


You may also alphabetize your information to manually discover the variations in two lists in your spreadsheets. Try our information to learn to try this.

RELATED: Easy methods to Alphabetize Information in Microsoft Excel



Supply hyperlink

Leave a Reply

Your email address will not be published.