Educate me about pivot tables please! 25 April 2013 at 6:29AM edited 25 April 2013 at 6:35AM in Mortgage-free wannabe 8 replies 973 views

fothers365

I have got pretty standard spreadsheets on google docs now for a mortgage and loan type thing we are tackling.


They have the usual daily/monthly/annual interest charges, standard payments, over payments, totals etc. It is based on the one FinancialBliss designed.

(Also a budget spreadsheet with a monthly page with categories of spending, eg food, car, unexpected bills, household sundries etc)

I keep reading about pivot tables but can't work out how to make one or what to include.

ETA- I know which tab to click to get it there I just can't work out what I should put for each column etc iynwim!
What do you have in yours?:)

Funky_Bold_Ribena

I've used them for years.

What do you want the pivot table to show?

Just select the data, and start creating some pivot tables on another sheet, choosing different column titles for the left hand and top summaries and see what you get.

The only way to work out what they are and how to use them is to play.

I'd probably go with month along the left hand side and totals along the top and see what happens and then start adding stuff that you want totalised.

fothers365

Thank you, I will have a go and see what I come up with. I tried last night but it was late and I was tired and ended up confused.

I think half the problem is I can see that it could be useful but don't really know what it can show me as I don't understand it properly and am only learning about spreadsheets so am quite a novice.

R_P_W

A pivot table is basically an easy way to sum up or count a list of data.

So if for example you had a list of expenditure including the amount spent, month spent and a category of spend. If you did this for say 3 months you could create a pivot which would show spend per category per month (in about 10 seconds).

fothers365

Thank you. I have kept trying to fiddle with them with various sheets I have but can't seem to make them give data in a sensible way. I don't know how to get it to lay it out in a meaningful fashion.

I will get DP to have a play when he has some time since he has a much better understanding of spread sheets than I do.

I have however fiddled with a lot of charts today and made a few good ones.

edinburgher

I don't suppose you have Office 2010?

Because the Pivot Table wizard is awesome

I got a copy for £11? through an employee purchase programme, good deal.

So if for example you had a list of expenditure including the amount spent, month spent and a category of spend. If you did this for say 3 months you could create a pivot which would show spend per category per month (in about 10 seconds).

You can also use count, sum, average and max min in 2010 (not sure about other versions). That said, you can use formulae for some (if not all of these) as well, it's just not as pretty or simple to interpret.

fothers365

edinburgher wrote: »

I don't suppose you have Office 2010?

Because the Pivot Table wizard is awesome

No I am using google docs. It doesn't seem very intuitive as to what to put where. I will be a bad tradesman and blame my tools!!:rotfl:

I am sure it is just me, I will get there eventually.

Maybe_its_because

Try watching a pivot table instruction video on youtube. Seeing it visually with an explanation usually helps me

Thrugelmir

fothers365 wrote: »

Thank you. I have kept trying to fiddle with them with various sheets I have but can't seem to make them give data in a sensible way. I don't know how to get it to lay it out in a meaningful fashion.

Start by giving data consistent column labelling.

For example if you've a list of outgoings. Then in the column before the amount. Use terms such as rates, petrol, food, etc. Then the pivot function can consolidate these together to produce a summary.

Once you've mastered the basics then you make tables as complex as you like.