Share via

Calculated fields and formulas in pivot tables

  • Article

imageYou've been meaning to get round to it for years haven't you?  Did you realise that there are some brilliant training courses online that can help.  A much underadvertised resource IMHO is the office online training.  It takes you through the lesson with audio narration and flash demos, then there is a practice file to work through - like homework.  It has an example spreadsheet that it opens for you and a set of instructions in another window to work through.  Most of the courses are 30 minutes or so - easy to fit in to your day.  Why not take one lunch hour a week to improve the way you use Office tools?

Case in point about using Excel better: how to add a calculated field in a pivot table?

Easy.  Click in your pivot table and on the pivot table tab, click formulas, calculated field.  Create a formula using the fields of the pivot and it adds it in as an extra column.  Easy.  What most people try and do is create a formula outside the table and this gets messy when you cant address pivot fields and the formula won't copy down a column etc. 

So a little bit of study pays off..

image image

Comments

  • Anonymous
    July 11, 2007
    very informative indeed. I liked it n I guess I would go n get a start today...Hope the other guys would love it too.

  • Anonymous
    July 11, 2007
    It is not as easy as you make it sound. There is no documentation explaining the syntax for the forumulas you create nor is there any useful examples. I have had to resort to trial and error. I'd like to see some specific examples showing the use of forumlas containing  functions and conditional formatting. Dean

  • Anonymous
    July 11, 2007
    I had been doing it the hard way for ages - maybe you are as well.  Don't create a formula in a cell outside the pivot table - the trick is to create the calculated field as part of the pivot itself.  Maybe I should record a demo because it really is easy peasy.

  • Anonymous
    July 15, 2007
    Darren, please do create a video. I'm not quite sure what you mean by creating the field as part of the pivot itself. Dean

  • Anonymous
    August 08, 2007
    When I type a formula referencing a pivot table, I cannot copy the formula down, is there a solution to this?

  • Anonymous
    August 23, 2007
    Since I mentioned using calculated fields in Excel , I've had a few questions through about what I meant.