Function Problems

How to Compare Two Excel Lists to Find Added and Removed Items

Partner Editor Oct 10, 2026 869 views

Question details

The user needs to compare two inventory lists in Excel to identify which items have been added to the new list and which items have been removed from the old list.

How to Compare Two Excel Lists to Find Added and Removed Items

Product: Excel
Device & OS: not provided
Scenario: Comparing an old inventory list with a current list to accurately track inventory changes and updates.
Observed behavior: The user wants to clearly extract or highlight the unique items that exist in only one of the two lists.

Before you start

Ensure both the old and new lists are cleaned of any leading or trailing spaces, as invisible extra spaces can cause exact match formulas to fail.

Solution 1Recommended

Use the COUNTIF Formula to Find Additions and Removals

Using the COUNTIF combined with the IF function is the most precise way to extract exactly which items were added or removed between two columns.

This formula checks if an item from one list exists in the other. If the count is zero, the item is unique to the current list and gets extracted.

Identify Added Items

In a blank column next to your new list, enter =IF(COUNTIF($A$2:$A$100,B2)=0,B2,"") (assuming the old list is in A2:A100 and the new list is in B2:B100).

Apply Formula Downwards

Press Enter and drag the fill handle down to apply the formula. Newly added items will appear in this column, while existing items will remain blank.

Identify Removed Items

To find removed items, enter =IF(COUNTIF($B$2:$B$100,A2)=0,A2,"") in a blank column next to your old list to see which original items are missing from the new list.

Filter Results

Select the result columns, go to the Data tab, and click Filter. Uncheck the 'Blanks' option to display only the newly added or removed items.

Use the COUNTIF Formula to Find Additions and Removals

Absolute References: Remember to use absolute references (the $ signs) for the lookup range so that the range does not shift when you drag the formula down.

Solution 2

Highlight Unique Items Using Conditional Formatting

If you only need a quick visual cue to identify the differences between the two lists, conditional formatting is the fastest method.

Solution 3

Compare Tables Using Power Query

For large datasets or recurring comparison tasks, Power Query offers a robust way to merge and compare tables without relying on formulas.

Efficient Data Comparison Tool

Compare Lists Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like COUNTIF, VLOOKUP, and conditional formatting, making it incredibly easy to compare two lists and track inventory changes with familiar workflows.

1. Open Your Lists: Open the spreadsheet containing your old and new inventory lists in WPS Office.
2. Apply COUNTIF Formula: Use the =IF(COUNTIF(...)) formula in an adjacent blank column to automatically flag additions and removals.
3. Use Conditional Formatting: Alternatively, select both columns and use the 'Conditional Formatting' tool under the Home tab to highlight unique items visually.
4. Filter the Data: Use the Data Filter feature to sort and extract the highlighted or flagged items effortlessly.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats and formulas.Lightweight and runs smoothly even with large data sets.Built-in conditional formatting and data filters to quickly spot unique or duplicate items.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the COUNTIF formula sometimes fail to recognize identical text?

This usually happens due to hidden trailing spaces or non-breaking spaces in one of the lists. You can use the TRIM function to clean the data before comparing, e.g., using =TRIM(A2) to remove extra spaces.

Can I use VLOOKUP instead of COUNTIF to compare two lists?

Yes, you can use VLOOKUP to search for items in the other list. If the VLOOKUP formula returns an #N/A error, it means the item is missing from the list you are searching against, effectively identifying a removed or added item.

Is there a way to compare lists across different worksheets?

Yes. When writing your COUNTIF or VLOOKUP formula, simply click on the other worksheet tab and select the data range there. The spreadsheet software will automatically add the sheet name reference to your formula (e.g., Sheet2!$A$2:$A$100).

Excel & Data Problems