Excel/EXCEL 2010 / 2003


I have 2 spreadsheets, each with 4 columns.  Both spreadsheets are supposed to represent the exact same information.  One is maintained by me (Mydata.XLS)and the 2nd by a user (Userdata.xls)out in the office.  I want to compare the users information to my own, and update my sheet to reflect the users out in the office.  My data is from a database and her's is from an Excel spreadsheet she maintains.  Bottom line is my database may not have been updated as it should have and I want to correct that.  Each spreadsheet has 4 columns A - Cube Number, B - MAC Address, C - Phone Number, D - Computer Name.

The comparrison should be based on the cube number (Column A).

I would like to hilight on my spreadsheet (Mydata.xls), where my data is different from her data in ANY of the 3 additional columns, based on the Cube Number in Column A.  If a cube number exists in her sheet and not in mine, I would like that hilighted on her sheet (Userdata.xls).

How difficult is that?

Thanks as always for your help.



I would see this as a conditional formatting requirement.  

You would need to set up conditional formatting to text whether the value in one sheet differs from a value in the other sheet based on match cube number.

you can use


so to compare that to the value in B2  (the 2 as the third argument in vlookup says to return the value from the second column in the range A:D for the row that was found to match in column A.  A three would return the value from the third column and so forth)

So for the conditional formula in the conditional formatting you would use the value returned
by that formula to test against the value in B2 (the cell being conditionally formatted)


That formula would return True if the two values are equal and false if not.  
This assumes both workbooks are open in the same instance of Excel.

Hopefully that example will provide you the information you need to implement a solution.  

For the Userdata worksheet, you would use the same type of formula linking back to the mydata.xls worksheet.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.