Financial Guides

Excel bookkeeping: How to do small business accounting in Excel

Getting your small business accounting in check is one of the most important things you can do as a small business owner. We cover everything you need to know to get started on your Excel bookkeeping.

Jump to:
section

Table of Contents

One of the most important aspects of small business accounting is managing day-to-day finances. This may range from tracking net income and expenses, to bank reconciliation and everything in-between. A good bookkeeping system can help you accurately record and save all these business transactions.

By definition, bookkeeping refers to the process of recording and categorizing all the financial transactions of your business. Through bookkeeping, you can properly organize all your income and expenses, including information on invoices. This includes customer name, vendor name, date issued, due date, amount, outstanding accounts receivable, accounts payable etc.

Ultimately, proper business bookkeeping can give you a clear picture of your sources of revenue and main expenses. That’s not only important for cash flow management, but you can also use the information to prepare more comprehensive financial statements like a balance sheet, income statement and cash flow statement.

While some larger companies rely on premium accounting software like QuickBooks, if you’re running a small business or startup, you can consider using Microsoft Excel for your bookkeeping needs. The online version of Excel is completely free, which may help you save on business costs.

To help you get started, we’ve put together this Excel bookkeeping guide, complete with a customizable Excel bookkeeping template that you can use for your small business.

Key takeaway

  • Microsoft Excel offers an excellent platform for basic bookkeeping, particularly for small business owners and entrepreneurs.
  • While you can create your own bookkeeping template from scratch, the easiest way is to download a ready-made Excel bookkeeping template. Here’s one that you can download and use for free.
  • A good Excel bookkeeping template should have a chart of accounts, transactions and an income statement.
  • When your business outgrows Excel bookkeeping, you may consider using accounting software like QuickBooks and Xero.

What you can and can’t do in Excel

Microsoft Excel has been around since 19852. As the most used spreadsheet software1, it’s quite adaptable and accessible. You probably already have it on your computer. And if you don’t, you can use the online version for free or download the offline version for a small fee.

Either way, Excel is useful for a wide range of applications, including basic bookkeeping and business accounting. Because of its widespread popularity, there are several Excel tutorials available online that you and your team can use to get up to speed with its bookkeeping functions.

That being said, Microsoft Excel can’t do everything; it does come with some limitations. For example, it doesn’t have the option to automatically import bank transactions to your books. This is where more specialized applications like QuickBooks and Xero get some edge. Here’s a look at the things you can and can’t do with Excel bookkeeping:

What you can do with Excel

  • Create a single-entry bookkeeping system on which you can record transactions manually.
  • Set up and organize a list of accounts. These are transactions that you will record.
  • Categorize transactions.
  • Track sent invoices.
  • Perform basic bookkeeping calculations like addition, subtraction, multiplication and division.
  • Generate an income statement and manually create other financial statements like a balance sheet and cash flow statement.
  • Save all your bookkeeping records on your computer’s hard drive or in the cloud.

What you can’t do with Excel

  • Detect and get notified of data entry errors.
  • Have transactions automatically categorized
  • Automatically import bank account or credit card transactions to your book.
  • Automatically generate financial statements.

So, while Microsoft Excel is helpful for bookkeeping, you’ll still need to put in some work to ensure that all the data you record is accurate. You’ll also need to do manual bank reconciliation and create financial reports manually.

Something worth mentioning is that while Microsoft Excel is the most popular bookkeeping spreadsheet1, it’s not the only one available. Google Sheets is a close alternative, but you can also use WPS Spreadsheets, Apple Numbers, Quip and even OpenOffice if you prefer open-source software. But keep in mind that OpenOffice is discontinued, so it won’t offer much in the way of support and updates.

For the sake of this guide, we will only use Excel bookkeeping, which works pretty much as Google Sheets bookkeeping.

How to do your bookkeeping in Excel

Single-entry vs. double-entry accounting systems

When it comes to bookkeeping, there are two types of accounting systems: single-entry and double-entry bookkeeping. Double-entry is mostly used by big corporations. It’s based on a two-sided accounting system where every transaction affects at least two different accounts in equal measures. One account is debited while the other is credited.

Say, for example, that you sell inventory worth $10,000 for cash. It means that you’ve lost inventory of $10,000 but gained a cash sale of the same amount. Thus, you will record that single transaction twice.

  • First as a debit in the cash account because the company is receiving cash from a sale, and
  • Secondly, as a credit in the inventory account because the company is losing inventory.

Single-entry bookkeeping relies on one-sided accounting, meaning you only record each transaction once. Think of it as a cash book. Many small business owners use this accounting system because of its simplicity. You basically categorize every transaction as either income/profit (coming into the business) or expense/loss (going out of the business).

Thus, if you make a sale of $10,000, you’ll simply record it as revenue worth $10,000. That’s money coming into the business. And if you pay utility bills of $900, you will record it as an expense of $900 because that’s the amount going out of the business.

After entering all the transactions for a period of time (usually a month, but it can be quarterly or annually), you then use them to generate an income statement. Also known as a profit and loss statement, an income statement essentially shows your company’s realized profits or losses for the specified period of time. That profit or loss is what you transfer to the balance sheet.

Therefore, your business accounting system should start with you recording revenue and expense transactions in an Excel spreadsheet. From that spreadsheet, you can generate a profit and loss statement, which you can use to create a balance sheet.

Besides simplicity, single-entry bookkeeping takes less time and minimizes the risk of errors. These are benefits that every small business accounting system can take advantage of. But if your company grows to the point where you think double-entry bookkeeping makes more sense, then you can consider upgrading from Excel accounting to using specialized bookkeeping software like Xero and QuickBooks.

Creating a small business bookkeeping system in Excel

If you have sufficient experience with Microsoft Excel, you may be able to create a bookkeeping spreadsheet from scratch. But if you’re just getting a hang of the program, you can consider using a ready-made Excel bookkeeping template.

There are several websites – including Office Templates – that offer downloadable spreadsheets for income reports, expense reports and other financial reports. However, for the best results, you will want to choose accounting templates that have been specifically designed for small business owners. These usually allow you to record all your transactions on one spreadsheet template rather than dividing them into three or more different spreadsheets.

For example, while Office Templates has a spreadsheet for income and another for expenses, it doesn’t have one spreadsheet that captures both at the same time. Ideally, entrepreneurs need a single spreadsheet where they can record incomes and expenses, and also generate an income statement.

Nearside’s free Excel bookkeeping template offers all that, thus providing a simple yet effective bookkeeping system that you can implement in your small business. Download it today and give it a try.

The spreadsheet template subdivides your accounting system into three parts. Each part is a worksheet on its own. Here’s what you need to know about the parts of our free Excel bookkeeping template:

  • Chart of accounts – this is a list of all the accounts that are in your business bookkeeping system. Each account is essentially a category and every transaction you record is placed into one of these categories. If you’ve already downloaded our free bookkeeping template, you will find your chart of accounts in the first worksheet.
  • Transactions – this is where you record details of every transaction that your business performs. Some information to capture includes the date, description, account and amount of the transaction. You can have all your transactions in one worksheet, or you can divide them into two worksheets – an income report and an expense report. For the sake of simplicity, our downloadable Excel bookkeeping template keeps all transactions in one sheet.
  • Income statement – this section uses information from your transactions worksheet to calculate how much money your business has earned and spent over a specified period of time. You can then use that information to create a cash flow statement and ultimately other financial reports like income statement and balance sheet.

How to use the excel bookkeeping template for your small business accounting

If you’ve already downloaded the Nearside Excel bookkeeping template, you can go ahead and customize it for your small business. We will go through the steps required to adapt and use it to maintain your company books.

Chart of accounts

The first worksheet of the template is named “Chart of accounts”. As already mentioned, this is where all the categories of your transactions go. In short, this account holds your incomes, expenses and costs of sale.

These items vary from one company to another. For example, a company that has taken out a business loan will have interest expense. On the other hand, a company that doesn’t have any loan won’t have any interest to pay. Therefore, you wouldn’t expect it to have interest expense.

For this part, simply enter all the categories that are relevant to your business. All you need to do is make a list of all your company’s sources of income, costs of sales and expenses.

Remember that your chart of accounts won’t look exactly as the one above because different companies have different types of transactions. Therefore, feel free to add or remove accounts as necessary. Overall, this sheet will serve as your point of reference to remind you how to categorize your various transactions.

Transactions

The second worksheet of our downloadable free Excel bookkeeping template contains your business’s transactions. This is where you record details of each transaction, including its date, description, amount, category and type. Make sure that the category and type of each transaction match its category and type in the chart of accounts.

Income statement

By default – if you use the Nearside Excel bookkeeping template – the income statement will automatically pull data from the transactions worksheet and use it to generate your profit and loss statement for the period.

Remember, if you add or remove an account from the chart of accounts, you have to do the same for transactions and the income statement. For example, if you no longer use SEO or any other marketing strategies, make sure to remove “Advertising” from your chart of accounts, then proceed to remove “SEO” from transactions and finally remove “SEO” from your income statement. It’s an expense that you no longer track, thus it should not appear on your financial reports.

Similarly, if you introduce a new account, you will need to add it to your chart of accounts, transactions and income statement. Say, for instance, that you start spending petty cash. To properly record this new business expense, you will have to add it to your “Chart of accounts”, then add it to “Transactions” as an expense, and finally add petty cash to the list of business expenses on your income statement.

Something to keep in mind is that you will also need to adjust your Excel formulas (in the income statement) as necessary every time you add or remove an entry. For example, if you add an expense, make sure that the new formula includes that new entry in your list of business expenses.

If you would rather create your income statement manually, you can download this profit and loss statement template and fill it in with your transactions manually. That’s an option to consider if you’re not comfortable editing spreadsheet formulas.

Keep your accounting sheet up to date

Once you’ve finished customizing your business bookkeeping template, make sure to save it in a secure location. Consider using a cloud service like Google Drive or Microsoft OneDrive, which allow you to store the file on the cloud and still access it offline on your computer. That minimizes the risk of loss, while still giving you unlimited access to your spreadsheet.

With that done, you’ll also want to update your transactions as they happen. You will mostly be using the Transactions worksheet for this. Every time a transaction occurs, record it in the Transactions sheet and then categorize it appropriately (based on your chart of accounts). This applies to incomes, expenses and cost of sales.

Start by recording the transaction. Whether it’s a sale, income from credit card or utility payment, be sure to record it in your Transactions sheet. Capture the date, description, amount, category and type of account. If you use Nearside’s Excel bookkeeping template, it will automatically pick the values and generate an income statement for you. Otherwise, you may need to manually record the same transactions in your profit and loss statement to generate the income statement.

Nonetheless, always make sure to record transactions as they happen. This reduces the risk of entering wrong values for transactions. Plus, it’s generally more tasking to store your receipts and invoices for a month before starting to sort and record them in your bookkeeping system. Therefore, record all outgoing and incoming money as soon as the transaction completes. Here’s an invoice template that you may use to get paid faster and record the cash inflow as soon as it happens.

Finally, don’t forget to create other financial reports. A good business practice is to prepare a cash flow statement, profit and loss statement and balance sheet on a monthly basis. But that’s not written in stone. The Nearside bookkeeping template will generate an income statement each month. You can then decide the frequency you want for your cash flow statements and balance sheets.

In case you’re not sure how to create these other accounting templates, you may find online tutorials to guide you. Here’s a step-by-step process for creating cash flow statements and another one for making a good balance sheet template for a small business.

When to upgrade to accounting software

As your business develops and expands, you may eventually outgrow Microsoft Excel accounting. This happens for many reasons, from increased number of transactions to the need for double-entry accounting and so on. Generally, you’ll know that you’re ready to move on from Excel bookkeeping if:

  • You want to move from single-entry to double-entry bookkeeping to improve accuracy of financial reports.
  • Your transactions are becoming too many that manually entering them is time-consuming.
  • You want to automatically generate complex financial reports rather than entering data manually on a cash flow or balance sheet template.
  • Your business generates enough income to afford paying for premium accounting software.
  • You bring in a professional accountant and they require access to specialized accounting software.
  • Your computer is prone to malware attacks and phishing, and you want a more reliable and secure way to maintain your accounting records.

Among the many bookkeeping and accounting software, QuickBooks and Xero rank quite highly among small business owners. They not only automatically (and instantly) record transactions on your chart of accounts, but they also generate financial statements – including balance sheets, income statements and cash flow statements. They also reconcile with bank statements, do tax calculations and tell you when your taxes are due.

However, they are premium software that will eat into your business income. That’s something to keep in mind before upgrading from Excel. Think you’re ready for QuickBooks and Xero? Check out our Perks to claim up to 50% off and enjoy business accounting software at a discounted price.

Free report and guide
How COVID-19 Impacted Incomes of the Self-Employed Workforce
How did the pandemic impact the income of  gig workers and entrepreneurs? Download to learn more.
Get The Report