Excel for Small Business Owners: The Skills That Pay Off

Excel for small business owners comes down to a few skills that answer everyday money questions: how much came in, where it went, and what is left. You don't need macros or advanced charts. You need clean lists, a few formulas such as SUM and SUMIF, a budget, an invoice template and pivot tables. This guide shows each skill on a real example, in the order that pays off fastest.

Why Excel still helps a small business#

Excel gives you a flexible place to see your numbers your way. Accounting software records transactions, but owners often want their own views: spending by category, a budget for next quarter, or a quick "what if" on prices.

Excel is a good fit for:

• Tracking income and expenses before you need full accounting software.
• Budgets and forecasts, where you change numbers and see the effect.
• Invoices, price lists and quotes built from formulas.
• One-off questions, such as "which customer bought the most this year?"

The skills below build on each other. Start with the first and add one at a time.

Skill 1: Keep your data in clean lists#

Every useful report starts with a clean list: one row per transaction and one type of information per column. Put the headers in row 1, and avoid blank rows, merged cells and totals in the middle of the data.

Here is a month of expenses kept the right way:

Type each category the same way every time. "Supplies" and "supplies " (with a space) look alike but count as different labels in some tools. A drop-down list is the easiest way to keep them consistent.

Skill 2: Total anything with SUM and SUMIF#

SUM adds a range, and SUMIF adds only the rows that match a condition. Together they answer most money questions.

To total all October spending:

=SUM(C2:C8)

The result is 2,093.15. AutoSum does the same in one step: select the cell under the amounts and press Alt+= (Mac: ⌘+Shift+T). The SUM function guide has more examples.

To total one category, use SUMIF:

=SUMIF(B2:B8,"Supplies",C2:C8)

SUMIF checks the categories in B2:B8, finds the three "Supplies" rows and adds their amounts: 85.50 + 140.00 + 62.25 = 287.75. Put the category in a cell, such as E2, and write =SUMIF($B$2:$B$8,E2,$C$2:$C$8) to build a whole summary table by filling down. See how SUMIF adds matching numbers for criteria like dates and "greater than", and the SUMIF function guide for the syntax.

Skill 3: Build a budget versus actual sheet#

A budget sheet compares what you planned to spend with what you spent, so problems show up early. Put the categories in one column, the budget in the next, the actual spending (from SUMIF) in the third, and the difference in the fourth.

The difference is =Budget-Actual, so a negative number means you overspent. Here, Supplies is 37.75 over budget and Marketing is 75.00 under. Add a percentage column with =Actual/Budget formatted as a percentage to see how much of each budget is used. The department budget practice lesson walks through a sheet like this step by step.

Skill 4: Create invoices and a profit and loss summary#

An invoice template with formulas removes typing errors. Each line multiplies quantity by price, a SUM adds the lines, and a tax line multiplies the subtotal by the rate. Change a quantity and everything updates. The guide to building an invoice with formulas shows the full layout.

The same habits produce a simple profit and loss summary: total income minus total costs for the month. Use SUMIF to pull each income and cost category from your lists. The article on making a profit and loss statement in Excel builds one from a transaction list.

Skill 5: Plan ahead with a cash flow forecast#

A cash flow forecast shows when money will come in and go out over the next few months, so you can see a shortfall before it happens. Each month starts with the previous month's closing balance, adds expected income and subtracts expected costs:

Closing balance = Opening balance + Cash in - Cash out

In Excel, the opening balance for each month is a formula pointing at the cell for the previous month's closing balance. Change one assumption, such as a late customer payment, and the whole forecast updates. See how to build a cash flow forecast in Excel for a full template.

Skill 6: Summarise a long list with pivot tables#

A pivot table summarises a list by category, month or customer in a few clicks, with no formulas. Select a cell in your data, go to Insert > PivotTable, and drag Category into Rows and Amount into Values. Excel totals each category for you.

Pivot tables are ideal once your list grows to hundreds of rows, or when you want to switch quickly between views, such as by month and then by supplier. The first pivot tables lesson shows how to build one from a raw list.

Mistakes that cost small businesses time#

These habits cause most spreadsheet problems:

1. Typing totals instead of using formulas. A typed total doesn't update when a row changes.
2. Inconsistent labels. "Supplies", "Supply" and "supplies " split one category into three.
3. Several months in one messy sheet. Keep one list with a Date column, and let SUMIFS or a pivot table split it by month.
4. No backup. Save copies with dates in the file name, or keep files in cloud storage that keeps version history.

If you later hire help, the guide to Excel skills for bookkeepers shows what an experienced bookkeeper would expect to see in your files.

Practise it#

The quickest win is SUMIF, because it turns a list of expenses into a category report. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then build a budget with the department budget practice, or try the exercise on this page to total the Supplies spending yourself.