General ledger template in Excel: balances by account
General ledger template in Excel: balances by account
The general journal keeps what happened in the order it happened. When somebody asks how much cash is on hand, how much a supplier is owed or how much the business sold this month, the journal does not answer quickly: you have to walk through hundreds of lines and add them up by hand. That is the moment a general ledger starts to matter.
This general ledger template in Excel takes the same entries and sorts them by account instead of by date, with a balance that updates line by line, and adds a balances sheet where every account shows its total debit, its total credit, its balance and whether that balance is a debit or a credit. It comes with two working sheets, an instructions sheet and a final check that both sides of the balances add up to the same figure.
⬇ Download the template (Excel .xlsx)What a general ledger is, and how it differs from the journal
The general ledger is the same journal sorted by account. In the journal, a rent payment sits between a sale and a purchase, because the sort key is the date. In the ledger, every line of the Rent account sits together and each row carries that account balance down to the next line. It is not a new record and not a parallel set of books: it is another way of reading the same movement, which is why both must count exactly the same.
That explains something that confuses people at first: a ledger line looks exactly like a journal line. It carries account, date, description, document, debit and credit. What changes is the row order and the running balance column. If you copy the journal lines into the ledger sheet and sort them by account, the ledger is already built, with nothing typed twice.
The value of this template is not in the movement sheet but in the second one. There the ledger appears as balances: one row per account, with the total debit, the total credit, the resulting balance and its nature. That is the sheet you read when somebody asks how much we have, and the one that later feeds the financial statements.
Who this template is for
It works for the small business that keeps its books in Excel and needs to review its accounts without depending on anyone; for the accounting assistant who has to prepare balances at month end; for the outside accountant who receives a file and wants to check in minutes whether the figures make sense; and for the owner who wants to understand two or three key accounts, such as Cash, Customers and Suppliers, without learning to read a full journal.
You do not need to be an accountant to use it, but you do need to respect two ideas. First, every line carries one account and one single amount, in debit or in credit. Second, the account code is always typed the same way, because that is what makes the running balance recognise the lines of that account rather than mixing them with another.
Nor does the file have to be complete to be useful. Many businesses start with six or eight accounts and grow from there; the template behaves the same with a short chart of accounts, as long as the codes stay stable from one month to the next.
What the template includes
| Item | What you get |
|---|---|
| Movement sheet | Two hundred lines and nine columns, one line for every account that an entry touches. |
| Account balance | Automatic column that accumulates debit minus credit for the lines of the same account only. |
| Balances by account sheet | Forty accounts with debit, credit, balance and nature, ready to read at a glance. |
| Final check | A block that compares total debit balances against total credit balances and warns you when they differ. |
| Instructions sheet | Explains the ordering by account, the running balance and what a negative balance means. |
Two notes on the scope. First, the balances sheet groups accounts, not third parties. If you need the balance of each customer or each supplier on its own, that belongs in a subsidiary record, not here. Second, the template does not calculate taxes and does not verify figures against outside documents; it only adds, subtracts and compares what you type, so its value depends on what you type being right.
Columns on the movement sheet
| Column | What you write | Type |
|---|---|---|
| Account | Account code, always written in the same format. | Manual |
| Account name | The name that matches that code. | Manual |
| Date | Date of the movement. | Manual |
| Description | Short explanation of the transaction. | Manual |
| Document | Number of the voucher or supporting paper. | Manual |
| Debit | Amount when the account receives. | Manual |
| Credit | Amount when the account gives. | Manual |
| Account balance | The accumulated balance of that account up to that line. | Automatic |
| Notes | Review notes or pending items. | Manual |
The manual columns sit empty, waiting for your data, and the balance column fills itself. Do not overwrite the formula cells: if you delete one, the running balance of that account breaks from there downwards and the error travels all the way into the balances sheet.
How the calculation works
The running balance column uses a conditional sum over the account range. For each row it adds the debit of every earlier row together with the current row, subtracts the credit under the same conditions and rounds to two decimals. Because the range grows row by row, the result is the balance of the account at that point, even when lines from different accounts are interleaved.
The balances sheet walks the other way: it reads the whole movement sheet and accumulates, for each code, the debit and the credit separately. Then it subtracts and calls the account a debit account when the balance is positive and a credit account when it is negative. That is the only split it needs: an account is a debit or a credit depending on the sign of its balance, not on its name.
With the five movements of the example, the work looks like this:
| Account | Movement debit | Movement credit | Balance | Nature |
|---|---|---|---|---|
| Cash | 1,200,000 | 500,000 | 700,000 | Debit |
| Inventory | 800,000 | — | 800,000 | Debit |
| Rent | 500,000 | — | 500,000 | Debit |
| Suppliers | — | 800,000 | -800,000 | Credit |
| Sales | — | 1,200,000 | -1,200,000 | Credit |
| Movement totals | 2,500,000 | 2,500,000 | — | — |
| Balance totals | 2,000,000 | 2,000,000 | — | — |
Read the last two rows. The movement adds up to 2,500,000 on each side, because every entry carries the same weight in debit and in credit; and the balances add up to 2,000,000 on each side, because the three debit balances, Cash at 700,000, Inventory at 800,000 and Rent at 500,000, weigh exactly as much as the two credit balances, Suppliers at 800,000 and Sales at 1,200,000. In the balance column a credit account shows a negative sign: that is how the sheet says the balance sits on the credit side. When both pairs agree, the ledger is in order and the balances sheet can be used with confidence.
Step by step
- Download the file and open the movement sheet. Fill in the header with the business name, the period and the currency before you write the first line.
- Bring the lines over from the journal. One line for every account an entry touches, keeping the entry number in its own column so you can trace the origin of any balance.
- Type the code and the name of the account. The code is the key to everything: the running balance and the balances sheet recognise the account by that field and by nothing else.
- Enter the amount in debit or in credit, never in both and never as a negative. A reversal goes in the opposite column, not with a minus sign.
- Check the balance column on two or three accounts you know. It must move only on the lines of that account and stay still on the lines of the others.
- Open the balances by account sheet and read the check at the foot: total debit balances must equal total credit balances. If they do not, look for codes typed in different formats before you touch any figure.
Tips and common mistakes
- A negative balance on an account whose nature is debit is not a mistake in the sheet. It is a signal to review: either a movement is missing or an amount sits in the wrong column. The template only adds and subtracts; judging the result is your job.
- A code with an extra space, a hyphen or a different dot creates a new account. To Excel, two strings that look almost the same can be different, and then the balance is split across two rows with nothing to warn you.
- Do not delete rows or formula cells. Leaving the line blank is better than removing it: delete a row in the middle and the ranges of the conditional sum shift, cutting the running balance.
- The two hundred lines are a capacity, not a target. Fill the rows you need and leave the rest blank; a file with open space is quicker to review than a crowded one.
- Keep one file per period and never overwrite the previous one. The ledger carries balances forward, and losing last month's file means losing the support for the balance this month starts from.
- This template is an internal control tool for private use. It is not an official document, it does not replace any mandatory accounting record, and it does not turn an Excel workbook into formal bookkeeping.
When it is time to move to software
Excel works very well while one person is typing, there is one file per period and the volume of movements can still be reviewed by hand. Trouble shows up when two people need to record on the same day: the file gets duplicated, versions with similar names appear and nobody knows which one is good. It also shows up when movements pass a few dozen a day and checking the balances stops being a glance and becomes a whole afternoon of adding.
That is where a system such as Kardex Tauro earns its place. Software keeps accounts, movements and balances in a single place, stops two people from overwriting the same thing, leaves a trace of who changed what, and builds the ledger and the balances without formulas that can be deleted by accident. The Excel template remains useful as a review and backup format, but it stops being the only place where the information lives.
The signal to make the move is not the size of the business but the number of hands touching the file and the number of questions to answer each week. If nobody asks about your balances today, the template is more than enough; once the questions start repeating, control asks for something like Kardex Tauro.
To close
A well kept general ledger turns two hundred movements into five balances you can read at a glance, and that is its whole promise. Start with the two or three accounts that move the most, let the check at the foot warn you when something does not balance, and the rest of the chart of accounts settles on its own.
⬇ Download the template (Excel .xlsx)
