Easy Bookkeeping Spreadsheet

Easy Bookkeeping Spreadsheet in Excel

Bookkeeping! The word is enough to drive anyone who is currently running a business into a panic. Keeping the books on all of your accounts can be something that you put off until you have time to it. The trouble is that that time rarely, if ever, comes.

The trouble is, you're too busy actually running your business. So if why should you do your accounts when there's another painting job to be done, car to be repaired or wall that needs plastering.

Then at the end of the month, or longer, you sit down and look at all the receipts that have piled up. You then look at the blank spreadsheet on your screen. Or it could be worse, it could be that you're looking at an accounts package or complicated spreadsheet template seeming more designed for a large organisation's tax return than simply being a record of expenses and money received.

You see that's all that you need as a one person band. You simply need the ability to total up all of the money you've received. Then add up all your expenses and see how much you have left.

Also, a running total might be handy. That way you can see how your finances are at any point in time. Then, looking back, you can see when you had the most money and when you had almost nothing.

If you simply want to download the template you can find it at the bottom of this page.

This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everyday Excel hints and tips.

Column Headings for your Bookkeeping Spreadsheet

Create Column Headers - 2:18

The first thing you need to do, after opening a blank spreadsheet, is to enter in the column headings. These headings will determine the information you will enter into your accounts spreadsheet.

Date	Under this heading you will enter the date of the transaction. Meaning every time you buy or sell something you will enter in the date.
Description	Under this heading you will type the description of the transaction. Try to be as consistent as possible when typing in repeated receipts and expenses so that the match the previous one. You will learn more about the importance of this later on.
Expense	If your entry is an expense then you will enter the number here in minus or negative numbers. This is because it is an expense you will be reducing your money.
Received	Any money received from work, you will enter under this column. You will do this as a positive figure. Because it is increasing your money.
Running Total	In this column you will enter a formula that will calculate the running total. This will allow you to see how much money you have at any one time.

Entering Accounts

Now that you have your column headers, it's time to start entering your accounts. The key here is to be accurate.

Dates

How to Enter Dates the Correct Way in Excel? - 4:21

When it comes to entering dates you should enter them using the forward slash. For example: "01/01/2021" would be the way you would enter the 1st of January 2021.

Quick Tip: Hold Ctrl & ; to the current date.

Description

In entering the description of the expense or receipt you should be consistent. You also don't need to type in too much detail. So, for example, if you spent time mowing Mr Henderson's lawn you could enter something like "Henderson - Lawn". This would be preferable to writing: "Mowed Mr Henderson's lawn in the morning".

The more consistent you are, the easier it is to analyse your accounts. Why would you want to analyse your accounts? Well consider the following situation:

Your Gardening business has really taken off and you are maxd out and you need to drop a client or two so you can have time to work on your business. So you want to see how much money each one of your clients is worth to you. Financially that is.

If you wrote "Henderson - Lawn" consistently every time you mowed Mr Henderson's lawn it will be so much easier to work out how much money you've earned from Mr Henderson. Coupled with the date you entered earlier you'll also be able to see how Mr Henderson has paid you each month. No matter how frequently or infrequently you mow Mr Henderson's lawn or any other job for Mr Henderson, entering consistent data makes it straightforward to see how much he's spending with you.

Entering Accounts Description - Good Example

Enter Accounts Description - Bad Example

The chances of the bad example being consistent are extremely low. Plus you don't need the extra words like "Went to do" or even the the type of flowers you were working with. That is, unless, you're a flower shop and want to analyse the most popular flowers.

Expenses

When it comes to entering expenses you will enter them using negative values. This is because each expense is money that you pay out to run your business. Things like lunch, supplies, anything you need for the day to day running of your business.

Changing Numbers to Currency in Excel

Change Numbers to Currency - 5:21

Once you've started entering numbers in Excel you'll want to know how to change numbers to currency on Excel. One time waster is typing in the Pound or Dollar sign every time you add your expenses or receipts. A better practice is to change the look, or format, of the numbers all at once. To change the format of expenses and receipts in Excel you can:

1. Use the columns at the top to select the expenses and receipts column.
2. Click on the Accounting Number Format button.

Now, when you start entering your accounts expenses and receipts in Excel, the numbers will have the currency symbol added. If you are asking how do I change the currency in Excel, you can use the tiny drop down triangle to the right of the Accounting Number Format button.

As you can see you can choose either Dollars or Euros. If you are looking for a currency other than Dollars or Euros in Excel then from the Accounting Number Format drop down list you can choose "More Accounting Formats". You can then use the Symbol drop down arrow in the Format Cells dialogue box to choose the currency format you require.

Receipts or Money Received

When it comes to money received I must admit that I was a little confused when at first I looked at the work receipt. You see I thought that a receipt was something I was paying out or spending. This was because every time I bought something, I would receive a receipt. So it must be the money I spent right? Wrong!

In accounting a Receipt is the word used to describe the money received. That is money that you have received. So whether, in your accounts, you use the column header "Receipts" or "Received" is up to you. Just as long as you're clear that the purpose of this column is to list all of the money you have received for any work you're done.

So if you've been following along with the above tutorial, you should now have an Excel Bookkeeping Spreadsheet that looks similar to this:

Sum Column in Excel

Easily Add Up Numbers in Excel - 9:04

Now that you have taken the time to enter all of your expenses and receipts in your bookkeeping spreadsheet, it's time to add up how much you've made and how much you've spent so you know how much money you have left.

By knowing and understanding how to sum column in Excel the task of totalling you're expenses and receipts is quick and straightforward.

Insert Blank Rows in a Sheet

Before you start typing in the formula to sum your bookkeeping sheet you need to know how to insert blank rows in a sheet. It's so much better that these summary formulas be at the top of your bookkeeping spreadsheet because these are the figures that you will be looking at most often.

1. Select the first three rows at the top of the sheet.
2. Press the right mouse button to display the option menu.
3. Click Insert

Excel - Sum Rows

Now that you have inserted multiple rows you can use Excel to sum the rows of your expenses and received columns. Just so you know after you've finished your bookkeeping spreadsheet should look something like this:

Of course the numbers might be a little different but so long as the formulas are correct, you'll be fine. Here are the formulas:

Cell	Formula
B1	=SUM(C5:C10000)
B2	=SUM(D5:D10000)
E1	=B1+B2

You can either type the formulas in or use the Auto Sum button.

Auto Sum

If you would prefer to use the Auto Sum button to help you write the formula, do the following.

1. Click in cell B1. (This is where you would like your answer displayed)
2. Click the Auto Sum button at the top right.
3. Click and hold the mouse button on cell C5.
4. Move your mouse down the sheet until you get to at least cell 10,000. (Here is how it should look when you let go of the mouse button).
   The formula bar at the top will show that I'm over 10,000 rows
   
   
5. To finish press "Enter" on the keyboard.

You will need to do the same to add your receipts. But this time you will sum column D.

Current Balance

Work Out the Current Balance - 10:40

OK, nearly done, you now need to know how much money you have. This is known as the current balance and is calculated by taking away your expenses from your receipts. Now, in this bookkeeping template, your expenses are already entered as negative numbers you will add them. This is how you work out the current balance.

1. Click in Cell E1
2. Press the "=" key on the keyboard
3. Click cell B1
4. Press the "+" key
5. Click cell B2 then press enter.

You should now see your current balance.

Running Total

How to Work Out the Running Total in Excel? - 11:19

Sometimes seeing the current balance isn't enough. Maybe your bank charged you for going into the red once during the last month. You want to compare your figures with the banks to see if they match. This is now possible without a running total.

A running total will allow you to see your balance at one point in time. So if your bank said that you went into the red on the 15th of the month you could check to see why.

How to Calculate Running Total

To create a running total you will use the Auto Sum feature. However, you will also need to know how to do cumulative sum in Excel. To do this you will add a dollar sign to the first part of the formula. The purpose of the dollar sign is to fix the top part of the formula. This way the running total becomes a cumulative sum adding up all the previous numbers as you copy the formula down the sheet. This is how to do it:

How to do Cumulative Sum in Excel

1. Click Cell E5.
2. Press Auto Sum or Type =SUM(
3. Select Cells C5 to D5
4. In the formula bar click between the C and the 5 and enter a $.
   (Your formula should read: =SUM(C$5:D5).
5. Click and drag the Auto Fill square in the bottom right corner all the way down say about 10,000 rows.
   

Now, because you've added a dollar sign in between the C and the 5 you have fixed that part of the formula. Think of the dollar sign as a nail. So in effect you are nailing the first part of the formula so that as you Auto Fill down the SUM function is cumulative Sum. See the below video to see an example.

Or if you want to jump to the Auto Fill section in the tutorial and see how to apply a formula to an entire column. Use Auto Fill in Excel - 12:58

There you have it, all you need to do the basic bookkeeping in Excel. In the next tutorial we will focus on how to make your spreadsheet look pretty. All the accounts tutorials along with a comprehension quiz can be found on our Easy Spreadsheet Accounts page. Make sure you come back regularly to see updates.

Other Excel Accounts Tutorials