Excel for Small Business: 6 Practical Uses and a Step-by-Step Example

Excel for small business is often the first real management tool a company has, and for many it stays the main one for years. It is already on your computer, it costs little, and one well-built workbook can tell you how much cash you will have next month, which clients owe you money and what to reorder before you run out.

The catch is that a messy spreadsheet is worse than none. This guide shows the six jobs Excel does best in a small business, walks you through building a cash flow tracker step by step, and ends with the signs that it is time to move part of the work to dedicated software.

In this guide

• What a small business can run in Excel
• Step by step: build a cash flow tracker
• Track invoices and payment reminders
• Keep an eye on stock
• Build a budget and compare it with actuals
• Analyze sales with PivotTables
• Put it together in a one-page dashboard
• The limits of Excel: when to switch
• The Excel skills that matter most
• FAQ

What a small business can run in Excel

You don’t need dozens of files. Most small businesses get the most value from a handful of workbooks built on the same principles: data in tables, summaries calculated from those tables, and no numbers typed twice.

Job	What you track	Key Excel features
Cash flow	Money in and out, week by week, and the balance ahead	Tables, SUMIFS, EOMONTH, conditional formatting
Invoices and reminders	Who owes what, since when, and who to chase	Date formulas, IFS, filters, invoice templates
Stock	Quantities on hand, reorder points, stock value	SUMIFS, XLOOKUP, conditional formatting
Budget	Planned vs actual revenue and costs by month	SUMIFS, variance formulas, Goal Seek
Sales	Revenue by product, client, channel and month	PivotTables, slicers, timelines
Dashboard	The few numbers you check every Monday	PivotCharts, KPI cells, sheet protection

Step by step: build a cash flow tracker

Cash is what keeps a small business alive, and a profit on paper does not pay rent. This tracker shows your balance month by month, including the months ahead. It takes about an hour to build.

1. Record every transaction in one table

On a sheet called Transactions, create five columns: Date, Description, Category, Type and Amount. Select them and press Ctrl+T to turn the range into a table, then name it tblCash on the Table Design tab. Enter amounts as positive numbers and use Type to say whether the money comes In or goes Out.

2. Lock down the categories with drop-down lists

Select the Category column, go to Data > Data Validation, choose List, and enter your categories: Sales, Rent, Payroll, Supplies, Marketing, Taxes, Other. Do the same for Type with In and Out. Consistent labels are what make every summary later on reliable.

3. Summarize by month

On a new sheet, put the first day of each month in column A (1/1/2026, 2/1/2026 and so on) and add these formulas in row 2, then copy them down:

• Cash in (B2): =SUMIFS(tblCash[Amount],tblCash[Type],”In”,tblCash[Date],”>=”&A2,tblCash[Date],”<=”&EOMONTH(A2,0))
• Cash out (C2): the same formula with “Out” instead of “In”
• Net (D2): =B2-C2
• Closing balance (E2): =OpeningBalance+D2 in the first month, then =E2+D3 in the next row, and so on

OpeningBalance is a named cell holding your bank balance on the day you start. Name it from the Name Box to the left of the formula bar.

4. Add the months ahead

A tracker that only looks back is a record, not a planning tool. Add the payments you already know about (rent, payroll, loan repayments, tax installments) and the invoices you expect to be paid, with their expected dates. The closing balance for the coming months now becomes a forecast.

5. Flag the danger zone

Select the closing balance column, go to Home > Conditional Formatting > Highlight Cells Rules > Less Than, and enter your safety threshold, for example one month of fixed costs. Any month that turns red is a month to act on now: chase invoices, delay a purchase or arrange financing early.

Track invoices and payment reminders

Excel includes ready-made invoice templates (File > New, then search for “invoice”), which work fine for a low number of invoices. The more useful part is the log of invoices you have sent. Create a table with Invoice #, Client, Invoice date, Amount, Terms (in days) and Paid date, then add three calculated columns:

• Due date: =[@[Invoice date]]+[@Terms]
• Days overdue: =IF([@[Paid date]]=””,MAX(0,TODAY()-[@[Due date]]),0)
• Status: =IFS([@[Paid date]]<>””,”Paid”,[@[Days overdue]]=0,”Not due”,[@[Days overdue]]<=30,”1-30 days late”,TRUE,”30+ days late”)

Filter on Status every Monday and you have your reminder list. Sort it by amount to chase the biggest balances first. For a complete aging report with 30, 60 and 90-day buckets, the method in our guide to Excel for accounting scales well beyond a few clients.

Keep an eye on stock

For a shop, a workshop or an online store with a few hundred items, two tables are enough:

• Items: SKU, Name, Unit cost, Reorder point.
• Movements: Date, SKU, Qty in, Qty out. Every delivery and every sale is one row.

In the Items table, add a Stock on hand column with =SUMIFS(tblMoves[Qty in],tblMoves[SKU],[@SKU])-SUMIFS(tblMoves[Qty out],tblMoves[SKU],[@SKU]), a Stock value column (stock on hand times unit cost) and a Reorder flag with =IF([@[Stock on hand]]<=[@[Reorder point]],”Reorder”,””). Use XLOOKUP in the Movements table to display the item name next to each SKU, so typing errors show up immediately.

This works as long as one person updates it and stock sits in one place. Several locations, barcode scanning or automatic sync with an online store are where an inventory system earns its price.

Build a budget and compare it with actuals

List your categories in rows and the twelve months in columns, and enter what you plan to earn and spend. Next to it, build the same grid for actuals with SUMIFS formulas that read from tblCash, so actual figures fill in as you record transactions. A third grid shows the variance (actual minus budget), with conditional formatting on anything more than 10% off.

Two what-if tools are worth knowing here. Goal Seek (Data > What-If Analysis) finds the monthly revenue you need to break even. Scenario Manager lets you save an optimistic, a realistic and a cautious version of the same budget. The guide to Excel for finance goes further with data tables and NPV for investment decisions.

Analyze sales with PivotTables

If your point-of-sale system, online store or invoicing tool exports sales to a file, a PivotTable turns that export into answers in a minute: revenue by month, best-selling products, top ten clients, average order value by channel. Put the export in a table, insert a PivotTable, drag Month to Rows, Product to Columns and Revenue to Values, then add a slicer for the sales channel.

Right-click the revenue figures and choose Show Values As > % of Grand Total to see which products or clients really carry the business. Most owners are surprised by the answer. Our step-by-step PivotTable tutorial covers grouping, slicers, timelines and calculated fields in detail, with practice exercises.

Put it together in a one-page dashboard

A small business dashboard should fit on one screen and answer the questions you ask every week. Four or five numbers are usually enough:

• Cash balance today and the lowest forecast balance in the next three months
• Revenue this month compared with the same month last year
• Total overdue receivables
• Number of items to reorder
• Gross margin for the month

Place these KPI cells at the top of a new sheet, each one pulling from the workbooks above with a simple formula. Below them, add one or two PivotCharts (revenue by month, revenue by product) and a timeline to change the period. Then protect the sheet (Review > Protect Sheet) so nobody overwrites a formula by accident.

The limits of Excel: when to switch to dedicated software

Excel is flexible, and that is also its weakness: nothing stops anyone from typing over a formula, and there is no built-in approval workflow or audit trail. These signs tell you it’s time to move a task elsewhere:

Warning sign	Why Excel struggles	What to look at instead
Several people edit the same file every day	Co-authoring needs the file in OneDrive or SharePoint, and simultaneous edits can still conflict	A shared app or a database built for the task
You run payroll or file sales tax	Rules and rates change and errors are costly	Payroll software or an accountant
Bookkeeping takes hours every month	No automatic bank feeds, no double-entry controls	Accounting software
Stock across several locations or channels	No real-time sync with your store or scanners	An inventory management system
Files slow down or reach hundreds of thousands of rows	A sheet holds 1,048,576 rows, and performance drops well before that	Power Pivot, a database or a BI tool
You send many recurring invoices	Manual sending and no online payment	Invoicing software

Moving one task out of Excel doesn’t mean dropping Excel. Most accounting and sales tools export to Excel, and the analysis skills in this guide stay useful for whatever those systems can’t report on.

Whatever tool you use, keep the records behind your numbers. The IRS generally recommends keeping records that support income or deductions for at least three years, and longer in some situations. Check the IRS guidance on how long to keep records and confirm with your accountant.

The Excel skills that matter most for a small business

You don’t need to be an Excel expert to run the workbooks above, but a few skills make the difference between a file you trust and one you double-check by hand:

• Tables and structured references, so formulas follow new rows.
• SUMIFS, COUNTIFS and XLOOKUP, the formulas behind almost every summary here.
• Date functions such as TODAY and EOMONTH for due dates and monthly views.
• Data validation and conditional formatting to stop bad entries and highlight problems.
• PivotTables, slicers and PivotCharts for sales analysis and dashboards.
• Power Query to clean and combine exports from your bank, store or accounting tool with one click each month.

If some of these are new, the Excel crash course covers the basics in a weekend, and the advanced Excel skills checklist includes a quick self-test to find your level. To learn them in order with practice files, a structured online Excel course from beginner to expert is faster than piecing together tutorials one problem at a time.

Excel for small business FAQ

Is Excel enough to run the finances of a small business?

For a very small business, Excel can handle cash flow, invoices, a budget and sales analysis well. Once bookkeeping, payroll or sales tax becomes regular work, dedicated accounting or payroll software is safer, and Excel stays useful for analysis and planning on top of it.

What Excel skills does a small business owner need?

Tables, SUMIFS, XLOOKUP, date functions, data validation, conditional formatting and PivotTables cover almost everything in this guide. Power Query is a strong addition if you regularly clean exports from other tools.

Can I create invoices in Excel?

Yes. Excel includes invoice templates you can customize with your logo and details. Keep a separate invoice log table so you can track due dates, payments and overdue amounts, which a template alone does not do.

Should I use Excel or accounting software?

Use accounting software for the books themselves: bank feeds, double-entry records and tax reports. Use Excel for what those tools do less well, such as cash forecasts, budgets, what-if scenarios and custom sales analysis. Many small businesses use both.

Do I need Microsoft 365 for these templates?

Most of the formulas here work in recent versions of Excel, but XLOOKUP and dynamic arrays require Excel for Microsoft 365 or Excel 2021 and later. Microsoft 365 also makes co-authoring through OneDrive or SharePoint easier.