TIL Excel version

[meep]

@BruteForce

Do we have a thread for existing Excel features we just found out about? I learned about Xlookup from this thread, so I figured I’d post about something cool I just realized: When you have some cells highlighted and a Sum is showing at the bottom right of the screen, you can left click on that and it copies it to your clipboard. Works for all of the numerical values shown (you can add/remove what’s shown by right clicking there). I used to have a personal macro to try and do that, but it only worked sometimes. I was super excited when I realized what I had accidentally discovered.

This deserves its own thread

[meep]

I still haven’t tried out LAMBDA or LET yet.

Should I?

[BruteForce]

Thanks for creating this. Another fun trick I learned not too long ago is that the prompt that pops up when you start typing a formula can actually be moved around. I used to struggle to click on column letters if that prompt was covering them. Now I can just move it out of the way!

[examsarehard]

Excel is finally adding an option to disable the automatic conversion of data, e.g. “00123” → 123. It is also going to warn the user when data has been automatically converted when reading a csv.

There are plans to disable the automatic conversion of some strings into dates(e.g. “12/12” → Dec-12) but it is not in the upcoming update.

Control data conversions in Excel

[meep]

Took them long enough.

[HedgePig]

@meep

I still haven’t tried out LAMBDA or LET yet.

Should I?

I like the LET function and it’s easy to use. It’s useful for not repeating a calculation within a formula but also simply for making formula more readable. i.e. you can separately calculate components of a formula, name them and put them together in the final combined formula - which is a lot easier to understand since you have named the components.

I’ve played around a little with LAMBDA and so far have mixed feelings.

1. Seems like a replacement for user defined spreadsheet function that you previously might have had to implement in a VBA function
2. There are some powerful examples that I’ve seen, e.g. cleaning up input by searching for an entire list of characters to be replaced. Furthermore you can iterate through an entire input list and spit out a cleaned up list with a single formula (and not copied down for each entry in the input list)
   However, there are some limits on the length of input list that can be handled recursively and these limits are not “obvious” in advance. As I understand, its limited to the size of the stack that is used for recursive functions and the amount that it put onto the stack with each recursion depends on the number of arguments passed.
3. However, I also strongly suspect that having more than one or two LAMBDA’s in a spreadsheet could lead to a debugging nightmare.
4. The editor for LAMBDA functions is the little box where you enter names for named ranges and that is less than ideal. Apparently there is an add-in available for better editing but I’m fighting with our IT to allow me to install it.
   Overall, my impressions are that LAMBDAs could be very useful in certain circumstances but those are probably a bit more limited than I had initially hoped. But I might well change my mind with more exposure.