How to Use Excel as a General Accounting Ledger

Microsoft Excel is a powerful tool that can be used for a multitude of purposes. For a small business with few transactions, a savvy business owner can use Excel as a substitute for accounting software. For business owners who need to hone their Excel skills before taking this route, there are lots of online Excel courses to try.

Excel has all of the tools needed to create and maintain a general ledger, but it requires a solid understanding of basic accounting and attention to detail. For business owners who do not 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. The following is a guideline that will help layout how best to utilize Excel for accounting purposes.

Using Excel as a General Ledger

It’s important to note that every business is different from one another and will, therefore, have different reporting needs. The first step in setting up a bookkeeping process is to determine what accounting metrics will need to be included based on the type of business.

A simple record-keeping setup should first include columns for the transaction date, transaction description, and a reference number, such as an 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. Double entry bookkeeping relies on entering both debits and credits for a transaction so that in the end the books are balanced.

Double Entry Bookkeeping in Excel

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 and opens a business checking account at her local bank with the funds she has saved.

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 Excel’s 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 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 to be calculated can be configured as needed, such as total assets, total liabilities, and total equity.

The Bottom Line

Running a business requires a tremendous amount of accounting, which is best served by utilizing accounting specific software. If this is not an option for a business owner, then utilizing Microsoft Excel is a good alternative. The individual keeping track of accounting records for the company should have a grasp of accounting terms and techniques and understand the different tools within Excel to produce accurate accounting entries. Once the business grows, it is recommended to utilize proper accounting software.