Start with a clean sales table

Create one row for each transaction and one column for each type of information. A practical sales table can use these headings:

Date	Invoice No.	Customer	Product	Category	Qty	Unit Price	Amount	Status
05/08/2026	INV-1042	ABC Stores	Printer Paper	Office	4	8,500	34,000	Paid

Select the data and press Ctrl + T to convert it into an Excel Table. Name the table Sales. Tables expand automatically, keep formulas consistent, and make formulas easier to read.

10 Excel formulas for everyday business records

These examples use structured table references. Replace the table and column names with the names in your workbook.

1. Calculate each line amount=[@Qty]*[@[Unit Price]]

Use in the Amount column to multiply quantity by unit price.

2. Add total sales=SUM(Sales[Amount])

Returns the total value of all recorded sales.

3. Total a category within a date range=SUMIFS(Sales[Amount],Sales[Category],H2,Sales[Date],">="&H3,Sales[Date],"<="&H4)

Summarises sales for the category in H2 between the dates in H3 and H4.

4. Count overdue invoices=COUNTIFS(Invoices[Status],"Unpaid",Invoices[Due Date],"<"&TODAY())

Counts invoices that remain unpaid after their due date.

5. Look up a product price=XLOOKUP([@Product],Products[Product],Products[Unit Price],"Not found")

Pulls the correct unit price from a separate Products table.

6. Flag stock that needs reordering=IF([@[Stock Balance]]<=[@[Reorder Level]],"Reorder","OK")

Shows a clear action when stock reaches its reorder level.

7. Handle lookup errors clearly=IFERROR(XLOOKUP(A2,Products[Product],Products[Unit Price]),"Check product")

Replaces an error code with an instruction your team can act on.

8. Calculate profit=[@Revenue]-[@Cost]

Shows the profit on each sale before you create a summary.

9. Create a monthly reporting label=TEXT([@Date],"mmm yyyy")

Converts each transaction date into labels such as Aug 2026.

10. Display only unpaid invoices=FILTER(Invoices,Invoices[Status]="Unpaid","No unpaid invoices")

Creates a live list of outstanding invoices for follow-up.

Excel version note: XLOOKUP and FILTER are available in Microsoft 365 and current Excel releases. In older versions, use INDEX with MATCH for lookups and the built-in Filter command for lists.

Check the workbook before making decisions

Store numbers as numbers, not as text copied with currency symbols.
Enter real Excel dates and use formatting to control how they appear.
Keep raw data, calculations, and reports on separate clearly named sheets.
Use data validation for repeated entries such as Paid, Unpaid, and Overdue.
Protect formula cells so routine data entry does not overwrite them.
Keep a dated backup before importing, cleaning, or deleting records.

When formulas are no longer enough

Use a PivotTable when you need to compare sales, expenses, products, branches, or months without writing a new formula for every question. Use Power Query when you repeatedly import and clean files from bank statements, branches, or sales systems.

Start with a reliable table, then add summaries and dashboards only after the underlying records are consistent.

Build business records your team can trust

Use JENECONK training and business tools to improve invoices, reporting, document workflows, and day-to-day office productivity.

Explore Business Suite Explore Excel Training

Continue working

Direct answer

Which Excel formulas matter most for a small business?

The most useful formulas are the ones that reliably total transactions, classify records, find matching details, count exceptions and flag overdue or low-stock items.

A practical starter set is SUM/SUMIFS for totals, COUNTIF/COUNTIFS for counts, IF for status rules, XLOOKUP for matching records and date arithmetic for ageing. Keep one row per transaction and avoid typing totals manually into source data.

Check function syntax in Microsoft's official Excel function reference. Continue to inventory management, SQL analysis and Business Suite.