Forum Discussion
Is now the time to abandon the concept of relative referencing?
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!
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.
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!
"a course specifically geared towards Excel 365"
Have you identified such a course? In some ways that is what this discussion was intended to cover. It is always possible to bolt Dynamic Arrays, LET and LAMBDA on to an existing course as 'new', 'advanced' topics. Then one might need to debate whether Lambda should wait until Power Query and Pivot Tables have been covered.
What I would argue for is that DA, LET and LAMBDA should not be left to the final steps of training as advanced topics but, rather, treated up front as the basis of modern Excel; it is traditional spreadsheet techniques that should be left to the end for those who need to address issues of backward compatibility.
I'm mostly an auto-didactic when it comes to Excel. I learn through creating challenges for myself and have learned from participating in this community.
I haven't actively searched for decent Excel 365 courses online so it's difficult to recommend one. I've seen some videos from some of the popular instructors and there's usually something that puts me off about their methods. Mostly it's the lack of LET being taught. Someone had linked a VSTACK video on this forum a few weeks ago. The instructor was stepping viewers through a 3D stack. The finished formula contained explicit references repeated a few times and no use of LET. To top it off, he was charging a few dollars for the template with the formula!What I would argue for is that DA, LET and LAMBDA should not be left to the final steps of training as advanced topics but, rather, treated up front as the basis of modern Excel; it is traditional spreadsheet techniques that should be left to the end for those who need to address issues of backward compatibility.
I agree with DA and I've come around on LET. In the 40 hour Excel 365 introduction course I teach within my organization I debated including LET and had left it out the last time I ran the course. Next time I teach it I'm going to include it.
