# How do I set up and use a spreadsheet as a small business's bookkeeping record, and where can I get a ready-made bookkeeping spreadsheet template?

Applies to: United States · Updated 2026-09-27

Use one workbook per business: a register with a row per transaction giving date, document reference, counterparty, account, category and amount in or out, a fixed category list, opening balances and period summaries. Enter rows from source documents, check them monthly against bank and card statements, save each closed month and lock formulas, keeping version history and an off-machine backup. Templates come from spreadsheet makers, the IRS's sample layout and others; adopt one only if it has all this.

## What must the spreadsheet hold to be the books rather than a list?

IRS Publication 583 says that, except in a few cases, the law does not require any specific kind of records: you can choose any system suited to your business that clearly shows your income and expenses, and your books must show your gross income, deductions and credits. Publication 583 also says to keep a business account separate from your personal checking account.

Give the register one row per transaction, or per category when a payment is split (such as a payroll tax deposit), and these columns:

- **Date.** Enter the day the money moved.
- **Document.** Enter the number or file name of the invoice, receipt or bill behind the row.
- **Counterparty.** Name who paid you or whom you paid.
- **Description.** Say briefly what the money was for.
- **Account.** Pick the bank or card account the money moved through.
- **Category.** Pick one entry from the fixed category list.
- **Money in and money out.** Keep two amount columns, never one netted figure.
- **Balance.** Let a formula keep that account's running balance. Because all accounts share one table, that is the account's opening balance plus money in less money out on its rows so far; in Excel, SUMIFS on the Account column down to the current row totals each amount column.

For any period the register must give each category's total, money in and out, and each account's closing balance. Deposits that are not income need their own categories: Publication 583 suggests a checkbook with space to identify the source of deposits as business income, personal funds, or loans.

## How should the tabs and periods be organized?

Use five tabs: About (the basis, start date and how the workbook works), Categories (the fixed lists of categories and accounts, each described), Opening balances, Register (the year's transactions for all accounts in one continuous table) and Summary (each category's total by month and year). In Excel, Microsoft's SUMIFS function page (undated) says SUMIFS adds all of its arguments that meet multiple criteria; here, the category and the month's first and last dates.

Keep one workbook per tax year, and never start a month by clearing the last. When a month passes its completeness check, save a copy named for the month and marked closed, and stop editing its rows; a later correction is a new row, dated with the original transaction, naming the row it corrects. For an error in an earlier year whose return is not yet filed, add the correction row to that year's workbook and save a new closed copy marked revised beside the old one; never carry a correction across the year end, since Publication 538 generally puts income and expenses in the year received and paid (cash method) or earned and incurred (accrual method). An error in a year already filed is not fixed that way: take it to whoever prepares your return. Microsoft's Create a template page (undated) describes saving a file as a template to use as your starting point instead of recreating it; start each year from your empty set-up workbook that way.

### What goes on the opening balances tab on each basis?

IRS Publication 538 says that under the cash method you include in gross income all items of income you actually or constructively receive during the tax year, and generally deduct expenses in the year you actually pay them. So on the cash basis, invoices and bills unpaid at the start date are listed for follow-up, not posted as balances, and each reaches income or expense as a register row when paid. Under an accrual method, Publication 538 says, you generally report income in the year it is earned and deduct or capitalize expenses in the year incurred; the same items are then opening amounts owed to and by the business, which their payment settles without counting again. Starting mid-year, add the earlier months' category totals from your previous records to the summary once, without re-entering those transactions.

### Should the register be single-entry, or journal and ledger?

The register above is a single-entry system. Publication 583 says a single-entry system is based on the income statement (profit or loss statement) and can be a simple and practical system if you are starting a small business, but may not suit everyone, and that double entry has built-in checks and balances to assure accuracy and control. For double entry, Publication 583 says transactions are first entered in a journal and then posted to ledger accounts, and total debits must equal total credits after posting. In a spreadsheet that means a journal tab, a ledger tab totalling each account and a cell checking that debits equal credits, a layout that can carry receivables and payables. On the accrual basis, a 1,800.00 invoice issued in February and paid in March is two entries:

| Date | Account | Debit | Credit |
|---|---|---|---|
| Feb 26 | Accounts receivable | 1,800.00 | |
| Feb 26 | Design income | | 1,800.00 |
| Mar 3 | Checking | 1,800.00 | |
| Mar 3 | Accounts receivable | | 1,800.00 |

Income is counted once, in February. On the cash basis the only entry is on March 3: debit Checking, credit Design income.

## How do you enter a period's transactions and show nothing is missing?

Work through each month in this order:

1. Enter rows from the documents as transactions happen. Publication 583 says it is generally best to record transactions on a daily basis.
2. Write each document's reference in its row and file the document under it. Publication 583 says to keep these documents because they support the entries in your books and on your tax return. Keep paper originals unless your scanned copies meet Publication 583's electronic records conditions below.
3. Use bank and card downloads only to find transactions you have not entered. Publication 583 warns that proof of payment of an amount, by itself, does not establish you are entitled to a tax deduction.
4. At month end, tick each statement line against one register row, and each register row for that account against one statement line. A correction row and the row it corrects, or the rows of a split payment, are ticked together against their one statement line and must add up to it. An unticked statement line is a missing row; an unticked register row is a timing item, such as an uncleared payment, or an error; anything ticked twice is a duplicate. Publication 583 says you should reconcile your checking account each month.
5. When each account's balance agrees with its statement after timing items, close the month. Laying out a full reconciliation in a spreadsheet is a separate question.

## Why must categories come from a fixed list?

Typed categories drift: "Printing", "Printing costs" and "Printng" are three values, and a SUMIFS total for "Printing" silently omits the other two. Fix the list once on the Categories tab and make the register's Category and Account columns pick from it:

- **Excel.** Microsoft's Create a drop-down list page (undated) says drop-downs based on a table update automatically, and that choosing Stop in the Style box on the Error Alert tab stops people entering data that isn't in the list; Information or Warning only show a message.
- **Google Sheets.** Google's Create an in-cell dropdown list page (undated) takes the list from a range of cells and rejects entries that do not match it, unless you choose to allow them with a warning.

Build the list from your tax return's lines; Publication 583's sole-proprietor example carries annual totals for interest, rent, taxes and wages to the appropriate lines in Part II of Schedule C. Add categories for transfers, owner money in and out, loan money received and loan principal repaid, all kept out of income and expense; how owner categories work depends on the business's legal form. Add categories as needed, but never rename one already used, or its rows stop matching.

## What does a filled register look like?

In this cash-basis example a designer's workbook opens on March 1 with 3,000.00 in checking and 5,000.00 in savings. Invoice 1041 for 1,800.00, issued in February and unpaid on March 1, is listed on the opening balances tab, not posted, so it reaches income when paid.

| Row | Date | Document | Counterparty | Description | Account | Category | In | Out | Balance |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Mar 3 | INV-1041 | Harbor Cafe | Invoice 1041 paid | Checking | Design income | 1,800.00 | | 4,800.00 |
| 2 | Mar 7 | R-0307 | City Print Co. | Business cards | Checking | Printing | | 240.00 | 4,560.00 |
| 3 | Mar 12 | T-0312 | Own savings | Transfer to savings | Checking | Transfer | | 1,000.00 | 3,560.00 |
| 4 | Mar 12 | T-0312 | Own checking | Transfer from checking | Savings | Transfer | 1,000.00 | | 6,000.00 |
| 5 | Mar 7 | R-0307 | City Print Co. | Corrects row 2 | Checking | Printing | | 5.00 | 3,555.00 |

Row 5 corrects row 2: the month-end check showed the bank paid 245.00, not the 240.00 keyed, so a new row records the 5.00 difference rather than overwriting row 2, and rows 2 and 5 together tick against the 245.00 statement line. The transfer appears once in each account and nets to zero. The March summary reads:

| Summary line | March |
|---|---|
| Design income | 1,800.00 |
| Printing | 245.00 |
| Net (income less expenses) | 1,555.00 |
| Transfers, net | 0.00 |
| Owner money in less out | 0.00 |
| Loan money received less repaid | 0.00 |
| Checking closing balance | 3,555.00 |
| Savings closing balance | 6,000.00 |

Opening balances of 8,000.00, plus the 1,555.00 net and the three 0.00 lines, give closing balances of 9,555.00 (3,555.00 plus 6,000.00). Build that tie-out into the summary from register rows only, excluding totals carried in from before the start date: opening balances plus money in less money out for every category equal closing balances. It fails when a row misses the category totals, such as through a mistyped category.

## How do you protect the workbook, attribute edits and keep a backup?

One overwritable file on one computer is not a dependable record. Put four safeguards in place:

- **Locked formulas.** Microsoft's Protect a worksheet page (undated) says the first step is to unlock cells that others can edit, then protect the worksheet with or without a password; worksheet protection isn't intended as a security feature and simply prevents users from modifying locked cells, and Microsoft cannot retrieve a forgotten password. Google's Protect, hide & edit sheets page (undated) lets you choose who can edit a range or sheet, though people can still copy and export a protected spreadsheet.

  After protecting, add a test row and confirm it still reaches the totals. If it does not, unprotect the sheet, allow users to insert rows when you protect it again (one of the actions Microsoft lists), and retest.
- **Edit access.** Store the file where only the people keeping the books can edit it, each with their own sign-in.
- **Revision history.** Microsoft's View previous versions of Office files page (undated) says version history in Microsoft 365 only works for files stored in OneDrive or SharePoint in Microsoft 365. Google's Find what's changed in a file page (undated) shows who updated the file and their changes, and lets you name a version so it is not merged; name one at each close.
- **Backup off the machine.** The FTC's Cybersecurity for Small Business guidance (September 2025) says to back up important files regularly, in the cloud or on an external hard drive, and to make sure backups are not connected to the network. Keep the closed-month copies there too.

## Where can you get a ready-made template, and how do you judge one?

Templates come from three kinds of publisher:

- **Spreadsheet makers.** Microsoft's Create a template page (undated) says that in Excel for Mac you start a workbook from a template with File > New from Template, and can search by keyword in the Search All Templates box. In other applications, screen what you find with the tests below.
- **The IRS.** Publication 583 lists what a small business's system might include, such as a daily summary of cash receipts and a check disbursements journal, and works them through for a sole proprietor. It is a layout, not a file, and Publication 583 says these sample records should not be viewed as a recommendation of how to keep your records.
- **Other publishers.** Apply every test below to a workbook downloaded from any other publisher.

A template can serve as your ledger only if it passes every test:

- It has a column for each row's supporting-document reference.
- It keeps money in and money out apart, with transfer, owner and loan categories outside income and expense.
- It records each row's bank or card account, so every statement can be checked.
- Its categories come from one editable list that every total refers to.
- Its totals are readable formulas reaching every row, not typed figures.
- It fits your basis, including receivable and payable records for accrual.
- It shows any period's totals without overwriting an earlier period.

## How do you adapt a template, or restructure an old sheet, without breaking its totals?

Adaptation fails quietly: a total whose range stops short of new rows, or whose formula names an old category, still shows a plausible figure. Work in this order:

1. Keep the downloaded file unchanged and adapt a copy.
2. Note what each total adds up: which range, category names and tabs.
3. Rename and add categories and accounts on the list itself, then check that no summary formula still holds an old name in quotation marks.
4. Delete an unused tab only after checking that no formula refers to it.
5. Make the register a table. Microsoft's Using structured references with Excel tables page (undated) says the names in structured references adjust whenever you add or remove data from the table; with a plain range, check that every total's range runs past the last row.
6. Test with the example's opening balances and rows: confirm the summary matches the figures above, add a row below the last and confirm the totals change, then delete the test rows.

To restructure an existing informal sheet, move its rows into the register columns, add document references and replace typed categories with list values (sorting the column exposes variants), then follow steps 2 to 6 and run the month-end check for each month already entered. Building from scratch needs steps 5 and 6.

## What must the finished workbook satisfy to stand as the record?

The IRS's Automated records page defines a machine-sensible record as data in an electronic format intended for use by a computer, as a spreadsheet file is. Publication 583 says that if you use a computerized system:

- You must be able to produce sufficient legible records to support and verify entries made on your return and determine your correct tax liability.
- The machine-sensible records must reconcile with your books and return.
- The records must provide enough detail to identify the underlying source documents.
- You must keep all machine-sensible records and a complete description of the computerized portion of your recordkeeping system, detailed enough to show the functions performed as data flows through the system, the controls ensuring accurate and reliable processing, the controls preventing unauthorized addition, alteration or deletion of retained records, and the charts of accounts with detailed account descriptions.

Write that description on the About tab: how a row moves from document to register to summary, the month-end check, who can edit what, and where version history and backups are kept. Because Microsoft and Google both say sheet protection is not a security measure, rely on version history and the off-network backup to show whether a retained record was changed. Following these steps does not by itself establish that a workbook meets these requirements; Publication 583 points to Revenue Procedure 98-25 for more.

Publication 583 says all hard copy requirements also apply to electronic storage systems, including those that keep records by electronic imaging, such as scanning, and that such a system must index, store, preserve, retrieve and reproduce the records in legible format. Paper originals may be destroyed, it says, provided the system has been tested to establish that it reproduces them in compliance with IRS requirements and procedures keep it compliant; if the system falls short, you may be subject to penalties unless you keep the originals. Publication 583 points to Revenue Procedure 97-22 for details.

Publication 583 also says you must keep your business records available at all times for inspection by the IRS, and that if it examines a return you may be asked to explain the items reported; the closed copies show a past month as it stood. Keep records, Publication 583 says, as long as they may be needed for the administration of any provision of the Internal Revenue Code, which generally means keeping records that support an item of income or deduction on a return until the period of limitations for that return runs out. Publication 583 sets a separate rule for employment tax records, and says to keep records relating to property until the period of limitations expires for the year you dispose of the property in a taxable disposition. How long those periods run is a separate question, and state requirements are separate from these federal rules.

## When does a simple register stop being enough?

### What changes on the accrual basis?

A register records money when it moves. On the accrual basis the workbook also needs lists of invoices issued and bills received, each with its date, amount and date settled, or the journal-and-ledger layout with receivable and payable accounts; a register alone is not enough. On the list route, the summary takes income and expenses from the lists, when earned or incurred under Publication 538's accrual rule, and register rows settling a listed invoice or bill go to settlement categories, such as Invoice payments received or Bills paid, kept out of income and expense, so each amount counts once.

### What about payroll or inventory?

Both sit outside the register. Publication 583 says there are specific employment tax records you must keep, listed in Publication 15, and its example keeps a separate employee compensation record, including the deductions withheld and the monthly gross payroll. On the cash basis, carry gross wages for the period's pay dates from the payroll record to the summary, as that example carries its monthly gross payroll, and put net pay rows in a category excluded from expense. Split each tax deposit row using the payroll record: the employer's share, which the example counts in its taxes total, goes to a payroll taxes expense category, generally deducted when paid under Publication 538's cash method, and the withheld amounts go to an excluded category, so wages count once. Tick both rows together against the deposit's one statement line. Confirm this mapping with whoever prepares your return.

For inventory, Publication 583 says your supporting documents should show the amount paid and that the amount was for inventory, and points to Publication 538 for methods of valuing inventory; keep purchases in their own category and the year-end count and valuation in a separate record. Publication 583 defines expenses as costs other than the cost of inventory, so keep purchases out of the expense total and show them below the net; it says a business that keeps materials and supplies on hand generally must complete the inventory lines in Part III of Schedule C. Publication 538 says that, generally, if you produce, purchase or sell merchandise, you must keep an inventory and use an accrual method for sales and purchases of merchandise, with exceptions it explains; if that applies, the accrual section above covers them.

### What if several people enter transactions?

Two people saving over one file, or emailed copies, lose each other's changes, so name one custodian and keep one master file. Microsoft's Collaborate on Excel workbooks at the same time with co-authoring page (undated) sets out what co-authoring needs, including a Microsoft 365 subscription and a workbook uploaded to or created on OneDrive, and lets you review changes others have made since you opened the file, including previous cell values. That page does not say Excel records who made each change, so in Excel have the custodian log on the About tab who entered each month's rows. Give each person their own sign-in and restrict formula and closed-month ranges to the custodian. Whether these limits call for accounting software is a separate decision.

## What is the setup order?

Before the first entry, set up in this order:

1. Write the basis, start date, each tab's purpose and the system description on the About tab.
2. List categories from your return's lines plus transfer, owner and loan categories, each described, and the accounts.
3. Create the register table with dropdowns on Account and Category, set to the Stop style in Excel.
4. Enter opening balances from each statement, listing unpaid invoices and bills as your basis requires.
5. Build and test the summary and its tie-out, then delete the test rows.
6. In Excel, unlock the input columns, lock the rest and protect each sheet; in Google Sheets, protect each sheet except the input columns and restrict who can edit it. Then retest.
7. Store the file where version history works, with individual sign-ins and an off-network backup.
8. Schedule the monthly statement check and closed copy.

## Sources

1. Internal Revenue Service — *Publication 583, Starting a Business and Keeping Records*, Rev. December 2024 (Publication 583 (12/2024)). https://www.irs.gov/publications/p583
2. Microsoft — *SUMIFS function*, undated. https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b
3. Microsoft — *Create a template*, undated. https://support.microsoft.com/en-us/office/create-and-use-your-own-template-a1b72758-61a0-4215-80eb-165c6c4bed04
4. Internal Revenue Service — *Publication 538, Accounting Periods and Methods*, Rev. January 2022 (Publication 538 (01/2022)). https://www.irs.gov/publications/p538
5. Microsoft — *Create a drop-down list*, undated. https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b
6. Google — *Create an in-cell dropdown list*, undated. https://support.google.com/docs/answer/186103?hl=en
7. Microsoft — *Protect a worksheet*, undated. https://support.microsoft.com/en-us/office/protect-a-worksheet-3179efdb-1285-4d49-a9c3-f4ca36276de6
8. Google — *Protect, hide & edit sheets*, undated. https://support.google.com/docs/answer/1218656?hl=en
9. Microsoft — *View previous versions of Office files*, undated. https://support.microsoft.com/en-us/topic/5c1e076f-a9c9-41b8-8ace-f77b9642e2c2
10. Google — *Find what's changed in a file*, undated. https://support.google.com/docs/answer/190843?hl=en
11. Federal Trade Commission — *Cybersecurity for Small Business*, September 2025. https://www.ftc.gov/business-guidance/small-businesses/cybersecurity
12. Microsoft — *Using structured references with Excel tables*, undated. https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
13. Internal Revenue Service — *Automated records*, Page Last Reviewed or Updated: 21-Aug-2026. https://www.irs.gov/businesses/automated-records
14. Microsoft — *Collaborate on Excel workbooks at the same time with co-authoring*, undated. https://support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104

## Related questions

- [How should a self-employed person or small business set up a simple bookkeeping and recordkeeping system?](https://uppago.com/resources/how-to-set-up-a-simple-bookkeeping-and-recordkeeping-system)
- [What app, tracker or template should a small business use to record and keep track of its business income — on its own or together with its expenses?](https://uppago.com/resources/how-to-choose-an-app-tracker-or-template-to-record-business-income)
- [How do I perform an account reconciliation in a spreadsheet?](https://uppago.com/resources/how-to-reconcile-an-account-in-a-spreadsheet)