Forum Discussion
Is now the time to abandon the concept of relative referencing?
The default action when one clicks a cell whilst inputting a formula is for Excel to return a relative reference. That allows a user of legacy Excel to address the members of a list or array by dragging the formula down or across the sheet for such distance as they determine appropriate using their judgement (a prolific source of error?).
Since Excel 365 is founded on the concept of arrays as fundamental computational objects, wouldn't it be more logical to expect the developer to select the array they wish to process rather than thinking about single elements. I find it difficult to persuade users that dragging a formula is a bad idea; it is deeply embedded in muscle memory and changing the habits of a lifetime is hard to do.
I propose that the default action for 365 should be to generate an absolute reference (and the user then encouraged to apply a defined name to the array they have selected to describe its business relevance?)
What are your thoughts?
21 Replies
- SergeiBaklanDiamond Contributor
I fully agree with Patrick2788 , any change in default behaviour which was for ages shall be done by option only. By option in user interface available for average user.
At the same time I won't be surprised if such option exists in registry.
- Patrick2788Silver Contributor
I think this is a good idea for an option that could be enabled in Options | Formulas. I approach a given sheet with the mindset of how can I get it done in 1 move with a dynamic array? I'm totally on board with Excel 365 as an array-based powerhouse capable of so much more than the scalar-based legacy versions of the past. It's fun to use Excel to solve linear programming puzzles! There's a certain art to authoring elegant solutions in Excel that has really come on in the last couple years that I enjoy.
I think there's still a certain tendency to underestimate what Excel 365 is capable of and make a bee line for vba solutions. LET/LAMBDA fundamentally changed how formulas can be authored but it's very much a takes one to know one club at present. One doesn't often stumble into LET/LAMBDA by accident. I don't mean to be a gatekeeper but it's usually something someone takes up after years of study. For this reason, I think the proposed change could be added as an option to be enabled but defaulting it might cause a stir!
- Craig HatmakerIron Contributor
Patrick2788
"defaulting it might cause a stir"
Please take this in the spirit of humor. Your point is very valid, but a bit ironic given the topic and how enabling dynamic arrays required a major change to Excel's calculation engine default behavior.- Patrick2788Silver Contributor
Thank you for taking the time to read my reply and respond.
I do believe the introduction of dynamic arrays marked a turning point in the history of Excel - a major change in the calculation engine for the better. I can only speak for myself. Colleagues of mine that have used Excel since the 80s and 90 have not completely bought into Excel-based-on-arrays for lack of time to learn new skills, being content in one's current skill set, not being interested, etc. - whatever the reason may be. I still hear of columns being re-arranged to accommodate the limits of a base VLOOKUP, Advanced Filter being used instead of UNIQUE, helper columns used instead of LET, and plenty of other examples. The awareness of functions like FILTER, XMATCH, LET, CHOOSECOLS, CHOOSEROWS, TAKE, DROP is close to 0.
Clients are usually very open to dynamic arrays. Lambdas are usually received very well. Though I still have to talk people off the VBA ledge. There's a certain mindset that if someone doesn't know how to do it in the application that VBA must be the answer. I think anyone beginning their studies of Excel in 2024 would be wise to find a course specifically geared towards Excel 365 and stay away from macros!
- PeterBartholomew1Silver Contributor
"defaulting it might cause a stir"
Yes, but that is at the core of the argument. Users will follow the path that is most easily discovered. For me, that path leads firmly in the wrong direction, enticing users to follow. The scalar operations do lead to a very direct sense of the user manually interacting with their numbers. That suits many users, but they should receive some warning/indication that they are opting for legacy methods that deliver little in the context of modern Excel.
They will come to think of being limited to scalar arithmetic as representing 'simplicity' yet, in reality, the inherent complexity of the problem has simply moved to the task of wading through a sea of semantically limited cell formulas trying to divine the path of the overall calculation. Such users may seek more knowledge from the 'tips and tricks' genre of multiply plagiarised content, perhaps thinking that memorising over 350 shortcuts will in some mysterious way add wisdom to the proceedings.
With dynamic arrays, I look for 'simplicity' not in the individual scalar operations but in the overall patterns of array calculation. Operations like sorting, filtering, wrapping, grouping emerge along with the concept of arrays; they are not meaningful at the level of scalar thinking. Many problems, such as that of banded bonuses given to salesmen or taxation bands (attached) have array solutions that follow a different path from that adopted by fill-down formulas.
This process of abstraction, in which the details of a calculation are left to Excel, is a key element in the fight to control the error rates that characterise spreadsheet solutions and give the spreadsheet such a poor reputation. Overall, I would not suggest attempting to force users to adopt modern methods (that would simply drive many casual users away) but neither do I want to signpost a default route that ultimately leads to a dead end.
- djclementsBronze Contributor
PeterBartholomew1 My two cents...
I'm more of the attitude that if the situation calls for it, use it. If not, just keep it simple. This is especially true when helping out beginners. If I were to hazard a guess, I would say that the "average" user falls into this category. Intermediate users would have a decent chance at adapting, but people who are still using =SUM(A2+A3) formulas are not very likely to grasp major concept changes. I still feel the "default" behavior should be the path of least resistance, so no one gets left behind.
On the topic of defining names, I say each to their own. For me, less is more (in general). I find spreadsheets that were created with excessive, unnecessary named ranges to be harder to modify, even if I was the one who built it. Too much time is wasted jumping back and forth between the worksheet formulas and Name Manager trying to reverse engineer everything. I would much rather see the cell references directly in the formula, so I know exactly what they're referring to. Having said that, there are plenty of situations where named ranges are essential, and the majority of my spreadsheets have at least a few.
Further to that, when it comes to defining names within the LET function, I'm all for defining each variable to improve readability, especially when first introducing others to LET. However, it's easy to get carried away here too. One thing I'll probably never get on board with is defining the LAMBDA function first for BYROW, BYCOL, MAP, REDUCE, SCAN, etc... it's like putting the cart before the horse, and actually reduces readability in my opinion, as you end up having to backtrack to figure out what's happening, rather than just reading each step in logical order.
I often find myself suggesting solutions on this forum that I would never use personally, simply because I would never setup my spreadsheets in a manner that calls for them. Rather than trying to explain the pitfalls of certain design choices, I just end up answering the question directly, even if it goes against my own preferred methods. The beauty of Excel is that there are many ways to get the job done. Some methods are easier to understand, while others can be more efficient. In many cases, though, it boils down to personal preference.
By the way, the custom TaxCalcλ function you shared in the TaxBands file is awesome. It's so simple and yet beyond the comprehension of any normal person (looks like Thunks was used?). You sir, are in a league of your own. 🙂