How to use Excel for accounting and bookkeeping

When you start your business, time and money may be in short supply. Your top priorities may be to find customers and deliver a great product or service. So you may not have automated administrative tasks like bookkeeping and accounting when you open your doors.

Many small business owners use an Excel accounting template when they start operations. Most people are familiar with Microsoft Excel, and using the application is straightforward, plus, it provides you with a free accounting solution while your business is still in its early stages. But keep in mind that Excel bookkeeping is not a solution for a growing business. As your business grows, you’ll need to post more accounting transactions. And bookkeeping in Excel can lead to errors and inefficiency.

In the meantime, you’ll still need to understand the accounting process and how you can complete each task using Excel accounting. You’ll need to set up accounts, post-financial transactions, and create financial statements using Excel.

Let’s look at an example. Centerfield Sporting Goods opened on January 1, 2020, and are using an Excel bookkeeping template to manage the business. The first thing they’ll need to do is create a chart of accounts.

How to start a chart of accounts

The chart of accounts lists every account number and the account’s description. Balance sheet accounts, such as cash and accounts receivable, are listed first, followed by income statement accounts. Centerfield uses one revenue account (#6000 sales) in the income statement and several business expense accounts.

Centerfield manufactures and sells baseball gloves. The business uses the cost of sales accounts for both materials and labour. They can add, remove, and change accounts as needed.

Once you build a chart of accounts in an accounting spreadsheet, create a separate Excel document to post journal entries.

Centerfield’s chart of accounts

How to post transactions using journal entries

An accounting journal entry is a record of each accounting transaction listed in chronological order. Bookkeepers post financial transactions using a journal entry.

The journal includes the journal entry number, accounts, dollar sign amounts, and a description of each entry. Journal entries include debits and credits that determine where to post a particular dollar amount in the entry.

Centerfield’s journal entries

What is a debit in accounting?

Post debit entries on the left side of each accounting journal entry. Asset and expense accounts increase with a debit entry, with some exceptions. In entry No. 1, inventory (an asset account) increases with a $10,000 debit.

What is a credit in accounting?

Post credit entries on the right side of each journal entry. Liability and revenue accounts increase with a credit entry, with some exceptions.

Accounts payable, for example, is a liability account that increases with a credit. In entry No. 2, accounts payable decreases with a debit when Centerfield pays cash to remove a payable balance.

The total dollar amount you post to each debit account must equal the total dollar amount of credits. Each accounting journal entry has an equal amount of debit and credit dollar amounts. The total debits and credits for the partial listing total $20,000. The number of debit and credit entries, however, may be different.

Using the balance sheet formula to post journal entries

The balance sheet formula (or accounting equation) determines whether you use a debit or a credit for a particular account. The balance sheet is one of the three basic financial statements that every business owner should analyse to make financial decisions. Business owners also review income statements and cash flow statements.

A balance sheet reports your business’s assets, liabilities, and equity as of a specific date:

• Assets are what your business owns. Assets are the resources you use to produce revenue.
• Liabilities are what your business owes to other parties. Liabilities include accounts payable and long-term debt.
• Equity is the difference between assets and liabilities. You can think of equity as the true value of your business.

A balance sheet formula connects the balance sheet components. Add liabilities to equity to determine your assets.

Assets = liabilities + equity

You can use the formula to create financial statements. But assets must stay balanced with liabilities and equity.

Assume, for example, that a business issues a $10,000 bond and receives cash. The company posts a $10,000 debit to cash (an asset account) and a $10,000 credit to bonds payable (a liability account).

On your balance sheet, you’d add the $10,000 increase in liabilities to the $0 change in equity to get a $10,000 assets increase.

$10,000 assets increase = $10,000 increase liabilities + $0 change equity

Accounting software ensures that each accounting journal entry balances the formula and total debits and credits.

How to post accounting journal entries using Excel

Follow these steps to create accounting templates for your journals each month:

• Copy and paste your chart of accounts into the spreadsheet.
• Set up the column headings for date, account number, account title, etc.
• Add rows to the document each time you need to post a journal entry.
• Copy and paste the account numbers and account titles from the chart of accounts into the entry.

Use formulas to ensure that total debits equal total credits. Add a total formula that tracks the total dollar amount of debits and credits for all accounting journal entries. Now, you’ve created your journal entry for your general ledger.

How to use the general ledger to create financial statements

A company’s general ledger is a record of every transaction it posts throughout its lifetime, including all journal entries. Business owners and accounting professionals use the data in the general ledger to create financial statements.

General ledgers sort financial transactions by account. Each account lists the journal entries that posted activity to the account during a particular month. General ledgers provide the date, journal entry, and the entry description, along with the debit or credit amount and the ending balance.

It’s important to note the cash general ledger page only lists the cash portion of each journal entry. Journal entry No. 1 only lists the $10,000 reduction (credit) in cash. The inventory increase (debit) is listed in the inventory general ledger but not in the cash general ledger.

The benefit of the general ledger is that you can review every journal entry that impacted a particular account. Unfortunately, creating a general ledger with general journal entries using Excel is time-consuming. And a large number of data entries may increase the risk of error.

Centerfield’s general ledger

How to manage a general ledger using Excel

Use the column headers that you see in the Centerfield general ledger example to set up a general ledger template for each account in your chart of accounts.

At the beginning of each month, post each balance sheet account’s beginning balance in a blank general ledger template. Income statement balances are adjusted to zero at the end of each month.

Each time that you post a journal entry, post the activity to the general ledger. If you post a journal entry that impacts cash and inventory, you also post the activity to those accounts in the general ledger.

At the end of the month, calculate the ending balance for each account. Use the ending balances in the general ledger to create the trial balance.

How to create a trial balance in Excel

The trial balance is a listing of each account used to post transactions and the current account balance.

Follow these steps to create a trial balance in Excel:

• Take the account numbers and descriptions from the chart of accounts and copy the date into a new spreadsheet.
• Add a debit and credit column next to each account. Post each account’s ending balance from the general ledger into the spreadsheet.
• Total the debits and credits for all accounts. If you’ve posted journal entries and general ledger activity correctly, total debits should equal total credits.

If total debits and credits don’t balance, check each journal entry and verify you posted the data to the general ledger correctly. If you still have an error, check the formulas in your general ledger spreadsheet. Once you have an accurate trial balance, you’re ready to generate financial statements.

How to generate financial statements in Excel

The most useful financial statements are income statements and balance sheets. The statement of cash flow is also one of the basic financial statements. But it can be challenging to generate in Excel. As your business grows, you can use software to produce all three statements. If you’re using Excel, stick with the income statement and balance sheet.

Create an Excel template and use it to generate the income statement each month. Keep the formulas for the subtotals so that you can check your work. Post the account balances from your trial balance.

Centerfield’s income statement

How to post net income in Excel

You won’t find net income listed in the chart of accounts. Net income in the income statement increases equity (account No. 4800), which is a balance sheet account. When you produce the income statement, net income subtracts total expenses from total revenue. You’ll use net income in the balance sheet.

Create an Excel template with an accounting format Excel provides for you and use it to generate the balance sheet each month. Keep the formulas for the subtotals so that you can check your work. Post the account balances from your trial balance.

Net income from the income statement increases equity in the balance sheet. This is a big source of confusion for small business owners, so you may get help from an accountant if you’re using Excel. Ask your accountant to verify that you’ve added net income to the equity account correctly.

Centerfield’s balance sheet

How to close the accounts in Excel

Balance sheet accounts are permanent. Income statement accounts are temporary. So you can “close” income statement accounts to the net income.

Net income is the net impact of all income and expenses for the month. When you post the net income balance to equity in the balance sheet, the revenue and expense accounts are adjusted to zero. On the first day of the next month, the income statement accounts start with zero balances.

Balance sheet accounts, on the other hand, are permanent accounts. The balances carry forward from one month to the next.

Streamline your accounting and bookkeeping processes

Accounting spreadsheets require manual entry, so your risk of error is high. Business owners who use spreadsheets may use an outdated version of the program and risk losing documents altogether. And if spreadsheet links contain errors, your data may be incorrect.

Use QuickBooks to save time and increase the accuracy of your accounting records. If you need a real-time spending tracker, select from the expense reports available. Then scan receipts and other source documents and attach them to journal entries. Use QuickBooks to download your bank statements and credit card activity into your accounting records. You’ll save time and reconcile your bank account faster.

With accounting software, you can process more transactions in less time. Make the switch to accounting software, so you’re ready to take on more business. Compare QuickBooks Online to spreadsheets.