Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Sometimes I visually compare two datasets by pasting them side-by-side in Excel with a formula in the middle like =A1=C1. This will return true or false based on whether the cells are an exact match. When the datasets are missing a record, I can get them to line up again by simply inserting a new cell and shifting the existing ones down. However, when I do this it updates the formulas to shift the referring cell down by the amount of cells I inserted, like this:

Excel Question

This causes problems because I have to redo all the formulas down the middle each time this happens. On a long dataset, this gets very tedious. Is there any way to "lock" the formulas in the comparison columns so it doesn't care if I insert or delete cells, they will always stay pointing to the original cells I set up?

BTW, I know that $A$1 will lock a reference, but that only works if you're moving that cell around, not moving the referenced cell. If you put that in at the top and drag them down, they will all stay $A$1, no matter what row you drag to (plus, it still does the update after inserting a cell in the reference column).

share|improve this question
I don't have time to try it now, but could you use a combination of OFFSET and ROW to match the values in the formula's row, regardless of where cells are added? – dav Jul 24 '12 at 20:42
up vote 2 down vote accepted

If I understand your question, I think this may work:


Where B1 is an arbitrary anchor for your formulas and you always calculate the offset to the cell on either side of your comparison.

share|improve this answer
From your original comment I was not sure what to do with ROW, but I actually got it to work with OFFSET alone, like this: =OFFSET(B1,0,-1)=OFFSET(B1,0,1). I'm still curious if there is a way to lock the formulas, but if no one has posted anything to that effect in a few days I'll accept this answer. Thanks! – techturtle Jul 24 '12 at 21:04
Thanks, your formula is actually a bit more elegant than mine. The ROW() should "lock" the formula to the current row, but its really unnecessary as your modification shows. As for "locking" the formulas, what are you trying to accomplish-I'm not sure I understand completely. – dav Jul 25 '12 at 11:57
Basically I would like to be able to continue using =A1=C1 (easier to setup and read) and drag that down, then select the column and say "Lock" so it won't update the formula/cells anymore. I thought there might have been a setting in options I could turn on temporarily, something like "don't update formulas on move", but I haven't found anything that works. – techturtle Jul 25 '12 at 15:10
Ok, I think I understand better. I've never heard of something like that, although it seems like VBA should be capable of it (but I'm not very good with VBA). To improve readability (but short of locking like you've mentioned) you could probably use named ranges. I'll see if I can get something to work... – dav Jul 25 '12 at 19:52

I put one set of data in column A and the other in column B. Then I pasted the formula below in each cell in column C. The ROW() function returns the current row number, so you can insert/delete data in column A or B without having to paste and repaste your formulas. You can paste the formula in any column and it will still work – comparing column A and B in the row it is in.

=IF(INDIRECT(ADDRESS(ROW(),1)) = INDIRECT(ADDRESS(ROW(),2)), "ok", "***diff***")

I do agree – I wish there was some way to tell Excel not to change formulas when you insert and delete data in a column.

share|improve this answer
Welcome to Super User.  Your answer didn’t quite make sense, so I “fixed” it.  Please edit it back if you disagree with my changes.  Good luck! – G-Man Jun 8 '15 at 4:42

Instead of inserting cells, you could copy the cells you want to shift down, and use paste-special-values to put them where you want them. In other words, moving the values instead of the cells will prevent the formulas from adjusting.

share|improve this answer

Your Answer


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.