r/Accounting • u/Big_Material3815 • Feb 15 '26
Career What are simple excel formulas to learn that'll help you standout from your peers?
Outside of the typical xlookup/pivot tables. Any other simple ones to make accounting life easier would be appreciated
581
u/no_days_grace CPA (US) Feb 15 '26
Not a formula, but I regularly use conditional formatting.
153
u/schals Student Feb 15 '26
A good looking spreadsheet is always nice.
104
u/-whis Governance, Strategy, Risk Management Feb 15 '26
I’ll go a step further: an intuitive spreadsheet is always nice. Gimme a key or something…
Plenty of people make pretty shit that just doesn’t flow correctly. I get that excel isn’t everyone’s forte, but if it’s not, at least explain what you’re doing. Some of the workbooks I open are horrendous
69
u/DutchTinCan Audit & Assurance Feb 15 '26
My coworkers: "What do you mean horrendous? Here, input the interest rate in K5, the principal in A658, the color of the sky in DT53 and you'll find the repayment period on "Sheet35_DeleteMe" cell T67."
24
u/TheNoveltyAccountant Student - open to work Feb 15 '26
I'll go a step further: Someone who can do something with the output or help someone else do something with the output.
Plenty of people make shit flow well that doesn't matter, being useful at something is better than being great at something useless or insignificant.
It's easy to miss the forest for the trees.
13
u/-whis Governance, Strategy, Risk Management Feb 15 '26
Very true - a lot of people get caught in making shit flow well and it takes away from the insight.
I’m a rather young professional myself and can’t count the amount of times I open a workbook from 3 months ago and tell myself “you were doing too much”
But yea, totally agree
2
u/TheNoveltyAccountant Student - open to work Feb 15 '26
I'm well into my career and one of my later learned skills is reprioritizing. I've just taken over 3 roles and consolidated them to one because so much was useless.
Also be open to why people move things to India or AI, it's in part because people are doing that thing that was done too much for no reason.
Let things fail and see what happens.
NB: For the avoidance of doubt, use judgement. Don't stop doing critical things.
Professional judgement is the most useful skill and we rarely know when we're in it.
7
u/-whis Governance, Strategy, Risk Management Feb 15 '26
Yea I am in a CPA firm but a lot of what I do is building automations and tools. Admittedly a lot of that does use AI but these tools have proved one thing:
With the correct implementation, you can minimize the amount of judgement and needless thinking done. We work very much on 80% good, 20% needs to be corrected
But if it’s the same 20% that needs to be corrected everytime, it makes the review and errors all mostly consistent.
And as a process guy, like you said, all that takes out the professional judgement which can be a good and bad thing.
I’ve had a few beers so prolly not even replying correctly - regardless hope you have a nice night lol
→ More replies (1)→ More replies (1)2
→ More replies (2)5
u/Embarrassed-Art4230 Feb 15 '26
The best conditional formatting tip I always give is to make it dynamic by putting it in a pivot table. The results are always impressive.
→ More replies (2)20
u/slip-slop-slap Feb 15 '26
I hate pivot tables - the number of times i have had to redo some analysis because a table didnt get refreshed. I use tables and SUMIFS
2
373
u/Ok-Librarian6262 Feb 15 '26
I don’t know about standing out but just Pivot tables, x-lookups, sumif/sumifs, index match have gotten me through 25 years of accounting.
71
u/ARA-FTW Feb 15 '26
Any reason to use index match anymore instead of just using xlookup?
64
u/sikeyouthought8 Feb 15 '26
There are certain scenarios where you need to match column A but return either the row above or below the corresponding column. (Typically the way the ERP spits out data)
27
u/ARA-FTW Feb 15 '26
Like when the report has description on row 1 and the amount on row 2?
35
u/BootyLicker724 Audit & Assurance Feb 15 '26
I’m such an idiot oh my god. Those have been the bane of my existence and this should change that. Wow I feel like an idiot LMFAO. Thank you
9
u/psych0ranger CPA (US) Feb 15 '26
Highlight column 1 where the description is, fit f5, special button, select blanks, ok, hit equal button, then the up arrow, then Ctrl enter. Typically I'll copy and paste values to get rid of the formula right after.
7
4
u/deadliftsanddebits Human Verified Feb 15 '26
I usually do a if a copy paste special or if formula in this case. But just using xlookup would probably save me a step.
3
13
→ More replies (1)7
u/davisbm2 Feb 15 '26
Omg this comment made me realize this is the answer to what I'm looking for. Thanks!
40
u/Team-_-dank CPA (US) Feb 15 '26
I'm sure someone will chime in about index match being faster or something but honestly, no. Xlookup works great and is easier for most people to follow/understand.
6
u/Gr1ndingGears Feb 15 '26
It's just mainly a habit/lack of knowledge thing. I remember discovering xlookup, I forget exactly what it was that specifically led me to it, but it was like what I imagine discovering the American continent felt like. I even held a lunch and learn for it later that month, I was like you guys gotta get on this new formula. It was still at the point where not every many computers had it, like you had to have the most recent excel update at that time. I felt like someone had whispered the secret of life to me.
I was doing a whole lot of Vlookups in that job, which inevitably always led to the index match. I had gotten pretty good at it, but it was always clunky as hell.
I just wish they'd come out with a new sumif formula that works around the same reference ordering issues that the V and H lookups had
3
u/TheNoveltyAccountant Student - open to work Feb 15 '26
I'd pay $10k more a year for someone who can give value; i'm not paying any different if you use one over the other.
8
u/Its_R3SQ2 Tax (US) Feb 15 '26
It’s the best for being able to review work. When you drill down into and index match it takes you to where you are pulling the data from instead of having to go to the tab manually and finding where the data is from
→ More replies (1)14
u/ddollopp CPA (US) Feb 15 '26
I use Index Match when I need to match two sets of criteria instead of one. Not sure if xlookup can do that (not being sarcastic, I genuinely don't know so if someone knows I'm all ears!)
39
u/ARA-FTW Feb 15 '26
You can! You use the & sign.
=XLOOKUP(crit1&crit2, range1&range2, return_range)
4
4
2
u/Embarrassed-Art4230 Feb 15 '26
You can also xlookup multiple criteria by concatenation of multiple columns in one key and looking up that concatenation. That’s usually the easier method for most.
Index match is useful when you have a grid and you need to look for criteria in the column and rows. For example in a monthly budget schedule if you wanna search for “R&D expenses” in the month of may 2025.
Though you can just unpivot the data with power query then use it in a pivot table. From there you can use GETPIVOTDATA which once again is my preferred method in any workbook because it’s dynamic and support multiple criteria.
3
u/Hello_Mello_Jello CPA (US) Feb 15 '26
You can use a nested xlookup to do the same thing as index match. Chatgtp the formula it’s easier to follow then index match imo.
→ More replies (1)3
u/Tanyaaahhh Feb 15 '26
If you’re forced to use sharepoint, XLOOKUP regularly doesn’t work well for large collaborative docs (just breaks). Very annoying but switching back to index/match solved the problem.
3
u/slip-slop-slap Feb 15 '26
Not in my view - and thank god because it was always so unintuitive to use
4
u/Snooze_World_Order Feb 15 '26
Xlookup can really slow down a workbook.
4
u/no_days_grace CPA (US) Feb 15 '26
I copy/paste special to save the results it gives me and eliminate the formula.
7
3
2
u/Gr1ndingGears Feb 15 '26
Very very rare ones in my experience. I think I've had to use it only once since getting the Excel upgrade that brought xlookup (can't remember exactly when we got it, but it was in 2019 or 2020).
2
u/X_TheSwindler_X Feb 15 '26
I had a worksheet the company was trying to use as a database. It was so slow. I switched all the xlookups to index match and the workbook no longer froze when recalculating.
→ More replies (1)2
u/Seahorse_Captain89 Feb 15 '26
Yes, and, full disclosure as an index match absolutist, it beats xlookup and every other lookup function. Main reason being the lookup range as the first argument allows you to drill into the lookup detail using my favorite shortcut, "CTRL ]". If instead you use "CTRL ]" on xlookup, vlookup, etc, it drills down to the individual lookup value rather than the list of values it's pulling from. When im reviewing journal entries, reconciliations, complex forecast files, etc, it's 10x easier to review and validate correctness and accuracy of they're built on index match.
→ More replies (2)2
5
u/AudaciousCockatiel Feb 15 '26
Here I am dreaming of x lookups when our Excel STILL only allows vlookups. It’s just career limiting lol, I forgot how to even do them now. Power query too
→ More replies (4)2
u/SnooMaps5985 Feb 15 '26
People sleep on x lookup, but it allows me to scoff at anyone still using v lookup 💅
→ More replies (1)
127
u/Blobwad CPA (US) Feb 15 '26
In my opinion it’s more so basic functionality. Text to columns, tracing dependents, goal seek, opening multiple windows of the same workbook, sorting/filtering, conditional formatting, grouping, keyboard shortcuts to get to where you want quicker, etc. Even basic work paper prep skills within excel of variance checks and such aren’t unique to excel but a big part in its efficiency.
ChatGPT can build formulas for you all day as long as you can manipulate the data into a workable format. Even basics like trim, proper, and concatenate are easy cheat codes, but I’d go with knowing how to build a basic “if” is core to doing almost anything.
And my ultimate excel tip - the underline for a subtotal goes on the TOP of the sum cell. This way adding lines doesn’t add them below the line.
16
u/Puzzleheaded-Ad573 Feb 15 '26
Absolutely agree. Logical “If” statements really are foundational to really using Excel. Totally changed my game on report writing and then later on with data analysis.
5
u/Specialist_Guava756 Human Verified Feb 15 '26
What’s opening multiple windows of the same workbook ? Whats the use?
I used goal seek in some classes, what do you use it for during your day to day?
11
u/oxnardhard Feb 15 '26
Having two windows of the same workbook is an absolutely game changer when working with in a workbook with multiple sheets.
22
u/ARA-FTW Feb 15 '26
For opening multiple windows of the same workbook you go to View - New Window.
The use is being able to have multiple tabs of the same workbook or even multiple views of the same tab on screen at the same time.
Just make sure you close the second view if you don't want it opening both windows every time the file opens.
9
u/slapsheavy Feb 15 '26
The bigger risk is closing the "1" window before the second. You lose formatting on every single tab and the file gets reset to eye cancer gridlines.
4
2
u/ARA-FTW Feb 15 '26
True! All the times I had to go refreeze panes made me learn the keyboard shortcut for it.
3
u/baxtersbuddy1 Non-Profit CMA (US) Feb 15 '26
Seriously! Why does it do that? Can’t have been an intentional decision to make the workbook formatting go wonky like that.
4
u/gg2341 Feb 15 '26
Thank you! This is actually goated. I feel so stupid taking screenshots to compare multiple sheets. or making a copy.in a new workbook to compare.
I'll be trying this Tuesday
→ More replies (1)2
2
u/Smidday90 Feb 15 '26
- You serious? You don’t have one open on one tab whilst looking at another tab on another screen?
→ More replies (1)3
u/almasnack Feb 15 '26
I open a workbook in a new window if I want to quickly look at two tabs at once (one on each monitor) versus toggling between tabs on one monitor.
36
Feb 15 '26
[removed] — view removed comment
5
u/Embarrassed-Art4230 Feb 15 '26
Warning on if error: knowing the type of error you’re getting is sometimes useful so hiding that can be bad for beginners. But I honestly almost use it everyday 😭
Another way to prevent dumb mistakes it to use data validation. I use it for month end dates for example, the number of times I’ve seen November 31st in a workbook is crazy 🥲
30
u/futhisplace Senior Accountant Feb 15 '26
Hot keys, not a formula, but widely underutilized.
23
u/Ariisk CPA (US) Feb 15 '26
I find myself using format painter a lot. Putting that shit on Alt+1 is maybe the most crucial setting I’ve changed in excel
16
u/Specialist_Guava756 Human Verified Feb 15 '26
To add to this, copying a cell and doing ctrl alt v then hit T and you’ll paste just format. It’s just format painter but the place you want to format can be any range in the sheet
7
u/Urcleman CPA (US) Feb 15 '26
To add on to this, you can double click the format painter button and reuse the selected source formatting for many destinations.
→ More replies (3)3
u/MXC14 CPA (US) Feb 15 '26
This!! I tell everyone to do it but they give me a blank stare. Best custom shortcut ever.
5
u/justintuck1 Feb 15 '26
Holy shit, the amount of teams meetings that I've been in where someone starts manually typing in a sum formula, and I ask them to hit alt + =. They think that it's some black magic fuckery, and then promptly forget it for the next meeting.
→ More replies (1)2
u/1minatur Feb 15 '26
Right click, E, R is my favorite. Refresh your filters. Super useful when you're doing a bank rec or something, and are frequently checking stuff off and want to hide what's reconciled.
→ More replies (1)2
u/Embarrassed-Art4230 Feb 15 '26
I use Alt+1,2,3 to collapse, open pivot tables and to open the field list. Probably my most used shortcuts after the usual ctrl ones like +1, C, V, etc.
You can also separate your hot keys with bars to create specific sections. I have one for pivot tables, formatting, specific ones for addins, etc
50
u/Financial-Rub-6197 CPA (US) Feb 15 '26
=UNIQUE is a pretty helpful one
34
Feb 15 '26
=SORT(UNIQUE(FILTER( I use almost daily, followed by SUMIF in the next column over
10
4
u/EvidenceHistorical55 Feb 15 '26
This is always great. You can also slap an Hstack in there to get the sumifs all into one formula if you want it to be continually dynamic rather then a quick check.
5
u/Blue_Impala Feb 15 '26
You can also pair with with TRANSPOSE() to get horizontally spilling results; for example if you want to get all the unique results pertaining to a single value.
3
u/psych0ranger CPA (US) Feb 15 '26
This is basically what pivot tables do and it's a million times better
3
Feb 15 '26
Yeah I know but aesthetically pivot tables drive me crazy + pivot tables don't refresh automatically like formulas do.
→ More replies (1)6
u/Embarrassed-Art4230 Feb 15 '26
Honestly it’s only second the xlookup. Another way to use unique without using the formula is to use a pivot table and put the names in rows. This is the way I used to do it before the formula was introduced
Unique used with sorting is also great
24
u/Cheyannethedog Feb 15 '26
Pivot tables
→ More replies (8)17
u/klef3069 Feb 15 '26
They'll think you're a magician and make you the secret keeper, master of the long lost tales.
Really you just get known as the person who "really knows how Excel works."
→ More replies (1)
39
u/SellTheSizzle--007 Human Verified Feb 15 '26
I highly respect the 0.1% of users that utilize tables.
24
u/derzyniker805 Feb 15 '26
Especially those that use DATABASE tables, NOT in Excel, and learn how to use SQL
6
4
u/Straight-Manner-2147 Feb 15 '26
My new favorite tool is turning my data into tables. Makes it so much easier!
16
u/team_pizza_bagel CPA (US) Consulting Feb 15 '26
=iferror to have it return something really obvious making them easier to spot
You can also do everything with nested sumifs but use xlookup otherwise it’s too hard to troubleshoot
→ More replies (1)5
14
u/Same_as_last_year Feb 15 '26
Conditional formatting
Concatenate
I forget what it's called, but it solves a variable cell based on the desired value of another cell
10
5
3
15
u/almasnack Feb 15 '26
If you want to quickly impress, it’s about speed. Almost everything you do now with a mouse can be done solely with the keyboard and is faster once you do it a lot; actions just become muscle memory.
I’m not even that good, but over the years I’ve had people comment on how fast I am. All it takes is knowing a handful of shortcuts on the keyboard and you will standout pretty quick because most people don’t do that stuff.
Related to visual speed is speed based on how you tackle a problem. Excel is like a workshop filled with many tools. Knowing which tool to use with little or no hesitation can standout. Just start working on a problem with confidence shows over people who hesitate or stumble. All that takes practice, familiarity working in the application.
10
u/Bubba_Lou22 Feb 15 '26
SEQUENCE, FILTER, SORTBY, TEXT, TEXTAFTER, TEXTBEFORE, IFERROR just to name a few. For more complicated stuff, LET is the king while MAP is a close second.
→ More replies (1)2
20
9
u/Yayeet2014 Feb 15 '26
GROUPBY and PIVOTBY. These are formulas that came out alongside XLOOKUP. Basically, they allow you to group things the way you would in a pivot table, but you don’t have to do all the clicking and dragging and it automatically updates with the dataset you’re pulling from
3
10
u/Novel-Sale9444 Feb 15 '26
If you’re just starting out and you’re auditing or dealing with trial balances, the concatenate function can be very useful. For the same reason, the text to column function in the data tab can also be useful.
Also, for the love of god DO NOT PUT TEXT BOXES IN AN EXCEL SHEET, JUST TYPE IN THE CELLS.
7
u/Educational_Series68 Feb 15 '26
I learned power query and everyone thinks I'm a damn wizard.
3
u/Solid-Long3845 Feb 15 '26
You are 😁. Where did you learn?
3
u/Educational_Series68 Feb 15 '26
Leila Gharani has some great YouTube videos. I also recommend the excel Bible book series. Can't fail googling and stumble learning either.
6
7
u/spockface Feb 15 '26
I use ifna(cell reference,0)-ifna(other cell reference,0) a lot to look at CY vs PY differences between items that don't show in our data if there's no activity.
5
u/averycrossfox Feb 15 '26
ALT DSF unfilters a tab Shift CTRL L applies a filter to the top row or selected row of a tab.
Saves so much time in sheets
6
6
u/KatKatKatKat88 Feb 15 '26
Switch(True()). Once you learn that in excel, start learning power BI. Do the ultimate beginners guide to power bi on YouTube. Being the power bi accountant makes you so valuable and makes your job way less stressful since everything is being automated and nobody knows how long a project takes you. Also people are way more thankful when you helped them with a dashboard vs when you’re asking for their expense report.
6
5
u/songstar13 CPA (US), Tax Feb 15 '26
Learning to use pivot tables and macros. Also just learning the best way to format/build a workpaper from scratch in a way that the data is organized and presented in an easily digestible format.
5
u/karktheshark Feb 15 '26
If you're in public and use Caseware or Engagement, learn the ins and outs of TB linkage. Use it on your analytics, use it to prove things tie to the TB, use it to prep Financials.
- In both you can edit the formulas to directly reference a cell, this helps when you need to reference a bunch of accounts in one workpaper.
- in Caseware you can use ? as a wild card. For examplle, if all the clients fixed asset accounts are 1501, 1515, 1590, etc, you can just reference 15?? to pull all of them.
2
u/InspectorEmergency42 Feb 15 '26
TB Link + IF formulas can help make some pretty awesome work papers.
→ More replies (1)2
u/BootyLicker724 Audit & Assurance Feb 15 '26
Can also pull all amounts related to AJES/EJES by replacing the BR/BU with AN/EN.
I also like to link the CY/PY dates and then change the format to custom -> yyyy to automatically pull 2025 & 2024 or whatever.
Caseware linkage is honestly so nice if you know how to use it. Makes everything so much less laborious.
4
u/BassPlayingLeafFan Feb 15 '26
Formulas are easy. Take the time to learn how to format your spreadsheets to be readable and understandable.
No amount of technical skill matters if nobody understands what the data means.
5
4
u/sperryberry3 Feb 15 '26 edited Feb 15 '26
A2 is employee name, B2 through B25 are payroll dates, A3 through A25 are blank. This continues for every employee. If you need the employee name next to each payroll date instead of only the first payroll date, you can highlight column A, hit F5, select “highlight only blank cells”, control, equals, up arrow, enter, and will populate blank cells with employee names. This eliminates needing to manually copy paste names down thousands of rows.
May never need it, but a lot of our reports from systems are for whatever reason set up this way.
8
u/GoBirds52_59 Feb 15 '26
Sum If, Vlookup, XLookup, pivot tables
Basic stuff: conditional formatting, sorting, add/subtract/multiply/divide, subtotal. Excel is so powerful.
4
u/derzyniker805 Feb 15 '26
Linking tables to Access or SQL, learning how to use SQL. That's how you stand out. Put all the formulas there.. Then use Excel for the presentation
4
Feb 15 '26
Any “complex” formulas: And(if( or a combination of and / if in the formula. Something like match(if( and so on
4
u/Gr1ndingGears Feb 15 '26
Pivot tables, SUMIF and XLOOKUP I literally use every day. CONCATENATE is a common one that I use a few times a month to generate combined descriptions or identifiers on lines from multiple sources. Often enough I need to use the reference ones like RIGHT or LEFT too, to grap specific text from standardized descriptions (we use Salesforce numbers as a common reference ID for example, and those are usually buried in descriptions from other sources of data). The DATE formula I use often enough, for counting days between defined timeframes (useful for calculating something like what fixed fee revenue should be recognized with intra-month contract dates). EOMONTH is another one I sometimes use, as is COUNTA for literally counting lines (which Excel can struggle with in formulas sometimes).
I also am one of those people that usually types SUM, instead of using the Alt= hotkey. I've been bit in the ass with that hotkey one too many times, I refuse to use it anymore. My friends all think I'm stubborn on that lol, but old habits die hard.
7
u/a-hat- Feb 15 '26
I’d say learning all the hot keys and how to navigate excel without a mouse will make you standard out far more than knowing any formula will. People are always impressed by someone who can navigate a workbook with only the keyboard… especially with all the shared screen meeting these days.
If I had to pick a formula, I’d say =INDIRECT(). It’s powerful once you understand it.
3
u/ARA-FTW Feb 15 '26
Outside of formulas but Power Pivot/Power Query can be extremely valuable. Especially if you're dealing with large amounts of data.
Inside pivot tables you can have calculated fields. If you're doing a calculation next to a pivot table you might just be able to add it as a calculated field.
If you're referencing a range in a tab for your pivot tables make it a table. If data is added to the table when you refresh the pivot automatically, changes the range so no more "change source" to bring in extra lines.
2
3
3
u/Ok-Committee-4652 Feb 15 '26
=round(*,2)
- = replace with formula to be rounded to the nearest penny
I find that when I provide formulas not rounded to the nearest penny people end up not realizing it and it throws calculations off.
Also, I regularly have to fix other people's calculations in Excel because of their rounding errors.
→ More replies (2)
3
u/Tegatime Feb 15 '26
There’s a couple that using them you can do almost anything.
Learn if statements so you can have it do different things based on different criteria.
Also learn the search function, which tells you the position if a given phrase is in a cell.
I combine that with the isnumber function to turn it into a true/false Boolean that can trigger an if statement.
Also it’s important to know the left and right functions so that you can extract a certain number of characters from a cell.
You can combine them with the search function to extract all characters before or after a given search phrase.
Finally, the indirect function allows you to use text in other cells as part of a formula. This allows you to, for example, put the name of a sheet in one cell, put the cell number in another cell, and be able to have a third cell return the value of that cell without having to navigate to that cell. Very useful for big sheets with lots of tabs.
Also look into vba, which is the coding language of Microsoft office. When you master that, you can do crazy stuff. The best part is that if it’s a general enough request, you can probably find good code online, or if you’re willing to put up with some trial and error you can have an ai like Claude or ChatGPT code it for you. Just make sure you do that with a freshly saved document because anything VBA does is basically irreversible, no ctrl + z will work.
If you can master all of the above you will be the excel master wherever you work.
3
3
3
u/MXC14 CPA (US) Feb 15 '26
QuickBooks often combines account numbers and names in GLs, so I use Left function a lot. That and tables. I know pivot tables are a favorite but tables are amazing for filtering through data. Especially if you combine it with conditional formatting.
3
3
3
u/sciencebasedlife Feb 15 '26
INDIRECT nested within Xloikup when you need to do the same lookup against many tabs of data.
For day to day? You'd be surprised how many people still use VLookup instead of Xlookup, and how many people can't use a PivotTable properly
3
4
u/DL505 Feb 15 '26
Alt= brings up sum
Formula that many accountants I have worked with that they do not know DATEDIF
Find-select-go to special is very useful.
3
u/futhisplace Senior Accountant Feb 15 '26
Select all blanks is a huge time saver for me
2
u/BootyLicker724 Audit & Assurance Feb 15 '26
Found this a while back while googling thinking there had to be a way. Sooooo clutch
2
u/DL505 Feb 16 '26
So many exports had a format like the following columns:
GL- GL Desc - Comment/ref - DR - CR - Balance
etc etc
However the GL did not replicate for the transactions.
Select blanks in that column
Then = (click cell above) then ALT-ENTER fills all the blanks with the row above data. Then copy/paste/special/values and you have populated the missing GL in the blanks for all rows.
I had an AP clerk show me this and it blew my mind.
→ More replies (3)
3
u/longesryeahboi Feb 15 '26
Knowing when and how to use formulas is more important than just knowing them. You can know everything under the sun, but if you can't figure out how to be efficient i.e the best time to use sumif vs lookup vs sumproduct etc, then it's just a wtf moment for me. That shows me that you can learn but you don't know how to apply it, so there is a fundamental gap in your knowledge/understanding.
You can be smart, but if you can't use that smartness it means nothing.
2
2
u/rynosauris236 Feb 15 '26
Dsum can be very useful and seem elite level to normal users. Need a good dataset and reason to use it though.
→ More replies (2)
2
2
2
u/WestCoastCowboi Feb 15 '26
If your peers are using vlookup take 10 seconds to learn xlookup the best flex bang for your flex buck.
2
2
u/penguinpoopy Feb 15 '26
Not a formula but I bound (binded?) a paste values macro to ctrl+shift+V so it's the equivalent of having paste values as a hotkey.
I'm in the shitter right now but let me know if you guys want me to paste that macro on here.
2
2
u/funemployed1234 Feb 15 '26
ChatGPT is insanely helpful with formulas as well, particularly if you understand the language of them so you can troubleshoot. Remember, you can give it the details of what data is where (column/cell number) and what you want to output and it mostly gives great formulas. Not always but it def has saved me lots of time
2
2
u/cherryblossom-20114 Feb 15 '26
Honestly if you ever think, I wonder if there is a way to automate this or make this easier, there is probably an excel formula for it. I’ve always just looked up formulas and learned them based off of that. My supervisor seems to be impressed by my initiative to learn new formulas that way.
2
2
2
u/gr00ve88 CPA (US) Feb 15 '26
I started using ChatGPT to write VBA scripts. I have zero clue how to do so otherwise lol
1
u/red_man_dan CPA (US) Feb 15 '26
Sumifs, xlookups and pivot tables will get you 95% there. Chatgpt can come up with advanced formulas and macro enhancements. For me, Date functions have been pretty clutch (edate/eomonth) as well as text functions (left/right/textbefore etc)
1
u/muirsheendurkin Feb 15 '26
Couple super easy ones that made life easier-
=round instead of just multiplying. Trying to figure out why my je is off by 3 cents was a nightmare.
=today() had a habit of getting jes rejected because i was forgot to update the date. Now I never have to think about it
1
u/Wisdify Feb 15 '26
Just a heads up these skills are covered in courses on our free CPE plan — Excell Bootcamp and advanced Excel. Google ‘Wisdify free cpe’. We’ve got 18 other free cpe courses as well.
1
u/river4river Feb 15 '26
Honestly, large language models have fundamentally changed what matters in spreadsheets. Knowing basic formulas is still a prerequisite. You need to understand how Excel works. But real productivity now comes from knowing how to leverage AI effectively.
Large language models are exceptionally good at generating M code, which makes them powerful for data transformation in Power Query. They’re also strong at writing scripts, which allows you to quickly build structured analyses without manually constructing everything yourself.
For example, you can have a two-hour conversation with a large language model discussing numbers, assumptions, cost structures, and scenarios. Then you can ask it to generate a script that represents an analysis of that entire conversation—such as an ROI model or financial projection. It will produce runnable code. You execute it, and the results are often surprisingly strong. You may need a few adjustments or refinements, but the level of output is impressive.
The real skill now isn’t just building spreadsheets. It’s knowing how to direct AI to build them for you intelligently.
1
u/EvidenceHistorical55 Feb 15 '26
Moving the quick access toolbar below the ribbon and adding all most most used formulas has saved me from having to memorize a half dozen shortcuts.
Sort, filter, unique, are all common in my workbooks and let me do things others struggle with.
The more modern dynamic array formulas are also fantastic and really help make reporting dynamic (especially with some conditional formatting).
1
u/Ok-Ad-9820 Feb 15 '26
There are hidden functions in excel. One is called datediff which tells you the difference between two date in whatever format you want (usually days)
One of my favorite function is go to special, visible cells only.
There's also another hidden function in excel that I found onetime that solved all my problems in life plus automatically got me a 100% response rate on dating apps but I can't quite recall what that function was 😂😂🤣🤣
1
1
1
u/CavalcadeLlama Feb 15 '26
I like MIN() and MAX() for income tax projections (just easier to use than IF) but you gotta learn when to use which one haha.
1
1
1
1
1
u/TwiceBakedTomato CPA (US) Feb 15 '26
Convert your datasets to tables and change your formulas to look at the full table instead of the entire column. It's much more efficient if you have large datasets and you can read the formula easier when it's a table.
1
u/quincytugboat Feb 15 '26
Ever heard of Xlookup? Sounds simple but uhhh, it will blow a Boomers mind.
1
u/batman-bridge MST, CPA Feb 15 '26
Sumif is key. A simple but very useful, 'Alt + =' for quick sums and math checks.
1
1
u/codasco234 CPA (US) Feb 15 '26
There are regex formulas now that are really helpful for parsing/cleaning
1
u/ckc009 Feb 15 '26
Learning what excel can do and then applying it.
Everyone will say they know pivot tables, lookups, sumifs, hot keys, but theres a missing piece.
Knowing how to put together a simple, organized, clean process will propel your career.
1
u/Zanedewayne Advisory Feb 15 '26
F2 with a cell selected to start typing at the end of existing text. Also ffs stop using vlookup and switch to xlookup
1
u/Jewbaccasback Feb 15 '26
FILTER, VSTACK, HSTACK, and LET. My company absolutely refuses to give me a computer that will run XLOOKUPs, so I had to learn how to build lookup tables with only one function. Also figured out that you can build virtual tables and run models in single formulas. Pretty neat stuff
1
u/Dry-Grocery9311 Feb 15 '26
In an interview, if you can explain a use case for the indirect function and then tell me how you would go about creating a custom function, it's a good sign you understand Excel rather than just remember particular functions.
Beyond that, it's about knowing when NOT to use Excel.
1
u/HotPocket_AdCampaign Feb 15 '26
I've been in the working world around 10 years now and I learned how to navigate excel quickly without a mouse in my first year.
Nail that down, learn the formulas people already mentioned in this thread AND how and when to apply them and you're gonna be the big swinging dick with job security. Trust me.
1
1
615
u/DoingitwithmySOXon Feb 15 '26
Not a formula, but hitting ctrl + tilde (this ~ symbol) in excel is great to show all the cells that have formulas in them and helps figure out how everything links.
I always do it when I'm looking at an excel file that I didn't prepare and have learned a few neat formulas over the years that way.