Excel: compare items in different worksheets


Excel 2016

I have a spread sheet with 4 worksheets.


worksheet4 has a long list of items, most of which are also in either 1,2 or 3.

How can I highlight the items in worksheet4 which do not appear in either 1,2 or 3?

Gareth McKeeCEO/OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I would suggest that you use Conditional Formatting with a Named Range and a Formula

I'll post some instructions if required
Roy CoxGroup Finance ManagerCommented:
1. Enter the names of the sheets to compare in the master sheet or an empty sheet. Then create a Named Range to refer to that Range. I have used "Sheets"

  1. Select the cell(s) to be named
  2. Click in the Name box, to the left of the formula bar
  3. Type a valid one word name for the list, e.g. Sheets.
  4. Press the Enter key.

2. This is the formula for the Conditional formatting =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!$A$1:$A$18"), A1))

The Named Range "Sheets" refers to the sheets to check
$A$1:$A$18 refers to the range to check, note the $ signs make the range Absolute, i.e. a Fixed Range.
A1 is the cell to compare, because it is not Absolute the formula will check A1, B1, etc for each respective Row

3. Apply the Conditional Formatting Rule

To create conditional formatting rules with a formula
  1. Select the cells to Format
  2. Then from the Home Tab -> Conditional Formatting -> New Rule.
  3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
  4. Under Format values where this formula is true, type the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!$A$1:$A$18"), A1))>0
  5. Click Format.
  6. In the Color box, select a Fill Colour. In the Font Style box, select Bold.
  7. Click OK until the dialog boxes are closed.

Adjust the Ranges and Sheet Names for your own WorkBook.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gareth McKeeCEO/OwnerAuthor Commented:
Thanks Roy, I will give this a go.
Gareth McKeeCEO/OwnerAuthor Commented:
Thanks Roy
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.