Excel tips: Subtotals based on a petty cash scenario

Spreadsheets can make accounting tasks infinitely more efficient, producing more robust results. But we have to get familiar with the functionality first.

It requires a combination of accounting knowledge and spreadsheet skills, and in this article, we’re going to do just that using the sort function and subtotals to analyse the data.

The context for this article is a children’s home and its monthly petty cash record. The issues are that the information is produced by a number of non-accounting staff who have a range of Excel skills and that the current template is designed to fulfil internal needs as opposed to accounting requirements. We amended the template in a previous article by creating a list to restrict the data that could be entered into certain cells. Now we are going to analyse the information so that it can be posted to the accounts.

Download the raw data to have a go as you read through.

Backing up the month-end spreadsheet

You can see in the spreadsheet the complete record for September with the accounting categories that have been selected from the drop down list. The restrictions that the list imposes means that the information in column D is consistent and therefore can be easily sorted, unlike the descriptions in column C.

Before we sort the data though, let’s make a copy in a new sheet so that the original record is maintained for reconciliation and internal purposes but then we can delete any information that’s not required for the subtotals.

Right click on the Sheet1 tab, then select ‘move or copy’. Ensure you check the ‘Create a copy’ box then press ‘OK’:

Right click the Sheet1 (2) tab and this time select ‘Rename’ and call the sheet Accounting Analysis:

Sorting the month-end Excel spreadsheet

Now delete row 3 and columns G and I, so the top of the sheet becomes:

Highlight the data A2:F61, then select ‘custom sort’ from the Editing options on the Home ribbon:

Ensure that the ‘My data has headers’ box is ticked then select ‘Category’ from the ‘Sort by’ options in the ‘Sort’ box:

Selecting ‘OK’ will sort the information into alphabetical order which can then be analysed by category.

We could sort the information manually so to speak, by inserting rows at the end of each category and using the SUM function to add up the amounts to be posted to the accounts:

Alternatively, we can use the subtotal function to do the job for us.

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Preparing to sort the data

Before we start, select rows 18 to 21, the ‘Impress top up’ rows, and move them to the bottom and slightly separate from the data set. Then delete rows 18 to 21 so there is no gap. 

Now highlight the data A2:F57, then click on ‘Subtotal’ in the Outline section of the Data ribbon:

In the ‘Subtotal’ box, we want:

1. at each change in ‘category’
2. a ‘sum’ to
3. calculate the subtotal and add it to the ‘out’ column
4. replacing any current sub-totals
5. and providing a summary at the end.

Once ‘OK’ is pressed the summary titles are shown in bold and the sub-total figures can simply be dragged into an adjacent column:

If you want to remove the boxes around your moved figures, you can highlight column H and, using the font section on the home ribbon to remove the boxes.

Reconciling the subtotals

Once all the subtotals have been dragged across, they can be reconciled. We could simply use the grand total, but it highlights the totals, making them easier to find. Total the moved figures and reconcile.

Firstly, with the subtotal summary:

And secondly, with the opening and closing balances from the original data in Sheet1, showing the total of the impress top up figures that we moved earlier in a reconciliation statement.

Now the figures can be taken from the spreadsheet and posted to the accounts via a journal.  Check my worked example if you would like compare it to yours.

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.