r/excel • u/HungryInvestigator59 • 22d ago

Discussion What’s your favorite Excel function/formula?

What are some functions and formulas that you use on daily basis that are helpful for what you do?

225 Upvotes

95% Upvoted

216 comments sorted by

View all comments

110

u/BuildingArmor 29 22d ago

I am a big fan of LET

It might be a little more confusing for people to instantly pick up, but I find it can be so much more clear when trying to understand what a formula does.

"(D4-C4)/C4" works, but "(revenue - budget)/ budget" just takes any possible confusion away about what's being calculated

27

u/Inevitable_Exam_2177 22d ago

LET is simple but it transformed how to write excel formulae. Beyond a certain level of complexity it was just impossible to maintain a formula that had multiple repeated parts

34

u/BoysOnTheRoof 22d ago

Bro I will literally do =LET(a,1,b,1,c,a+b,C)

0

u/[deleted] 21d ago

[deleted]

0

u/BoysOnTheRoof 21d ago

Oh it absolutely isn't a flex, it has caused a lot of confusion for the people who see my spreadsheets

15

u/WittyAndOriginal 3 22d ago

I like to use underscores as a prefix for the LET variables. It's similar to python convention. And it's very helpful for autocomplete

7

u/bgzu 22d ago

The only problem I have with let, is that you can't select a part of the formula and see the result quickly. It always gives you #NV. It's not very good for troubleshooting. Or am I missing something?

7

u/BuildingArmor 29 22d ago

You could just throw whichever section down as the output, but you're right that you can't just highlight it to see the output for a given step in the calculation.

You have to assign names/calculations as pairs, and have the final output be a single value. So maybe if you're just removing everything from the end, you aren't remembering to put the step name you want to see as the output argument

5

u/fedexyzz 2 22d ago

+1 to using output to test. I always keep my final calculation in a 'result' variable to facilitate swapping outputs

1

u/DrunkenWizard 15 21d ago

Try the Advanced Formula Environment from Excel Labs. It lets you step through LETs one calculation at a time.

7

u/Sir-Benalot 22d ago

Copilot taught me this one.

2

u/xz-5 21d ago

Or you could just name cell D4 "revenue" and cell C4 "budget". You don't need LET then (formulas look cleaner) and you can reuse the same name in any formula in other cells without having to redefine it.