Fill Formula function not working when using R1C1 reference style for columns

[KayB1595]

I've discovered that the formula auto fill function does not work when my workbook is using the R1C1 reference style, with numbers instead of letters in the columns.  It just copies the exact same formula into the next cell without updating the formula to apply to the next row/cell.  This simply copies the exact same value into all the the cells.  Switching back to labelling my columns with letters resolves the issue.

I don't like using numbered columns, and generally keep all my workbooks in this format, but curious why this would be happening.

I have confirmed that Calculation in Formulas is set to Automatic.

[#2]

this is probably because R1C1 reference is the absolute version and R[1]C[1] is the relative notation and even then the formula won't change since that relative aspect is built in so R[1]C[-1] means the row below and column to the left of that cell and it doesn't need to change as you copy down.

[#3]

Wow...so translation is "be glad you can just keep using letters" 🙂

[#4]

actually RC format may feel weird but can be very useful. It is just a matter of getting used to it.
another useful format is structured references for referring to tables (e.g. TABLE1[NAME]). It is also a little different and takes some getting used to but also very useful.

[#5]

I struggle to see how the R1C1 reference is useful for the average user. It is more complicated, involves typing in longer formulas and renders the formula fill-in non-functional. In what circumstances would the R1C1 reference be preferred or useful? Genuinely curious.

[#6]

TBH the main place is when using VBA (macros) and you can fill in a whole line of cells with the same formula and not need excel to autoincrement. Using A1 style you might have a running sum starting in cell B2 as =A2+B1 and then as you fill down you expect and need Excel to increment
but using RC style you have =RC[-1] + R[-1]C and that is the exact same formula entry down the whole column. Note also that in this format you know this formula will add the cell to the left to the cell above. The =A2+B1 you have to also know that you are looking at cell B2 to know that. Again, it isn't necessarily that one is better than the other, they are just different and each has their advantages and disadvantages.