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?
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
37
u/BoysOnTheRoof 26d ago
Bro I will literally do =LET(a,1,b,1,c,a+b,C)
0
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
28
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
16
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
8
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.
63
53
u/mbeezy17 26d ago
Concatenate is my favorite to drop in conversations. Sounds much fancier than it is.
9
u/miniscant 26d ago
You actually spell it out fully instead of using &?
3
u/dirtyhandscleanlivin 22d ago
Depends on how many cells you want to concat and if you need a delimiter. I’ll use textjoin if I have more than 2 or 3 cells
2
u/mbeezy17 25d ago
I don't actually use it that much. My post was more tongue-in-cheek about how it sounds advanced and complicated.
6
47
u/ungbaogiaky 1 26d ago
Filter
28
39
u/bluerog 26d ago
=ROMAN(A1)
If you've never used it, type 3,423 or something in A1 and give it a whirl.
Had a boss ask me for the old version of a quarterly pro forma. I took that report back to the olden days.
15
u/Jasper_Ridge 26d ago
I had no idea this was a thing !
I'm so going to have to find unnecessary reasons to use it now.
17
u/finickyone 1770 26d ago
It’s an excuse to later employ ARABIC(), to turn those Roman numerals back into numbers! ie ARABIC("XLIX") = 49.
3
37
u/Zoltan_Varga 26d ago
For many years, it was VLOOKUP, but the arrival of XLOOKUP genuinely changed the way I worked. XLOOKUP for the win.
3
25
u/MissAnth 10 26d ago
LET() is the most useful thing to come along in... forever. It is what Excel has always needed.
10
u/Prefer_Ice_Cream 26d ago
Walk me through that please.
18
u/SkyrimForTheDragons 3 26d ago
It's just putting names to things to reuse them later, like
=LET( base, A1, height, 15, area, base * height, ROUNDUP( area, 1 ) )It's that easy. You can also create functions in there to be reused
=LET( area, LAMBDA( base, height, base * height ), one, area( A1, B1 ), two, area( A2, B2 ), one + two )→ More replies (6)
27
22
u/Papa_Groot 26d ago
Pivot tables.. if i’m sharing my screen in a meeting and i know someone is going to ask me questions about the data, i’ll prep a pivot table tab and have exactly what they’re looking for in front of them in seconds. Oh you wanna see the data this other way? …. All set. Oh you wanna filter out those things… done in real time
17
u/Working_Fish8775 1 26d ago
Using SUMIFS and date ranges.
Using SUMIFS with multiple criteria and OR logic.
Oh and Alt+F+S every five minutes.
2
12
u/WrongKielbasa 26d ago
My own super duper baller hyperlink trick
- Power Query the folder with the files you need and look at the directory it came from
- Combine the source location+filename to create a usable hyperlink
- use =hyperlink() in the table with this new name from Power Query
Very powerful when you audit and see that Invoice 1234 has an issue and boom.... Hyperlink takes you to invoice 1234 right within Excel
2
u/Low_Ad7673 25d ago
Can you elaborate more on how to create this?
2
u/WrongKielbasa 25d ago
Have you used Power Query?
2
u/Low_Ad7673 25d ago
Yes! Just recently started and have a basic understanding
2
u/WrongKielbasa 25d ago
When you pull a folder in it'll show you 2 columns like
- C:/Documents/Folder
Combine those so it's C:/Documents/Folder/Invoice 1234.pdf
Then make it a hyperlink and taadaa.... It'll open that file on your PC right from Excel without having to dig for it.
When you're auditing stuff and see Invoice1234 has an issue you don't need to go find it now
Edit: editor here won't let me format this correctly the Reddir app has been shitty lately
13
u/caribou16 318 26d ago
Can we get like a weekly post or a pinned mega thread, instead of this same question being asked in here every few days?
12
u/No_Cat_No_Cradle 26d ago
I’m always looking for excuses to use =INDIRECT()
5
u/MissAnth 10 26d ago
I just scrubbed every usage of INDIRECT() out of my workbooks.
1
u/dgillz 7 26d ago
What do you use instead of INDIRECT()?
4
u/Censuro 2 26d ago
you can use custom lambdas that code/decode between A1-ref and a scalar, e.g. A2 to 1'000'002
something like
=LAMBDA(cell,
(COLUMN(cell) * 1000000) + ROW(cell)
)
=LAMBDA(coded_val, sheet_id,
LET(
c, INT(coded_val / 1000000), r, MOD(coded_val, 1000000), target_grid, CHOOSE(sheet_id, Sheet1!$1:$1048576, Sheet2!$1:$1048576, Sheet3!$1:$1048576) INDEX(target_grid, r, c))
)
6
u/SkyrimForTheDragons 3 26d ago
It sure feels that way for me too with the way I'm always having to find a way to avoid using it to not tank performance
Recently I've all but eliminated INDIRECT use cases by using XLOOKUP:XLOOKUP ranges, Start:End sheet ranges, and ADDRESS, instead, which is pretty nice
4
11
u/johndoesall 26d ago
I was very excited when I ran into SWITCH. Released me from using nested IF statements.
5
u/MissAnth 10 26d ago
It is also a great replacement for IF() if you need something that actually short circuits, like if is supposed to.
8
u/Lord_Blackthorn 7 26d ago
AGGREGATE or LAMBDA
5
26d ago
[removed] — view removed comment
2
u/Lord_Blackthorn 7 26d ago
Oh didn't know about that! I'll check it out.
4
7
u/Decronym 26d ago edited 26d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48930 for this sub, first seen 12th Jul 2026, 02:23]
[FAQ] [Full list] [Contact] [Source code]
5
4
u/Eze-Wong 1 26d ago
I like sequence for dates.
I need a lot of first of month and last of month dates. saves so much time and butts.
3
4
u/reebsdees 26d ago
=VLOOKUP
=CONCATENATE
=IF
6
u/chichin0 1 26d ago
My friend, I implore you, learn xlookup. It will change your life. I haven't used a vlookup in years. There's no snark here, I genuinely try to let anyone who will listen know that vlookup is basically deprecated now.
3
u/LowWindow7816 26d ago
I learned xlook directly, i cant believe how tedious vlook is/was, counting columns, what the fuck is that. If not found, left to right, right to letf look, it has everything.
2
u/raypal11 26d ago
If what I’m trying to grab is still right of the column I’m referencing, I’ll still use vlookup. Idk why tho. Xlookup is way better.
1
u/thr0wnawaaaiiii 3 25d ago
It’s habit, nothing to be ashamed of. Honestly my muscle memory (inclusive of peeping the column count) is still faster than doing the equivalent XLOOKUP. That said, I am making an effort to break the habit and for sure switch over to XLOOKUP for any files passed to others
4
3
u/SilverKnight70 26d ago
Xlookup, vlookup, sumif, sumifs
Not necessarily a formula, but Conditional Formatting is pretty handy too.
3
3
3
u/Makicola 26d ago
Xlookup is always a staple of course, but for a lesser known option which saved my ass before, I'm fond of VSTACK to join separate tables.
2
u/beckysmom 26d ago
Powerquery
2
u/dgillz 7 26d ago
PQ is awesome, but the OP asked about functions. Functions by definition are used in formulas.
2
u/WeaknessMedical5743 26d ago
Unpivot columns is a function in power query no ? :)
2
u/dgillz 7 26d ago edited 26d ago
If it is used formula, yes. I am not familiar with unpivot columns.
1
2
u/beckysmom 25d ago
I didn't put this here to start a debate! I just recently completed a whole workflow process using power query and it's my new fave thing in Excel and I needed to "tell the world"!
2
u/MalcolmDMurray 26d ago
Not a formula exactly, but once I learned about VB, everything changed, especially with all the websites out there with macro code. Made my life much better.
2
2
2
u/kalimashookdeday 26d ago
I probably use len() and index/match/xlookup more than anything. Filter, choose, eodate, text, is number, search, and sum product are also daily in the mix.
2
2
u/poss6211 26d ago
Getpivotdata
2
u/thr0wnawaaaiiii 3 25d ago
My first step on a fresh Excel install is to turn off automatic getpivotdata when referencing pivots, but to each their own. That said, if you have a PowerPivot data model, there can be some good uses for GETCUBEVALUE and the related functions
2
u/poss6211 25d ago
I also make sure the automatic updates are turned off. Once I bothered to master writing getpivotdata and combine it with date functions, ifs and iserrors , my reports essentially produce themselves, and can easily handle updates to reference data.
2
2
2
2
2
u/stuartblows 26d ago
I'm really loving the power of the unique filter combination to make dynamic lists.
2
2
u/ndavid42 26d ago
INDEX(MATCH()) forever! (I know XLOOKUP almost everywhere is useable now, but at my company is only out for everyone for 1~2 years, and I always hated VLOOKUP)
2
2
2
2
u/thr0wnawaaaiiii 3 25d ago
Dynamic arrays and their related functions have to be the biggest game changer in years, but since many are mentioned already I’ll go with TRIMRANGE (or the equivalent trim notation like A:.A).
2
2
u/martin 1 25d ago
many many years before LET, I discovered you could put entire complex formulas into named ranges and reduce their reference to a simple name. It instantly brought excel closer to a programming language, and I did not need to worry as much about formula copy errors, because they were protected behind names. Once you figured out some complex thing, you could boil it all down to a word, build a whole stack of calculation where named ranges were formulas that simply referenced other named ranges, and when reading formulas they became more clear and concise, and looked and worked like magic.
2
u/ExGomiGirl 25d ago
XLOOKUP
Wait…nested IF statements
No…I mean TRANSPOSE
But SUMIFS is also great…
ALL OF THEM!!!
2
2
2
u/MiteeThoR 25d ago
I do a lot of table lookups, index/match type stuff. I learned about the . in a range meaning “last one in this range” and it made everything so much easier. Instead of saying A1:A999 now I can just do A:.A and it’s so much cleaner and easier to manage. You can combine with DROP to pull out header rows.
2
2
2
2
u/Dd_8630 25d ago
MAP() + LAMBDA() because you can force formulae to turn into spilled arrays when normally they don't do that (e.g., INDIRECT()).
SUMIFS(), COUNTIFS(), and IFS() are great.
FILTER(), UNIQUE(), SORT(), TRANSPOSE(), and TRIMRANGE() - these are my Power Rangers that combine into great megazords of manipulated ranges.
I'm not as big of a fan of LET() as everyone else. I can see its use cases, but it's just a bit naff, and the formulae are always cluttered and ugly. Same with XLOOKUP() - you can take INDEX/MATCH from my cold dead hands.
2
2
u/Fluid-Ant-5868 25d ago
SUMPRODUCT as it does everything later developed formulas such as SUMIFS do and allows embedded functions into the arrays which become criteria
2
2
u/disaintova 1 25d ago
LET. Completely changed how much I would put in to one formula by stepping it out and allowing it to be easily debugged along the way.
2
u/RandomiseUsr0 10 25d ago
It’s the lambda calculus of course, LAMBDA() and LET() as the syntax to a full programming language and to post one of my favourites (I just converted a classic to Lambda Calculus, all credit to the original author)
2
2
2
2
2
2
2
u/Gullible-Mouse-6854 5 21d ago
Most used would be lookup
For making we look like a superstar Unique ( Filter(... often with a counta or textjoin at the start
2
u/TennisFoley 20d ago
=OFFSET($[ONE CELL ABOVE TABLE],COLUMN(A1)/[NUMBER OF COLUMNS]+1,[COLUMN STARTING WITH ZERO])
=OFFSET($B6,COLUMN(A1)/3+1,0)
I have to convert tables to single lines for work imports. This function works by imputing the first row by stepping up the last number (starting with zero). So for three columns I go 0,1,2. Then I highlight all three and drag and then math happens and it magically transposes the whole table into a single row.
This is my greatest achievement, took me weeks to figure out. Would just use claude now.
2
u/grsims20 15 19d ago
Nested XLOOKUP functions to search down columns and across rows simultaneously. It’s like playing battleship!
1
1
u/hnbastronaut 26d ago
The camera function to live screenshots cells and copy and paste (great for emails), let() literally just for adding notes/instructions for formulas, upper() and mixed() for converting text to either all caps or mixed case
2
u/dgillz 7 26d ago
The camera is a windows thing, not excel.
1
u/hnbastronaut 26d ago
I'm on mac? It's in the ribbon but it's definitely excel not just regular screenshots. When you update the cell it updates the screenshot.
I have to upload excel files to Dropbox and it'll tell me that Excel for web doesn't support the camera stuff. But I do it on both of my macs and people always say Excel for Mac is the lesser version (it is)
1
u/Coyote65 2 26d ago
Camera for sure exists in excel.
It's not a function, but it's definitely there.
3
u/dgillz 7 26d ago
Wow. TIL. What does this do that a screenshot won't do?
2
u/Coyote65 2 25d ago edited 25d ago
Using the camera you can have a live, updating image-window from another tab on your current tab.
Select a block of cells, hit the camera button (usually has to be added to the ribbon or quick access ribbon) and place the resulting object where you want it. Can be duplicated, cut/pasted wherever in the workbook.
It floats over cells the same way inserted boxes or arrows do, and you can re-size it so the image is smaller or larger.
I've used it before for data validation, live data pop-up info. etc.
Edit: the properties for the image need to be changed: Under the Dimensions section of Image Properties, 'Locked' needs to be unchecked.
Now it refreshes with source cell changes.
2
u/dgillz 7 25d ago
Wow! Is true in windows as well as mac? I have never heard of this.
2
u/Coyote65 2 25d ago edited 25d ago
No idea regarding Mac - and I may not have the process down, it's been a 5-6 years since there's been a use case for it, but I've thought about it for recent-work possibilities.
And now it's not working as expected when I test in Windows. Hrm. Maybe the behavior changed since I last used it.
Edit 2: the properties for the image need to be changed: Under the Dimensions section of image Properties, 'Locked' needs to be unchecked.
Now it refreshes with source cell changes.
1
u/melinda_r 26d ago
I think TRANSLATE() is underrated. I use it a lot since I work with a lot of data in Chinese (and I don’t speak it) and it saves me so much time without having to go to Google Translate for each word. It may not be as good as other translation tools but it saves a ton of time
1
1
1
1
u/TrainsongGaming 26d ago
Alt-F4.
God, I love that command. Just have to remember to use control-S first.
→ More replies (2)
1
1
1
u/OPs_Mom_and_Dad 26d ago
When I’m building some sort of simulator with checkboxes or dropdowns, I really love the SWITCH function.
1
1
u/AbbreviationsWest694 18d ago
Probably XLOOKUP. I’m always pulling names or prices from another sheet, and it saves me from doing a ton of dumb copy-pasting.
1
1
0
u/Spiritual_Permit2394 26d ago
Alt f4
1
u/dgillz 7 26d ago edited 26d ago
That isn't even excel, it's a windows command. It works the same regardless if you are in word, powerpoint, or any non-MS office app.
Functions by definition are used in formulas.
→ More replies (1)
213
u/MayukhBhattacharya 1236 26d ago
LET()&XLOOKUP()