Twelve Excel Formulas Every Small Business Owner Should Actually Know

Not a list of a hundred functions. Twelve formulas that cover most real business spreadsheet work, with the specific situation each one solves.

Published 2026-09-07 · 4 min read

Excel has hundreds of functions. You need about twelve.

These are the ones that come up repeatedly in real business spreadsheets — tracking sales, managing stock, monitoring clients, building quotes. Each one is here because it solves a specific problem you will actually hit.

1. SUM — with the part people miss

=SUM(B2:B50)

Everyone knows this one. What most people miss is that including empty rows below your data means the total updates automatically when you add records.

=SUM(B2:B1000)

Sum a generous range. New entries are included without touching the formula. This one habit prevents the most common spreadsheet error there is — a total that quietly stops counting the last twenty rows.

2. SUMIFS — the most useful formula in business

=SUMIFS(C:C,A:A,"Paid",B:B,"Smith Ltd")

Sums column C where column A says Paid and column B says Smith Ltd.

This answers almost every business question you will ask a spreadsheet. Revenue from one client. Expenses in one category. Stock sold of one product. Hours worked by one person.

Add as many condition pairs as you need. The sum range comes first, then each pair of range and criteria.

3. COUNTIFS — the same thing, counting

=COUNTIFS(A:A,"Quoted",D:D,">1000")

How many quotes are outstanding above a thousand. Same structure as SUMIFS without the leading sum range.

4. XLOOKUP — pulling data between sheets

=XLOOKUP(A2,Products!A:A,Products!C:C)

Find the value in A2 within the products sheet's column A, return the matching value from column C.

This is how you avoid retyping information. Enter a product code, the price appears. Enter a client ID, the contact details appear.

If your Excel version does not have XLOOKUP, the older equivalent is:

=VLOOKUP(A2,Products!A:C,3,FALSE)

XLOOKUP is better — it looks left as well as right and does not break when columns are inserted. Use it if you have it.

5. IFERROR — hiding the noise

=IFERROR(XLOOKUP(A2,Products!A:A,Products!C:C),"Not found")

Lookups fail. Divisions hit zero. Without error trapping your sheet fills with error codes and stops being readable.

Wrap anything that can fail. Return something meaningful — a blank, a zero, or a short message.

6. TODAY — dates that move

=TODAY()

Returns the current date, updating every day the file opens.

Its value is in subtraction:

=TODAY()-C2

Days since an invoice was sent, days since last contact, days until a deadline. Nearly every overdue calculation starts here.

7. IF — the decision

=IF(D2<TODAY(),"OVERDUE","")

If the due date has passed, say OVERDUE, otherwise nothing.

Keep them simple. Deeply nested IF statements become unreadable within a month. If you need more than two levels, use IFS or a lookup table instead.

8. EOMONTH — monthly reporting made possible

=EOMONTH(TODAY(),0)

Last day of the current month. Change the zero to -1 for last month, 1 for next month.

Combined with SUMIFS this gives you monthly figures that never need updating:

=SUMIFS(C:C,A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<="&EOMONTH(TODAY(),0))

Everything in column C dated within the current month. This formula is the backbone of most reporting sheets.

9. TEXT — dates that read properly

=TEXT(A2,"dd mmm yyyy")

Turns a date into readable text. Useful in headings and anywhere you are joining a date to other text.

="Report for "&TEXT(TODAY(),"mmmm yyyy")

Produces a heading that updates itself.

10. ROUND — because money needs it

=ROUND(B2*0.2,2)

Two decimal places. Without rounding, calculated currency values carry long decimal tails and your totals disagree with your line items by a cent — which looks careless on anything a client sees.

Round anything involving money at the point of calculation.

11. MAXIFS — the most recent one

=MAXIFS(Log!A:A,Log!B:B,A2)

The latest date in the log for this record.

This is how a summary sheet stays current without manual updating. Last contact date, last order date, last payment date — all pulled automatically from a log sheet.

12. Absolute references — the dollar signs

Not a formula, but the thing that breaks more spreadsheets than any function.

=B2*$F$1

The dollar signs lock cell F1 in place. Copy the formula down and B2 becomes B3, B4, B5 — while F1 stays F1.

Use it for anything sitting in one cell that many formulas reference: a tax rate, an exchange rate, a markup percentage.

You can lock one part only. $F1 locks the column, F$1 locks the row. That mixed form is what makes conditional formatting rules work across a whole row.

Two habits worth more than more formulas

Put your constants in labelled cells. Tax rate, markup, hourly rate — each in its own cell with a label beside it, referenced absolutely. Never type a rate directly into a formula. When it changes, you want to update one cell, not hunt through forty formulas.

Build the formula on one row first, check it is right, then copy down. Copying a wrong formula to five hundred rows and finding out later is a bad afternoon.

What to learn next, and when

Pivot tables, once you are regularly summarising more than a few hundred rows. They do in thirty seconds what takes twenty formulas.

Named ranges, once your formulas reference so many sheets that reading them is hard.

Power Query, once you are importing data from elsewhere repeatedly.

But none of those before the twelve above are automatic. These twelve cover the overwhelming majority of real business spreadsheet work, and knowing them properly beats knowing forty functions vaguely.