As an accountant, there’s a good chance someone, somewhere, has asked you for help with Excel – especially if you’re the only finance professional in the office. And, to be honest, this can feel really good. Showing some rube how to format their spreadsheet with one simple formula is intoxicating.
So let’s don our capes, grab our staffs, and look at some Excel formulas that are easy to use, but make you look like a wizard.
🔍 XLOOKUP – VLOOKUP, but better
What it does:
Looks up values in a range — like VLOOKUP, but without the left-hand column restriction, and with cleaner syntax.
Why it impresses:
You use XLOOKUP once in front of someone still battling INDEX/MATCH or VLOOKUP’s #N/A errors, and you’re instantly promoted to "absolute legend”.
Use case:
Find a customer's email address based on their ID
=XLOOKUP(A2, CustomerIDs, Emails, "Not found")Great for:
Cleaner lookups, more readable formulas, and not explaining why "column index number 3” keeps changing.
📋 UNIQUE – the one-click declutterer
What it does:
Returns a list of unique values from a range automatically. No helper columns, no pivot tables, no fuss.
Why it impresses:
People spend hours filtering data manually. You do it with one formula.
Use case:
Get a list of all unique product names from a giant order list
=UNIQUE(A2:A1000)Great for:
Clean reports, dropdown lists, and showing that you respect your time.
🧼 FILTER – dynamic filtering without buttons
What it does:
Filters a range based on criteria, but dynamically. Like magic, but real and not as good.
Why it impresses:
FILTER lets you build live reports that change automatically when the data changes. It feels like Excel is alive!
Use case:
Pull only the rows where region is "North"
=FILTER(A2:D100, B2:B100="North")Great for:
Dashboards, segmented reports, and convincing people you’re at one with the computer.
📊 GETPIVOTDATA – make PivotTables obey you
What it does:
This formula extracts values from PivotTables with pinpoint accuracy, like a cowboy at the Grand Corral.
Why it impresses:
You’re no longer tied to what the PivotTable gives you – you can build custom, dynamic summaries outside the Pivot.
Use case:
Grab specific figures from your Pivot. It auto-updates when the data changes, and no one has to touch the table itself.
=GETPIVOTDATA("Sales", $A$3, "Region", "North")Great for:
Dynamic dashboards and stress-free reporting.
🧪 LET – the formula tidy-up tool
What it does:
LET lets you define named variables within your formula. It feels like you're writing code! But you’re not writing code.
Why it impresses:
Instead of a monstrous formula with repeated logic, you name your variables once and call them back as needed. Elegant and efficient.
Use case:
Calculate A1*10, use it twice, but only type it once
=LET(x, A1*10, y, x*5, x + y)Great for:
Dashboards, complex metrics, or anything you need to revisit in a week and still understand.
🔧 LAMBDA – build your own Excel functions
What it does:
With LAMBDA, you can wrap a formula and save it as your own custom function, right in Excel. No macros, no VBA, just simple spreadsheet sorcery.
Why it impresses:
Creating your own Excel functions is obviously impressive. It really is!
Use case:
=LAMBDA(cost, revenue, (revenue - cost) / revenue)Then name it in Name Manager – let’s say, NetMargin – and call it like any other Excel function:
=NetMargin(B2, C2)Great for:
Reusable logic, clean reports, or convincing IT you know your stuff.
🧠 Final thoughts
Most of what accountants do day-to-day is completely opaque to the average colleague. So, every now and then, it’s nice to pull back the curtain and reveal our awesome, terrible powers – like Gandalf facing the Balrog on the Bridge of Khazad-dûm.
Except in this version, the Balrog is a messy spreadsheet, and you’re Gandalf the White, overpowering the spreadsheet as you transform from using VLOOKUP to using XLOOKUP.
Use your powers wisely – or use them unwisely – but definitely enjoy the moment of spreadsheet wizardry. 💻🪄
📚 Want to Master More Excel Magic?
If those formulas got your brain buzzing, imagine what a full Excel course could do.
Explore our full range of Excel CPD and level up your spreadsheet skills:
You need to sign in or register before you can add a contribution.