Forum Discussion
Is now the time to abandon the concept of relative referencing?
"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.
To get the average user to think in terms of arrays instead of scalars, the application must be consistent across features like those mentioned above. I've taught Excel within my organization for nearly 15 years. My students are newer employees who often don't have any previous knowledge of the application. The good thing is they start out learning Excel 365, how to spill, how to think in terms of arrays, etc. from the start. The challenge for me is getting them to understand spilling yet pumping the brakes for something like conditional formatting with a formula, for example.
Excel has come a long way since 2018.
Sadly, conditional formatting is full of gimmicks (admittedly, some rather neat) but the basics are antiquated. All I need is to be able to do is apply formatting based upon an array formula to its spill range, given a user-specified anchor cell. The formula should be capable of including range intersection operators. In other words, all I require is what is missing!
It is my understanding, though, that the CF code base is so old that it would require a massive effort to bring it into the 21st century.