Business Accounting & Bookkeeping in Microsoft Excel: The Complete Guide | LearnTCard

LearnTCard.com · Practical Skills for Real Careers · Browse All Guides →

✦ Excel Accounting & Bookkeeping · LearnTCard

Run Your Entire Business
Finances in Microsoft Excel

A complete, practical guide to bookkeeping, invoicing, payroll, financial reporting, and tax preparation — all built in Excel, with real formulas, spreadsheet templates, and step-by-step examples for every skill level.

🌱 Beginner ⚡ Intermediate 🚀 Advanced

Dedicated accounting software like QuickBooks costs anywhere from $30 to $200 per month. But for millions of freelancers, small business owners, sole traders, startups, and non-profits worldwide, Microsoft Excel is already installed, already paid for, and more than capable of handling every core accounting and bookkeeping function a small business needs. With the right structure, the right formulas, and an understanding of accounting fundamentals, Excel can do everything from tracking your first expense to generating a balance sheet that satisfies your auditors.

This guide from LearnTCard.com teaches you to build a complete, professional-grade bookkeeping system entirely in Excel. Every section includes real spreadsheet examples with actual formulas you can copy directly into your own workbook. We cover the same financial management tasks that professional accounting software handles — just built by you, in Excel, with full transparency and zero monthly fees.

Why Excel Works for Small Business Accounting

Excel is not a second-best substitute for accounting software — for the right business, it is the ideal tool. It is infinitely customisable to your specific business structure, it requires no subscription, every formula and number is fully transparent and auditable, it integrates naturally with the rest of your Microsoft Office workflow, and the skills you develop using it are transferable to any industry and any employer. The accounting principles that govern an Excel bookkeeping system are identical to those used in QuickBooks, Xero, or Sage — the only difference is that in Excel, you build and control the system yourself.

Small business owner reviewing financial documents and laptop, representing Excel-based bookkeeping and accounting

For millions of small business owners, Microsoft Excel is the most powerful, flexible, and cost-effective accounting tool available — and this guide shows you exactly how to use it.

Excel-based bookkeeping is most suitable for: sole traders and freelancers, businesses with fewer than 50 monthly transactions, startups managing costs carefully, and businesses whose accounting needs are straightforward (no complex multi-entity structures or large inventory operations). If your business grows beyond what Excel can comfortably manage, the accounting knowledge you build here translates directly to any dedicated software platform.

ℹ️ What You Will Build in This Guide

By the end of this guide, you will have a complete, professionally structured Excel accounting workbook with: a Chart of Accounts, a General Ledger transaction log, a professional invoice template, an expense tracker, a bank reconciliation sheet, an accounts receivable tracker, a Profit & Loss Statement, a Balance Sheet, a Cash Flow tracker, a payroll calculator, and a KPI dashboard — all linked together with formulas that update automatically.

Setting Up Your Excel Accounting Workbook

Your entire accounting system will live in a single Excel workbook with multiple worksheets — one worksheet per function. This single-workbook approach keeps everything in one file, allows formulas to reference data across sheets, and makes backing up your records as simple as copying one file. Structure your workbook with these worksheet tabs, in this order:

Business Accounts 2025.xlsx

📋 COA

📒 Ledger

🧾 Invoices

💳 Expenses

🏦 Bank Rec

📊 P&L

⚖️ Balance Sheet

💧 Cash Flow

👥 Payroll

🎯 Dashboard

Create the workbook and name it clearly

Open Excel → Save As → name it "YourBusinessName Accounts 2025.xlsx." Store it in a cloud-synced folder (OneDrive, Google Drive, or Dropbox) for automatic backup. Never keep your accounts file only on a local hard drive.

Create and name each worksheet tab

Right-click the default "Sheet1" tab → Rename → type "COA" (Chart of Accounts). Right-click the tab again → Tab Color → choose a colour to visually group related sheets. Add new sheets with the + button and repeat for each tab in the structure above.

Set up a Cover Sheet

Add a "Cover" tab as the first sheet. Record: Business Name, Fiscal Year, Owner Name, Last Updated date (using =TODAY()), and a brief description of each worksheet tab. This orientation sheet is invaluable when sharing with an accountant.

Protect formula sheets

Sheets like P&L and Balance Sheet should have their formula cells locked (Review → Protect Sheet) to prevent accidental overwriting. Leave only data-entry cells unlocked. Set a password and record it safely.

Building Your Chart of Accounts in Excel

The Chart of Accounts (COA) is the master list of every financial category in your business. Every transaction you record — every invoice paid, every expense incurred — is assigned to an account in this list. In Excel, your COA lives on its own worksheet and is referenced by every other sheet in the workbook. Build it once and it becomes the backbone of your entire system.

There are five fundamental account types. Every account in your COA must belong to one of these five types, and the type determines which financial statement it appears on:

🏦 Asset — What You Own

Resources with Value

Bank accounts, cash, money owed to you (receivables), equipment, vehicles, prepaid expenses, inventory. Assets appear on the Balance Sheet.

⚠️ Liability — What You Owe

Obligations & Debts

Bills you owe suppliers (payables), loans, credit card balances, VAT/sales tax collected, deferred income. Liabilities appear on the Balance Sheet.

◆ Equity — Your Ownership

Net Worth in the Business

Owner's capital invested, retained earnings, owner's drawings. Equity = Assets − Liabilities. Appears on the Balance Sheet.

📈 Income — Revenue Earned

Money Flowing In

Sales revenue, service fees, consulting income, rental income, interest received, other operating income. Appears on the Profit & Loss.

📉 Expense — Money Spent

Costs of Running the Business

Rent, utilities, salaries, software subscriptions, marketing, supplies, professional fees, depreciation. Appears on the Profit & Loss.

Practical Example: Chart of Accounts for a Freelance Consultant

Marcus runs a freelance management consultancy from home. Here is his complete COA worksheet in Excel, with account numbers for easy reference.

COA — Chart of Accounts

	A	B	C	D
1	Account No.	Account Name	Type	Notes
2	1000	ASSETS
3	1010	Business Current Account	Asset	Barclays Bank ****4821
4	1020	PayPal Business Account	Asset	Online payments received
5	1100	Accounts Receivable	Asset	Invoices sent, not yet paid
6	1200	Laptop & Equipment	Asset	Business equipment at cost
7	2000	LIABILITIES
8	2010	Accounts Payable	Liability	Bills received, not yet paid
9	2020	Business Credit Card	Liability	Amex Business Gold
10	2030	VAT Payable	Liability	VAT collected, owed to HMRC
11	4000	INCOME
12	4010	Consulting Fees	Income	Primary service revenue
13	4020	Training & Workshops	Income	Group training sessions
14	4030	Report & Document Writing	Income	Deliverable-based work
15	5000	EXPENSES
16	5010	Home Office (% of rent)	Expense	25% of monthly rent
17	5020	Software Subscriptions	Expense	MS 365, Zoom, Canva, etc.
18	5030	Professional Development	Expense	Courses, books, conferences
19	5040	Travel & Transport	Expense	Client visits, mileage
20	5050	Marketing & Advertising	Expense	LinkedIn ads, website
21	5060	Professional Fees	Expense	Accountant, solicitor
22	5070	Bank Charges & Fees	Expense	Bank fees, PayPal fees

Marcus names this range COA_Table (select A1:D22 → Formulas → Name Manager → New → "COA_Table"). Every other worksheet in his workbook uses =IFERROR(VLOOKUP(acct_no,COA_Table,2,FALSE),"") to pull account names automatically by number — so if he ever renames an account, the change cascades everywhere.

Recording Daily Transactions — The General Ledger

The General Ledger is your central transaction log — a chronological record of every financial event in your business. Every invoice sent, every bill paid, every bank transfer, every expense — goes here first. In accounting, every transaction has two sides (debits and credits), but for a simplified Excel system for small businesses, a single-entry format (Date, Description, Category, Amount In, Amount Out, Balance) works perfectly well and is immediately understandable without an accounting degree.

Practical Example: Monthly Transaction Ledger

Priya runs a small events planning business. Here is her General Ledger for January — a structured Excel table with running balance calculated automatically.

Ledger — January 2025

	A	B	C	D	E	F	G
1	Date	Ref No.	Description	Account Code	Money In (£)	Money Out (£)	Running Balance (£)
2	01/01/2025	OB-001	Opening Balance	1010	3,200.00	—	=E2-F2
3	03/01/2025	INV-001	Invoice paid — Bloom Events Ltd	4010	2,400.00	—	=G2+E3-F3
4	05/01/2025	EXP-001	Venue hire deposit — Spring Gala	5080	—	800.00	=G3+E4-F4
5	07/01/2025	EXP-002	Zoom Pro subscription (annual)	5020	—	14.99	=G4+E5-F5
6	10/01/2025	INV-002	Invoice paid — Heritage Corp	4010	3,750.00	—	=G5+E6-F6
7	14/01/2025	EXP-003	Printing & stationery	5090	—	47.50	=G6+E7-F7
8	18/01/2025	EXP-004	Train tickets — client visit	5040	—	62.80	=G7+E8-F8
9	22/01/2025	INV-003	Deposit received — March Wedding	4020	1,500.00	—	=G8+E9-F9
10	28/01/2025	EXP-005	Bank monthly fee	5070	—	8.00	=G9+E10-F10
11	JANUARY TOTALS →				=SUM(E2:E10)	=SUM(F2:F10)	=G10

The Running Balance formula in G3 is: =G2+E3-F3. Copy this formula all the way down — it always takes the previous row's balance, adds any Money In, and subtracts any Money Out. The January total row uses =SUM(E2:E10) and =SUM(F2:F10) for the totals and =G10 for the closing balance. Converting this range to an Excel Table (Ctrl+T) makes it auto-expand as new rows are added.

✅ Best Practice — Reference Numbers

Always assign a unique reference number to every transaction (INV-001, EXP-001, etc.). This lets you quickly look up any transaction using =VLOOKUP("INV-001",Ledger,3,FALSE) and makes audit trails clear. Use a consistent numbering system: INV for invoices, EXP for expenses, PAY for payroll runs, JNL for journal adjustments.

Creating Professional Invoices in Excel

A professionally designed Excel invoice template makes an immediate impression on clients and ensures every invoice contains the information required for legal compliance and prompt payment. Your Excel invoice template should auto-calculate totals, automatically number invoices, and be printable to PDF with one click. Build it once and duplicate it for every new invoice.

Professional reviewing an invoice on a laptop representing Excel invoice creation and accounts receivable management

A well-structured Excel invoice template auto-calculates totals, applies tax, and can be exported to PDF for professional client delivery — no accounting software required.

Practical Example: Complete Excel Invoice Template with Formulas

Daniel is a freelance web developer. Here is his Excel invoice template layout with the exact formulas that make it work automatically.

Invoice Template — INV-2025-014

	A	B	C	D	E
1	DANIEL OSEI WEB DEVELOPMENT			INVOICE
2	dan@doseiweb.co.uk \| +44 7700 123456			Invoice No:	INV-2025-014
3	12 Maple Street, Bristol, BS1 4PL			Invoice Date:	=TODAY()
4	UTR: 1234 567890			Due Date:	=E3+30
6	Bill To:
7	Thornton Digital Agency
8	accounts@thorntondigital.com
10	Description	Qty / Hrs	Rate (£)	Line Total (£)
11	Website Redesign — Front End Development	40	65.00	=B11*C11
12	CMS Integration (WordPress)	12	65.00	=B12*C12
13	SEO Optimisation & Meta Setup	1	350.00	=B13*C13
14	Hosting Setup & Configuration	1	120.00	=B14*C14
16				Subtotal:	=SUM(E11:E14)
17				VAT (20%):	=E16*0.20
18				TOTAL DUE:	=E16+E17
20	Payment: BACS to Sort Code 20-44-18, Acc No. 12345678 \| Ref: INV-2025-014 \| Due: 30 days

Key formulas explained: E3 uses =TODAY() so the invoice date always shows today when opened. E4 uses =E3+30 to set the due date exactly 30 days from invoice date. Each line total is =Qty*Rate. The subtotal is =SUM(E11:E14). VAT is =E16*0.20 — change 0.20 to whatever rate applies. Total Due is =E16+E17.

To create a new invoice: Right-click the Invoice sheet tab → Move or Copy → Create a copy → rename to "INV-2025-015." Update only the invoice number, client details, and line items. All formulas recalculate automatically.

To export as PDF: File → Export → Create PDF/XPS. Set print area first: Page Layout → Print Area → Set Print Area (select only the invoice cells, not the entire sheet).

Tracking Expenses & Bills

Your Expenses sheet records every business cost in a structured format that feeds directly into your Profit & Loss report. The key design principle: every expense must have a Category that matches an account in your Chart of Accounts. This consistency is what allows SUMIF formulas to automatically populate your P&L report.

Practical Example: Monthly Expense Tracker with Auto-Category Totals

Kenji runs a digital marketing agency. His Expenses sheet captures all spending for the month, and a summary section at the bottom uses SUMIF to total by category automatically.

Expenses — March 2025

	A	B	C	D	E
1	Date	Supplier	Description	Category	Amount (£)
2	01/03/2025	Meta Ads	Facebook ad spend — March	Marketing	420.00
3	03/03/2025	Adobe	Creative Cloud subscription	Software	54.99
4	05/03/2025	Google	Google Workspace monthly	Software	14.40
5	07/03/2025	WeWork	Coworking desk — 8 days	Rent & Office	280.00
6	10/03/2025	TfL	Monthly travel card	Travel	168.00
7	15/03/2025	Amazon	Office supplies & printer ink	Office Supplies	43.28
8	20/03/2025	Coursera	Digital marketing course	Training	79.00
9	28/03/2025	Barclays	Monthly account fee	Bank Charges	8.00
11	CATEGORY SUMMARY				Total
12	Marketing				=SUMIF($D$2:$D$100,"Marketing",$E$2:$E$100)
13	Software				=SUMIF($D$2:$D$100,"Software",$E$2:$E$100)
14	Rent & Office				=SUMIF($D$2:$D$100,"Rent & Office",$E$2:$E$100)
15	Travel				=SUMIF($D$2:$D$100,"Travel",$E$2:$E$100)
16	TOTAL EXPENSES				=SUM(E12:E15)

The Category column (D) uses Data Validation → List to ensure staff can only select from approved category names — preventing the typos that break SUMIF totals. The summary SUMIF formula =SUMIF($D$2:$D$100,"Marketing",$E$2:$E$100) adds every row where column D equals "Marketing." These category totals feed directly into the P&L report using cell references like ='Expenses'!E12.

Bank Reconciliation Spreadsheet

Bank reconciliation is the process of confirming that your Excel ledger matches your actual bank statement — and finding and correcting any differences. Done monthly, it is the most important financial control habit a business owner can maintain. In Excel, you build a dedicated Reconciliation sheet that compares your book balance to your bank statement balance and clearly shows any discrepancy.

Practical Example: Monthly Bank Reconciliation in Excel

James runs a small catering business. His February bank statement shows a closing balance of £8,492.30. He builds a reconciliation sheet to confirm his Excel ledger matches.

Bank Reconciliation — February 2025

	A	B	C
1	BANK RECONCILIATION — FEBRUARY 2025		Amount (£)
3	BANK STATEMENT SECTION
4	Closing balance per bank statement (28 Feb)		8,492.30
5	ADD: Deposits in transit (recorded in books, not yet on statement)		=SUM(F10:F20)
6	LESS: Outstanding cheques (issued but not yet cleared)		=SUM(G10:G20)
7	Adjusted Bank Balance		=C4+C5-C6
9	BOOKS SECTION
10	Balance per Excel Ledger (28 Feb)		='Ledger'!G[last row]
11	ADD: Interest received (not yet recorded in books)		4.80
12	LESS: Bank charges not yet recorded in books		8.00
13	Adjusted Book Balance		=C10+C11-C12
15	RECONCILIATION DIFFERENCE		=C7-C13
16	Status		=IF(C15=0,"✅ RECONCILED","⚠ DIFFERENCE — Investigate")

The reconciliation is complete when C15 (the Difference) equals zero. The IF formula in C16 shows "✅ RECONCILED" when it does, or "⚠ DIFFERENCE — Investigate" when it doesn't. When James finds a discrepancy, common causes to check are: a bank charge he forgot to record, a duplicate transaction entry, a transposition error (e.g., £82 entered as £28), or a deposit recorded in the wrong month. After reconciling, James records any adjustments (like the bank charges) directly in his Ledger to bring the book balance in line.

Accounts Receivable Tracker

Accounts Receivable (AR) is the total of all invoices you have sent that have not yet been paid. Your AR tracker is your cash collection dashboard — it shows at a glance which clients owe you money, how much, and how overdue each invoice is. In Excel, this is a structured table with automated age-banding calculated from today's date.

Practical Example: Invoice Aging Report

Nina runs a graphic design studio. She tracks all outstanding invoices in her AR sheet. Excel automatically calculates how many days each invoice is overdue and categorises them by age band.

Accounts Receivable Aging — as at today

	A	B	C	D	E	F
1	Invoice No.	Client	Due Date	Amount (£)	Days Overdue	Age Band
2	INV-047	Apex Corp	15/01/2025	1,800.00	=TODAY()-C2	=IF(E2<0,"Not Due",IF(E2<=30,"1–30 Days",IF(E2<=60,"31–60 Days","60+ Days")))
3	INV-051	BrightStar Ltd	01/02/2025	950.00	=TODAY()-C3	=IF(E3<0,"Not Due",...)
4	INV-054	Nova Media	28/02/2025	2,200.00	=TODAY()-C4	=IF(E4<0,"Not Due",...)
5	INV-058	Green Valley Co.	15/03/2025	675.00	=TODAY()-C5	=IF(E5<0,"Not Due",...)
7	AGING SUMMARY			£ Owed	No. Invoices
8	Not Yet Due			=SUMIF(F2:F100,"Not Due",D2:D100)	=COUNTIF(F2:F100,"Not Due")
9	1–30 Days Overdue			=SUMIF(F2:F100,"1–30 Days",D2:D100)	=COUNTIF(F2:F100,"1–30 Days")
10	31–60 Days Overdue			=SUMIF(F2:F100,"31–60 Days",D2:D100)	=COUNTIF(F2:F100,"31–60 Days")
11	60+ Days Overdue			=SUMIF(F2:F100,"60+ Days",D2:D100)	=COUNTIF(F2:F100,"60+ Days")
12	TOTAL RECEIVABLE			=SUM(D8:D11)	=SUM(E8:E11)

Column E formula: =TODAY()-C2 automatically recalculates every time the file opens, so the overdue days are always current. Conditional Formatting makes 31–60 day rows amber and 60+ day rows red. Nina uses this sheet every Monday morning to decide which clients to chase for payment — the red rows get an immediate phone call, not just an email.

Profit & Loss Statement in Excel

The Profit & Loss (P&L) statement — also called the Income Statement — summarises all revenue and all expenses for a period and shows whether your business made a profit or a loss. In Excel, your P&L is powered by SUMIF formulas that pull category totals from your Expenses sheet and Ledger, meaning it updates automatically every time you add a new transaction.

Practical Example: Automated Monthly P&L Statement

Amara runs an online tutoring business. Her P&L sheet uses SUMIF to pull from her Ledger and Expenses sheet automatically. The report updates the moment new transactions are entered.

P&L — Profit & Loss Statement — January 2025

	A	B	C
1	PROFIT & LOSS STATEMENT		January 2025
3	INCOME
4	Online Tutoring Sessions		=SUMIF(Ledger!D:D,"4010",Ledger!E:E)
5	Course Sales (Digital)		=SUMIF(Ledger!D:D,"4020",Ledger!E:E)
6	Group Workshops		=SUMIF(Ledger!D:D,"4030",Ledger!E:E)
7	TOTAL INCOME		=SUM(C4:C6)
9	EXPENSES
10	Software Subscriptions		=SUMIF(Expenses!D:D,"Software",Expenses!E:E)
11	Marketing & Advertising		=SUMIF(Expenses!D:D,"Marketing",Expenses!E:E)
12	Home Office		=SUMIF(Expenses!D:D,"Home Office",Expenses!E:E)
13	Professional Development		=SUMIF(Expenses!D:D,"Training",Expenses!E:E)
14	Bank Charges		=SUMIF(Expenses!D:D,"Bank Charges",Expenses!E:E)
15	TOTAL EXPENSES		=SUM(C10:C14)
17	NET PROFIT / (LOSS)		=C7-C15
18	Net Profit Margin		=IF(C7>0,TEXT(C17/C7,"0.0%"),"N/A")

How the automation works: The formula =SUMIF(Ledger!D:D,"4010",Ledger!E:E) searches column D of the Ledger sheet for the account code "4010" and sums the corresponding values in column E (Money In). Every time Amara adds a new tutoring session payment to her Ledger with code 4010, the P&L updates automatically. The Net Profit Margin formula uses the TEXT function to display the result as a formatted percentage.

For a year-to-date P&L, use =SUMIFS(Ledger!E:E, Ledger!D:D,"4010", Ledger!A:A,">="&DATE(2025,1,1), Ledger!A:A,"<="&DATE(2025,12,31)) — the SUMIFS version adds date range conditions.

Balance Sheet in Excel

The Balance Sheet is a financial snapshot of your business at a specific point in time — showing everything you own (Assets), everything you owe (Liabilities), and the difference (Equity). The fundamental accounting equation that every Balance Sheet must satisfy is: Assets = Liabilities + Equity. If your Excel Balance Sheet does not balance, there is an error somewhere in your ledger that needs to be found and corrected.

⚙ Balance Sheet Core Formula — The Accounting Equation = Total Assets → Everything your business owns and is owed
= Total Liabilities + Total Equity → Must always equal Total Assets

// Excel check formula — put this in a prominent cell:
=IF(TotalAssets=TotalLiabilities+TotalEquity,"✅ BALANCED","⚠ ERROR — Does Not Balance")
// Retained Earnings (Equity component):
=OpeningRetainedEarnings + NetProfitThisPeriod - OwnerDrawings

Cash Flow Tracker

Cash flow is the actual movement of money into and out of your business bank account. A business can be profitable on paper (P&L shows positive net profit) but still run out of cash — because income is recognised when invoiced, not when paid. Your Cash Flow tracker in Excel shows the actual timing of cash movements, helping you predict when you might have a shortfall and plan accordingly.

Practical Example: 13-Week Cash Flow Forecast

Leon runs a landscaping business with seasonal revenue fluctuations. He builds a 13-week (quarterly) cash flow forecast in Excel to predict when cash will be tight.

Cash Flow Forecast — Q1 2025

	A	B	C	D	E
1	Category	Week 1	Week 2	Week 3	Week 4
2	Opening Cash Balance	£4,200	=E2	=E3	=E4
3	CASH IN
4	Invoices expected to be paid	1,800	0	3,500	2,200
5	New customer deposits	500	750	0	1,000
6	Total Cash In	=SUM(B4:B5)	=SUM(C4:C5)	=SUM(D4:D5)	=SUM(E4:E5)
7	CASH OUT
8	Staff wages	1,200	1,200	1,200	1,200
9	Materials & fuel	340	280	420	310
10	Van lease payment	450	0	0	450
11	Total Cash Out	=SUM(B8:B10)	=SUM(C8:C10)	=SUM(D8:D10)	=SUM(E8:E10)
12	NET CASH FLOW	=B6-B11	=C6-C11	=D6-D11	=E6-E11
13	CLOSING BALANCE	=B2+B12	=C2+C12	=D2+D12	=E2+E12

The Closing Balance row (row 13) formula is =Opening Balance + Net Cash Flow. The Opening Balance for Week 2 (C2) is =B13 — the previous week's closing balance. This chain means that if Leon changes any forecast figure, every subsequent week's balance recalculates instantly. He applies Conditional Formatting to row 13: any closing balance below £1,000 turns red, giving him an immediate visual warning that cash will be tight that week.

Sales Tax / VAT Tracking in Excel

If your business is registered for VAT or sales tax, you must track the tax you collect from customers (Output Tax) and the tax you pay on purchases (Input Tax), remit the difference to the tax authority each period, and keep detailed records to support your returns. Excel handles this with a dedicated VAT column in both your Invoices and Expenses sheets, and a summary return calculation worksheet.

⚙ VAT / Sales Tax Formula Reference // Net amount FROM a tax-inclusive total (e.g., receipt shows £120 incl. 20% VAT):
Net = GrossTotal / (1 + VATRate) → =120/1.20 → £100.00
VAT = GrossTotal - Net → =120-100 → £20.00

// VAT to ADD to a net price (e.g., charging £500 + VAT):
VAT = NetPrice * VATRate → =500*0.20 → £100.00
Gross = NetPrice * (1 + VATRate) → =500*1.20 → £600.00

// VAT Return summary formula:
VAT Owed = OutputTax - InputTax → =SUMIF(Type,"Output",VAT) - SUMIF(Type,"Input",VAT)

Inventory Management in Excel

For businesses that sell physical products, Excel can manage a fully functional inventory system that tracks stock levels, calculates Cost of Goods Sold (COGS), and alerts you when stock falls below a reorder threshold. The key is a structured product table combined with a transactions log that automatically updates quantities using SUMIF formulas.

Practical Example: Product Inventory Tracker with Auto-Reorder Alerts

Carlos runs an online stationery store. His Inventory sheet tracks every product, links purchases and sales, and flags items that need reordering.

Inventory — Stock Level Tracker

	A	B	C	D	E	F	G
1	SKU	Product	Opening Stock	Purchased	Sold	Current Stock	Status
2	PEN-001	Ballpoint Pen (Box 12)	200	=SUMIF(Purchases!B:B,A2,Purchases!C:C)	=SUMIF(Sales!B:B,A2,Sales!C:C)	=C2+D2-E2	=IF(F2<30,"🔴 Reorder Now",IF(F2<60,"🟡 Low Stock","🟢 OK"))
3	NB-A4-001	A4 Notebook (Ruled)	150	=SUMIF(...)	=SUMIF(...)	=C3+D3-E3	=IF(F3<20,...)
4	MRK-HI-SET	Highlighter Set (5 colours)	80	=SUMIF(...)	=SUMIF(...)	=C4+D4-E4	=IF(F4<15,...)
6	COGS CALCULATION		Unit Cost	Units Sold (MTD)		COGS (MTD)
7	PEN-001	Ballpoint Pen (Box 12)	£3.50	=E2		=C7*E2
8	NB-A4-001	A4 Notebook	£1.80	=E3		=C8*E3
9	TOTAL COGS					=SUM(G7:G8)

Column D (Purchased) uses =SUMIF(Purchases!B:B,A2,Purchases!C:C) — this searches a separate "Purchases" sheet for all rows where the SKU in column B matches the current row's SKU, and sums the quantities. Column E (Sold) does the same against a "Sales" sheet. Current Stock (F) is simply =C+D-E. The Status column uses a nested IF to show traffic-light emojis based on stock level thresholds you set per product — no conditional formatting needed, the colour is part of the text itself.

Payroll Calculator in Excel

For small teams, an Excel payroll calculator can handle the core calculations: gross pay, tax withholdings, National Insurance (or Social Security), pension contributions, and net pay. While dedicated payroll software automates tax filings, Excel gives you full transparency into every calculation and is ideal for businesses with 1–10 employees where payroll is straightforward.

Business owner processing payroll on a laptop, representing Excel payroll calculation and employee payment management

Excel payroll calculators give small business owners full transparency into every deduction and net pay calculation — with no monthly software fee.

Practical Example: Monthly Payroll Calculator (UK Example)

Raj owns a small IT firm with 3 employees. He processes monthly payroll in Excel. The key rates (tax thresholds, NI rates) are stored in a separate "Settings" tab so updating them for a new tax year takes 2 minutes.

Payroll — March 2025

	A	B	C	D	E	F	G
1	Employee	Annual Salary	Monthly Gross	Income Tax	NI (Employee)	Pension (5%)	NET PAY
2	Amy Chen	£32,000	=B2/12	=MAX(0,(C2-Settings!B2/12)*Settings!B3)	=MAX(0,(C2-Settings!C2/12)*Settings!C3)	=C2*Settings!D2	=C2-D2-E2-F2
3	Tom Rivera	£28,500	=B3/12	=MAX(0,(C3-Settings!B2/12)*Settings!B3)	=MAX(0,(C3-Settings!C2/12)*Settings!C3)	=C3*Settings!D2	=C3-D3-E3-F3
4	Keisha Brown	£42,000	=B4/12	=MAX(0,(C4-Settings!B2/12)*Settings!B3)	=MAX(0,(C4-Settings!C2/12)*Settings!C3)	=C4*Settings!D2	=C4-D4-E4-F4
5	TOTALS	=SUM(B2:B4)	=SUM(C2:C4)	=SUM(D2:D4)	=SUM(E2:E4)	=SUM(F2:F4)	=SUM(G2:G4)

The Settings sheet holds all rate variables in named cells: PersonalAllowance (£12,570), BasicRateTax (20%), NI_PrimaryThreshold (£12,570), NI_Rate (12%), PensionRate (5%). Using MAX(0,...) prevents negative tax values for low earners below the threshold. When tax rates change in April each year, Raj updates the Settings sheet in 2 minutes and every employee's payslip recalculates automatically. Each payslip is generated by a separate template sheet that uses VLOOKUP to pull the employee's figures from the payroll table by name.

Project & Job Costing in Excel

Job costing tracks all revenue, labour, and expenses associated with individual projects so you can see — precisely — how profitable each job is. This is critical for contractors, consultants, agencies, and anyone who works on discrete projects. In Excel, each project gets its own section or sheet, with SUMIF formulas consolidating transactions from the main ledger by project code.

Practical Example: Project Profitability Tracker

Elena runs a renovation contracting company. She adds a "Project Code" column to her Ledger and Expenses sheets, then uses SUMIF to instantly calculate each project's margin.

Job Costing — Project Profitability Summary

	A	B	C	D	E	F
1	Project Code	Project Name	Revenue (£)	Total Costs (£)	Gross Profit (£)	Margin %
2	P-2025-001	Davidson Kitchen Remodel	=SUMIF(Ledger!H:H,A2,Ledger!E:E)	=SUMIF(Expenses!G:G,A2,Expenses!E:E)	=C2-D2	=IF(C2>0,TEXT(E2/C2,"0.0%"),"—")
3	P-2025-002	Hartley Bathroom Renovation	=SUMIF(Ledger!H:H,A3,Ledger!E:E)	=SUMIF(Expenses!G:G,A3,Expenses!E:E)	=C3-D3	=IF(C3>0,TEXT(E3/C3,"0.0%"),"—")
4	P-2025-003	Office Fit-Out — Nexus Ltd	=SUMIF(Ledger!H:H,A4,Ledger!E:E)	=SUMIF(Expenses!G:G,A4,Expenses!E:E)	=C4-D4	=IF(C4>0,TEXT(E4/C4,"0.0%"),"—")
5	PORTFOLIO TOTALS		=SUM(C2:C4)	=SUM(D2:D4)	=SUM(E2:E4)	=TEXT(SUM(E2:E4)/SUM(C2:C4),"0.0%")

The key is adding a "Project Code" column (column H in the Ledger, column G in Expenses) when entering each transaction. Transactions not related to a specific project can be left blank or assigned "OVERHEAD." The SUMIF formulas then aggregate all transactions for each project code automatically. Elena can see at a glance that her kitchen remodel hit 33% margin, her bathroom renovation hit 41%, but her office fit-out is only at 18% — prompting her to investigate labour overruns on that job.

Annual Budget vs Actuals in Excel

A budget is a financial plan for the year ahead — projecting how much you expect to earn and spend each month. The Budget vs Actuals report compares your plan to what actually happened, showing variances that require explanation and action. In Excel, this is a powerful management tool that combines your projected figures with live actuals pulled via SUMIF from your transaction sheets.

⚙ Budget vs Actuals Formula Pattern // For each income / expense line in your budget sheet:
Budget = Manually entered projection for the month
Actual =SUMIFS(Ledger!E:E, Ledger!D:D,AccountCode, Ledger!A:A,">="&MonthStart, Ledger!A:A,"<="&MonthEnd)
Variance =Actual - Budget → Positive = over budget (bad for expenses, good for income)
Var % =IF(Budget<>0,Variance/Budget,"N/A") → Format as % — highlight if >10% variance

Pivot Table Financial Analysis

Pivot Tables transform your flat transaction ledger into instant, interactive financial summaries — no formulas required. For financial analysis, the most powerful Pivot Table applications are: revenue by customer (who are your best clients?), expenses by category by month (where are costs growing?), profit by project (which jobs are most profitable?), and year-over-year comparisons. Convert your Ledger to an Excel Table first (Ctrl+T) to ensure the Pivot Table always includes new rows.

Business analyst reviewing financial data dashboards on multiple screens representing Excel pivot table analysis

Pivot Tables turn your transaction ledger into actionable financial intelligence — monthly revenue trends, customer profitability, and cost breakdowns available in seconds.

Practical Example: Monthly Revenue by Customer Pivot Table

Tina runs a consultancy and wants to see which clients generate the most revenue, broken down by month, so she can identify her most valuable relationships and spot declining accounts.

Setup: Click anywhere in the Ledger Table → Insert → PivotTable → New Worksheet → OK.

Configuration in the PivotTable Fields pane:

Drag Description (or a dedicated "Client" column) to ROWS
Drag Date to COLUMNS — right-click a date in the table → Group → select "Months"
Drag Money In to VALUES — it auto-sets to SUM
Filter ROWS to show only Invoice transactions (using the Account Code filter)

Result: A matrix showing every client's monthly revenue across the year. Right-click any cell → Show Values As → % of Row Total — this switches to showing each client's revenue distribution by month, immediately revealing seasonal patterns. Add a Pivot Chart (PivotTable Analyze → PivotChart → Line Chart) and a Slicer for Year — you have a fully interactive client revenue dashboard built from your raw transaction data in under 5 minutes.

KPI Financial Dashboard in Excel

A well-designed Excel financial dashboard consolidates your most important business metrics onto a single screen — giving you (and any stakeholders) an instant view of financial health without digging through multiple sheets. The best Excel dashboards combine large KPI "cards" showing headline numbers, sparkline charts showing trends, and data pulled live from your underlying sheets via formulas.

Practical Example: One-Page Business Dashboard

Gabriel wants a dashboard that shows his key numbers at a glance every Monday morning. He builds it on a dedicated "Dashboard" sheet that pulls live data from all other sheets.

Step 1 — Suppress gridlines and create a clean canvas: View → uncheck Gridlines. Fill the background with a very light grey (#F5F7FA). This immediately makes the sheet feel like a designed dashboard rather than a spreadsheet.

Step 2 — Build KPI cards using merged cells: For each KPI, merge a block of cells (e.g., B3:E6), give it a white background and a subtle drop shadow (format cells → border → outer border in light grey). Inside, use two cells: one for the KPI label (small, grey, UPPERCASE font) and one for the value (large, bold, coloured).

KPI Card formulas:

⚙ Dashboard KPI Formula Examples // Monthly Revenue (current month):
=SUMIFS(Ledger!E:E,Ledger!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),Ledger!A:A,"<="&EOMONTH(TODAY(),0))

// Outstanding receivables:
=SUMIF(AR!F:F,"Unpaid",AR!D:D)

// Month-over-month revenue change %:
=ThisMonthRevenue/LastMonthRevenue-1 → Format as % with + sign: [Green]▲+0.0%;[Red]▼-0.0%

// YTD Net Profit:
='P&L'!C17 → Direct cell reference to the Net Profit cell on the P&L sheet

// Overdue invoices count:
=COUNTIF(AR!F:F,"60+ Days")&" invoices overdue 60+ days"

Step 3 — Add Sparkline charts inside cells: Select a single cell next to each KPI → Insert → Sparklines → Line → select 12 months of data as the source range. These tiny inline charts show the trend at a glance without taking up dashboard space.

Step 4 — Add a single dropdown to control the reporting period: Use a Data Validation dropdown in one cell (e.g., "Jan", "Feb", "Mar"...) and reference it in every formula using MATCH to return the correct month number. Now the entire dashboard updates when you change the month selector.

Automating with Formulas & Macros

The most powerful Excel accounting users eliminate repetitive manual steps through smart formulas and VBA macros. Here are the highest-value automation techniques specifically for financial management in Excel.

Auto-Populating Descriptions with VLOOKUP

Store a reference table of common vendors and their default expense categories on your COA sheet. Then in your transaction ledger, when you type a vendor name, use VLOOKUP to auto-fill the account code — eliminating repetitive category selection for recurring expenses like monthly subscriptions.

⚙ Auto-Categorise Recurring Transactions // In the Category column of your Ledger, when vendor name is in column C:
=IFERROR(VLOOKUP(C2,VendorDefaults,2,FALSE),"")
// "VendorDefaults" is a named range: Column 1 = Vendor Name, Column 2 = Default Category
// e.g.: "Adobe" → "Software", "WeWork" → "Rent", "Meta Ads" → "Marketing"

// One-click month-end summary macro trigger cell:
// Assign a macro to a button that:
// 1. Copies this month's Ledger rows to an "Archive" sheet
// 2. Clears the Ledger for the new month
// 3. Sets the Opening Balance from last month's closing balance
// 4. Saves the workbook with a timestamped filename

Month-End Close Macro

Record a macro (View → Macros → Record Macro) that performs your standard month-end procedure: copying the month's transactions to an archive sheet, clearing the input range for the new month, updating the opening balance, and saving a timestamped backup copy. Once recorded, this entire process runs in under 10 seconds with a single button click.

Year-End Tax Preparation Checklist in Excel

A well-maintained Excel accounting system makes tax preparation dramatically faster and more accurate. The following checklist covers every task you should complete in your Excel workbook before filing your tax return or handing your books to an accountant.

Reconcile every bank account and credit card through December 31st — no unreconciled statement periods
Categorise all uncategorised transactions — run COUNTIF on your Ledger to find rows with blank Category columns and resolve every one
Review the AR Aging Report — write off any invoices more than 12 months old as Bad Debt (deductible expense) by adding a negative entry to the Ledger
Reconcile your VAT/Sales Tax account — total of all VAT columns should match the total of your filed returns plus any outstanding balance
Verify fixed asset additions — any equipment or vehicle purchased during the year should be in an Asset account, not an Expense account, so depreciation can be correctly calculated
Produce your year-end P&L — set the date range to the full fiscal year (Jan 1 – Dec 31) and verify every line is correct before sharing with your accountant
Produce your year-end Balance Sheet — verify Assets = Liabilities + Equity and the Balance Sheet check formula shows "✅ BALANCED"
Run a transaction count check — =COUNTA(Ledger!A:A) versus your bank statement transaction count for the year. Large discrepancies suggest missed entries
Export summary reports to PDF — File → Export → Create PDF for P&L, Balance Sheet, and Cash Flow Statement. Archive these PDFs alongside the Excel file
Save a locked year-end archive copy — Save As "Business Accounts 2025 — FINAL YEAR END.xlsx." Protect all sheets with a password to prevent accidental changes to closed-year data

Accountant carefully reviewing financial statements and tax documents at a desk with laptop and calculator

A well-maintained Excel accounting workbook means year-end is a straightforward export exercise — not a stressful scramble to reconstruct a year of missing records.

Advanced Excel Accounting Formulas

These formulas solve the most common complex financial calculations that arise in Excel-based bookkeeping — from loan amortisation to depreciation to multi-condition financial lookups.

⚙ Advanced Financial Formula Reference // LOAN AMORTISATION — monthly payment on a fixed loan:
=PMT(AnnualRate/12, TermMonths, -LoanAmount)
→ =PMT(0.06/12, 60, -25000) → £483.32/month on a £25k 5-year loan at 6%

// STRAIGHT-LINE DEPRECIATION — annual depreciation of an asset:
=SLN(Cost, SalvageValue, UsefulLifeYears)
→ =SLN(12000, 2000, 5) → £2,000/year depreciation on a £12k asset

// REDUCING BALANCE DEPRECIATION — year N value of an asset:
=DB(Cost, Salvage, Life, Period)
→ =DB(12000, 2000, 5, 3) → Depreciation in year 3

// DAYS SALES OUTSTANDING (DSO) — average collection period:
DSO = (AccountsReceivable / TotalRevenue) * DaysInPeriod
→ =(AR_Total/Annual_Revenue)*365 → 35 days = you collect in 35 days on average

// GROSS PROFIT MARGIN:
GPM = (Revenue - COGS) / Revenue → Format as %

// BREAK-EVEN POINT (units) — how many units to cover fixed costs:
BreakEven = FixedCosts / (SellingPrice - VariableCostPerUnit)
→ =15000/(25-8) → 882 units to break even

// CURRENT RATIO — liquidity health check:
CurrentRatio = CurrentAssets / CurrentLiabilities
→ >2.0 is healthy; <1.0 means liabilities exceed assets — cash flow risk

Excel Accounting Shortcuts & Power Tips

Action	Windows Shortcut	Why It Matters for Accounting
Insert current date in a cell	`Ctrl + ;`	Instantly timestamps every transaction as you enter it
AutoSum selected range	`Alt + =`	One keystroke to sum a column of figures — use constantly in P&L and balance sheets
Toggle absolute reference ($)	`F4`	Lock cell references in SUMIF and lookup formulas without retyping
Format as Currency	`Ctrl + Shift + $`	Format selected cells as currency in one keystroke
Format as Percentage	`Ctrl + Shift + %`	Format margin and rate cells instantly
Format Cells dialog	`Ctrl + 1`	Access all number, border, and protection formats in one dialog
Fill Down (copy formula)	`Ctrl + D`	Copy the formula from the cell above to fill down a column instantly
Select visible cells only	`Alt + ;`	When copying filtered data, ensures only visible rows are copied — critical for filtered ledger exports
Add filter / remove filter	`Ctrl + Shift + L`	Toggle filters on your ledger for instant transaction searching
Go to last used cell	`Ctrl + End`	Jump to the last entry in your ledger instantly — see where your data ends
Show all formulas	Ctrl + `	Toggle between formula view and value view — essential for auditing a workbook you did not build
New worksheet	`Shift + F11`	Add a new month/sheet quickly
Save As (save a copy)	`F12`	Save a new timestamped backup copy instantly — do this before any major data entry session

8 Expert Excel Accounting Tips

Always Use Excel Tables (Ctrl+T) for Transaction Data

Converting your Ledger and Expenses ranges to named Excel Tables means SUMIF formulas automatically include new rows, filters auto-extend, and structured references like =[@Amount] make formulas far more readable and robust than traditional cell references.

Store All Rates and Thresholds in a Settings Sheet

Never hardcode rates (VAT rate, tax thresholds, pension percentages) inside formulas. Store them in named cells on a "Settings" sheet. When rates change, update one cell and every formula throughout the workbook recalculates. This is the single biggest maintenance advantage in any financial workbook.

Use Data Validation Lists for Every Category Column

Every Category, Account Code, or Status column should have a dropdown list from Data Validation. This prevents typos that silently break your SUMIF totals — "Marketing" and "marketing" are treated as different values by SUMIF, producing wrong totals that are very difficult to trace without knowing to check for case inconsistencies.

Build a "Check" Row on Every Financial Statement

Add a row at the bottom of every P&L and Balance Sheet with an IF formula that confirms the numbers are internally consistent: =IF(TotalAssets=TotalLiab+TotalEquity,"✅ OK","⚠ ERROR"). If this cell ever shows ERROR, there is a data integrity problem to investigate before trusting any figures.

Use IFERROR() to Keep Reports Clean

Wrap every VLOOKUP and SUMIF formula in =IFERROR(formula,"") — this replaces ugly #N/A or #DIV/0! errors with a blank cell when data is missing. Your reports look professional and your formulas remain readable even in edge cases where source data is incomplete.

Colour-Code Input vs Formula Cells Consistently

Apply a consistent visual language throughout your workbook: blue fill = user input cell (type here), white/no fill = formula cell (do not edit), green fill = total/summary cell. Document this legend on your Cover sheet. Anyone opening the workbook instantly understands where to enter data and where not to.

Save Monthly Snapshots as PDF

At the end of every month, export your P&L, Balance Sheet, and Bank Reconciliation sheets to PDF (File → Export → Create PDF). Store these in a cloud folder named by year and month. These PDFs are your permanent financial record — if the Excel file is ever corrupted or accidentally edited, the PDFs preserve the figures as they stood at month-end.

Use the Watch Window to Monitor Key Balances

Go to Formulas → Watch Window → Add Watch → select your Net Profit cell, Cash Balance cell, and Balance Sheet check cell. These three cells are visible in the Watch Window no matter which sheet you are working on — so you can always see the big picture impact of any change you make, in real time.

"The business that understands its numbers owns its future. Excel doesn't just store your figures — built correctly, it reveals your story."

Your Excel Accounting System Starts Today

You now have everything you need to build a complete, professional-grade accounting system in Microsoft Excel — from your first transaction entry to a year-end Balance Sheet that satisfies your accountant. The formulas, templates, and structures in this guide have been used by thousands of small business owners, freelancers, and financial professionals worldwide.

Start with your Chart of Accounts and General Ledger today. Add your open invoices to the AR tracker. Build the P&L this weekend. Each piece you add makes the whole system more powerful, and the financial clarity it gives you will directly improve the decisions you make for your business.

At LearnTCard.com, every guide we publish gives you the practical, immediately applicable skills that create real results — not theory, not jargon, but working knowledge you can use today.

learntcard.com — Practical Skills for Real Careers