Excel Bookkeeping Template: Templates and Setup
An Excel bookkeeping template can keep cash records, receipt references and period totals in one local workbook, with separate input cells and protected formula columns.
Income: public sample
Fields from the original workbook. Filled rows are fictional examples; blank fields are for your records.
Reporting amounts use USD in this worked example. Dates use YYYY-MM-DD.
| Date | Reference | Company | Payer | Entry type | Amount | Account | Source | Signed income |
|---|---|---|---|---|---|---|---|---|
| 2026-01-05 | IN001 | A | Fictional client | Income | 500 | Checking | INV001 | Calculated in workbook |
On this page8
Open the blank XLSX for live entries and keep the fictional worked copy for checks. Set the company and currency first. Enter true dates, positive amounts and an Expense or Refund type. Review the signed amounts and monthly totals before you add more records. Sheet protection limits accidental edits; file access and backups need their own controls.
Open the Excel bookkeeping template
Use the macro-free XLSX format. There is no need to enable macros or add a bank connection. Save your working copy under a name that identifies the company and period. Keep the original blank and worked files in a separate folder so you can return to them after a mistake.
Read Start Here before entering rows. It states the currency rule, row capacity and expected worked totals. Open Settings and choose the reporting currency and company. The example uses USD and companies A and B. Those IDs are fictional; replace them with clear identifiers for your own records.
Enter and check the cash records
The Transactions tab separates inputs from formulas. Date, ID, company, project, payee, purpose, category, type, amount, currency, source, account and status are inputs. Signed amount and Exception are formula columns. Their different fill helps you avoid typing over them.
Use a nonnegative amount for both purchases and refunds. The type chooses the sign. For the worked sample, the 120 purchase and 20 refund produce a net Office cost of 100. A row with a different currency or an unknown type needs correction before its amount can form part of a sound summary.
| Check | Expected result in the fictional file |
|---|---|
| Company A, January | 180 recorded cost |
| Company B, January | 70 recorded cost |
| Company A Office, January | 100 |
| Company A Travel, January | 80 |
| Company A February boundary row | 999, outside January |
Scroll for more →
The formula uses the same row boundaries for the amount range and every criteria range. That rule comes from SUMIFS semantics. A formula may look almost right while missing the newest records, so always check the last entered row after a large paste or import.
Add rows without losing formula coverage
The supplied range is rows 2 through 1001. A thousand input rows are a capacity boundary, not a promise that any later row will enter every report. Before extending the file, identify every reference to that boundary. The summary, category, project and exception checks must cover the same expanded range.
Copy formulas into the new rows and extend validation. Check the named table range as well. Then put a small known example in the last new row, confirm that the intended total changes and remove that temporary example from the live file. Keep its check result in your own review record.
Unprotect the sheet only when you need to make a controlled edit. No password is required by the pack. Protect it again after checking the changes. This is an accidental-edit guard, not a security feature. Someone with access to the file can copy or alter its contents.
Fix common Excel entry problems
A date imported as text can sit outside a date-based total. Check the actual cell type, not only the displayed pattern. Set a consistent date display such as year-month-day and compare the source before converting a column of ambiguous dates.
Numbers can arrive as text too. A currency symbol typed into a text field is different from a numeric cell with a currency display format. Keep the entered amount numeric and the currency code separate. Formatting cannot convert a foreign amount to the reporting currency.
If a summary displays an error, inspect the exception column and the relevant rows. Do not wrap the total in a formula that turns every error into zero. That would hide a missing or invalid input behind an apparently harmless number. Resolve the row or state the uncertainty clearly.
Print, export and back up
Use the print preview for the tab you need. The workbook repeats the header row and fits columns to a page width. Check the smallest text before printing a long register. For handwritten use, the separate PDF forms provide larger writing areas than a compressed spreadsheet.
Keep an XLSX copy when exporting CSV. Each CSV holds one table of input rows. It does not retain formulas, validation or the rest of the workbook. Reopen the export to inspect dates, decimal marks and IDs before passing it to another process.
Back up a reviewed copy to private storage under your normal access controls. A saved file on one computer is not a second copy. Keep a way to recover a prior version if a later import replaces formulas or duplicates rows.
FAQ: the Excel file
Do I need to enable macros?
No. The pack uses ordinary spreadsheet formulas and input validation. Keep the file in XLSX format. If a message asks you to enable macros, check that you are opening the supplied file and not a different attachment. The workbook does not need executable code to calculate the stated totals.
Why is a formula cell locked?
The supplied sheet protection helps prevent accidental edits to calculated columns. Input cells remain editable. You can unprotect a sheet to make a planned change, then check and protect it again. This guard does not encrypt the file or limit who can read confidential records after receiving a copy.
What happens after row 1001?
The supplied formulas and checks use a fixed range ending at row 1001. Extend every relevant range, formula and validation rule before recording more rows. Check a known amount at the new boundary. A visible row is not proof that every summary already includes it, even if it looks like the rows above.
Can I use the workbook in Sheets?
Import a private copy into Google Sheets and save it in that format. Recheck dates, formula results, validation and protection after import. Keep the original XLSX as a reference. The local pack does not supply a hosted Sheets link or change access permissions in your Google account for you.
Sources and checks
Checked 15 October 2026. The examples are fictional and the arithmetic is original.
- IRS records: Business recordkeeping and supporting documents.
- Excel SUMIFS: Official multiple-criteria sum syntax.
- Sheets SUMIFS: Official Sheets multiple-criteria sum syntax.
- IRS books: Publication 583, December 2024 edition, Recordkeeping System section.
Related records and guides
Verdict on the Excel bookkeeping template
Use the Excel bookkeeping template when a local record file fits your work. Preserve its formula columns, check the range boundary and save reviewed copies. The file is useful only when entered records remain traceable to their sources.
Keep the worked file beside your records
Compare the fictional example with the blank fields before starting.