Forum Discussion
Rustyebu
Jan 28, 2025Copper Contributor
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.
- RustyebuCopper 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.
- insightsgeekBrass 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