|
Pivot Tables: Subtotal only certain columns?
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: Subtotal only certain columns?
|
Pivot Tables: Subtotal only certain 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. |
Pivot Tables: Subtotal only certain columns?
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: Subtotal only certain columns?
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. |
Pivot Tables: Subtotal only certain columns?
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. |
Pivot Tables: Subtotal only certain columns?
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. |
| All times are GMT +1. The time now is 05:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com