Search Not Just Numbers


Wednesday, 14 January 2009

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.

1 comment:

  1. In order to tumble in the league regarding dior replica timepieces, the total collection regarding generation with the wrist watch from your thought, to be able to advancement, to be able to creation, and also almost everything in-between will have to be accomplished inside Switzerland, along with it really is ultimate examination, completed simply by totes. As an alternative, hublot replica sale could be the observe business which is delivering the particular persona out from the buyer by providing a service to get something which includes numerous excellent features linked to that and also offering the buyer a feel for regarding value, hope and also that means. Top quality is probably the most critical qualities any rolex replica sale will need to have. Organizations as a result make an effort to be sure their particular brand name displays top quality throughout. Folks will need in order to program their particular evening so as to carry out every one of the rolex replica sale they will equally must and also want to serve. Addressing breitling replica punctually can be a need to while observing a favourite tv set plan is obviously one thing to be able to policy for. Timepieces have got as a result grow to be thus crucial that you people’s everyday lifestyles in which the necessity to wear them will be imperative.