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.
24
Utf
12/16/2009 4:28:02 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
26583 Views

Similar Articles

[PageSpeed] 9

Visit www.ExcelGoodies.Com
0
herochenna (62)
12/16/2009 4:37:53 PM
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.
0
Utf
12/16/2009 5:32:01 PM
 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.
0
Utf
12/16/2009 5:59:01 PM
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.
4
Utf
12/16/2009 7:36:01 PM
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.
0
Utf
12/16/2009 8:31:01 PM
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.
4
Utf
12/16/2009 8:54:02 PM
Reply:

Similar Artilces:

Color a certain range of cell if a cell contain specified word
E.g a b c d e f g h 1 2 3 Group1 4 5 6 7 8 9 Group2 if a cell contain the word "Group" + a single char, i want the row which is the same the cell containing "Group" to be autohighted In tis case, it will be Cell A3-H3, and Cell A9-H9 to be auto-highlight! Put cursor on col A>format>conditional format>formula is>=left($a4,5)="group">format as desired>copy format (use format tool) to col e then copy format the row down/up as desired. -- Don Guillett SalesA...

Need help with locking columns
I am trying to lock columns A, F, and H on Sheet1 so that when a user opens the workbook, these 3 columns are locked for any editing, however, I want the user to be able to edit any thing else on the sheet. I need help with the code. thanks Al Al, Try this. Select all cells by clicking the box above the 1 of row 1. Right click anywhere on the sheet Format Cells - Protection Tab and un-check 'Locked' - OK Select your 3 columns by holding down CTRL and clicking on the column header of each column Right click in one of the selected columns and repeat the formatti...

Formatting Rows / Columns of a Report
Ok i am having trouble wrapping my brain around this one. I have a table, tblTotals, with Fields: Station, WeekEnd, Revenue. (text, date, number respectively). The WeekEnd date is always a Friday. Here is some example data: ATL 1/9/2009 2000 BWI 1/9/2009 5000 LAX 1/9/2009 8000 ATL 1/16/2009 3500 .... I need to create a report that is similar to a CrossTab Query in format. Not sure if that is possible. So the Row Headers would be the Stations, and the Column Headers would be the WeekEnd Dates. i.e. 1/9/2009 1/16/2009 ...

Data in narrow columns truncated when saving as DBF
I have a requirement to save some data in an Excel file in DBF format which Excel can do. However I noticed that if the columns in Excel are not wide enough, the data in the columns is truncated when I re-open the DBF file in Excel. It's also truncated when I open the DBF file with this other 3rd party application. Please note that the amount of text in each column is not that much -- maybe 15-25 characters. I have to re-type the data, expand the columns to be wider than the actual data (so the data is completely visible and then some), then re-save the file in DBF format. Does...

how to write to database tables in Excel 2004
I need to write to a database using Excel 2004 (Mac, OS X, 10.4). What are my options? Hi Jolly, See: http://www.erlandsendata.no/english/index.php?t=envbadac --- Regards, Norman "jolly" <jolly@discussions.microsoft.com> wrote in message news:E505ACD5-8FB9-4065-986F-5C8483107FE7@microsoft.com... >I need to write to a database using Excel 2004 (Mac, OS X, 10.4). What are > my options? "Norman Jones" wrote: > See: > > http://www.erlandsendata.no/english/index.php?t=envbadac Are you suggesting that I can use adodb in Excel 2004? Near as ...

autoformat the column of a cell area
Hi, Is there any way to format one cell are so that that the width of a cell area (something like format column width to contents) but without formating the cells above or bellow the area I want to format I have tried autoformat but it really does not give me what I want ! Somehow I cannot remove the autofilter that appears in the formated area. Thank you, best Hugo Personal.xls. Personal.xls is located in the XLStart directory, and is used to store macros and things that you want to be available to all workbooks, whenever you start Excel. By default, it is hidden in Excel, but if yo...

Pivot Chart Format
Is it possible to prevent Excel changing the format of Pivot Chart lines on Refresh? No. I don't think we want extra features here - it would only slow down the action. It is no problem to link to other formatted sheets. Regards BrianB ========================================================= "Alex Burman" <burmanalex@hotmail.com> wrote in message news:<eLv8D9xYDHA.3248@tk2msftngp13.phx.gbl>... > Is it possible to prevent Excel changing the format of Pivot Chart lines on > Refresh? ...

Restrict email between certain users?
Hello All, I am looking for a way to restrict certain users to only be able to send/receive from a set list of internal addresses only. I run an exchange2003 server. I know how to restrict users from being able to send/receive external email, but having trouble restricting internal email. Thanks! - DT On 1 Sep 2006 07:22:34 -0700, "dxt178" <dominictiberio@gmail.com> wrote: >Hello All, > >I am looking for a way to restrict certain users to only be able to >send/receive from a set list of internal addresses only. I run an >exchange2003 server. I know how to re...

How to populate all cells of a column with the same comment?
Thanks in advance. Copy the cell with comment Select the target cells Right-click > Paste special > Check "Comments" > OK -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik <at>yahoo<dot>com for email --------------------------------------------- "cyril" <someone@microsoft.com> wrote in message news:#KMWYg9vDHA.2456@TK2MSFTNGP12.phx.gbl... Re: How to populate all cells of a column with the same comment? > Thanks in advance. �� 38 ��������� �ļ��ߣ�Ken Wright (ken.wright@NOSPAMntlwo...

Question on Subtotal Function
I am using the Subtotal function and it works good. Is it possible to high light the numeric figure. What it does it high lights the word TOTAL but not the numeric amount. Also is there a way to insert a line after the last Subtotal operation automatically? Thanks in advance for you help. Hi try: - select A1:B100 (adapt to your data layout) - goto 'Format - Conditonal format - enter the following formula: =ISNUMBER(SEARCH("total",$A1)) - choose a format -- Regards Frank Kabel Frankfurt, Germany "Daniell" <Daniell@discussions.microsoft.com> schrieb im N...

Creating Report on two tables that are linked
Hi, I am a novice access user who is looking at someone else's application. They have a Post Table and a Comments table. There is an id field in the Post table that links to a Post_Id field in the comments table. When I open the Post table, there is a blank field on the far left that has a plus or minus sign. If I click on the plus sign, it appears to open values from the Comments based on these two fields. In the Post table there is a Body field that has information that people have typed in. In the Comments table is also a Body field that has information. This access databa...

Get Msg "Cannot shift objects off sheet" when hidng columns" Why?
I have a large worksheet where I'm using most of the columns. There are 7 objects on the sheet, all buttons used for macros. They are in the top left hand corner of the sheet. When I try to hide columns, narrow the width of columns, or delete columns I now get the message "Cannot shift objects off sheet" when hidng columns". I do not understand why this is happening. maybe this'll help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Steve Ball wrote: > > I have a large wo...

Access 2000 Link to SQL Server db tables
I have a Access 2000 database that has approximately 100 tables. I would like to link the Access tables to SQL Server database tables to give better access to the report and forms in Access. Please let me know the best way to link Access tables to SQL Server database tables. Thanks, On Thu, 28 Feb 2008 12:36:03 -0800, Joe K. <Joe K.@discussions.microsoft.com> wrote: > >I have a Access 2000 database that has approximately 100 tables. I would >like to link the Access tables to SQL Server database tables to give better >access to the report and forms in Access. Plea...

Pivot Table
Hi guys I've made a massive pivot table.. however i want all rows which is 0 in one column to be hidden. Is there a setting for this, or do I have to make a macro to go and hide all rows which has 0 in a certain column ? Thansk -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27745 View this thread: http://www.excelforum.com/showthread.php?threadid=538405 First enter this small macro: Sub Macro1() Dim r As Range For Each r In Selection If r.Value = 0 Then ...

Web service on two tables
Please could anyone show me how to create a web service which accesses and updates information in two tables. I have seen examples in MSDN based on one table but these fail when using the DataAdapter Config Wizard tries to join two tables. Many thanks Gary, In my opinion will this always fail, it is not depending on the webservice. I searched for some messages, this one (and the thread) looked for me one who gives an idea, http://groups-beta.google.com/group/microsoft.public.dotnet.framework.adonet/msg/498476857ca1c062 I hope this helps cor Why don't you create sql state...

Usage (scope) of ALTER TABLE in sproc
hi @ll, I have a question about the usage of ALTER TABLE in a stored procedure (SQL Server 2005): First concurrent query, started first, should be later encapsulated in a stored procedure: BEGIN TRANSACTION [Test] ; ALTER TABLE [myTable] NOCHECK CONSTRAINT ALL ; -- Function from BOL, WAITFOR DELAY wrapper. EXEC TimeDelay_hh_mm_ss '00:00:10'; ALTER TABLE [myTable] CHECK CONSTRAINT ALL ; COMMIT TRANSACTION [Test] ; Second concurrent query: INSERT INTO [myTable] ( [Id], [Abbreviation], [Name] ...

Autofilling next empty cell in column?
Other than copying formulas manually to the next row of empty cells, can a function within the preceding data entry cell do the work upon completion, <enter>, or auto summing? All cells within the range will have entries, so no isolated empty cells are present to mess up the integrity. These are scientific, engineering functions with no limit to range of entries. Having searched through several manuals and this forum, I'm not too optimistic for a solution, but keep trying... Thanks, -- Bruce McC You can create a macro that does this. Otherwise, if you double click on th...

importing *.csv data into two columns
I have a data file that is delimited as comma separated values: escentially one long row of comma separated values. I need to import this long string into two columns: read two comma separated fields and start entering data into a new row and repeat. There are no empty cells as column 1 always has a matching value in column 2 for that particular row. I would appreciate any help. Thank you. Brian brian wrote: > I have a data file that is delimited as comma separated > values: ... I need to import this long string into two > columns: ... This looks like a job for Excel's ...

Different Cell Sizes in the Same Column?
Is it possible to have different cell sizes in the same column If it is possible, how do you size the cells differently? All cells in a column have the same width (they can have different heights). Cells in adjacent columns can be merged to form a wider cell unit. This can cause problems with sorting/formatting/copy&pasting, so I rarely recommend it. In article <24D650A3-564F-440D-8926-480AF023D208@microsoft.com>, Zaebos <zaebos@kalvikmedia.com> wrote: > Is it possible to have different cell sizes in the same column? > > If it is possible, how do you size t...

Copying an Excel 2002 Table into Word 2002
Are there any problems when copying an Excel 2002 table into Word 2002 document? Do you suggest to copy the Excel 2002 table as an object into Word 2002? When running both applications in XP environment. Please advise me. Copying an Excel object means that you can edit it directly from Word (The menus change to Excel). One possible problem is that a user may decide to delete the Excel file and so you loose the connection. Always keep a backup. You can Insert a sheet directly in to Word. Insert, Object, msExcelWorksheet. If you do not need to edit the file again use PasteSpecial ...

Display ranges in a Pivot
Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help Just wanted to explain a little more. Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both g...

Pivot Table Error
Thanks in advance. I create PivotTables where one of the datafields is a Calced Field. Sometimes the resulting calced field return #DIV/0! because of this the resulting tables in the PivotTable display #DIV/0! also instead of the totals. Any way around this problem? Fix your source data to remove the #DIV/0. =if(A1 = 0, "", B1/A1) divide by zero yeilds an indeterminate mathematical result. As such XL will not use it in any calculations as the result of those calcualtions will also be indeterminate... -- HTH... Jim Thomlinson "RussellT" wro...

Referencing Data in a Pivot Table
I have created a pivot table which shows me total money spent in various accounting categories. I want to reference the sums this creates to subtract them from a total budget to give me a remaining total. Unfortunately, as the pivot table is updated, cell references change. This means that the total I used as say total spent on fuel may become total spent on salary. Can anyone help? You can use the GETPIVOTDATA function to extract data. Look in Excel's help for information and examples for your version of Excel. Brian wrote: > I have created a pivot table which shows me total mon...

Merging adjacent repeated columns with a macro
I have a relatively large spreadsheet with numerous values repeated (within the same column) and I would like to merge adjacent, identical cells. I have attempted to tweak an existing macro that deletes repetitive, adjacent cells without any sucess. Does anyone have any suggestions? --Chris My existing macro for deleting these cells: Sub RemoveDuplicates() totalrows = ActiveSheet.UsedRange.Rows.Count For Row = totalrows To 2 Step -1 If Cells(Row, 2).Value = Cells(Row - 1, 2).Value Then Rows(Row).Delete End If Next Row End Sub Instead of...

Table Locking Problems
Greetings, I use the following code to create a relationship between table A and table B: Set rel = db.CreateRelation("A_B") rel.Table = "A" rel.ForeignTable = "B" Set fld = rel.CreateField("AID") fld.ForeignName = "BID" rel.Fields.Append fld db.Relations.Append rel This code works as expected, but a few lines later I call DLookup() on table 'B' and I get the following error: "Run-time error '3008': The table 'B' is already opened exclusively by another user, or it is already open through the...