Forum Discussion

Rustyebu's avatar
Rustyebu
Copper Contributor
Jan 28, 2025

Excel keeps pasting the formula below and beside depending on whether I press enter or tab

Like I said in the title, when I try to calculate a formula by pressing enter or tab, excel applies the formula to the two rows below or beside the anchor cell. I don't know why this is happening, when it started, or how I can stop it.

When I press enter, for example, the two cells below contain a greyed out version of the formula that cannot be deleted. Same when I press tab, the two cells to the right contain a greyed out, un-deletable version of the formula.

 

  • Rustyebu's avatar
    Rustyebu
    Copper Contributor

    Hi, thank you both for answering. I've looked into both solutions and I will go with CTRL+Enter for now since it suits what I need. I appreciate the help! :)

  • Your formula

    =AT58:AT60/O7

    takes the range AT58:AT60 (3 cells) and returns result into the range of another 3 cells as one object (spill). Cells within that object are not editable, you may edit only formula in the first cell which will be applied to entire spill.

    To make editable in each cell formula use =AT58/O7 and drag it down.

    I assume you are on Excel 365 or Excel 2021 and later.

  • insightsgeek's avatar
    insightsgeek
    Brass Contributor

    RustyebuI have a few solutions in mind for this issue.

    What you’re describing sounds like Excel’s AutoFill or Array Formula behaviour, likely caused by Implicit Column or Row Expansion due to Spill Formula Behaviour in newer versions of Excel (Excel 365 or Excel 2019+). Excel automatically tries to "spill" the results into adjacent blank cells. This usually happens with formulas that return multiple values like your "AT58:AT60/O7".

    1) Try to hit Ctrl+Enter to force the formula to stay in the selected cell.
    2) Add @ before the formula to force Excel to consider it as a scalar value.
    3) Check if It’s a Table Issue and disable auto-fill: File > Options > Formulas

     

Resources