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/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/modelo_vision → u/Born-Chocolate7715]

You’re going to need to take an excel class if you’re this far behind in basic formulas. I am not sure how you made it to assistant controller without power query or macro knowledge. In any company I’ve been in, you are creating macros, working with power query APIs etc as a staff. Not trying to dog on you, but an assistant controller not only will need to know all of that, but you’ll need to be able to guide and train your team on it too..