Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a user who has one spreadsheet with a bunch of data and sends the spreadsheet out to a client who will change some of the values.

The values changed will ONLY be in one row, but we don't know which rows were changed.

So, for a small example say I have one spreadsheet testData, with 5 rows and a column ACCEPT/FAIL and another one testData2 with the same number but I want to see if there is any rows that do not much in the ACCEPT/FAIL column -- I would like to highlight the rows.

I tried using this post for ideas, but I won't know how many rows there are...

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You should be able to use conditional formatting where a formula will provide the criterion.

  • First step is to create a new worksheet in the original workbook, since conditional formatting does not allow criterion formulas to compare values in different workbooks.

  • Use this sheet to bring over the other spreadsheet's data with a simple ='[otherworkbook.xlsx]Sheet1'!A1 reference (no $ signs).

  • Copy this formula down and across to get the rows and columns of data in the other workbook (or limit the the formulas to the specific column or columns you are interested in comparing).

Then, you can set the conditional format:

  • In the original worksheet, select the data range you want to compare, such as the Accept/Fail column), or the whole range if you want the rows in which a difference exists to be highlighted.

  • Select Conditional Formatting on the Home ribbon, and New Rule and Use a formula to determine which cells to format from the boxes that pop up.

  • Enter a formula something like =$F1<>Sheet2!$F1 in the input box labeled "Format values where this formula is true" (assuming F1 is the column you are interested in). The dollar signs shown here anchor the column but not the rows - if you want a cell-by-cell comparison over several columns, drop the $ completely.

  • Set the format to highlight the cells where the values are not equal in the two sheets.

Trickiest part of this is getting the dollar signs right, at least for me it is!

share|improve this answer
    
I'll try that tomorrow morning! –  envinyater Jun 27 '13 at 23:53
    
That is a lot of work if there are 500+ rows to add that formula in for each one...Is there a way to just completely bring it over? –  envinyater Jun 28 '13 at 12:17
    
For bringing the data over, you can enter the formula once in the top left corner of where you want the data to be, copy (control-C key combination or the little copy icon top left corner of home tab at top), select the range you to copy it to, and paste it (control-V or paste button at top left. Or, in the worksheet you want to bring over, highlight data area and copy, then do a special paste in the destination sheet (you can use drop-down menu on paste button to do this). If you'd like I can add steps to do this to answer. –  chuff Jun 28 '13 at 12:28
    
Or enter the formula once, With the cell still highlighted, you should notice a little black square on the lower right corner of the selection. With mouse over the square, click down left mouse button. Still holding left button down, move mouse right to copy formula across columns. Then highlight the row of formulas you'll now have and do the same left clicking and move (drag) mouse down the 500 or so rows. –  chuff Jun 28 '13 at 12:39
    
Ah, that worked out nicely. Now is there any way to highlight the whole row instead of the column? This isn't a big deal -- I'm mainly just curious. Thank you! –  envinyater Jun 28 '13 at 13:00

As a quickie, I'd export both files to CSV and do a quick diff, say use Windiff or WinMerge, on the csv files.

share|improve this answer
    
Just asked them and they want it to be done 100% in excel :( –  envinyater Jun 27 '13 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.