Pivot Table and Formatting Subtotals Automatically

Hi, 

by arranging some data into the pivot table, my boss 
exactly gets what he wants; the only thing, he asked me 
to format the subtotals (like putting all in bold or 
color background of the cells), so
1) I can do it manually but then i have to redo it every 
time i refresh the table
2) I should be able to do it automatically and so that if 
I refresh the table, it's not necessary to refresh all 
the time

And I'm looking after a number 2) solution.

Thx for any help

Dries.
..


0
anonymous (74722)
11/13/2003 8:32:35 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
388 Views

Similar Articles

[PageSpeed] 15

On the Pivot toolbar, choose PivotTable>Table Options.
Add a check mark to 'Preserve formatting', and click OK.

Dries wrote:
> by arranging some data into the pivot table, my boss 
> exactly gets what he wants; the only thing, he asked me 
> to format the subtotals (like putting all in bold or 
> color background of the cells), so
> 1) I can do it manually but then i have to redo it every 
> time i refresh the table
> 2) I should be able to do it automatically and so that if 
> I refresh the table, it's not necessary to refresh all 
> the time
> 
> And I'm looking after a number 2) solution.

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/13/2003 12:35:23 PM
Thx for your help, 

but is it also possible to format all subtotals at once?
now I'm doing it manually, but that's not the way I want 
to do it!

Dries.

>-----Original Message-----
>On the Pivot toolbar, choose PivotTable>Table Options.
>Add a check mark to 'Preserve formatting', and click OK.
>
>Dries wrote:
>> by arranging some data into the pivot table, my boss 
>> exactly gets what he wants; the only thing, he asked 
me 
>> to format the subtotals (like putting all in bold or 
>> color background of the cells), so
>> 1) I can do it manually but then i have to redo it 
every 
>> time i refresh the table
>> 2) I should be able to do it automatically and so that 
if 
>> I refresh the table, it's not necessary to refresh all 
>> the time
>> 
>> And I'm looking after a number 2) solution.
>
>-- 
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>
0
anonymous (74722)
11/13/2003 1:11:25 PM
Turn on 'Enable Selection' --  (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)

Then, point to the left side of one of the Subtotal heading cells
     (e.g. a cell that contains the text 'DeptA Subtotal')
The pointer should change to a black arrow.
Click, and all subtotal rows will be selected.
Format the selected cells.

Dries wrote:
> Thx for your help, 
> 
> but is it also possible to format all subtotals at once?
> now I'm doing it manually, but that's not the way I want 
> to do it!
> 
> Dries.
> 
> 
>>-----Original Message-----
>>On the Pivot toolbar, choose PivotTable>Table Options.
>>Add a check mark to 'Preserve formatting', and click OK.
>>
>>Dries wrote:
>>
>>>by arranging some data into the pivot table, my boss 
>>>exactly gets what he wants; the only thing, he asked 
>>
> me 
> 
>>>to format the subtotals (like putting all in bold or 
>>>color background of the cells), so
>>>1) I can do it manually but then i have to redo it 
>>
> every 
> 
>>>time i refresh the table
>>>2) I should be able to do it automatically and so that 
>>
> if 
> 
>>>I refresh the table, it's not necessary to refresh all 
>>>the time
>>>
>>>And I'm looking after a number 2) solution.
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>.
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/13/2003 1:30:23 PM
Thx Debra, 

that works just fine!!

Dries.


>-----Original Message-----
>Turn on 'Enable Selection' --  (From the Pivot toolbar, 
choose
>PivotTable>Select, and click on Enable Selection)
>
>Then, point to the left side of one of the Subtotal 
heading cells
>     (e.g. a cell that contains the text 'DeptA 
Subtotal')
>The pointer should change to a black arrow.
>Click, and all subtotal rows will be selected.
>Format the selected cells.
>
>Dries wrote:
>> Thx for your help, 
>> 
>> but is it also possible to format all subtotals at 
once?
>> now I'm doing it manually, but that's not the way I 
want 
>> to do it!
>> 
>> Dries.
>> 
>> 
>>>-----Original Message-----
>>>On the Pivot toolbar, choose PivotTable>Table Options.
>>>Add a check mark to 'Preserve formatting', and click 
OK.
>>>
>>>Dries wrote:
>>>
>>>>by arranging some data into the pivot table, my boss 
>>>>exactly gets what he wants; the only thing, he asked 
>>>
>> me 
>> 
>>>>to format the subtotals (like putting all in bold or 
>>>>color background of the cells), so
>>>>1) I can do it manually but then i have to redo it 
>>>
>> every 
>> 
>>>>time i refresh the table
>>>>2) I should be able to do it automatically and so 
that 
>>>
>> if 
>> 
>>>>I refresh the table, it's not necessary to refresh 
all 
>>>>the time
>>>>
>>>>And I'm looking after a number 2) solution.
>>>
>>>-- 
>>>Debra Dalgleish
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>>
>>>.
>>>
>> 
>
>
>-- 
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>
0
anonymous (74722)
11/13/2003 2:59:02 PM
Reply:

Similar Artilces:

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

Automatically Configure Outlook Profile
Is there any way to have my network (Server 2003) users Outlook profiles automatically configured when they log on etc.? Yes, using the Custom Installation Wizard or Custom Maintenance Wizard = from the Office Resource Kit.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Keith" <@.> wrote in message = news:%23UAyURLUFHA.3644@TK2MSFTNGP10.phx.gbl... > Is there any way to have my network (Server 2003) users Outlook = profiles...

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

OT: demoralized tables
Kirk Kuykendall has a good overview on Common Table Expressions http://www.sqlservercentral.com/articles/CTE/62291/ but his conclusion contains a "new db term" (at least I had never seen it before)... "As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables. Like imported spread sheets." I assume it was on purpose and couldn't resist passing it on. We've all been there... gary I'm wondering if demoralized tables might be an undiscovered cause of file corruption. I would think that one table wit...

9.0 Table Changes
Will be updating to V9, am looking for a list of the tables that changed and the documentation says it is on V9 cd 2, under the SDK But I don't see anything on the CD so do I have to install v9, and the SDK before I can get a list of the table names? -- Doug You need to install the SDK "Doug" wrote: > Will be updating to V9, am looking for a list of the tables that changed and > the documentation says it is on V9 cd 2, under the SDK > > But I don't see anything on the CD so do I have to install v9, and the SDK > before I can get a list of the tabl...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing tables in the CRM database and/or adding complete tables to the CRM database?? I am asking this from the point of view relating to other existing CRM products (saleslogix, frontrange - shh). From what I have played with so far from playing with my SBS2k/MSCRM install is I my need to have a MS developer skillset. Robb, Using the schema manager in the deployment Manager MMC snap-in, you can add fields to the existing tables. However, there is no way to add new tables. Matt "Robb D" <robbd@eproductscons...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

Pivot Table #46
Hello, I have pivot table based on data that identifies an item as "late" "late if not rec'd today" and "late if not shipped today". The totol lists the correct number from each category for a location but when I double click on that total I get a list of every entry for that location, not just the late etc. What am I doing wrong? When you double click on a total, does it generate a new worksheet? If yes, it is listing out all the data, which the total field is made up of. The other possibility is that you are unhiding a summary field. Govind. xgirl...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

CSV, Milliseconds default formatting of mm:ss.0
When I generate some CSV files I need to include milliseconds. When anyone opens the file in Excel (in my case part of Office 2003, SP2) the datetimes appear with a default formatting of mm:ss.0. This is frustrating and appears unprofessional on my part when clients open the file. They assume I have made some sort of mistake. I have already thought of generating using =DATE(blah)+TIME(blah)+millis/(24*60*60*1000) but I don't like the idea. How can I change the default format so that it would be something more sensible and my clients and I could avoid the drudgery of clicking on the...

exmerge automatic
Hi, Can anyone help med with an example batch to automate occasional exmerge dumps at night using scheduler. Does anyone have a batch, or command line reference? Are there any .ini file changes required? Thanks, Peter Jensen Use the scheduler service. The exmerge.doc provides more info on the exmerge.ini file options. On Sun, 24 Oct 2004 11:00:21 +0200, "Peter Jensen" <peter.topp@mail.dk> wrote: >Hi, > >Can anyone help med with an example batch to automate occasional exmerge >dumps at night >using scheduler. Does anyone have a batch, or command line ...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...