This is not a judgment on you, but I've seen this echoed in a couple places in this thread and yours is a top-level comment that starts with it.
I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.
Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.
On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.
I thought it was instructive to see that in A1 mode the cell formula magically changed as it was copied, while in R1C1 mode it did not; it was good circumstantial evidence that R1C1 is what Excel uses internally. But then I tried an experiment, deleting a column that was referenced by a formula - the formula was replaced with =#REF! indicating that there's still magical changes being applied.
I should probably have phrased what I said a bit differently. I have not had trouble understanding relative offset and how to use it. What I've always wondered about and finally got an answer to in this video is how the implementation works so that Excel is able to preserve the offsets even when pasting a cell in a random location into a spreadsheet :)
Ah gotcha. Yeah, for that I just had an implicit mental model of R1C1 without realizing it was physically implemented.
I interpreted you (wrongly it seems) as saying that the relative offsets were confusing and I responded to that. You were curious about the implementation instead.
I didn't care about the implementation when I was learning Excel, and simply assumed a mechanism like R1C1 without much interest in it.
I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.
Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.
On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.