Avoiding the Grand Total amount when using formulas in Pivot Tables

I have a pivot table which totals the data in column C, as a Grand Total, however I would like to use formulas like LARGE, AVERAGE etc; using C:C. My problem is the data C:C includes the Grand Total figure. How can I exclude the last row in calculations? I keep adding data on a daily basis, which means I have to use C2:C1143 (as an example)

Hi,

Welcome.

click on any part of the pivot table, this will show the pivot table options in the ribbon.
click on pivot table tools ----> options
on the left most side of the ribbon click options -----> click totals and filters, make sure show grand totals for columns is turned off. click ok to close the dialog box

click fields items and sets in the calculations part of the ribbon
click calculated field
type in the desired formula in the formula bar

let me know if it works.

Thank you very much, your answer certainly works. Is there a way I can use OFFSET to solve the problem and leave the Grand Total showing.

Geoles,

just go back to the options and turn grand totals back on, you can still have the calculated fields on and drag them around in your pivot table as you wish. the other option is to insert a calculated field that sums up the row for example...but you are just repeating what Excel will do for you.

I don't see the need to use offset unless I'm not understanding the question.