Educate me about pivot tables please!

edited 25 April 2013 at 6:35AM in Mortgage-free wannabe
8 replies 973 views
fothers365fothers365 Forumite
269 Posts
edited 25 April 2013 at 6:35AM in Mortgage-free wannabe
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?:)
Jan 2013-£140,231.65
Jan 2014-£120,081.94

Replies

  • 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.
    Sanctimonious Veggie. GYO-er. Seed Saver. Get in.
  • fothers365fothers365 Forumite
    269 Posts
    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.
    Jan 2013-£140,231.65
    Jan 2014-£120,081.94
  • R_P_WR_P_W Forumite
    1.5K Posts
    Tenth Anniversary 1,000 Posts Combo Breaker
    Forumite
    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).
  • fothers365fothers365 Forumite
    269 Posts
    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.
    Jan 2013-£140,231.65
    Jan 2014-£120,081.94
  • edited 26 April 2013 at 6:43AM
    edinburgheredinburgher Forumite
    13K Posts
    Part of the Furniture 10,000 Posts Name Dropper
    Forumite
    edited 26 April 2013 at 6:43AM
    I don't suppose you have Office 2010?

    Because the Pivot Table wizard is awesome :D

    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.
  • fothers365fothers365 Forumite
    269 Posts
    I don't suppose you have Office 2010?

    Because the Pivot Table wizard is awesome :D

    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.
    Jan 2013-£140,231.65
    Jan 2014-£120,081.94
  • Maybe_its_becauseMaybe_its_because Forumite
    440 Posts
    Tenth Anniversary 100 Posts Combo Breaker
    Forumite
    Try watching a pivot table instruction video on youtube. Seeing it visually with an explanation usually helps me
    MFW2020 #5 £2,000/2,000 MFW2021 #5 £1,850/3,500MFW2022 #5 £3.001/3,000Sep'12 £233,750 Jan'15 £222,329 Dec’21 £139,584 MFiT T4 #24 £48k/£34k MFiT T5 #24 £22,186/£41k MFiT T6 #24 £4,700/£29k
  • ThrugelmirThrugelmir Forumite
    89.5K Posts
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    Forumite
    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.
This discussion has been closed.
Latest MSE News and Guides

Boost your Nectar points

Get up to £25 in bonus points

MSE News

Ask an Expert: Scams

Watch MSE Katie's answers to your questions

MSE Forum

Hot Diamonds 40% off code

Including already-reduced outlet stock

MSE Deals