Green book with math symbols floating in the background

The Bench Guide to Bookkeeping in Excel (Template Included)

By Bryce Warnes on December 7, 2018

Sometimes life forces you to use spreadsheets. If you’re planning to do your bookkeeping with Excel, this is one of those times.

But there are ways to make the process easier. We’ll show you use, using Bench’s Income Statement Template to get started.

Cartoon illustration of an income statement

Do Your Own Bookkeeping with an Excel Income Statement Template

Enter your transactions into this Excel template, and voila! You get a ready-made Income Statement. Perfect for entrepreneurs who aren't ready for full-fledged accounting software.

First: What Excel can and can’t do

Excel (and its simpler, online cousin Google Sheets) is adaptable. The better you know how to use it, the more you can get done. That being said, even if you’re good enough at Excel to compete internationally, there’s only so much you can do with it in a bookkeeping context.

Here’s a summary:

Bookkeeping tasks you can do in Excel Bookkeeping tasks you can’t do in Excel
Set up a single-entry bookkeeping system, which you update manually Automatically import transactions from your bank accounts to your books
Perform basic calculations to create financial statements Have your transactions automatically categorized for you
Track invoices you’ve sent clients Catch human errors
Save business records on your hard drive, or in the cloud with Excel Online or Google Sheets Get answers to questions about your finances

Single-entry bookkeeping in Excel

Before we get started, download a copy of Bench’s Income Statement Template. Later, you may prefer to use a different template, or even create your own. But when you’re getting started, the Income Statement Template is tidy and approachable. And it has everything you need to get a grasp of single-entry bookkeeping in Excel.

Single-entry bookkeeping is a good choice if you run a small, simple business with a low volume of transactions. Every time money enters or leaves your business, it’s recorded once, as a positive (income) or negative (expense) value.

A quick heads up about double-entry bookkeeping. The double-entry method of bookkeeping is standard for larger, more complex businesses. It’s more effective than single-entry for tracking cash flow and protecting against errors and fraud. However, it’s also more work to maintain—and difficult to set up in Excel. If you’re at the point where you believe your business needs double-entry bookkeeping, it’s time to use accounting software. Or, think about hiring a bookkeeper (like Bench).

What you need to use the Income Statement Template for bookkeeping

Before you get started with Bench’s Income Statement template, make sure you understand the differences between single-entry and double-entry bookkeeping. You’ll also need access to spreadsheet software such as Excel or Google Sheets.

Cartoon illustration of an income statement

Do Your Own Bookkeeping with an Excel Income Statement Template

Enter your transactions into this Excel template, and voila! You get a ready-made Income Statement. Perfect for entrepreneurs who aren't ready for full-fledged accounting software.

Understanding the Income Statement Template

There are three sheets in Bench’s Income Statement Template:

1. The Chart of Accounts

This lists all the accounts that make up your books. Think of each account a category. Every transaction you record gets sorted into one of these categories.

2. Transactions

This contains details of every business transaction you perform—the date, a description, the amount, and the account.

3. The Income Statement

This pulls info from your Transactions and performs some basic calculations. Then it summarizes how much income your business has earned and spent within a defined date range.

Customizing the Income Statement Template for your business

The info in the copy of the Income Statement Template you downloaded is only there as a placeholder. Here’s how you adapt it to your business.

1. Add or remove accounts from the Chart of Accounts

The Chart of Accounts includes an account for “Gas & Auto.” But if you don’t drive for work, you don’t need this row—so you can delete it. The same follows for any other account that doesn’t apply to your business.

Likewise, if there are any transaction categories you need for your business that are missing from the Chart of Accounts, you should add them.

A note about account types:

  • Income designates any account used to track money coming into your business.

  • Expense designates any account used to track money leaving your business.

  • Cost of Sale (COS) and Cost of Goods Sold (COGS) are unique. These accounts track the money you have to spend to create the product you sell your customers or clients. They’re often used interchangeably—COS by retailers, and COGS by manufacturers. Learn more about calculating these expenses.

bookkeeping in Excel 1

2. Clear the Transactions sheet

Before you can start tracking your own transactions, you need to clear the placeholder data.

Select everything in the Date, Description/Transaction, and Category columns, and delete it.

bookkeeping in Excel 2

3. Add or remove accounts from the Income Statement

By default, the Income Statement includes data for every account listed in the Chart of Accounts. If you’ve added or removed accounts from the Chart of Accounts, make the same changes to the Income Statement. For instance, if you don’t track “Gas & Auto” as an expense, delete the row.

bookkeeping in Excel 3

4. Make copies of the Income Statement for each month

In order to generate monthly Income Statements, you’ll need separate sheets for each month. Make twelve copies of the original income statement, one for each month in the year.

Then, on each Income Statement sheet, change the Date Range (Beginning Rate and End Date) to cover the relevant month. Name the sheet after the month it covers.

bookkeeping in Excel 4

5. Optional—Add a sheet for tracking invoices

When you enter income from paid invoices into your Transactions sheet, include the invoice number in the description. That way, you can cross-reference it, and avoid errors—like forgetting to enter invoice payments, or entering the same invoice twice. This is a lot easier if you’ve got a separate sheet to track your invoices.

First, download a free invoice template for Excel, or get one for Google Sheets. Then, add a new sheet to your Income Statement Template. Copy and paste the data from the invoice template you downloaded into the new sheet.

Keep the tab for your invoice tracker to the right of the Transactions sheet, but to the left of your monthly Income Statements. It’ll be easier to check for reference.

6. Optional—Add a sheet for projecting cash flow

Spreadsheets aren’t up to the task of creating a proper Cash Flow Statement that you can present to investors. However, for your own personal planning, you can still plan your cash flow month by month in a simple spreadsheet.

To get started, check out Shopify’s Google Sheets Cash Flow Projection Template. Copy and paste it into a blank sheet next to your Transactions.

Keeping your books up to date in Excel

Once you’ve customized the Income Statement Template for your business, you’ll mostly be using the Transactions sheet.

Input and categorize transactions

Every time a transaction takes place—whether you’re moving money, cash, or credit—you need to enter it into the Transaction sheet, in its own row. Then you need to categorize it.

When you use Bench, your transactions are automatically imported and categorized for you. But for DIY bookkeeping in Excel, you’ll be doing it yourself.

  1. Get a record of the transaction. That could be a credit or bank card statement, info in your Paypal account, or receipt from a cash payment.

  2. Enter the date the transaction was recorded under the Date column, using DD/YY/MM format.

  3. Categorize the transaction. Enter its relevant account under the Category column. Be sure to type it exactly as it appears on the Chart of Accounts. The Income Statement will look at the data you enter in order to figure out how the transaction will be categorized.

  4. Add any notes for yourself in the Description/Transaction column.

Save income sheets

By the end of every month, the Income Sheet should have all the data it needs to summarize your bookkeeping for that period. Make sure to keep the Income Sheets separated, organized by month.

You may want to make a copy of each one at the end of the month, and either save it or upload it to the cloud. Come tax season, your accountant will need your income sheets for the year.

Outgrowing bookkeeping in Excel

If your business keeps growing, you’ll eventually need to move on from Excel.

Signs you’re ready for a bookkeeper:

  • Manually entering and categorizing transactions for your books is starting to take up too much of your time. (This can happen when you start processing more transactions per month)

  • You decide to upgrade to double-entry bookkeeping

  • You decide to switch to accrual accounting

  • You need complex financial statements such as a Balance Sheet or Cash Flow Statement

  • You start working with an accountant, and they need access to professionally kept books

  • You’ve just experienced a hectic tax season, and realize you need to be better organized for next year

  • You’ve experienced a phishing or malware attack on your business computer, and need a more secure way to maintain your business records

  • You have bookkeeping questions that can’t be answered by searching online

Sound like you? Learn how to dump spreadsheets and outsource your bookkeeping.

Bookkeeper

Want a free month of bookkeeping?

Sign up for a trial of Bench. We’ll do one month of your bookkeeping and prepare a set of financial statements for you to keep. No pressure, no credit card required.


This post is to be used for informational purposes only and does not constitute legal, business, or tax advice. Each person should consult his or her own attorney, business advisor, or tax advisor with respect to matters referenced in this post. Bench assumes no liability for actions taken in reliance upon the information contained herein.

Friends don’t let friends do their own bookkeeping. Share this article.

Want a free month of bookkeeping?

Sign up for a trial of Bench. We’ll do one month of your bookkeeping and prepare a set of financial statements for you to keep. No pressure, no credit card required.

Decorative patterns