Excel automatically filling in formulae

[Laurence_Steinpress]

Hi,

I am using Office 2021 on a new 2023 iMac running Sonoma (OS 14.1.2), having just transferred from using Office 2011 on a 2012 iMac.  Office seems to be working OK on the Mac except for a very important Excel function.

I don't know what it is called (I suspect that is a "non-documented feature", at least on Office 2011!), it causes automatic fill-down of formulae in cells which I use when plotting on scatter charts so that blank cells do not appear as zeroes, but get filled in automatically when data are entered in adjacent columns.  Other people have raised similar issues but none seem to be exactly the same thing.

I have gone over the settings, e.g.:Edit "Allow fill handle and cell dragging and dropping" and "Extend data range formats and formulas" ticked
All AutoComplete buttons ticked
All Tables and Filters boxes ticked (except the one for pivot tables)
Calculation set to automatic
I have tried "flash fill" but might not have used it correctly!

[#2]

Hi,

No one has replied to this, maybe I haven't made myself clear, so here is a procedure to reproduce what I'm on about:

1. Open a new spreadsheet
2. In cells A1 to A5 enter some numbers (any values will do)
3. In cell B2 enter =A1+A2
4. Drag the contents of B2 to fill down through to B5 using the cross handle on the lower-right corner of B2
5. Enter a number in cell A6

In Office 2011 for Mac, the formula =A5+A6 appears in cell B6, and it displays the result
In Office 2021 for Mac, cell B6 remains blank.

Note that the "auto-fill" doesn't work with fewer than 5 cells (e.g. A1 to A4).
I can't believe that such a useful facility has been discontinued - can anyone else reproduce this effect?