Chart of accounts template in Excel

Chart of accounts template in Excel

When one person records «rent», another writes «lease» and a third uses «office rent», the figures stop being comparable and no expense report closes. A chart of accounts is the ordered list of codes and names that prevents that mess: it is the structure the general journal, the general ledger and the trial balance all lean on.

This file brings a catalog of 100 accounts, five drop-down lists to classify them and a summary sheet that counts accounts by type and by status. It is an internal control and organization tool, not an official document before any authority.

What a chart of accounts is and who it is for

A chart of accounts is the accounting dictionary of a business. Every account gets a code, a name, a type, a nature and a level of detail, and that set is what every other record uses afterwards. When the dictionary is defined and respected, anyone can record a transaction without inventing new names, and any monthly report can be compared with the one before it.

Without a chart of accounts the same expense ends up spread across five similar names and the total never matches what was expected. The consequence is very practical: if the labels change every month, the monthly series can no longer be read and any comparison turns into an argument about names instead of a review of numbers.

This template works both for the small business that keeps its accounting in Excel and for the team that wants to tidy up its categories before migrating them to another tool. It also helps the accountant or the assistant who needs a clear base to talk about the accounts with a client, and the business that already has transactions and finds that every month it classifies them differently.

It is best understood as the starting point of the rest of the accounting: the general journal holds the transaction of the day, the general ledger sorts it by account and the trial balance summarizes it, but all three need accounts with a unique code and a stable name. That is exactly the job of this sheet.

The five decisions behind every account

Every row of the catalog answers five questions: what type of account it is, of what nature, at what level of detail, whether it accepts a third party and whether it is active. Answering those five questions the same way in every row is what turns a list of names into a real chart of accounts.

The type places the account in the large groups; the nature defines whether its normal balance is a debit or a credit; the level says whether it is a group, a subgroup or a detail account. The third-party flag is used when the account can be opened by customer, supplier or employee, and the status lets you retire an account without deleting it or losing its history.

What the template includes

The columns of the sheet

Code and name are the only columns written by hand; type, nature, level, third party and status are chosen from the lists. That is why the template can be shared with several people without each one inventing a private vocabulary or drifting away from the others.

How the sample catalog works

The catalog starts with 50 loaded accounts so that the logic of the code, the type and the nature is visible. The summary sheet counts them by type and reports them as active:

The six types add up to exactly 50 accounts and, since all of them are marked as active, the summary reports them complete. That total is the first control worth checking: if it does not match the number of loaded accounts, there is a row without a type or without a status, and the count exposes it right away.

How to read a hierarchical code

The code is not a loose number: it is an address. In 1.1.01 the first block identifies the account type, the second the group and the third the detail account. If a detail account already has transactions, it is not deleted or renamed: another one is created beside it and the previous one is left inactive, so the history stays readable.

That level-by-level reading is what allows you to add without opening every row: grouping by the first two blocks of the code is enough to get the total of a group. A catalog with the same code width in every row sorts itself and can be reviewed with the eyes; one with mixed widths forces a row-by-row review.

An example with figures shows why this matters: if the Cash account accumulates 1,200,000 in transactions during the month and another row of the catalog has a similar name, the two amounts split and neither account reflects the real total. The catalog summary, which groups by type, avoids exactly that split and shows how many accounts each group uses.

Important note: the sample catalog is generic and only serves to show how the file works. Every business must replace it with the chart of accounts of its own country or entity, with the structure, the codes and the names that apply to it. The template imposes no structure at all: it organizes yours and leaves it ready to be used in the other books.

Step by step to build your chart of accounts

1. Open the template and review the five drop-down lists before touching the catalog, so you know which values each column accepts.
2. Empty or delete the 50 sample rows and write the large groups of your structure first: assets, liabilities, equity, income, expenses and costs.
3. Assign the codes by level, starting with the group and going down to the detail, keeping the same code width in every row.
4. Complete the type and the nature of each account using the lists, and check whether the detail accounts accept a third party.
5. Mark as active the accounts you will use this year and leave inactive the ones that exist only as history.
6. Compare the total on the summary sheet with the number of loaded accounts and fix any row without a type or without a status.

Tips and common mistakes

• Use each code only once: two accounts with the same code are the most frequent cause of wrong matches in the other books.
• Do not change the name of an account that already has transactions; if you need more precision, create a new detail account.
• Keep the same code width in every row: if some use two levels and others four, the order and the reading break down.
• Do not open an account for every supplier or customer you know; those openings belong in the third party, not in the chart of accounts.
• Set accounts inactive instead of deleting them, so you do not lose the reference of previous months when a balance has to be reviewed.
• Remember that this template is an internal control tool: it is not an official document and it does not replace the records your accountant keeps on their side.

When to move to a software

While the catalog lives in one sheet and only one person touches it, the template works well. The problem shows up when several people record at the same time, when a query needs to cross the catalog with thousands of transactions or when someone asks how much was sold today while the file is still open on another computer. At that point, keeping the chart of accounts in Excel costs more time than it saves.

An inventory and accounting system such as Kardex Tauro keeps the catalog in the database and uses it for everything else: every transaction, every report and every query take the code and the name from a single place, and nobody can create a loose account from a separate sheet. The difference is not about having more columns, but about the catalog stopping being an annex and becoming the rule of the system.

If your business is still sorting out its accounts, start with this template and build a short catalog that is actually used. If you already have that clear, that same catalog is the first thing worth loading into Kardex Tauro.