How to Prepare a General Ledger in a Microsoft Excel Sheet?

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

Microsoft Excel is a financial spreadsheet software package that can keep track of revenues, expenses and more. If you need a little help with the creation of formulas, pages and the finer points of Excel, you may want to consider downloading a free template of a general ledger Excel sheet. The templates make the entries easy, but few of them include formulas for keeping a running total.

Advertisement

Step 1

Download an available Excel general ledger template from one of the free sites provided. The template should work in all Excel versions. A general ledger is a record of the transactions of each account within your accounting system. Since the use of a general ledger, and of proper accounting techniques, requires the use of double entry accounting, each transaction entered affects two accounts.

Advertisement

Video of the Day

Step 2

Click in the cell just above the "1" and to the left of the "A." This will highlight the entire page on the general ledger sheet. Hold the "CTRL" and "C" keys to copy the sheet. Click the "Insert" button in the top menu bar and scroll down to the "Worksheet" button, then click. Click in the upper-left corner cell above the "1" and to the left of the "A" in the new worksheet and hold the "CTRL" and "V" keys to paste the general ledger template into another sheet. In this template there is space to enter general ledger transactions for nine different accounts.

Advertisement

Step 3

Create at least five identical sheets, giving you a total of 45 different accounts you can keep track of. The first sheet should be for checking, savings or payroll accounts; entitle it "Bank Accounts." You create the title by right-clicking the tab in the lower-left corner labeled, "Sheet 1" and choosing the "Rename" button. The second sheet should be for "Income" accounts. These would include sales income, service income and refunds of fees paid out.

Advertisement

The third sheet should be for "Expense" accounts. These would include rents paid, utilities, labor and licenses or taxes. The fourth sheet should be for "Equity" accounts, including the initial investment for starting the company, any draws taken for the owner and any reinvestment into the company. Finally, the fifth sheet should be for "Liabilities," including loans for buildings, vehicles, equipment and credit cards.

Advertisement

Advertisement

Step 4

Create additional sheets for those areas that have more than nine accounts in them. If by some chance one of the categories has more than nine accounts, "Expenses" for example, create one more or two more sheets and rename them "Expenses 2" and "Expenses 3." Each account you create should be assigned a number. Typically, all bank accounts will begin with 10--, income accounts with 20--, expense accounts with 30--, equity accounts with 40-- and liability accounts with 50--.

Advertisement

Step 5

Note the columns on each general ledger entry. The date and description columns are self-explanatory. The "Post Ref." column is for entering the number of the other account affected by the transaction in the line. The remaining columns are the "Debit," "Credit," and "Balance".

Advertisement

Step 6

Most of the time a debit to an account adds money to that account, and a credit removes money from the account. For each debit, there must be a credit. Another way to think of it is this: A Debit is "what you got" and a Credit is "where's it from".

For example, you pay $400 for your electric bill. You pay for it with a check from your business checking. Ask yourself, what did you get? You got $400 worth of electricity, so debit the Utilities account (#3010) for $400. Now ask yourself, where did it come from? It came from your checking account, so credit the bank account (#1010) $400.

In the utilities account put #1010 for the Post Ref, and in the bank account put #3010 for the account number.

Video of the Day

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...