Pivot Nugget – Classic Layout & Subtotals

I have data as shown below and want to summarize the report with sales from few heads like region, product, salesman.

Image

I use Excel 2007 for work, the pivot on data will produce following result

Image

however i find it better to have a classic view as i feel it is better presented and less confusing

click inside the pivot table, goto PivotTable tools -> Options and follow instructions as below

Image

and you get the output data like this

Image

but there is still one thing that bugs me is Subtotals, i can get rid of them from  PivotTable tools -> Design -> Layout -> SubTotals

but it removes all the subtotals and directly gives you the Grand total whereas I want to keep region wise subtotals and get rid of product wise subtotals

So click inside the Pivot table goto field list, under the head Row labels Click on Products -> Field settings and select Subtotals None, you get the output

Image

VOILA…..

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s