Pivot Tables lose some formatting when Updated

A customer asked me this question about Pivot Tables and I 
want to make sure I give him the right info.
1)When my customer updates pivot tables the formatting 
always changes even though he has selected the check box 
stating to keep formatting.

2) I tried this out and it seems to keep most of the 
formatting like color, bolding and dollar signs but it did 
not keep the bordering.

3) Is there a way to have it keep all formatting for a 
report and have it maintain the integrity of the 
formatting even though he updates the data? 

Any help would be greatly appreciated !
Jugglertwo




0
anonymous (74722)
3/22/2005 10:14:12 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
240 Views

Similar Articles

[PageSpeed] 27

Set the pivot table to preserve formatting:
    On the pivot toolbar, choose PivotTable>Table Options
    Add a check mark to 'Preserve formatting', click OK
    Remove the check mark from AutoFormat Table
    Click OK

You may have to enable selection (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)

Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.

If the above suggestions don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want
to update.


Jugglertwo wrote:
> A customer asked me this question about Pivot Tables and I 
> want to make sure I give him the right info.
> 1)When my customer updates pivot tables the formatting 
> always changes even though he has selected the check box 
> stating to keep formatting.
> 
> 2) I tried this out and it seems to keep most of the 
> formatting like color, bolding and dollar signs but it did 
> not keep the bordering.
> 
> 3) Is there a way to have it keep all formatting for a 
> report and have it maintain the integrity of the 
> formatting even though he updates the data? 
> 
> Any help would be greatly appreciated !
> Jugglertwo
> 
> 
> 
> 


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

0
dsd1 (5911)
3/23/2005 12:48:05 AM
Reply:

Similar Artilces:

Items options table name???
I am trying to find the name of the table that holds the "item option" settings. It is located under configuration-->options tab. This is where you check or uncheck configuration settings of the pos software and the folders are called Customer options, General options, Item option, POS options, and so on. Thanks Dave Dave - look in dbo.configuration; there's a couple of entries for "options", but I don't know off the top of my head the specific options tracked. I have not seen a separate table that tracks "options". Hope this helps... "D...

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? http://excel.tips.net/Pages/T002980_Conditional_Formats_that_Distinguish_Blanks_and_Zeroes.html This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Sorting pivot table by specific field (column)
Hey guys (and girls), Anyone know how to sort a pivot table by a specific field (not the total sum of the fields)? For instance, if I have 5 years of data 2002-6 and 20 countries. If I make a pivot table of these I get 21 rows (the countries + total) and 6 columns for years (5 years of data + total). If I use the Field Settings --> Advanced --> Sort by field the Pivot table (PT) will be sorted by the sum of the different fields. What if I don't want this, but rater want to sort it by for instance year 2003. How do I do that? Anyone have a nice and clever solution? Much appreciated...

Auto-update Fill Series in column that has randomly spaced blank c
Hi, I have a worksheet of projects listed by quarter. A cell (say A6) contains QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 12). Then comes a blank row followed by a row containing words "QTR 2" and then the fill series continues from where it left under QTR 1 i.e. 13,14,15...say up to 18. And so on for QTR 3 and QTR 4. When a new project is added, I want to be able to add a row and with minimal steps, want the fill series below this added new project, to update. When I delete a row (project), I want the fill series to automatically ...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

Conditional Formatting with UsedRange Property for each Worksheet
I am getting an error indicated below in my code (Application-defined or object-defined Error, Run-Time Error '1004'). I want to have a bottome border line every 5 lines. Can someone explain to me why this portion of code will not work? Huge THANKS in advanced!!! Sub JobTracker1() Application.ScreenUpdating = False Call shUnProtect RowCount = 3 With Sheets("Archive") Do While .Range("L" & RowCount) <> "" myMonth = Format(.Range("L" & RowCount), "mmmm") Application.StatusBar = "Moving Sa...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

Conditional Formatting #49
Does anyone know how to apply conditional formatting to a row of cells (i.e. to create a digital dashboard) based on the value of a particular cell? Let's say you want to change the formatting of row 2 if A1 is greater than 10: 1. Select row 2. 2. Format > Conditional Formatting 3. Formula Is and enter: =$A1>10 4. Format as desired. HTH Jason Atlanta, GA >-----Original Message----- >Does anyone know how to apply conditional formatting to a >row of cells (i.e. to create a digital dashboard) based on >the value of a particular cell? >. > ...

How to show a count of Unique IDs in a Pivot Table
Is there a way to use Pivot tables in a way that shows a count of Unique IDs (over time). The problem I am having in using the Pivot table to show trended data, is that the "Count" option produces a count of each record that has any information, when oftentimes I simply need a count of unique IDs. So the below Column would have a count of 3 Names, when I prefer for it to produce a count of 1 Name. Any recommendations would be appreciated..... Name --------- David David David Excel 2007 PivotTable Count Unique IDs http://www.mediafire.com/file/dgmmaznjyy2/06_21_09.xlsx Thanks ve...

Multi Table Inser Query
Hello ... im new to Access so i need some expert help. im creating an application for my organization where i have this one form in which i collect all the required data and need to save it in table but the problem it this form is connected to to different tables. how can i save the data via query on one click to two different tables ? If you have several tables, each with essentially the same fields, and you need to connect up your form to different tables at different times, you have not built a database. That kind of thing might make sense in a spreadsheet, where you create a sheet ...

Microsoft Office 2003 Updates
I am able to download but not install the following updates - Update for Microsoft Office Outlook 2003 Junk Email Filter (KB975958), Office 2003 Service Pack 3 (SP3), and Update for Office 2003 (KB907417). For each failure I receive the same error code - 0x80070643. I have tried to download the Genuine Microsoft Tool to validate my Office 2003 Software but it won't download. I have tried to use logs to find the specific error but it does not work. Can anyone help me? I am no longer able to open Outlook 2003. -- Thank you. Lucy Description of the update for Office ...

Conditional formatting assistance
Hey all, I was hoping one of you could point me in the right direction... I have a single sheet Excel document to which I want to apply some conditional formatting. Rows 1 and 2 are header information (date). Column 1 and 2 also display information that doesn't contain data (city). The sheet contains a list of broken items that I've fixed. When I've fixed the same thing twice, I want the cells containing that item to turn color. When it's entered a third time, turn a different color, and a fourth time, a final color. For example, I enter Chicago_washer_1 in cell C3. Thr...

Caleder updation
Hi experts, We're installing Win2003+Exchange2003+Outlook2003. We are facing the problem that Exchange does not refresh user's caleder until he/she opens Outlook. Of course we know this is MS's specification. But we are now using web-based e-mail system and its "realtime refreshed" calender. So we have to show users our solution for it. Your any advise or comment is highly appresicated. Which web-based e-mail system are you using? Outlook Web Access? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "hanchan" &l...

how to setup quotionnaire format
please answer I'll take you mean questionaire == survey ? If so, have a look-see at Debra's page: http://www.contextures.com/xlForm01.html Excel -- Survey Form With Option Buttons (Techniques by Dave Peterson) There's 2 sample files you can download on the page -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lalitbogra" wrote: > please answer Re: how to setup quotionnaire format ...

Format DatePart
I'm trying to extract the month name (not just the number) from a ChkDate field in a query. ChkDate is a Date/Time data type. I use a DatePart expression which works fine to pull the month number. MonthNum: DatePart("m",[ChkDate]) But when I try to format that to get MonthName, it return Jan for any non- January month and Dec for any January month in ChkDate. Curious. Here's what I'm trying, and I can't see what's wrong according to syntax in help for Format and DatePart. Any ideas? MonthName: Format(DatePart("m",[ChkDate]),"mmm") Than...

New check format
I modified a copy of current check report, and trying to use this new format when I print a check. However, GP system didn't pick up the new format. What should I do to make them recognize each other? Thank you so much, Sophie Sophie, If you modified an existing check layout, you want to make sure to give users access to the modified check via Advanced Security or Security, depending on what your company uses. If the reports.dic is local and more than one user needs to use the modified check, you'll need to make sure each local reports.dic gets the modified check. Hope thi...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

Preserve format in pivot table
I have a pivot table where I formated the numbers using the "accounting" format. When I refresh the table, it goes back to the "general" format. How do I keep the "accounting" format when I refresh? Thanks Things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. Set the pivot table to preserve formatting: --On the pivot toolbar, choose PivotTable>Table Options --Add a check mark to 'Preserve formatting', click OK Depending on your version of Excel, you ...

Pivot table sorting #2
Hello, I have a pivot table report for partnumber and values. This report is based on a database containing details like partnumber source import flag Description total value... etc. In my report i would like to sort the table in descending order of total value. I have tried advanced option by double clicking the total value field and changing the autosorting option to descending(total). But i am not getting the required result. My version of excel is Microsoft office excel 2003. Pivot table report is given below Source (All) Imp (All) Sub (All) Description (All) ...

Try on corrective update
--bedhrrmk Content-Type: multipart/related; boundary="hjpjsgpgnoozjuaed"; type="multipart/alternative" --hjpjsgpgnoozjuaed Content-Type: multipart/alternative; boundary="hvvdwctiv" --hvvdwctiv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to prote...

Export Access pivot table to Excel
Hi, I am looking for a fix in below code, probably addition to it. I'm trying to export a query from ACCESS 2003 to EXCEL 2003. Default out put format of query is pivot table. I want to export same to excel. I do that each time with the export button which generates a temp file which I have to save all the time by naming it. So far I have managed to export only the data sheet. I'm stuck with the pivot table portion. These few lines I found while browsing and changed them w.r.t. my requirements. Code is below: ---------------------- Public Sub TransferReport()...

Conditional Formating Absolute Minimums
Howdy, I am trying to CF each row of a table that contains several columns. I want to highlight the number that is closest to zero. I have the following formula: =B2=MIN(ABS($B2:$D2)) and it partially works. If the minimum number is greater than zero the formatting happens, if the minimum number is less than zero, or negative, then no formatting happens. I have also tried =b2=min(abs($b2),abs($c2),abs($d2)) with the same result. Any suggestions? Regards Try: =ABS(B2)=MIN(IF($B2:$D2<>0,ABS($B2:$D2))) HTH Jason Atlanta, GA >-----Original Message----- >Howdy, > >I am ...

Automatically update pivot table when switching to sheet that the pivot table is in
What would the code be to automatically refresh a pivot table when you open the sheet that the pivot table is in? Today, I changed data in the sheet that the pivot table draws from and forgot to refresh the pivot table. Fortunately, I caught my error in time and refreshed the table before I gave it to the boss. I would like the pivot table to refresh whenever I switch to the sheet that the pivot table is in. Thanks Try using the Worksheet_Activate event to update the PivotTable like: Private Sub Worksheet_Activate() PivotTables("PivotTable1").RefreshTable End Sub hth, jay <b...

Office 2008 updates too problematic
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel After hours and hours of frustrating work trying most of the suggestions on these forums to get updates to install, I finally have the simplest of solutions: Don't even entertain the novel concept you need or want a custom install of Office. That's it. That's the new rule. DON'T CUSTOMIZE YOUR MAC (or at least not your Mac Office) !! I know, I know. Like me, some of you may THINK you don't need Portugese Proofing Tools and you may actually PREFER not to load Microsoft Messenger, but trust me, YOU'...