Pivot Table Defaults Turn Off Subtotal

Is there a way to change the default behavior of Pivot Tables?

Specifically, can I change the default behavior for a Field to NOT
show the subtotals?

I know how to turn it off easy enough - it's just frustrating doing it
over and over.

Thoughts?
0
4/17/2009 4:15:53 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1030 Views

Similar Articles

[PageSpeed] 22

I have not answer, just want to agree that this is a frustrating issue.

Ie, everytime I build a Pivot Table, I have to go through the process of 
manually turning off the subtotals.

Another issue I run into is that generally my pivot tables need to have the 
'Field Settings' / 'Layout & Print' changed via a manual process to check the 
option box 'Show Item labels in tabular form'.

Both of these issues are so frustrating that I often find other ways to 
summarize my data; ie, use Access or Microsoft Query.

"Commish" wrote:

> Is there a way to change the default behavior of Pivot Tables?
> 
> Specifically, can I change the default behavior for a Field to NOT
> show the subtotals?
> 
> I know how to turn it off easy enough - it's just frustrating doing it
> over and over.
> 
> Thoughts?
> 
0
NDPard (28)
4/17/2009 9:43:01 PM
In Excel 2007 you can turn off all subtotals for the active pivot table, 
and prevent new subtotals from appearing:
Select a cell in the pivot table
On the Ribbon, click the Design tab
In the Layout group, click Subtotals, and click Do Not Show Subtotals.

However, there's no setting you can change to make this the default 
behaviour for all pivot tables.

In earlier versions of Excel, you have to turn off the subtotal for each 
field individually, either manually or programmatically. There's a 
subtotals command in my pivot table add-in:

   http://www.contextures.com/xlPivotAddIn02.html

Commish wrote:
> Is there a way to change the default behavior of Pivot Tables?
> 
> Specifically, can I change the default behavior for a Field to NOT
> show the subtotals?
> 
> I know how to turn it off easy enough - it's just frustrating doing it
> over and over.
> 
> Thoughts?


-- 
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

0
dsd1 (5911)
4/21/2009 12:51:08 AM
Reply:

Similar Artilces:

A bug in GUI of creating query with more outer joins between two tables
Hello, in GUI of Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6521.5000) is generally very simple to make a select query between two tables joined with two or more outer joins (of the same kind normally). But you should be careful. If you build first join clicking on a field of the first table, drag then the connection to the field of the second table and choose the right outer join, then you have to make the next outer join between the same two tables on the same way. If you begin but with the second table first and although you choose the same type of outer join (the arrow...

Curve's turning point.
I wish to get the turning point in my curve. When I click the curve, excel shows a series of yellow dots on that curve. Its seems that it is exactly what I want. But I am curious how excel calculate those yellow dots. Regression model or calculus? Your help on this will be greatly appreciated. Best Regards, --Samuel What kind of chart is it? Scatter or line chart with smoothed lines? If so, Excel uses a bezier fit to draw the curved segments. For more information, check out this file from Brian Murphy: http://xlrotor.com/Smooth_curve_bezier_example_file.zip - Jon ------- Jon Peltier...

Turning an equation into a graph
For a school assignment I have to turn a number of equations into a graph. for example y=-56 over 2197 (X+38.5)to the third -7 when X = -45. HELP!!! Steffi - Here's the easy way. In column A (starting in A2) type a series of typical X values from within the range of interest. In B2 type the formula, referring to A2 as the variable X, and fill this down alongside all of your X values. I can't parse your formula, but here's an example. Suppose you want Y = 12 + 13 X - 0.003* X^2 In B2 enter this formula: =12+13*A2-0.003*A2^2 You can fill this down by selecting B2, and doub...

Default save of unsent messages to inbox NOT drafts: Why?
My Outlook automatically/by default saves unsent messages to the inbox not the drafts folder. Why? And how may I change this? Thank you. Vere Scott What version of Outlook? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> asked: | My Outlook automatically/by default saves unsent messages | to the inbox not the drafts folder. Why? And how may I | change this? | | Thank you. | | Vere Scott ...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Users accessing same table
I have an Access 2003 database on a small network (server + 4 pc's) with the database split and each PC having its own front end. Recently the users have reported not being able to access the same form at the same time, they have to wait until the other user has finished and closed it before accessing it. Any suggestions regarding this problem? What error message are they getting? They should have no issue opening the same form, but there could be a lock conflict if they try to access the same record at the same time. Please post some more information (the exact error) s...

Anchor clipart in a powerpoint table
How do I anchor clip art or inserted pictures into a cell in a Powerpoint table. We have different team logos 'on' a table to show roles and responsibility, I need to be able to get the logos 'in' the cells so they move relative to the table as it is edited. Regards There's no simple way to do this. There's a possible work around here: http://www.pptalchemy.co.uk/PowerPoint_Tables_Picture.html -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "iwaddo&...

Pivot Table Sort 03-05-10
Hi I'm trying to sort a pivot table but it'll only let me sort on the first column, I need to sort it on the third, please can anyone help? Hi You will need to drag your 3rd Field to the 1st position in the row Area of the PT. -- Regards Roger Govier jen2018 wrote: > Hi > > I'm trying to sort a pivot table but it'll only let me sort on the first > column, I need to sort it on the third, please can anyone help? ...

Pivot Tabel
hello, when i group dates by months from different years in a pivot table, excel returns: january, february, march,..., december but does not make a difference between 2003, 2004. how can i obtain jan-03, feb-03,..., dec-03, jan-04,...aug-04 ? thank you kindly for your help ! andy Group the dates by Years and Months. All the 2003 months will be listed, then all the 2004 months. Or, add a new column (e.g. YrMth) in the source data table, and use a formula to calculate the year and month. For example: =YEAR(A2)&"-"&TEXT(MONTH(A2),"00") Copy the formula do...

Best way to change style of a table
I have a table I added via VBA like this: set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTable(...) I then add some cells to it by: oTable.Cell(...).Shape.TextFrame.TextRange.Text = "some text" However, I cannot get a handle on these cells to update the font, fill, etc. What is the best way to do this? On Sun, 13 Dec 2009 21:01:01 -0500, Clifton Ivey <clifton ivey <"africom.mil>"> wrote: > I have a table I added via VBA like this: > > set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTa...

Turning off automatic mailto:
I just want to write email addresses on the worksheet cells. How can I prevent this f...ng Excel from automatically changing them to mailto: hyperlinks. "I miss my Lotus" Tools | Options | [Spelling] tab then click the [Auto Correct Options] button choose the [AutoFormat As You Type] tab and clear the check box next to "Internet and network paths with hyperlinks" option. "timo.lehtila@gmail.com" wrote: > I just want to write email addresses on the worksheet cells. How can I > prevent this f...ng Excel from automatically changing them to mailto: > hy...

Invalid partition table
I have a Toshiba Laptop which came pre-installed with Vista home. Lately, I have received an Invalid Partition Table on bootup. I tried using the recovery disk with the OS on it as a boot disk but it doesn't seem to be working even when I tell the bios to boot from CD/DVD. Any suggestions on how I can create a boot disk and have the computer "see" it upon startup? Thanks so much!! Sandy -- regnells Posted via http://www.vistaheads.com If it is still under warrantee, take it back to the store for a refund or another that works. "regnells&quo...

Pivot Table
I have a list in a file name abc.xls. I have defined the list with a name say XYZ. I have another file Summary.xls with a pivot table that I'm using to give me a summary of that list. The 'XYZ' which is the defined name of the actual list in abc.xls file works when both the files are open. When I open the Summary.xls file, first message says "Reference is not valid" and second message says "Cannot find "abc.xls file". If I first open the abc.xls file and then the summary.xls, then it works and not viceversa. Please update. Thx. As answered in microsof...

Pivot Table Grand Total Placement
Does anybody know if it is possible to get Grand Totals on a pivot table to appear at the top of the pivot tables columns rather than the bottom (not subtotals, I know you can do that)? I can't find anything that says it is possible with Grand Totals so I figure I would ask the forum. Thanks, Bill Horton No, the column grand totals can't be moved from the bottom of the pivot table, and row grand totals can't be moved from the right. William Horton wrote: > Does anybody know if it is possible to get Grand Totals on a pivot table to > appear at the top of the pivot tab...

Pivot Chart Refresh cancels all chart formatting
Hello folks, I am using pivot chart with calculated fields, on which I did some quite sophisticated formatting (combination chart stacked bar / dot plot with secondary axis ; bar & plot area & grid colors ; labels ; axis & chart titles). When I click the "refresh" (!) button to update the data, the chart formatting is lost and the chart reverts to the default formatting (stacked bar chart with all series) Is there any way to "memorize" the chart formatting on a pivot chart ? TIA -- NZ Loss of formatting is a known problem with pivot charts. There's inf...

How can I reset defaults for cell borders? i.e color and/or inte.
I want to be able to modify border colors and intesity for cells in a worksheet. 1-choose the cellsformat/cells 2-choose format/cells 3-click on Border tab 4-choose the line style and color 5-click kind of vorder u want to have. 6-to reset defaults for cell borders, click "none" "mffoster" wrote: > I want to be able to modify border colors and intesity for cells in a > worksheet. ...

Excel default number format problem
I have a user with an incorrect default cell format in Excel. His default number of decimal places for a cell with numbers is set to 4 instead of 2. I have no idea how he caused this and I have no idea how to correct it. In case I'm being ambiguous, here is the procedure: Open a new Excel workbook, choose any cell and go to Format. In the Format Cell dialog box go to the Number tab and choose the Number category. The Decimal Places box will be set to 2 by default but in this user's Excel it is set to 4. How do I get it back to the default of 2? http://www.imagedump.com/index.cgi?pick...

Using formulas to modify pivot table values
Is there a way to modify the output of the data in the body of a pivot table to be included in a calculaton. Of course this can be done post pivot table creation but I would like to do it in one go. I need to divide all the counted values in the body of the pivot table by a cell value, which is different for each row of the pivot table. Help would be much appreciated. --- Chris I don't think so. Maybe you could add an extra column and do your calculation against that (and include it in the pivottable). Or copy the pivottable and convert to values and do what you want. geechr wro...

Help with unusual table problem
I would like to be able to add additional data to a table of approx. 2 rows and 4 columns. I would also like the newest data to be on to while the remaining 19 lines all shift one row down and the leas current row of data to drop off. I hope this explains my problem. Thanks, Howd -- Message posted from http://www.ExcelForum.com Not sure if you might have missed my suggestion to your earlier post yesterday in microsoft.public.excel. ( http://tinyurl.com/239sq ) Based on your "revised" specs in this post: Let's say your table is in Sheet1 in A1:D20 [assume an initial tab...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Turn off Formula
This is a multi-part message in MIME format. ------=_NextPart_000_001D_01C550AF.BB019870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am fairly new at using Excel - I am attempting to create a spreadsheet = that includes vehicle information.... the Vehicle Title Numbers are = seventeen digits - after I insert those numbers they appear to be = calculated/converted in some way - the seventeen digit number is = converted into the following: 5.74094E+15=20 If I double-click within that cell, then the number reverts back to the = sevent...

Font Issue
When the form first opens all the labels look perfect, but as soon as I scroll through records using a command button suddenly a few of the labels start to take on a bold appearance. The issue occurs with a same labels very time. I can't figure out what’s happening to the labels. Any suggestions? Thanks -- Message posted via http://www.accessmonster.com User, Nope, never seen that, so let's see if it can be duplicated... -Access version? -Patches installed? -Continous form? -Font? -Code that you are using to scroll with? -- Gina Whipp "I feel I h...

importing leads, branches defined but won't show in the conversion table
Hi all, I'm trying to import loads of leads but am having some difficulty getting all the information in CRM 1.2 that I want. We wish to import a branch discription ("industrycode" ? / we use the Dutch version) but if I add values to this record they don't show up when I use the lead-import wizard. I can only select the original values that where there from the get go. Anyone any Ideas?? rgds Patrick you need to publish your changes through deployment manager and then do an IISRESET to restart the web server service "Haas" <p.haasewinkel@ecostar.nl> w...

Lookup value on a table??
I have the following table below. If I place in cell "A100" the figure -7 and in cell "A101" the figure -975. What formula do I need to put in cell "102" the result, in this case it would be 787. Also on column "A" if the figure is between two (ie -960) it should use the row with the highier value, in this case the result would still be 787. A B C D E F 1 -10 -9 -8 -7 -6 2 -1000 790 790 788 786 784 3 -975 791 791 789 787 785 4 -950 791 791 789 787 785 5 -925 79...

Inserting data into denormalized table
Hi, I need to insert data from a normalized table into a denormalized one and wondered if there's a way to do it in the SELECT statement instead of using a cursor (the way I'd normally do it)? Table1 structured as: STUDENTID int GUARDIANID int FIRSTNAME varchar(20) LASTNAME varchar(40) RELATION varchar (30) This table currently holds multiple rows for each student id where each row is a unique person that is a guardian of that student. I need to move that data into the following structure: STUDENTID int GUARDIAN1NAME varchar(50) GUARDIAN1RELATION varchar(...