Can only write formula in first cell of column

ebf6543

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Hello,

I recently updated Excel and am running into an error that I don't know how to fix.

I'm organizing data into columns and I'm only able to write formulas in the top cell of the column. When I try to drag the formula down as I normally would, I get a #SPILL! error.

When I mouse over the cells below the top cell in the column, the formula shows up in the formula box at the top (greyed out), but when I try to click it to edit it, the cell is actually empty. I thought this might be a quirky autofill setting, but I turned that off in my settings and this is still happening.

The formula is calculating mostly fine even though it's 'ghost' autofilled, but there are a couple of errors in random cells that I'd like to audit, but cannot because (as I said) Excel shows that the cell is empty, so I can't investigate that cell specifically.

What is going on here? How do I fix this? I just want it back the way it used to be, where I could click and drag formulas.

Thank you!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Don't panic. This is just the new dynamic array formula functionality of Excel 365, which with new functions like SORT, UNIQUE and FILTER, opens up all sorts of possibilities. You'll also no longer have to worry about array entering formulae - there's no need in Excel 365.

To illustrate dynamic arrays, in a clean worksheet enter something in cells A1:B2. Now in D1, enter the formula = A1:B2. Older versions of Excel would return the 2x2 array, but only display the value of A1 (the top left hand cell). Now you see the whole array spill into adjacent cells. But if any of these are occupied, you'll see a #SPILL error.

Normally we use formulae that collapse arrays into a single value, e.g. by summing, or averaging. And this will work in exactly the same way in Excel 365 (without the need to array enter from time to time).

What formula are you using that causing the #SPILL! problem. Can you post a screen shot, preferably using the XL2BB add-in: XL2BB - Excel Range to BBCode
 

ebf6543

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Thanks for the quick reply! I'm not sure how to use that add-in (my apologies!) but this is the formula:

=IF(Errors!$C$2:$C$300="CP",IF(Errors!$F$2:$F$300=B$1,Errors!$G$2:$G$300," ")," ")

I'm basically categorizing errors that are noted in aggregate on another tab. If I try to drag this formula down the column, as previously stated, I get a #SPILL! error. This is annoying, but fine, and, as you said, the dynamic array function is useful.

The particular thing that I'm struggling with right now is - curiously, two random cells in my error categorization table are returning #VALUE errors. There is nothing special about the data that those cells are referencing compared to the other cells that are returning fine, and I'd really like to be able to audit the formulas for those cells individually to source the problem, but because of 365, I cannot. Any suggestions?
 

ebf6543

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Nevermind - error solved! Was a simple misunderstanding with the Match function (who knew it can only lookup 255 characters?).

#SPILL errors are still pretty confusing to me, though, so any general advice you have there would be appreciated too!
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
I suggest you Google excel dynamic arrays - there's a wealth of good material out there. And if you don't mind spending US$3, I can recommend this eBook (about 80 pages): Guide to Excel Dynamic Arrays by MrExcel

Your formula in Post #3 uses arrays, and did so even before your 365 upgrade. If the formula was working correctly, then presumably it was entered in rows 2-300. Although you didn't realise it, it relied on a concept called Implicit Intersection.

In Excel 365, you can enter the formula once, as written. Or you can enter cell by cell as =IF(Errors!$C2="CP",IF(Errors!$F2=B$1,Errors!$G2," ")," "), copied down the column.

The latter will make it slightly easier to track the #VALUE errors. But it's not that much harder with the single array formula - obviously a #VALUE error in the Nth result will come from row N+1 in the Errors sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,840
Messages
5,627,186
Members
416,228
Latest member
JOOTEISHERE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top