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 drag...
I think if a setting like this became the new default there would undoubtedly have to be other advancements within Excel. Conditional Formatting and Data Validation still think in terms of scalars and the formula bar is long overdue for an upgrade.
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.
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.
