A recent Bluevine survey shows that a staggering 55% of small business owners report spending too much time on administrative tasks, a drain often linked to disorganized financial tracking. Before you invest in dedicated accounting software, you can make your finances clear and tax‑ready with Microsoft Excel.
Excel bookkeeping means recording your business’s income and expenses in an Excel workbook, typically a chart of accounts, income and expense logs, and a P&L summary built with formulas. Bookkeeping in Excel suits a small business with simple finances and a modest number of transactions. Below are 11 templates, a five-step setup, and the signs it’s time for accounting software.
What you need to know
- An Excel bookkeeping workbook starts with a Chart of Accounts, then adds separate income and expense logs and a P&L Summary sheet that totals each category with SUMIF.
- Data Validation dropdowns keep category names consistent, so SUMIF can match each entry to its category.
- Bookkeeping that takes more than a few hours a week, errors that keep surfacing, and reports pulled from several accounts are signs it’s time for accounting software.
How is Excel used for accounting?
Microsoft Excel is a digital ledger, journal, and report generator for small businesses. While a basic Excel workbook works as a single-entry accounting system (a system based on the income statement, also called the profit and loss statement), it’s powerful enough for most small businesses to track income and expenses, monitor cash flow, and generate financial reports without the cost of specialized software or hiring a dedicated accounting professional.
For startups, the limits of Excel are manageable, but they can lead to errors and unnecessary work when scaling.
Before your first entry, decide when you’ll record income and expenses. The cash method records them when money changes hands, and the accrual method records them when they’re earned or incurred. The IRS says you must use the same accounting method from year to year, so compare cash vs. accrual accounting first.
Pros and cons of using Microsoft Excel for accounting
| Pros | Cons |
|---|---|
| Cost-effective (bundled with Microsoft Office) | Manual entry increases risk of errors |
| Familiar to most users | No built-in automation for recurring transactions |
| Customizable to fit business needs | Can be difficult to scale as transaction volume grows |
11 excel bookkeeping templates to get started
The following templates cover every essential bookkeeping function for small businesses. You can build each one as a separate workbook or keep them all as tabs in one workbook. Each can be customized to fit your specific needs.
Transaction log & general ledger
A transaction log is a master file or journal that categorizes every financial transaction (debits and credits) across your accounts, in chronological order, to maintain the company’s official financial records. For each transaction, record the date, account name, a simple description, the amount credited or debited, and an optional reference like an invoice or check number.
A general ledger organizes those transactions by account and keeps a running balance. This provides a comprehensive audit trail for tax compliance, acts as the foundation for all financial statements (profit and loss, balance sheet, etc.), and quickly identifies discrepancies or potential fraud.
Simple budget template
A simple budget template is a tool for forecasting expected monthly income versus expenses. It helps business owners set and track financial goals, see where they’re over or under spending, and improve control over cash flow. Understanding key financial KPIs is crucial for successful budgeting.
To use this Excel template, enter your expected income and budgeted expenses at the beginning of each month. As the month progresses, enter your actual income and expenses. Compare the budget to the actuals and adjust spending accordingly.
Expense tracker template
Use an expense tracker template to record and categorize every outgoing business payment. For easy tax reporting, include details like date, vendor, amount, and business purpose. Attach a receipt or reference number for even better documentation.
A dedicated business expense tracker can take this further, sorting each cost into a category and turning the records into reports.
Profit and loss (P&L) template
A profit and loss statement summarizes a company’s financial performance over a period of time to calculate your net profits and profit margin. Knowing your net profit margin can help you make decisions on pricing and expenses.
To use this template, tally your total revenue for the month or quarter, then calculate the cost of goods sold for the same time period and subtract it to get your gross profit. Tally all your operating expenses, like rent, payroll, and utilities, then calculate your net profit and profit margin.
Our profit and loss statement template shows each line of a P&L with a filled-in example.
Income log/sales journal
An income log is a running list used to record all revenue-generating activities for a business. It’s important to record all sales in chronological order, noting the product or service, amount, customer name, date, and payment method, plus an invoice or reference number.
Accounts payable (AP) tracker
An accounts payable tracker lists all outstanding bills, invoices, and short‑term debts owed to vendors. It’s best used to manage payment deadlines and maintain good vendor credit and supplier relationships. Keeping your AP tracker up to date can help the business owner make accurate financial forecasts.
To use this spreadsheet template effectively, enter each bill as it arrives, recording invoice date, amount, vendor, and due date. Sort by due date to prioritize payments, and mark items as paid once settled so you can filter for outstanding balances. Keeping the tracker current improves cash‑flow forecasting and prevents unnecessary interest charges.
Accounts receivable (AR) tracker
An accounts receivable tracker records money owed to your business by customers for goods or services. It’s essential for cash‑flow management, invoice monitoring, overdue alerts, and collection prioritization. A clean AR tracker also supports credit‑control policies and reduces bad‑debt risk; automation tools can further streamline the process.
To use this template, add each new invoice with customer name, invoice date, amount due, and due date. Use a simple Excel formula to calculate days outstanding and highlight overdue items.
Did you know?
You can accept payments directly to your Bluevine Business Checking account via check, ACH, wire, credit card, and even in person with Tap to Pay.BVSUP-00192
Payroll journal
A payroll journal records every employee’s compensation details, and is ideal for small businesses. Include gross pay, mandatory/voluntary deductions, and net pay to ensure accurate, compliant wage payments and simplify tax filing.
To use this template, log hours worked, apply each employee’s pay rate to compute gross pay, subtract taxes and benefits, and record the net amount. Also note the payment date for auditing purposes.
Mileage tracker
A mileage tracker allows freelancers and businesses to claim the standard mileage deduction by substantiating business‑use vehicle mileage for tax purposes.
Use it to record trip details, including the date, odometer readings at start and end, and the business purpose. Take a photo of your odometer on January 1 to establish that tax year’s mileage baseline. For 2026, the IRS standard mileage rate for business use is 72.5 cents per mile from January 1 through June 30 and 76 cents per mile from July 1 through December 31.
Asset and depreciation log
An asset and depreciation log can be helpful if you make large business purchases like property or expensive equipment. Use this log to register all large purchases that will remain in use over a year. This enables your business to claim depreciation deductions and reduce your taxable business income.
To use this type of accounting spreadsheet, record the purchase cost, the date the asset was placed in service, its IRS recovery period, and the depreciation method you chose. Formulas in the sheet can then calculate each year’s depreciation for your tax preparer to check.
Inventory tracking
An inventory tracking spreadsheet is great for retail and e-commerce businesses that stock tangible products. Use this type of Excel template to maintain accurate stock counts and safeguard against loss or theft.
For each stock keeping unit (SKU), log the product ID, unit cost, sale price, quantity received, and quantity sold for a given period. This provides real-time inventory levels and reliable cost-of-goods-sold (COGS) data, and will be useful when implementing an inventory management strategy.
How to set up your excel bookkeeping system
Follow these five steps to transform a blank spreadsheet into a structured accounting system.
1. Define your Chart of Accounts
Create a Chart of Accounts (COA) worksheet, and use it as the reference point for how you categorize all transactions you record. Having well-defined categories for transactions will make it easy to categorize them consistently:
| Accounting type | Examples | Description |
|---|---|---|
| Income | Sales revenue, service fees, interest earned, consulting income, product sales | Money earned from primary business activities |
| Expenses | Rent, utilities, software subscriptions, marketing/advertising, office supplies, cost of goods sold (COGS) | Money spent to operate the business and generate revenue |
| Assets | Business checking (cash), accounts receivable (money owed to you), equipment, furniture | Items of value owned by the business |
| Liabilities | Accounts payable (money you owe), business loans, credit card balances, taxes payable | Debts or obligations owed by the business to external parties |
2. Create your Income and Expense Logs
Create two separate worksheets for your Income Log and Expense Log, and use them to record every business transaction. These logs will provide the raw data behind all of your financial reports.
Use these columns for all transactions (across both logs):
- Date
- Description/Vendor
- Invoice/Ref. Number
- Category
- Amount
3. Build the Profit and Loss (P&L) Summary sheet
Create a worksheet titled “P&L Summary.” List your COA categories vertically, with income categories on top and expenses below. Your P&L Summary sheet will use Excel’s SUMIF formula to automatically tally the total dollar amounts for each category in your Income and Expense logs.
Add a line at the bottom that subtracts total expenses from total income, and the finished sheet becomes a basic profit and loss statement for the period.
4. Implement Data Validation
Data Validation is Excel’s built-in feature for preventing manual errors (like typos) by enforcing consistency in your category selections. If a category name isn’t exactly right, the SUMIF formula on your P&L sheet won’t tally it, and your data will be inaccurate.
To use Data Validation, navigate to the Category column in your Income and Expense Logs. Use Data Validation (found in the Data tab) to create a dropdown list that pulls directly from the category names on your Chart of Accounts sheet. This ensures that users can only select from pre-approved categories.
5. Reconcile weekly against bank statements
Incorrect or infrequent reconciliation is one of the most common bookkeeping mistakes made by small business owners. Set a recurring weekly or bi‑weekly reminder to compare Excel totals with your business checking account statement. Any discrepancy between the two signals a missed entry or wrong amount on a recent transaction.
Our guide to bank reconciliation covers the full process, including how to handle checks that haven’t cleared and deposits in transit.
Did you know?
Each Bluevine Business Checking sub-account comes with a dedicated account number, which makes it easier to track how your money moves across your accounts. You can have up to 5 sub-accounts with the Standard plan, 10 with Plus, or 50 with Premier.
Must-know Excel formulas and formatting tips
Turning a spreadsheet into a professional accounting tool requires more than just columns and rows. Use these formulas and formatting techniques to automate your reporting, prevent data errors, and make tax preparation faster.
| Area | Formula | How to implement | Primary use |
|---|---|---|---|
| Data aggregation | SUMIF | =SUMIF(Category Column, “Rent”, Amount Column) | Core P&L reporting: Sums numerical values only if a neighboring cell meets a specific condition |
| Data aggregation | SUMIFS | =SUMIFS(Amount, Category, “Rent”, Date, “>=01/01/2025”) | Advanced reporting: Sums based on multiple conditions |
| Running balance | SUM | In cell D2: =Starting Balance + C2. In D3: =D2 + C3, then drag down | Bank reconciliation: Creates a running total essential for tracking cash position |
| Error prevention | Data validation | Select Category column > Data tab > Data Validation > List > Set source to COA | Consistency: Creates dropdown menu that eliminates typos |
| Financial clarity | Currency formatting | Select Amount column > Home tab > Number dropdown > Currency | Readability: Display dollar signs and align decimals |
| Data organization | Format as table | Click in data range > Insert tab > Table | Sorting: Adds banded rows and built-in filters |
| Visual flagging | Conditional formatting | Select column > Home > Conditional Formatting > Highlight Cells Rules | Spotting issues: Automatically highlights cells meeting criteria like overdue invoices |
How to do bookkeeping in Excel
Once the workbook is set up, the rest of small business bookkeeping in Excel is a routine. Most of it happens weekly, with a short review at the end of each month and a handoff to your tax preparer once a year.
- Record each transaction in the Income Log or Expense Log as it happens, or at least once a week, and fill in all five columns.
- Keep the receipt, invoice, or deposit slip behind each entry and note its number in the Invoice/Ref. Number column. The IRS lists sales slips, paid bills, invoices, receipts, deposit slips, and canceled checks as supporting documents.
- Reconcile the logs against your bank and credit card statements every week or two, using the reconciliation step from the setup above. IRS Publication 583 says you should reconcile your checking account each month, so treat monthly as the minimum.
- Update the accounts receivable and accounts payable trackers so you can see who owes you money and which bills come due next.
- At month end, compare the P&L Summary with your budget and save a dated copy of your bookkeeping spreadsheet.
- At year end, give the workbook and your records to your tax preparer. Keep records for as long as the IRS requires, which is generally 3 years and longer in some situations.
When to move from Excel to accounting software
Excel works well for early-stage businesses, but manual entry becomes time-consuming and error-prone as transaction volume rises. A few signs tend to show up before the spreadsheet stops keeping pace.
- You spend more than a few hours per week on bookkeeping.
- Reconciliation keeps turning up typos, broken formulas, or entries the SUMIF formula misses because a category name is off.
- Transaction volume has grown to the point where hand entry is the slowest part of the job.
- You export statements from more than one bank account or credit card and combine them by hand.
- A lender, a partner, or a tax deadline calls for cash flow and profit and loss reports, and building them means checking every formula first.
- Your accountant or bookkeeper needs to work in the books, and emailing versions of the workbook back and forth gets risky.
Accounting software works from the same chart of accounts and transaction records as your workbook. It brings transactions in from the accounts you connect and builds reports from that data.
Bluevine accounting, powered by Xero, is built into your Bluevine Business Checking dashboard. You can connect outside business accounts and credit cards, categorize transactions, and run cash flow and profit and loss reports without relying on separate accounting software or spreadsheets. Every plan includes a free trial, one month for Standard and three months for Plus and Premier. After the trial, the service costs $12 per month, making it an affordable option for essential accounting tools.BVSUP-00207
If Bluevine accounting isn’t the right fit, Bluevine Business Checking customers can instead choose to sign up for a Xero subscription from their Bluevine dashboard, with the first 6 months of Xero free on Plus and Premier and the first 3 months free on Standard.BVSUP-00181
Track income and expenses with Bluevine
A well-structured Excel workbook gives your bookkeeping a sound start, though manual entry gets harder to keep up with as your transaction volume grows. Whichever tool you use, the books are simpler to keep when all your business money moves through one account. With Bluevine Business Checking, you can receive ACH, wires, and checks directly to your Bluevine account and create and send unlimited invoices from your dashboard. Sub-accounts let you separate taxes and other expenses from your business profit, and automatic transfer rules can allocate funds for specific expenses.
Open a Business Checking account
FAQs
Can I use Excel for bookkeeping?
Yes. The IRS says you may choose any recordkeeping system suited to your business that clearly shows your income and expenses, and it holds electronic systems to the same basic recordkeeping principles as paper books. An Excel workbook can meet that standard when it captures every transaction and you keep the supporting documents, such as receipts, invoices, and deposit slips, behind each entry.
How do I set up basic bookkeeping in Excel?
Follow five steps. Define your Chart of Accounts on its own worksheet. Create your Income and Expense Logs with columns for date, description or vendor, reference number, category, and amount. Build a P&L Summary sheet that totals each category with SUMIF. Implement Data Validation so the Category column only accepts names from your chart. Then reconcile weekly against bank statements to catch missed or wrong entries.
Is Excel good for small business bookkeeping?
For a business with simple finances and a manageable number of transactions, yes. IRS Publication 583 describes a single-entry system as simple and practical for someone starting a small business. The limits are manual entry, no built-in automation for recurring transactions, and harder scaling as volume grows. A double-entry system adds built-in checks and balances.
What is the difference between a bookkeeping spreadsheet and accounting software?
A bookkeeping spreadsheet runs on manual work. You type in each transaction, build the formulas, and check that the totals still add up. Accounting software brings transactions in from the bank and card accounts you connect and builds reports from that same data. Some tools suggest a category for each transaction, which is the idea behind AI bookkeeping. Many also let an accountant or bookkeeper sign in and work in the books directly.
How often should I update my bookkeeping spreadsheet?
Enter transactions as they happen, or at least weekly, so receipts and deposits don’t pile up. Compare the logs with your bank and card statements every week or two, and no less than monthly, the cadence IRS Publication 583 gives for a checking account. Once a month, check the category totals against your budget and keep a dated copy of the workbook.

