Excel Tip of the Week #323 - Creating a trial balance from accounting data

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week - and a very happy New Year to you all as well.

This week, we have a Creator level post in which we're going to look at a classic accounting Excel task - transforming raw accounting data into a trial balance.  To do this, we are going to look at a few tips and tricks for transforming the data into our desired format.  The dataset we are going to use, and the finished product, are attached at the bottom of this post for you to download.

Sorting out our data

Here's a look at the basic data we have gotten out of our accounting system.  I have deleted any extraneous fields to make the example clearer but the ones we have kept are the key ones.

We are going to make this data into an Excel Table (see TOTW #163 and #164) - this will make it easier for us to manipulate the data in a way that will be repeatable with future data.

Our first task will be to create a single unified transaction amount column, rather than currently where the debit and credit numbers are in two separate columns; this will make further analysis much simpler:

Note that we have used the N formula to convert the blank cells into 0s - see TOTW #279 for more on that.  This data has null text instead of truly blank cells, which can trip up formulas.

Next, we need to classify our transactions as balance sheet or P&L, and into more fine-grained headings.  We'll need a reference table of what each account code maps to.  If we don't already have a list of all the account codes, we can make one by copying the Account code and Account name columns, then using Data => Remove Duplicates.

After making our list, we then need to bring across the appropriate descriptors for each row.  We can do this using an INDEX MATCH formula (see TOTW #201):

We now have the detail we need to produce our trial balance.

Creating a trial balance

The easiest way to create a summarised version of our data is to insert a PivotTable.  We can then design our summary by allocating the fields - that is, columns - of the table to different areas in the report designer.  Here's a work-in-progress shot:

Here we have just added Account Name to Rows and set the Values to Sum of Net amount.

To round out our report, we can add the headings and sub-headings as additional Rows (in the appropriate order), and can also click on the Values item to change the format of our numbers to something more appropriate.

We can also use the filter menu at the top of the Row Labels to apply a Value filter, hiding any accounts which total 0.

Here's the finished product:

Now, there are more improvements that we could make still - here are some ideas for other things we could include in this report:

  • Use a Slicer to make a quick and easy filter to zero in on particular items
  • Add numberings to the various sub-headings so that they sort into a more sensible order
  • Right click and Group some related accounts to combine them into one figure
    • We could also do this in the raw data by mapping between the "basic" account names and some "final" ones
  • Add more formatting to increase the appeal of the trial balance
  • Make this into an Extended Trial Balance by adding an identifier column to the data that labelled journals as either original TB or a number of journals, and then adding this new field to a Report Filter so we can include or exclude post-balance sheet journals as we wish
  • Collapse the sub-headings so that the whole balance sheet can be seen at once
  • Add another lookup column to extract prior year numbers from a reference sheet and add these to the face of the TB
  • Adding a Timeline slicer so we could see how the TB changed over time

It's up to you to decide what's important!  But critically, because we've used an automatically extending Table to build our analysis, we could reuse this template the following year by just pasting over the existing data.

Previous post - Quartiles and percentiles
TOTW index 
Next post - Introduction to formulas and functions

Creating a trial balance from accounting data.xlsx
Anonymous