How to Prepare a General Ledger in a Microsoft Excel Sheet?
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.
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.
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.
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 labelled, "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.
The third sheet should be for "Expense" accounts. These would include rents paid, utilities, labour 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.
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--.
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".
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 £260 for your electric bill. You pay for it with a check from your business checking. Ask yourself, what did you get? You got £260 worth of electricity, so debit the Utilities account (#3010) for £260. Now ask yourself, where did it come from? It came from your current account, so credit the bank account (#1010) £260.
In the utilities account put #1010 for the Post Ref, and in the bank account put #3010 for the account number.