Need Help? Give us a call.
1 (800) 386-3372
Doing your own bookkeeping in Excel makes sense if your business is just starting out, and you don’t have a professional bookkeeping solution in place. Before your business expands, you may find you can handle the general bookkeeping tasks needed to stay organized and maintain your business’s finances.
But if using Excel intimidates you (you’re not alone!), this article will make it manageable with ready-made templates and simple directions. Here’s what you need to get started doing your own books.
When you do your bookkeeping in Excel (or any other spreadsheet software), you’ll be doing things like:
This article will focus on the single-entry method of bookkeeping. It’s pretty straightforward—every transaction is entered once and categorized as either leaving your business (expense/loss) or entering it (profit/income).
The transactions you enter are used to generate an income statement, which summarizes your income and expenses for a specific period of time (usually a month). That way, you can understand how your business is making or losing money.
A quick aside about double-entry: The double-entry method of bookkeeping is standard for larger, more complex businesses.
Not everyone is confident in their Excel skills and you might not have the time to build bookkeeping sheets from scratch. To get started with single-entry bookkeeping, your best bet is to download a template.
This income statement template for Excel from Bench is a good place to start. It’s got everything you need to enter and categorize transactions and generate income statements. All your transactions—both income and expenses—are recorded on one sheet. Then the income statement sheet uses that info to generate a summary.
If you’d prefer to use Google Sheets rather than Excel, check out this basic profit & loss workbook from The Spreadsheet Alchemist. Make a copy of the Workbook for your own use by selecting “File > Make a copy…” and save it to My Drive.
In this workbook, expenses and income are recorded on separate sheets. And, in order to create an income statement, you’ll have to manually add up some transactions, and input them into the income statement.
Regardless of what template you use, you need to be sure it has each of the following:
Here are the main steps for how to create a bookkeeping system in Excel.
Make a list of every type of expense or income your business uses, and create an account type for each. List those account types in the chart of accounts. You’ll use this sheet for reference when you enter and categorize transactions later.
Every business has different income and expenses. For instance, if you travel for work, you might have an account for “Gas & Auto.” If you telecommute, you might have one for “Monthly Internet.”
Each account should be categorized as one of the following:
Next, enter the info from your chart of accounts into your income statement sheet. Include all your accounts, but keep them separated according to whether they’re income, expenses, or COGS. Your template should have separate sections for each.
Your template only comes with one income statement sheet. In order to generate monthly income statements, you’ll need separate sheets for each month. Make 12 copies of the original income statement, one for each month in the year. Label each one, and be sure to enter the date range in the appropriate cell.
When you enter income from paid invoices into your transactions sheet, you should include the invoice number. That way, you can cross-reference it, and avoid errors—like forgetting to enter invoice payments, or entering the same payments twice. It’s much easier to cross-reference 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, create a new sheet in your template. Copy and paste the contents of the invoice template you downloaded into the new sheet.
Keep the tab for your invoice tracker next to your transactions sheet—it’ll be easier to check for reference.
Spreadsheets aren’t up to the task of creating a proper cash flow statement that you can share with potential investors. But, for the sake of your own personal planning, you can still use a simple spreadsheet to plan your cash flow month to month.
To get started, download our cash flow template. You can copy and paste it into a new blank sheet next to your transactions.
Once you’ve customized your bookkeeping template for your business, you’ll mostly be using the transactions sheet to keep everything up-to-date. Here’s how:
Every time a transaction takes place, you need to enter it into the Transaction sheet, in its own row. Then you need to categorize it.
Some templates, like the Bench one mentioned at the beginning, will automatically generate an income statement for you, by pulling your transactions into the income statement tab. If you’re using a different setup, you’ll need to do it by hand.
At the end of each month, review your transactions for that time period. Then add totals for each account.
For instance, let’s say you have three expense transactions categorized as “Computers or Equipment.” Take the dollar amounts from those three transactions, and add them together—you’ll get your “Computers or Equipment” total for the month.
You might want to create an extra “scrap” spreadsheet where you can take note of these numbers for the time being.
Once you’ve totaled each account, copy that info to your income statement sheet for the month.
As your business grows, you’ll find DIY bookkeeping becomes more complex and time-consuming. Eventually, you’ll need to invest in a professional solution. Until then, with the help of this article, you know how to create a bookkeeping system in Excel.