Using Excel Pivot Tables with manual records

Today, I was asked by a client who had purchased my Pivot Table training videos how he could use Pivot Tables to help him with preparation of accounts for clients from incomplete records.

I thought it would be useful to paraphrase my response for others to read, along with some additional thoughts on using Pivot Tables where the data is not held electronically.

Think about how you record the data as you piece it together. For example,
bank transactions:

Even if you don't have them electronically,
sometimes you can save yourself a lot of time later by banging them into a list
in Excel. Then you can add an extra column, being how you want to analyse it,
i.e. an expense code or description. A quick pivot table then summarises the
whole list by your expense code. You can change or add these expense codes on
the fly as you gather more information. You also have a detailed record of how
you have treated things.

The same principles apply to lists of
invoices or expenses claims for example.

An immediate use, if you are already
listing invoices in Excel for VAT purposes is to switch off the sorting and
summary functions and use a pivot table to summarise the data. As you already
have these listed for VAT purposes, you can add a column (as above) to summarise
the same data by expense code, or customer, etc.
Also, try to encourage data to be collected in Excel lists by users/clients.
Once you realise that you can report on the data any way you like once you have it in a list format, this opens up so many options.