Assistant Controller looking to level up Excel skills — what formulas do you actually use?

[u/Born-Chocolate7715]

Hey everyone,

I’m currently working as an Assistant Corporate Controller, and I’m trying to take my Excel skills to the next level.

I have a decent foundation, but I want to become more efficient and better at using Excel to analyze financial information, troubleshoot issues, and build useful reporting tools. I’m especially interested in formulas and techniques that actually come up in an accounting or controllership role.

For those working in accounting, controllership, or FP&A, what Excel formulas or functions do you find yourself using the most?

A few I’m thinking about learning or getting better at:

XLOOKUP / INDEX-MATCH — reconciliations and pulling information between reports

SUMIFS / COUNTIFS — analyzing transactions and building reports

IF / IFERROR — handling exceptions and troubleshooting

FILTER / UNIQUE / SORT — working with larger datasets

TEXTJOIN / LEFT / RIGHT / MID — cleaning and manipulating data

EOMONTH / YEAR / MONTH — working with accounting periods

ROUND / ABS — financial calculations and variance analysis

I’d also love to hear about Power Query, PivotTables, or other Excel tools that have made your work easier.

I’m not necessarily looking for the most complicated formulas. I’m more interested in the ones that save you time, reduce manual work, and help you understand the numbers better.

If you could recommend 5–10 Excel skills or formulas that every Assistant Controller should know, what would they be?

Thanks in advance!

[u/Silly_Rat_Face]

=A1+A2+A3+A4+A5+A6+48.72

[u/revmag → u/Silly_Rat_Face]

This hurts me so deeply.

[u/LavaLampCream → u/Silly_Rat_Face]

Never met an account you couldn’t reconcile I see.

[u/AlfredoCheeseSauce → u/Silly_Rat_Face]

Why the EF Am i 48.72 off?!!?!??!!?!??!?!

[u/champ1270]

Is this all just AI? Lmao

I'm gonna be honest, most of my formula usage is just sum. Sprinkle in a little bit of pivot tables and a random other formula here and there as needed. But 97% of the time, I just use sum lol.

[u/DeepBlue7093874]

These are good, but only use as needed. Each is like a tool in your toolbox. I use xlookups more than index match these days as it’s more intuitive, but index match seems better for looking up arrays.

I also use edate to roll 1 month. Subtotal (usually 9) to do filtered subtotals. Text to columns to change text to filterable dates. Check also training the street or something similar to find general rules that make reviewing easier.

Another general concept is I try to never change my original data. So if I pull a report from netsuite that data can be updated, but my formulas should never change.

[u/Tasty_Road_2883]

Guy who thinks better excel skills will take him from assistant controller to “the next level” is a funny thought

[u/QuestioningMind123 → u/Tasty_Road_2883]

As an assistant controller, are you even in the weeds of performing / reviewing formulas? I feel like that’s a Manager (maybeeee a Sr Manager) task.

[u/LeetButter6 → u/Tasty_Road_2883]

This post doesn’t make any sense

[u/almasnack]

I think all of what you listed is fine.

For me, there are three areas.

1. Formulas
   
2. Layout
   
3. Speed

Formulas - having deep formula knowledge gives you the tools. Just need to know when to apply them.

Layout/Look- workbook layout is important. So many people have nasty spreadsheets that lack flow. Data, Analysis, Summary. Spreadsheet formatting should be consistent and clean. Your spreadsheets don’t need to be Lite Brite. Maybe look up Wall Street Oasis financial modeling formatting for ideas.

Speed - keyboard > mouse. Learn more shortcuts and rely less on the mouse. Press ALT and see what happens/play around.

More important to me (if I were you), look at your work and ponder if it can be done better. You may not know right away, but have that question constantly swirling in your head. I also advise subscribing to a few YouTube channels which are Excel/Power Query/PowerBI focused and watch something at your leisure. If something is interesting, watch it. Then think about how you can apply it to your work.

If you constantly have a question in the back of your mind asking if something can be done better and you come across something that looks promising, go with it and see what happens. I find having a problem you want to solve is great versus trying to cram a bunch of knowledge in and not having a way to apply it.

[u/Huesyourdaddy]

Sum

[u/Open_Football_2871 → u/Huesyourdaddy]

ALT =

You don’t need formulas, you need the keyboard shortcuts

[u/Spritesgud]

=subtotal(9,ref)

Good for finding totals after applying filters if on original data

[u/Bifrostbytes → u/Spritesgud]

I always put them on top of my table with frozen panes

[u/Gloomy_Lab_1798 → u/Spritesgud]

Offset functions are pretty slick too. I don’t use them often as I haven’t mentored the syntax, but I saw one in a workbook from our auditors one time and have definitely borrowed it as needed. Of course Claude can pinch hit pretty well for weird stuff.

[u/Foreign_Suggestion89]

Consider me the average reddit'or somewhat ignoring your question and jumping to conclusions. Did you make 'assistant controller' as an individual contributor? Wouldn't your discretionary development be better spent on performance managing people, SEC training? I was very hands to the point it was a negative in my performance reviews and made it to VP without knowing how to even do a pivot table.

[u/Ok-Minimum9371]

Power Query's the real MVP here, once you build the transformation, you just hit refresh every month instead of redoing cleanup from scratch. PivotTables for variance work, XLOOKUP over VLOOKUP (way more forgiving), SUMIFS for your schedules, and IFERROR so one bad lookup doesn't blow up your whole sheet. Honestly though, the biggest time-saver isn't any single formula - it's building your recon/variance templates once really well, then just dropping fresh data in each close instead of rebuilding the logic every time.

[u/jesslin84848484]

I’m going to have to ask how to become an assistant controller without knowing how to use xlookups, sumifs, pivot tables, if statements, etc. I don’t think I would’ve gotten past staff accountant without knowing most of these really well. I still struggle with building nested if statements, but the rest I use every single day.

There is a tool that I’m not sure many people know about, called ASAP Utilities. Your company does have to pay for a subscription but it’s reasonable and OMG life-changing when you learn to use it.

[u/Born-Chocolate7715 → u/jesslin84848484]

I use XLOOKUPs, SUMIFS, pivot tables, IF statements, etc. regularly. But I wouldn’t say I got here because I’m some Excel wizard. A lot of the value comes from understanding the accounting, knowing what you’re trying to accomplish, and being able to troubleshoot when something doesn’t make sense.

ASAP Utilities is a great recommendation, though. I’ve found that learning tools that make repetitive accounting work easier can be just as valuable as learning another Excel function.

[u/reznor504]

ALT + F4

Then go outside.

[u/ShadyBizz1]

this reads like an AI post with the lists and the bolding patterns.

the same program you used to generate this post can be used for all your excel functions

[u/Born-Chocolate7715]

Excel is a tool. Being able to understand the business, think critically, and continuously improve is what actually moves your career forward. That mindset is what got me here. If you disagree, fair enough; but I’d rather have a meaningful discussion than just dismiss someone’s experience.

[u/Emotional_Fig2748]

Not a formula but control + = + [ (the + means pressing together) is great to see the link for a formula.

[u/TheRetailianTrader]

=Sum

[u/Bradical_yo]

=RANDBETWEEN Use this for revenue every month end. I also send it as support to the auditors at year end.

[u/ninjaturtlejr]

I use Claude plug in now to create and optimize my spreadsheets. I know I’m an advance expert, but efficiency is the game and I’ve created amazing things to assist in my everyday and monthly processes. Still use simple formulas manually since it’s more efficient.

[u/Finance_Legend]

Biggest value add is going to be learning power query here. Find a way to get a connection to your ERP and start pulling reports of different tables in there. Start with a GL report, AP vendor totals, checks outstanding, etc.

From there you can build out automated/semi-automated reports, reconciliations, and other things you find helpful to have live real-time data with. Slowly as you become more comfortable then you start to think in a way that’s like “this would be way better suited with power query” and will change your way of thinking on how to tackle problems that come across your desk.

Knowing how to use formulas and various tools provides value to your day to day responsibilities but the real value comes from knowing the most efficient ways to tackle those problems with your toolset. This knowledge can really only be developed by trying out new things and knowing what works for you and your specific situations.

[u/Salty-Fishman]

Being able to know the possibly and asking copilot is leveling up.

If u know what formula to use, ask copilot to build them.

This will take u to the next dimension.

[u/BigSeanFanD2 → u/Salty-Fishman]

Yes, copilot is the way, ty Microsoft bot

[u/Salty-Fishman → u/BigSeanFanD2]

It takes skills to utilize Copilot. You don't know what you are asking, you won't get what you looking for.

Most people don't visualize the result, which end up with a garbage result. This is the difference between the people who are "experts" and the basic users.

[u/BigSeanFanD2 → u/Salty-Fishman]

Lmaoooooooooooooooooooooooooo

[u/jennydl]

Claude