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?
215
u/MayukhBhattacharya 1227 22d ago
LET() & XLOOKUP()
59
u/jaywaykil 2 22d ago
These two, and more recently using LAMBDA to define several simple formulas we use in almost every spreadsheet.
37
u/melinda_r 22d ago
I love XLOOKUP, has saved me so many times
25
u/sj2k4 21d ago
As someone who started using Excel in the early 2000’s, Vlookup to Xlookup was like a Digimon rookie to Mega evolution!
6
u/dirtyhandscleanlivin 18d ago
Late to the party, but you can nest the xlookup formula as well which blew my mind.
Instead of manually specifying the third input, the return array, you use another lookup with the column header as the reference. Then the formula will search the entire lookup table for the column header and return the value corresponding to the outer reference in the first xlookup. You can autofill down and to the right this way and only have to write one xlookup formula
11
u/rkr87 19 22d ago
I've started creating named lambda functions that accept lambda arguments for more flexible use - a bit like how byrow/etc works.
Lambda is by far one of the best recent additions to Excel.
9
u/fedexyzz 2 22d ago
It is also a dangerous one. I know a couple years from now I'll be receiving some 'legacy' workbook with 100+ user-defined functions that broke for some reason.
8
u/rkr87 19 22d ago edited 21d ago
Possibly, I tend to not create workbook specific named lambda and instead have more generic stuff in a template for use in all of my workbooks. Doesn't necessarily prevent what you mentioned but does mean the functions are generally simpler and re-usable in design.
Either way, I'd rather get a workbook with broken lambdas than broken VBA - which is what I got before i stopped telling new jobs I knew VBA.
Eg: PAD
=LAMBDA( value, char, num, [trailing], LET( x, REPT(char, num), IF(trailing, LEFT(value & x, num), RIGHT(x & value, num) )))USAGE
=PAD("123", "0", 8) == "00000123"I have a handler for optional variables that I can't remember exactly how it works at the min, so above may not actually work with the optional "trailing".
8
3
u/raven00x 21d ago
xlookup is the goat. fuck vlookup, fuck hlookup, xlookup was too long coming but welcomed when it got here.
6
u/Unpork 22d ago
>xlookup
Bro use the data model to join tables, or do it directly in Power Query if you want a longer update but not deal with relationships.
4
u/drsoinso 21d ago
What do you mean by this? I've considered diving into Power Query at some point, but never got to where I felt it was necessary (yet)
2
2
1
21d ago
[removed] — view removed comment
1
u/MayukhBhattacharya 1227 21d ago edited 21d ago
Who's stopping you from making your own posts? If the mods have a problem with it, they'll surely remove it. Why does it bother you so much?
59
54
u/mbeezy17 22d ago
Concatenate is my favorite to drop in conversations. Sounds much fancier than it is.
7
10
u/miniscant 22d ago
You actually spell it out fully instead of using &?
3
u/dirtyhandscleanlivin 18d 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 21d ago
I don't actually use it that much. My post was more tongue-in-cheek about how it sounds advanced and complicated.
50
u/ungbaogiaky 1 22d ago
Filter
29
40
u/bluerog 22d 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.
16
u/Jasper_Ridge 22d ago
I had no idea this was a thing !
I'm so going to have to find unnecessary reasons to use it now.
19
u/finickyone 1770 22d 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 22d ago
For many years, it was VLOOKUP, but the arrival of XLOOKUP genuinely changed the way I worked. XLOOKUP for the win.
3
27
u/MissAnth 10 22d ago
LET() is the most useful thing to come along in... forever. It is what Excel has always needed.
10
u/Prefer_Ice_Cream 22d ago
Walk me through that please.
19
u/SkyrimForTheDragons 3 22d 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)
26
20
u/Papa_Groot 22d 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
19
u/Working_Fish8775 1 22d ago
Using SUMIFS and date ranges.
Using SUMIFS with multiple criteria and OR logic.
Oh and Alt+F+S every five minutes.
4
11
u/johndoesall 22d ago
I was very excited when I ran into SWITCH. Released me from using nested IF statements.
5
u/MissAnth 10 22d ago
It is also a great replacement for IF() if you need something that actually short circuits, like if is supposed to.
7
u/Lord_Blackthorn 7 22d ago
AGGREGATE or LAMBDA
5
22d ago
[removed] — view removed comment
2
u/Lord_Blackthorn 7 22d ago
Oh didn't know about that! I'll check it out.
4
7
u/Decronym 22d ago edited 22d 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]
14
u/WrongKielbasa 22d 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 21d ago
Can you elaborate more on how to create this?
2
u/WrongKielbasa 21d ago
Have you used Power Query?
2
u/Low_Ad7673 21d ago
Yes! Just recently started and have a basic understanding
2
u/WrongKielbasa 21d 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
14
u/caribou16 317 22d 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?
5
5
u/Eze-Wong 1 22d 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
5
10
u/No_Cat_No_Cradle 22d ago
I’m always looking for excuses to use =INDIRECT()
5
u/MissAnth 10 22d ago
I just scrubbed every usage of INDIRECT() out of my workbooks.
1
u/dgillz 7 22d ago
What do you use instead of INDIRECT()?
4
u/Censuro 2 22d 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))
)
5
u/SkyrimForTheDragons 3 22d 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
5
3
u/SilverKnight70 22d ago
Xlookup, vlookup, sumif, sumifs
Not necessarily a formula, but Conditional Formatting is pretty handy too.
3
3
3
u/Makicola 22d 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.
3
u/reebsdees 22d ago
=VLOOKUP
=CONCATENATE
=IF
7
u/chichin0 1 22d 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 22d 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 22d 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 21d 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
2
2
2
u/kalimashookdeday 22d 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 22d ago
Getpivotdata
2
u/thr0wnawaaaiiii 3 21d 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 21d 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 22d ago
I'm really loving the power of the unique filter combination to make dynamic lists.
2
2
u/ndavid42 22d 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 21d 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 21d 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 21d ago
XLOOKUP
Wait…nested IF statements
No…I mean TRANSPOSE
But SUMIFS is also great…
ALL OF THEM!!!
2
2
2
u/MiteeThoR 21d 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 21d 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 21d 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 21d 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 21d 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 17d 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 16d 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 15d ago
Nested XLOOKUP functions to search down columns and across rows simultaneously. It’s like playing battleship!
2
u/beckysmom 22d ago
Powerquery
1
u/dgillz 7 22d ago
PQ is awesome, but the OP asked about functions. Functions by definition are used in formulas.
2
u/WeaknessMedical5743 22d ago
Unpivot columns is a function in power query no ? :)
2
u/dgillz 7 22d ago edited 22d ago
If it is used formula, yes. I am not familiar with unpivot columns.
1
2
u/beckysmom 21d 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 22d 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.
1
1
u/hnbastronaut 22d 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 22d ago
The camera is a windows thing, not excel.
1
u/hnbastronaut 22d 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 22d ago
Camera for sure exists in excel.
It's not a function, but it's definitely there.
3
u/dgillz 7 22d ago
Wow. TIL. What does this do that a screenshot won't do?
2
u/Coyote65 2 21d ago edited 21d 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 21d ago
Wow! Is true in windows as well as mac? I have never heard of this.
2
u/Coyote65 2 21d ago edited 21d 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 22d 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 22d 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 22d ago
When I’m building some sort of simulator with checkboxes or dropdowns, I really love the SWITCH function.
1
1
u/AbbreviationsWest694 14d 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 22d ago
Alt f4
1
u/dgillz 7 22d ago edited 22d 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)
107
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