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.
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:
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 percentilesTOTW index Next post - Introduction to formulas and functions
Much as I love the example - it'd be quicker to press the Trial Balance button in Sage to get the report for this data...
Because the output is created as a standard Excel Table, using Power Query/Get & Transform, instead of a PivotTable, to do this sort of thing can have the advantage of making it easier to create references to the output table that adapt to changes in the dimensions of the output.