An Overview of Bookkeeping in MS Excel

An Overview of Bookkeeping in MS Excel

Done are the days when we people are using the old paper ledger for accounting. Although some are still using it nowadays, they tend to be very uncommon already.

The usage of automated tools in bookkeeping has become a significant factor in accounting. One of the most widely used tools in bookkeeping is MS Excel.

In this article, you’ll learn its basics and how you can use it for bookkeeping.

The Basics

After making a spreadsheet you shall use a template for the following sheets:

  • Accounts
  • Transaction
  • Income statement

Customization of the template is what you will need to do because there is no specific template for accounting. Compared to other tools used in bookkeeping, MS Excel is not fully automated.

You still need to do most of the tasks manually. However, you’ll get more accurate results using MS Excel as the margin of error is slim. This is still 100 percent better compared to paper ledgers.

You should classify your daily transactions inside the worksheet. You must ensure that your books are updated and error-free. Being organized and updated is what you must aim at all times.

Before anything else, do not expect that MS Excel is like QuickBooks, You should manage your expectations as MS Excel is good all-around software but not specially catered for bookkeeping alone.

Creating the System

Creating the system is the next thing to do after you have all done your research. Everything is pretty simple, once you have the basic knowledge of it, you can already tweak it on your own.

First Step: Open a Bookkeeping Excel Sheet Template

You can opt not to use a template and make your own. However, it will save you a lot of time and effort if you will use the already given template.

Bookkeeping templates are available all over the internet. You will not have any problems finding it as it is a very popular MS Excel template.

Afterward, it is now the time that you can divide the template into 3 sheets. This is the one that we have discussed a while ago namely, the accounts, transactions, and income statement sheets.

The income statement sheet is the first one that you will see.  This is where the records of your transactions will be placed. Your earnings and expenditures are encoded here. In short, this is a summary of the activities that you’ve had in your business.

Second Step: Customization of the Chart

The balance sheet is the first chart to customize. This is important as the accounts chart is considered as the reference point for categorizing your transactions.

Your goal is to create a list of your income and expenses. Afterward, you have to make an account for them. Be sure that you are using the bookkeeping template before anything else.

Take note that charts are not required to be identical to each other. This is because the income and expenses of every business vary.

To give you an idea, here are the categories that you can put on the accounts:

  • Income: For tracking earnings in your business
  • Expenditures: For the cash that you are spending on your business
  • Capital returns: It is also known as the cost of goods sold or COGS. It is the cash that you shelled out for making a product you liquidate.

Third Step: Modify your Income Statement Sheet

In this sheet, this is where you will input the data from your accounts chart. Do not forget to make a copy of this sheet because you will need it for your monthly tally. It is advisable to make 12 sheets in advance which is usable for a year.

Fourth Step: Make a Sheet for Invoice Tracking

This is an optional but very useful step when doing bookkeeping in MS Excel which is perfect for cross-checking. With this, you can prevent inputting errors on your invoice details.


Fifth Step: Create a Cash Flow Projection Sheet

Although this is not required, it would be a huge help to have this. It will let you have an overview of the financial aspect of your business. It will make things more organized for you.




After you have done all the steps, it is now time to save the file. Be sure that you save it in a secured location. This might take a while to master but once you took a full grasp of it, you’ll surely benefit from it.




Leave a comment

Your email address will not be published. Required fields are marked *