I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.

Visit www.ExcelGoodies.Com

---
frmsrcurl: http://msgroups.net/microsoft.public...ertain-columns

Can you post an example of what you are getting and what you want...
--
HTH...

Jim Thomlinson


"Robbro" wrote:

I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.

Basically its a report of products by plant and packing code with a number
of columns detailing sales, costs and such. Some #'s are in absolutes (lbs
sold, $'s sales etc...) and some are in per lb (price per lb, mat's per lb
etc...). The absolutes need sub totals, the per lb #'s are just nonsense when
totalled, averaged or any other operation I can use from within a pivot
table. They need to be there for information, but no summary information
needs to be in place for those columns, only for the absolute # columns. For
example

lbs sold $ sales $/lb cost/lb gross margin/lb total
gross margin
subtotal subtotal no sub no sub no sub subtotal


Theres a lot more info on the report than what I put above, but that should
give you the idea of what I need it to do.
"Jim Thomlinson" wrote:

Can you post an example of what you are getting and what you want...
--
HTH...

Jim Thomlinson


"Robbro" wrote:

I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.

Pivot tables aggregate. That is what they do. While there are some options on
subtotaling there is nothing in what you have that will allow you to suppress
the subtotals... That being said what you can do is use conditional formats
on those columns. Essentially if the right 5 characters of the text in column
A are "total" then change the font colour to match the background. I have
used that successfully in the past. The only issue is if you add or delete
columns from the pivot the CFs do not move with them so get your columns
worked out before adding the CF's...

In column C add this CF
Formula
=right(A1, 5) = "Total"
--
HTH...

Jim Thomlinson


"Robbro" wrote:

Basically its a report of products by plant and packing code with a number
of columns detailing sales, costs and such. Some #'s are in absolutes (lbs
sold, $'s sales etc...) and some are in per lb (price per lb, mat's per lb
etc...). The absolutes need sub totals, the per lb #'s are just nonsense when
totalled, averaged or any other operation I can use from within a pivot
table. They need to be there for information, but no summary information
needs to be in place for those columns, only for the absolute # columns. For
example

lbs sold $ sales $/lb cost/lb gross margin/lb total
gross margin
subtotal subtotal no sub no sub no sub subtotal


Theres a lot more info on the report than what I put above, but that should
give you the idea of what I need it to do.
"Jim Thomlinson" wrote:

Can you post an example of what you are getting and what you want...
--
HTH...

Jim Thomlinson


"Robbro" wrote:

I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.

For any column you don't want subtotaled, right-click the field header and
then select "Field Settings" from the drop-down. Then, on the "Subtotals &
Filters" tab, change the 'Subtotals' from "Automatic" to "None." Do this for
any and all fields you don't want to be subtotaled, and you should achieve
your goal.

Jeff


"Robbro" wrote:

I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.

Guess I should have mentioned I'm on Office 07 if that matters. When I right
click on a field header the closest option I have is "Value Field Settings".
The only 2 tabs under that option are "Summarize By" and "Show Values As".
Any other place that I disable Subtotals from disables them for the entire
table, not just that one column.

"jb_tenor1" wrote:

For any column you don't want subtotaled, right-click the field header and
then select "Field Settings" from the drop-down. Then, on the "Subtotals &
Filters" tab, change the 'Subtotals' from "Automatic" to "None." Do this for
any and all fields you don't want to be subtotaled, and you should achieve
your goal.

Jeff


"Robbro" wrote:

I'm new to pivot tables and really like them, using one could help me greatly
simlplify some reporting I do, however I cannot figure out how to show
subtotals only on certain columns, it appears to be all or nothing. Showing
them results in some non-sense garbage on my reports that I cannot have. I
think this is my last hurdle to actually using pivot tables and hope there is
a way to remedy this.