Excel tips: Creating financial statements series part 2 – How to avoid errors when using the sort functions

Excel tips: Creating financial statements series

• Part 1 – Creating an extended trial balance
• Part 2 – How to avoid errors when using the sort functions
• Part 3 – Using basic functionality when creating financial statements

Working with spreadsheets can make accounting tasks much more efficient and reliable once we have learnt to use the required functionality. However, in order to achieve that we have to combine accounting knowledge with spreadsheet skills and for many of us, bringing both areas together is a real challenge. 

In this article we’re going to use the sort function to alter the presentation of an extended trial balance (ETB) in preparation for the creation of year-end financial statements.

Click here to download the ETB we created in the last article.

The first tab in the spreadsheet contains the ETB and the second, the year-end adjustments. The accounts in the ETB aren’t listed in any particular order and if you click on any of the cells you can see that they all contain either original entries, links or formulas. 

Sorting the ETB

In order to make the presentation of the ETB more helpful for the purpose of creating the financial statement we are going to change the order of its rows. However, sorting information can cause all sorts of problems if not done carefully, for example, not selecting all the information can result in it becoming mixed up and unless we realise straight away, it can be difficult to use the undo function:

There’s also a challenge using the sort function when a worksheet uses formulas that operate across a number of rows because they will no longer work as intended, once the order of the rows has changed. This applies to some of the cells in our adjustment columns. Therefore, we are going to create a copy of the worksheet before we start, as a backup. Right click on the ETB tab at the bottom of the screen, select Move or Copy, tick the Create a copy box, indicate where the copy should be in the order of tabs and then press OK:

Now we can return to the ETB tab and sort it alphabetically by account names. First we select the data range A6:I29, being careful to not include the profit or total rows, then use the Sort & Filter button on the HOME ribbon.

Checking for errors

As we are aware that the formulas in the adjustment columns are unlikely to still be working correctly we can check them against our copy. A quick comparison shows that the closing inventory and insurance prepayment adjustments are still okay, as is the original entry for the irrecoverable debt. However, the link to the receivables row is now incorrect, as is the depreciation formula and link to the accumulated depreciation row.

Before we amend these errors, it is worth doing a further advanced sort to separate the SoPL accounts from the SoFP ones. Select the data again, A6:I29 but then choose the Custom Sort option from the Sort & Filter drop down menu:

Make sure that the My data has headers box is not ticked, then select column F from the Sort By drop down list. Now press Add Level and select column G, then press OK:

Now all the SoPL accounts are in the top half of the ETB in descending order of value and the SoFP accounts are still in alphabetical order in the bottom half. Be mindful of the closing inventory though, it is included in both statements and there is a green triangle showing in cell G20, which can be ignored as it is just bringing an inconsistent formula to our attention:

Correcting the errors

However, we have some #REF! error messages showing that can’t be ignored but at least we are already aware that they have arisen due to the sorts altering formulas that work across rows. So, now check and correct the entries in the adjustment columns. Be careful to double check all the adjustments, because even if a figure is showing it may still be incorrect. 

In this case, E10 need to equal D28, and E29 should equal D11. The formula in D6 must be corrected to: (B26-C27)*0.25 and then linked to E27. The error messages in the profit and total rows should resolve themselves once the depreciation formula is corrected.

A comparison with the copy we made enables us to check all the figures are now the same as they were originally.

The ETB is now in a much more useful order for us to use to prepare a statement of profit or loss and a statement of financial position.  You can follow how to do that in the next article on how to use basic functionality when creating financial statements.

Read more tips on Excel here

Browse the full range of AAT study support resources here

Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.