Neddy Bate
Valued Senior Member
I was creating a new spreadsheet when I noticed a really strange behavior in Excel. I have used Excel for decades and never noticed anything like this before. I would love to hear any explanation for any logic that might be behind this.
Follow the steps below to replicate the strangeness for yourself.
Open a brand new spreadsheet and type a simple formula into cell A1 like this:
=1+2
Next, copy that cell down to the 3 cells directly below it, so now the four cells A1, A2, A3 and A4 are like this:
=1+2
=1+2
=1+2
=1+2
Just to the right of the first cell, type the following formula into cell B1:
=A1
Next copy that cell down to the 3 cells directly below it, just as before, so now the formulas in those four cells B1, B2, B3 and B4 are like this:
=A1
=A2
=A3
=A4
And now, to see the strange behavior of Excel, type the following into the cell A5:
=B1
Boom! You should now see that Excel has taken it upon itself to fill in cell B5 with a formula! Why?!?!? I was pulling my hair out thinking I must have had a corrupted spreadsheet, until I finally isolated the cause.
This does not happen if you use less than four rows. It also does not happen if you type in the 1+2 formula manually in each cell, you have to copy them down. I have never seen Excel do anything like this before. Can anyone explain the reasoning here?
Follow the steps below to replicate the strangeness for yourself.
Open a brand new spreadsheet and type a simple formula into cell A1 like this:
=1+2
Next, copy that cell down to the 3 cells directly below it, so now the four cells A1, A2, A3 and A4 are like this:
=1+2
=1+2
=1+2
=1+2
Just to the right of the first cell, type the following formula into cell B1:
=A1
Next copy that cell down to the 3 cells directly below it, just as before, so now the formulas in those four cells B1, B2, B3 and B4 are like this:
=A1
=A2
=A3
=A4
And now, to see the strange behavior of Excel, type the following into the cell A5:
=B1
Boom! You should now see that Excel has taken it upon itself to fill in cell B5 with a formula! Why?!?!? I was pulling my hair out thinking I must have had a corrupted spreadsheet, until I finally isolated the cause.
This does not happen if you use less than four rows. It also does not happen if you type in the 1+2 formula manually in each cell, you have to copy them down. I have never seen Excel do anything like this before. Can anyone explain the reasoning here?
Last edited: