autocopy of formulas

[Threazy]

I have noticed that in some spreadsheets Excel will automatically copy formulas (and formatting) from the previous row. This is a great feature, but doesn't behave consistently. Does anyone know the rules or conditions under which Excel does this?

 

[shg]

From https://support.office.com/en-us/article/Advanced-options-33244b32-fe79-4579-91a6-48b3be0377c4

Extend data range formats and formulas Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.

 

[Threazy]

Thanks, shg, but there's more it is than this, which is why I asked about rules/conditions.

It works fine if I'm entering values immediately to the left of formulas to be copied. If, however, the formulas are to the left of the data I'm entering, or there are blank cells between the entered data and the formula to be copied, then Excel does not do it - it seems to simply assume "extend formulas immediately to the right" and doesn't consider the entire row.

Maybe I'm answering my own question here, but I'd like to confirm my understanding of how Excel "thinks about" the range extension.

Bill

 

[FDibbins]

Are you using a Structured Table here or just a regular range?

 

[Threazy]

FDibbins: Just a regular range. (my experiences with structured tables haven't been positive.)