Forum Discussion

TrueNorth's avatar
TrueNorth
Occasional Reader
Jun 05, 2026

Excell function problem

I have been using Excel for a good few years, i have a spreadsheet that has been working fine until now. 

My main problem is that when i copy & paste data everything works fine, i have been able to add cells to make a new total.

but when i cut & paste the value of cells i get a REF error.

so when i need to move a series of cells higher or lower any cells that contain word they move fine, the cells containing numbers move but the cell with the function just goes to #REF, the function was working okay with my previous versions of Excel but now it will not.

if anyone has got any idea what the problem is or any way of fixing it i would be most gratefull   

excel

1 Reply

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 05, 2026

    It sounds like the problem you are having is how Excel handles excel formulas when you copy vs when you cut&paste  or move.  Since you didn't include more information like the formulas and the data structure and a copy of the sheet I will have to just use an example to explain the concept.

    So first, excel will do its best to guess how you want to modify your formula.  You have some control over this also.  If you move (or cut & paste) it will not change the references in the formula.  If you copy (or use fill) it will try to increment the references to a corresponding relative location.  So take the following simple example:

    in cells A1:A3 I just have the number 1,2,3 and in cell B1 I have the formula =A1+A2

    I then copied the formula down to B2 and B3 and those formulas became =A2+A3 and =A3+A4 respectively

    but if I highlight A3:B3 and move it down 1 row I get:

    this is because the formula referenced A3 & A4 and A3 moved to A4 so it updated that to A4 but what was A4 no longer exists as it was overwritten.