Max Count in a Pivot table

[strummer87]

i have a table from a survey with columns of favourite fish and age group,for example

Please Login or Register  to view this content.

i have created a pivot table to show the count of favourite fish in each age group

Please Login or Register  to view this content.

But i only want to display the overall favourite fish in each age group. is there a way to display the max value from each column in the grand total column instead of the grand total?

Thanks in advance for your replies.

[NBVC]

Add a column just outside the pivot table with Max() formula

e.g. =MAX(B3:G3) copied down

where B3:G3 is the row of counts for each age group.

You can then also use an INDEX formula to get Fish for Max in each row

e.g.

=INDEX($B$2:$G$2,MATCH(MAX($B2:$G2),$B2:$G2,0))

copied down, where B2:G2 have the column headers.

You can also use Conditional Formatting to highlight the max value in each row...

e.g.

Select the numbers in the Pivot table and go to Format|Conditional formatting and then Formula Is, then enter: =B3=MAX($B3:$G3)

click Format and choose from Pattern tab.

Note: Adjust All ranges to suit.

[strummer87]

Thanks, I created a new table using the max and Index function as you said. However i thought you would be able filter out the max favorite fish from the pivot table, i guess not.