Bookkeeping Spreadsheet

This bookkeeping spreadsheet will help anybody wanting a basic template in Excel for tracking self-employed business income and expenses.

What's in this Free bookkeeping spreadsheet

This Excel template is a workbook containing six sheets.

Here is a summary of those sheets - their name and what they are for:

1. About: An explanation of who designed this workbook and what it's for. 
2. How to Use: Describes the purpose of this spreadsheet and the five main steps to follow to use it.
3. Categories: Enter the categories for grouping similar kinds of transactions (more details below).
4. Transactions: The main sheet for entering your income and expenses.
5. Summary: Provides a summary of all the income and expenses grouped in their categories.
6. Examples: Shows filled in examples of the three main worksheets which are the Categories, Transactions and Summary sheets.

There are screenshots of these sheets further on in this article.

You must have Microsoft Excel to use this bookkeeping spreadsheet.

If your version of Excel is too old the Summary may not work as it is a pivot table.

the benefits of using this spreadsheet

You can use it to record all your income and expense transactions from any source like your bank accounts, credit cards or cash.

You do not have to record or track any bank account balances or credit card balances.

You don't even have to record specific dates but you can enter months.

The summary will help you quickly see if you are making a profit or if you are losing money.

This spreadsheet isn't locked, so if you are familiar with using Excel, you can fully customize it to suit your requirements.

Please note, this workbook contains a pivot table type report which may not work in the Open Office software if that's what you are using.

How to use this Bookkeeping Spreadsheet

Step 1: Look at the examples on the Examples sheet.

Step 2: Go to the Categories sheet and key-in your types of income and expenses. There is a large list to prompt you.

Step 3: Go through all your bank and credit card statements, or shop receipts at least once a month and highlight or mark the income and expenses transactions that relate to your small business activities.

Step 4: Go to the Transactions sheet and enter in all the business income and expense transactions that you marked off above. Refer to the examples sheet if you get stuck.

Step 5: Go to the Summary sheet and "refresh" the pivot table with will instantly give you an overview of all transactions.

Categories Sheet

There is a long list of account categories on this sheet to give you ideas of what account categories you can use, but it is far from an exhaustive list.

If the term 'account categories' has you stressed out, don't be! These are just header names under which you will list similar types of transactions*. 

For example, perhaps on one day you buy printer paper, then on another day you buy a file, then on another you buy a paper punch (all specifically for your self employment of course, not for personal use) then you can use a header (account category) called "Stationery". 

So, you will choose your categories and type them into the list in Column A.

It's important to keep them within this column because it forms the list that is accessed on the Transactions sheet.

There are 250 rows into which to type your categories.

You can type them in any order and then sort them into alphabetical order by clicking on Data (top of your Excel screen), then AZ sort.

transactions sheet

You will enter your transactions* into this sheet. The columns/rows are in a data table.

*By the way, a transaction is when you pay for something or somebody pays you and it is listed on your bank statement or credit card statement or receipt.

You do not have to enter dates but you must at least enter the month for each transaction if you want to see a month by month total on the Summary sheet.

You can insert more columns into this table if you want extra details. If you want less columns you could delete the Description column.

You must enter new transactions inside this table (the rows with borders) if you want the transactions included on the pivot table in the Summary sheet.

Inside all the cells of the Category column are drop-down arrows - these link to your list of account categories so you can easily select which account you want to use without having to try and remember them. The list will be blank when you first use this workbook until you complete the Categories.

The table can be extended by dragging the small corner mark.

Summary Sheet

As already mentioned, this is a pivot table and is a summary of your transactions. 

When you enter new transactions into the Transactions sheet you must Refresh the pivot table otherwise it won't show the new information. 

Either right click the summary and tap "refresh" or type "refresh" in the search field at the top of your Excel screen.

The information can be filtered to show one month at a time or all months, or any in-between.

You may see "(blank)" data which is just because the pivot includes the blank rows on the Transactions sheet. This "(blank)" data can be filtered out.

The Profit/(Loss) is the difference between Income and Expenses (so Income minus Expenses).

If expenses are more than Income the result is a loss - meaning you are losing money. 

This summary can help you with totals when you have to complete your tax return.

But make sure you do not include any personal income or expenses, or non-deductibles in this spreadsheet.

Here is more about deductible and non-deductible expenses - also called allowable expenses.

Looking for an Excel workbook that can track your bank balance/s and provide more details than this Bookkeeping Spreadsheet?

Try my free Excel Cash Book.

Site Sponsors

From The Blog

• 

1. How is Sales Tax Calculated

   Jul 25, 24 01:07 AM

   
   How is sales tax calculated? The main formula is: the price multiplied by sales tax rate, see more formulas, lots of examples, get free Excel calculator.

   Read More

2. What is Sales Tax

   Jun 05, 24 11:07 PM

   
   What is sales tax? It is a consumer tax charged to customers and paid on to the government by the business making the sale. Technology can help automate this process.

   Read More

3. Types of Bookkeeping Accounts Used To Organize Income and Expenses

   Nov 21, 23 08:10 PM

   
   The main types of bookkeeping accounts are assets, liabilities, equity, revenue and expenses which can be further classified into real and nominal accounts

   Read More