How to use Excel as a general accounting ledger

For a small business with few transactions, a savvy business owner can use Excel as a substitute for accounting software. Excel has all of the tools needed to create and maintain a general ledger, but it requires a solid understanding of basic accounting and an attention to detail. For business owners who are unsure if they are ready or have the resources to invest in accounting software, Excel may be a good place to begin keeping accounting records until the accounting software is purchased.

A simple record-keeping setup should first include columns for the transaction date, transaction description and a reference number, such as invoice or check numbers. The next columns will list each of the accounts to be used during the year. Careful thought should go into the selection of accounts, as it will be difficult to go back and make changes later. From left to right, the first accounts listed should be income statement accounts, beginning with revenues and ending with expenses. Next will be the asset accounts, then liability accounts and lastly any equity accounts.

The general ledger uses double entry bookkeeping to keep track of all of the company’s transactions, and it is important to have a solid understanding of this concept before beginning to use Excel as a general ledger.

If this is a new company, the first transaction will likely be the equity invested in the company. As an example, let's say an entrepreneur named Jane decides to open a machine shop. She has $750,000 from savings to start the business with and opens a business checking account with the funds.

Using double-entry bookkeeping concepts, Jane knows that this transaction will increase the company’s cash account (the debit entry) and will be offset by an increase in the owner’s equity account (the credit entry). In this Excel system, all debit entries should be recorded as positive values, while all credit entries should be recorded with negative values.

In this example, Jane should enter the transaction date and a description such as “capital investment” into the first line of the spreadsheet. She will then put a positive $750,000 entry into the “cash” account column of the spreadsheet, and a negative $750,000 entry into the “owner’s equity” account column of the spreadsheet. To verify the numbers, Jane should use a Sum formula to ensure that all of the entries on each row sum to zero dollars.

From there, Jane will continue to enter a line for each transaction. If she purchases equipment with the company cash, Jane will enter a transaction to show a debit, or positive entry, to the "equipment" account column, and a credit, or negative entry, to the "cash" account column. If she sells machining services to a customer, she would record a credit to the "revenue" account, and a debit to an "equity" account.

Finally, the total for each account column should be summed at the bottom of the spreadsheet. The sum of all of the income accounts can be added together to calculate current net income. Other columns can be configured as needed to calculate, for example, total assets, total liabilities and total equity.