Making dynamic Chart of Accounts, Journal + Ledgers in Excel – How To

Excel is one of the favourite tools for accountants and for everyone else who has anything to do with accounting information. It is a tool of choice when it comes to data extraction and reporting. With Pivot tables, Power Query and Power Pivot, Excel is powerful than ever.

Every now and then I am asked if there is a way to make accounting records solely in Excel like from journals to ledgers, trial balance and finally financial statements. Though I would prefer to use dedicated software for accounting needs, but still no harm in trying and addressing curiosities and possibilities.

So here is one attempt I made to work out three important aspects of accounting cycle:

Chart of accounts

Technique used: This was probably the hardest part as we want expandable categories on the go. Using basic financial statements categories as primary list items and then for each element we have several sub-lists that are actually accounts as following:

I used Excel tables for both main list and each of accounts sub lists and each list is a separate table. I used custom table names for better control with main list named as “list” and accounts list as “sublist1”, “sublist2” so on.

With tables in place it will help me with dynamic lists that I can expand/contract without any problem and even add subtract lists.

Journal

his is where debits/credits are recorded. It can get more complex with dedicated journals for specific type of transactions but purpose of this write-up is to hint on possibilities so I kept journal single and simple.

Technique used: With chart of accounts in place I will use those lists as data validated lists involving one dependent data validation list. This seemingly easy step involved one workaround.

For some reason data validation lists and structured referencing system does not work together. To fix this we either have to use INDIRECT function or better yet use name manager to have defined named ranges.

A quick look at the journal first:

To enable structured reference in data validation, I created a name with main list address in the formula bar as following:

Once defined I use this name in the data validation list as following:

This has enabled fetching contents of main list items in the cell that user can select:

With primary list implemented, implementing account list that will depend on the selection of “element list” involves impressive mix of structured reference and Excel functions wrapped as a “named range” and requires some explanation.

But here is the formula itself:

=INDIRECT(“sublist”&MATCH(journal[@Element],list[#Data],0))

Accounts

This is where transactions are grouped to show their affect during the period and balance at particular date.

Technique used: Extracting accounts out of transactions recorded in journal involves pivot table and its not a tedious process in itself. We will simply generate pivot table report with running total concept implemented. I explained the technique some time back in this tutorial: Running Totals and Running Balance with Excel Pivot Tables – How To

This is how the pivot table report was journal used as data source:

You can see that date field from the journal is used as row identifier and accounts are strategically used as filter and why is that I will come to that in a minute.

I simply dragged the debits and credits in the value column and then inserted a calculated field to calculate the difference between debit and credit columns and later perform running total calculation on this column.

Once, we have the pivot table report in place, I employed a nifty trick that pivot table allows and that is to show each filter on a separate worksheet and thus we got accounts!

Now what remains is extracting Trial Balance if we really need it but can skip it and making financial statements directly. That will be our discussion of related tutorial in near future.

There is more in the template file that I haven’t mentioned above:

1. Error identifiers to warn user while entering data
2. How numbers are formatted to get Dr and Cr appended to Running balance column
3. How each account report is formatted
4. Access to names manager where I have defined several named ranges with formulas to get the whole template working