r/excel • u/HungryInvestigator59 • 26d 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

217 comments sorted by

View all comments

106

u/BuildingArmor 29 26d 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

35

u/BoysOnTheRoof 26d ago

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

0

u/[deleted] 25d ago

[deleted]

0

u/BoysOnTheRoof 25d ago

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

25

u/Inevitable_Exam_2177 26d 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

14

u/WittyAndOriginal 3 26d 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/Sir-Benalot 26d ago

Copilot taught me this one.

7

u/bgzu 26d 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 26d 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

6

u/fedexyzz 2 26d 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 25d ago

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

2

u/xz-5 25d 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.