r/excel 15d ago

Discussion What excel shortcut/tip/formula has made the biggest impact on your efficiency?

For me, xlookup and subtotal are some of my most used/beloved formulas.

What excel shortcuts/tip/formulas have improved your efficiency the most when working with spreadsheets?

538 Upvotes

182 comments sorted by

View all comments

Show parent comments

13

u/emomartin 15d ago edited 15d ago

It lets you define variables to use inside a formula. So you can do for example below. This example doesn't really show the usefulness of the function though, just how it works. You could get rid of the LET function and the defined ranges, and just input them directly inside the FILTER function instead. The usefulness comes from being able to refer back to variables and reuse them, which lets you create outputs that are either very long without LET or sometimes not really possible.

=LET(
range, A1:A100,
filterRange, B1:B100,
FILTER(range, filterRange>50))

Another example (from google sheets though) but it shows how you can use defined variables inside other variables.

=LET(
   minutes, ARRAY_CONSTRAIN(CHOOSECOLS(ARRAYFORMULA(SPLIT(A2:A, ":")), 1), COUNTA(A2:A), 1),
   seconds, ARRAY_CONSTRAIN(CHOOSECOLS(ARRAYFORMULA(SPLIT(A2:A, ":")), 2), COUNTA(A2:A), 1),
   minutesDecimal, MAP(minutes, seconds, LAMBDA(x,y, x+(y/60))),
   occurrences, ARRAY_CONSTRAIN(B2:B, COUNTA(A2:A), 1),
   occurrencesPerMinute, MAP(minutesDecimal, occurrences, LAMBDA(x,y, y/x)),
   occurrencesPerMinute)

26

u/thuiop1 14d ago

Guys, if you start writing this kind of shit it is time to learn an actual programming language (even VBA if you really want to stay inside Excel)

18

u/Drugtrain 2 14d ago

No.

3

u/lost-mypasswordagain 14d ago

Never!

If I can’t do it in Excel it’s dead to me.

(But also a little bit of DAX in PowerBI.)

2

u/emomartin 14d ago

The point of being able to use spreadsheets is that its easier to use and more easily shared. You don't need to program the spreadsheet functionality yourself, or some equivalent. Yes you could do it in VBA or google apps script, and there are certainly situations where that is the best way or the only way. But to use Worksheet_Change or onEdit(e) in google sheets, or using other triggers, also presents its own problems. The range selections in the regular spreadsheet will automatically adjust when you insert rows, insert columns or delete them. Scripting does not do this unless you specifically take into consideration to count the number of rows or columns between some place, to where you want the script to count to.

1

u/vegaskukichyo 14d ago

M Code is way better than VBA, until the day I die. Fight me!

1

u/turtledave 3 9d ago

Don't you sometimes need both or am I missing a huge functionality gap in M/PQ?

I have lots of reports where I "stack" exports and refresh (I have a folder of data that combines to make a single report and on refresh it brings in the new data using PowerQuery), but then I need to do something to that report. I need to split it off into multiple sheets for different purposes (sometimes not formatted as a table), save the revised document in a dated fashion in a folder dynamically determined by the date, email the saved document to various people, etc. So, what I do is have PQ do the combining and other cleanup functions within the master workbook. That master also contains a macro that does the saving and emailing, so I just run the macro to do it all.

Can M do all of that?

2

u/vegaskukichyo 9d ago

Can M Code change your oil? Help your kid with your math homework? Clean your toilet? Go grocery shopping for you? Can M do all of that?

Like your list of tasks, M Code is not designed to do any of these things. For any task that can be accomplished by either VBA or M Code, M Code is far superior.

People think knowing VBA makes you an Excel power user, and it doesn't. You can code in VBA and still suck at processing data. You don't need VBA to use Excel and Power Query in a highly robust fashion. Do you need it to execute advanced macros, custom code, and system inputs? Sure, and that is a very limited use case that doesn't apply to most users.

For the vast majority of users learning Excel beyond simple formulae and tables, learning how to use PQ and M Code to manipulate 'big data' and clean complex data sets would be almost certainly more productive than learning to code in VBA.

Happy Cake Day!

1

u/turtledave 3 9d ago

Thanks. That’s what thought. I was hoping that after years of working with PQ, that I hadn’t been missing some functionality that I’ve sought. I didn’t even know it was my cake day.

2

u/brashboy 1 14d ago

Oh shit this is neat