Guides · Spreadsheets
10 Excel Formulas Every Small Business Owner Should Know
You don't need to be a spreadsheet person to run a business on spreadsheets. About ten formulas cover invoices, inventory, sales tracking, payroll hours and pricing. Each one below works in both Excel and Google Sheets, with an example you can adapt.
1. SUM: the total of a range
=SUM(D2:D200)
Adds every number in D2 through D200. Use whole columns (D:D) on a sheet that only holds data, so new rows are always included.
2. SUMIFS: totals that match conditions
=SUMIFS(D:D, B:B, "Acme Co", C:C, "Paid")
Total of column D where the customer is Acme Co and the status is Paid. This one formula replaces most manual filtering: sales by customer, expenses by category, revenue by month.
3. COUNTIFS: how many match
=COUNTIFS(C:C, "Unpaid", E:E, "<"&TODAY())
Counts invoices that are unpaid and past their due date in column E. Put it at the top of your invoice sheet and you'll never lose track of what's overdue.
4. XLOOKUP: find a value in a table
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")
Looks up the product code in A2 on your Products sheet and returns its price from column C. It's the modern replacement for VLOOKUP: it can look left, it doesn't break when you insert columns, and it lets you choose what shows when nothing matches. (Older Excel? Use =INDEX(Products!C:C, MATCH(A2, Products!A:A, 0)).)
5. IF: make a decision
=IF(F2>=500, F2*0.9, F2)
Applies a 10% discount to orders of $500 or more. Combine with AND/OR for more conditions: =IF(AND(C2="Paid", D2>0), "Ship", "Hold").
6. IFERROR: hide ugly errors
=IFERROR(B2/C2, 0)
Shows 0 instead of #DIV/0! when C2 is empty. Wrap it around lookups and divisions on anything a customer might see.
7. ROUND: money to the cent
=ROUND(D2*0.0825, 2)
Sales tax at 8.25%, rounded to cents. Formatting a cell to show two decimals doesn't change the stored value; ROUND does, which keeps totals matching your invoices exactly.
8. TODAY and EOMONTH: dates that update themselves
=E2-TODAY() gives days until a due date (negative means overdue).=EOMONTH(TODAY(), 0) gives the last day of this month; =EOMONTH(A2, 0)+15 gives "net 15 after month end" terms.
9. TEXT: labels for grouping
=TEXT(A2, "yyyy-mm")
Turns a date into "2026-10", perfect for monthly totals with SUMIFS. =TEXT(A2, "ddd") gives the weekday, handy for spotting your busiest days.
10. UNIQUE and SORT: instant lists
=SORT(UNIQUE(B2:B500))
A clean, alphabetical list of every customer (or product, or vendor) that appears in column B, which updates as you add rows. Pair it with SUMIFS next to it for a live "sales by customer" report.
Putting them together: a one-sheet invoice tracker
| Column | Contents |
|---|---|
| A | Invoice date |
| B | Customer |
| C | Status (dropdown: Unpaid / Paid) |
| D | Amount |
| E | Due date: =A2+30 |
| F | Days left: =IF(C2="Paid", "", E2-TODAY()) |
Add =SUMIFS(D:D, C:C, "Unpaid") at the top for money owed to you, and conditional formatting on F to turn negative numbers red. That's a working accounts-receivable system in six columns.
If you'd rather not build it, Chimera makes working spreadsheets with real formulas and a short note on how to use them. Describe what you want it to track; she quotes it first and you see it before you pay. New to budgets? Start with our budget spreadsheet guide.