You don’t need to be an Excel expert to get real value from formulas in your small business. A handful of the right ones can save you hours every month, reduce manual work and make your spreadsheets work much harder for you.
So, here are the formulas I think are worth learning first.
The everyday formulas every small business should know
SUM is the obvious starting point, but it’s worth mentioning because so many people still add columns up by hand (!). It totals a range in a split second and updates itself the moment your figures change.
=SUM(A1:A10)
AVERAGE is just as simple and tells you more than people expect, whether that’s average monthly spend or average invoice value. Small formula, genuinely useful insight.
=AVERAGE(A1:A10)
Add up and count exactly what you need
SUMIF and SUMIFS let you total figures against a condition. Want to know total sales for one customer, or expenses in one category? SUMIF handles a single condition, SUMIFS handles several at once, and either one saves you a lot of manual filtering.
=SUMIF(A1:A10,”Customer A”,B1:B10)
COUNTIF and COUNTIFS work on the same logic, just counting instead of totalling. Super handy for things like how many invoices are still unpaid, or how many orders came from a particular supplier.
=COUNTIF(A1:A10,”Customer A”)
Make Excel do the checking for you
IF is where formulas start doing real work. Set up a test, such as flagging any invoice over thirty days as overdue, and Excel gives you the answer automatically instead of you checking each one by eye.
=IF(C1<=TODAY()-30,”Overdue”,””)
VLOOKUP or XLOOKUP earns its place whenever you’ve got two lists that need matching up, customer names against account numbers, for example. Rather than scrolling back and forth comparing them manually, the formula does it instantly.
=VLOOKUP(A1,Tbl_Customer,2,FALSE)
XLOOKUP is the newer, more flexible version, and should be available for Microsoft 365 users now.
=XLOOKUP(A1,Tbl_Customer[Customer],Tbl_Customer[Code],”No Code”,0)
Top Tip: XLOOKUP has built-in handling for when a match can’t be found. In the example above, it will return “No Code” rather than the #N/A error that the VLOOKUP example would return.
Keep your figures, dates and data tidy
TODAY simply returns today’s date and updates as the date changes. Pair it with IF (as in the example above) and you’ve got yourself an automatic overdue invoice flag with barely any setup.
=TODAY()
Top Tip: Use this formula with Conditional Formatting to automatically change the cell colour when an invoice becomes overdue.
ROUND keeps your figures tidy, particularly useful when you’re working with prices, VAT, wage rates or percentages and don’t want six decimal places creeping into a report.
=ROUND(B1,2) (2 decimal places)
=ROUND(B1,0) (0 decimal places)
CONCATENATE, TEXTJOIN, or the ampersand symbol, pull text together, first name and last name into one cell, or building a reference number out of a few different bits of information.
=CONCATENATE(A1,” “,B1)
=TEXTJOIN(” “,TRUE,A1,B1)
=A1&” “&B1
Make your spreadsheets smarter and more professional
TEXT lets you display a number the way you actually want it to look, i.e. a date formatted as day month year, or a value shown with a currency symbol attached, without changing the underlying figure itself. It’s a small formula that quietly makes reports look far more professional.
=TEXT(C2,”ddd dd mmmm yyyy”) returns Sat 01 August 2026
=TEXT(B2,”£0.00″) returns £3.56
IFERROR catches the moment a formula breaks, a lookup that can’t find a match, a division that hits zero, and lets you show something sensible instead of a screen full of error messages. Wrapping your trickier formulas in IFERROR is one of the simplest habits that makes a spreadsheet look properly finished.
=IFERROR(VLOOKUP(A2,Tbl_Customer,2,FALSE),”No Code”)
This is the Vlookup example from above, but with Iferror added for error handling.
Start with a real problem, not a practice spreadsheet
Things always make much more sense when they’re based on real-life data rather than fictitious scenarios or examples.
So, open a spreadsheet you already use for your business, pick one formula from this list that could save you time this week. Try it on your own data and solve a problem you actually have.
A lot of business owners tell me they learned more from fixing one real problem with one formula than from sitting through an entire course. If invoices, stock counts or a weekly rota are eating your time right now, that’s your starting point, not a random tutorial covering something you’ll never actually use.
Choose two or three formulas that solve a problem you have today, get comfortable with them, then add more as you need them. That’s how most people genuinely learn Excel, not by trying to swallow it all at once.
