Pivot charts - Losing formatting

I am using pivot charts to make lots of mini charts from a large set of data. 
Every time I add to the data and refresh the charts they lose their 
formatting and I have to go through and reformat everything! I have to change 
the colours and the font size, it’s driving me crazy.

Is there a way to stop it reformatting everything and making it keep my 

Rob E.
RobE (7)
2/8/2006 6:08:27 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 16


This is a know problem.

Changing a PivotChart removes series formatting in Excel


Rob E wrote:
> I am using pivot charts to make lots of mini charts from a large set of data. 
> Every time I add to the data and refresh the charts they lose their 
> formatting and I have to go through and reformat everything! I have to change 
> the colours and the font size, it’s driving me crazy.
> Is there a way to stop it reformatting everything and making it keep my 
> changes?


Andy Pope, Microsoft MVP - Excel
andy9699 (3616)
2/8/2006 8:27:02 PM

Similar Artilces:

Chart with wide range of data
Hi I had a line chart displaying wide range of data Example: Training Expenses DeptA 100,000 DeptB 2,000 DeptC 150 DeptD 5,000 Tried to plot DeptA data in 2nd axis but feel that it is not easy to understand the chart cus there is a large gap between DeptA and DeptC data. Appreciate if anyone could advise or suggest better way of plotting this chart. TIA Conventional wisdom suggests using a log scale, but you're still have a wide span across all the data. Also, most people who read figures about training expenses for different departments are not well vers...

Conversion of Cross-Tab Formatted data to qualify for Pivot Table
It's amazing how one can take pivot table data -- double-click in the data section and it "converts" the PT format to a data-table format (fit for a database environment, say access)... Well any way, I receive files that are in a sort of a crosstab format, which I'd love to be able to get to respond to (the creation) of a Pivot table. Is there a way to make this "conversion"? Thanks in advance for any direction offered.. Do you mean something like this: http://j-walk.com/ss/excel/usertips/tip068.htm from John Walkenbach's site. Jim May wrote: > > It&#...

Conditional Format on "Active Cell"
I have a spreadsheet where the rows in Column A are descriptions and Columns B thru H are Mon thru Fri. As you arrow down entering dollar amounts by day for each description I would like the Description is say A44 to bold or change color when the cell in say H44 is active. Is this possible? Thanks, Mike I'd suggest you take a look at Chip Pearson's RowLiner add-in: http://www.cpearson.com/excel/RowLiner.htm But if you just want to change the color of the cell in column A: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim rArea As Range ...

Possible to Enforce Users to place dates in a column using dd/mmm/yy format
Hello - is it possible to use validation to ensure that users (a) use the date format in a given column, and do not stray from the dd/mm/yy format. Is formating the column with a custom format my only option? Can I also lock the format for a particular column in some way? Thanks in advance for any suggestions. Date formatting is not for data entry. Dates are numbers. Formatting is used to express the numbers for visualization, not data entry. Formatting does not make people enter Excel date numbers which range from 1 to 2,958,465 When those numbers are formatted for display purposes as ...

Label columns in column charts
Now, it's Easter time, but I still need to work to complete my thesis...well, let's go on...:-) I've got a column chart in which the height of each column represents the sum of unique values, so that if the column's height is 4, it means it represents the occurence of 4 single events (1+1+1+1) Now, referring this example I wish to label each column placing the name of the single events, so, if the column's height is 4 the labels should be from 0 to 1 -> e1 from 1 to 2 -> e2 from 2 to 3 -> e3 from 3 to 4 -> e4 (note that I have the labels in my active sheet)...

Ungrouping a Bar Chart
I have a bar chart that I put into Powerpoint 2007. I have learned to ungroup by copying and pasting special as a emf. The problem I have is that it ungroups the labels and lines but the bars still stay grouped together. Does anyone know how to get the bars to ungroup from each other. In 2003 this was very easy and useful to do, but in 2007 that functionality went away. Also is there a way to change individual bars into some with coloring like slash marks or other types - not change the color gradient. This was also available in 2003 -- EsN Finally someone else is complaining a...

Fonts in Formatting Toolbar/Palette
How do I change Calibri as a default selection for (Theme Headings) and Cambria for (Theme Body) for fonts of my choice? ...

Pivot Table Question #2
Hi I use Excel 2000 I have a worksheet that has duplicate (or more) entries in some columns. When I generate the pivot table these items that are duplicated only display once on the table and there is a blank line/s under them. Example GREY 555 AAA 555 ZZZ BLUE 666 BBB 666 CCC I would like that if there is a duplicataion of an entry that that duplication also shows on the pivot table. In the case of the example above that the work GREY & BLUE shows up in every line of the pivot table. How do I format the pivot table not to hide the duplicates. Th...

Change field format based on condition
On my report I am displaying field with control source: =IIf([Status]="Closed",[Closed Date],[Last Update]) however I would like to display Closed in Short Date format and if condition is false [Last Update] should be displayed in Short Time format. How do I set this control source? Thanks Greg, Try it like this: =IIf([Status]="Closed",Format([Closed Date],"Short Date"),Format([Last Update],"Short Time")) -- Steve Schapel, Microsoft Access MVP Greg wrote: > On my report I am displaying field with control source: > =IIf([Status]="Clos...

Unrecognizable format message
Version: Older version Operating System: Mac OS X 10.6 (Snow Leopard) A coworker and I have been successful exchanging .xls documents. I then took 4 of the Excel documents and merged them into one document making 4 sheet tabs so that all the info could be in one document. <br><br>She now can't open this document and gets a message that says it is an &quot;unrecognizable format&quot;. <br><br>I do not know what program she uses to work with Excel but I could ask. Also, is it a size thing...the new document is 111 KB. <br><br>It is a Microso...

How To Refresh Chart Data Without Calculating The Worksheet
I am trying to refresh a chart in an Excel 2000 worksheet without recalculating the whole worksheet. Tried using the following syntax to no avail (this is not a pivot chart): ActiveSheet.ChartObjects("Chart 70").Select ActiveChart.Refresh Thanks, Carl Carl, Please try one of these: Sub RefreshChart1() ActiveSheet.ChartObjects("Chart 70").Activate ActiveChart.Refresh End Sub or Sub RefreshChart2() Dim Cht As Chart Set Cht = ActiveChart Cht.Refresh End Sub ---- Regards, John Mansfield http://www.pdbook.com "Carl Bowman" wrote: > ...

Short date format
When I enter a birthdate that is prior to 1925, the display is 20.. instead of 19.. I would like to make the default year 1901. For instance, if I enter 2/2/02, the display should be 2/2/1902 insteand of 2/2/2002. Thanks for any help you may render. narfla Try enterin the year as 4 digits. The problem you are having is how a DLL sets the dates for entries of 2 digits. Changing it, changes it for everything on the computer. Right now the date of century change is 2029. That can be altered in Control Panel >>> Regional Settings >>> Regional >>> Customize -- ...

adding data to chart
I have Excel 2000 and am have a line chart with quarterly data from 2002 to now. When adding the 1Q07 data to my chart, the computer won't accept the new cells and erases all the data for that particular series. I've tried starting over with the same results. Any suggestions to get around this? Please say more. Your original words are subject to various interpretations. There are 21 quarters from 2001 to Q1 2007. A line chart has no difficulty displaying much more than 21 x-categories in a single series. Also, a line chart has no difficulty displaying much more than 21 d...

conditional formatting #67
Using XL2000 I have a range of cells containing days of the week as text: MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY The text of each day name is 'centered across cells' with the cell to its right. I would like both cells conditionally formatted in yellow pattern. I can format one with 'Cell Value Is | equal to | =TEXT(TODAY(),"dddd")', but I would like the adjacent cell formatted the same way. I tried '=A1=TEXT(TODAY(),"dddd")' for B1, but got ugly results if I tested by changing the system date, i.e. only part of the 2-cell range was colored. ...

Formating Post Import
Good Morning, Hope everyone had a pleasant holiday weekend. Would someone please explain to me how to delete all instances of a quotation mark across all fields in a table? Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200709/1 Depending on which version of Access you are using, take a look at the Replace() function. Regards Jeff Boyce Microsoft Office/Access MVP "NeonSky via AccessMonster.com" <u23580@uwe> wrote in message news:77b89be05dfe1@uwe... > Good Morning, > > Hope everyone had a pleas...

Summarize project time from different sheets in one without losing references when sorting the cells.
hi I have a a lot of sheets in one excel-file, about 25 upwards , and in these I fill in the time for each project each person is involved in. What I want is a sheet with a summary for all the projects. Every project has a six digit code. Can I create a formula that searches through the entire project for example project 440011 and return the value to the sheet where I summarize all the time? The problem is that I want the projects to be sorted by the six digit code but when I sort them in each persons sheet the references in the summarized sheet will be wrong. Does anyone understand what ...

Cell Number Format
Is there a way to format a cell's number from 1.2 to 1,200,000? I know how to format millions to ones but how do I convert from ones to millions? (I don't want to have to multiply in another cell). Thanks!!! I could be wrong but, suspect there is no way to achieve what you want using format. You could use the sheet change event to multiply numbers entered by one million and format the result as you wish using code below. Just right-click on the tab of the appropriate worksheet, click view code and paste. Note. this will multiply value in column C only. You will need to modify ...

formatting color based on source column
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not ...

Question on pivot tables
Let's say a pivot table has two fields - "country" and "fruit". There are seven products and seven countries, and I want to flip through all 49 permutations. However, if one product isn't present in one country (for example, let's say "apple" does not exist in "england"), then rather than returning the error, the vba code forces "apple" over the top of another fruit that does exist. So the code firstly selects "england", then (if I were doing it manually) I would drop down the fruit box and see that "...

Functions greyed out when using Pivot tables
I have an end user who is using pivot tables, and they were working fine up until the end of Jan. Whe she added her data for Feb and went to sort using the privot table order function they were all greyed out. She was originally using Excel 2k. Think that something had just become corrupt we upgraded her to Office 2K3. This did not solve the issue. I have insured the Office has been activated and that the sheet is not protected. We can send the sheet to another user and it functions correctly, as should. Anyone see this before or have any ideas? ...

Excel 2000 Pivot table using offline OLAP CUB as the data source
Hello professtionals, I need to use Pivot table using offline OLAP CUB as the data source to analys a large amount of data. I would like to know: What is the maximum size for a CUB file in Excel 2000? What is the maximum size of data that Excel 2000 pivot table support? Thank you very much. Not sure what the maximum size is for an external data source, or even if it's restricted. Maximum size for a pivot based on a worksheet is 65,536 records (maxm. worksheet length). I regularly use pivots reading from Access databases of 2-300,000 records without a problem. "Samson Tang&quo...

Symbols not displayed properly in Excel chart
Hello. I am trying to create a chart of percentages with the data table represented with the chart in Excel 2003. My problem is that I have symbols in my original data table (e.g., <1%) that are not displayed properly when I chart the data with the data table displayed as part of the chart (the cells with symbols show up as zeros). I would appreciate any suggestions as to how to get these symbols to show up properly in a chart's data table. Thanks! -Aaron ...

Adding in another conditional format on the same cell
Dear everyone.. I have a cell containing "conditional format" when 2 weeks before the due date ...the cell colour changes to "orange"....it turns red when it reaches the "due date" ..... Now.......i need to add in another condition ....when i add in another column e.g. "B" and the font colour will change to "yellow" ....i think its something to do with....if equal to....then it would change to this colour.... Can someone teach me the formula pls? urgent....thanks everyone.... Hi! What exactly are you trying to add as another condit...

Microsoft Excel Pivot Tables
I keep getting the message "A pivot table report cannot overwrite another piot table report" when I refresh the data in my pivot table................I do not know what is the cause of this ??? I guess you have 2 pivot tables on the sheet. Pivot tables normally expand in size when data is added. If the pivot table expanded it would overwrite the other table. You will need to move a table to allow extra space between. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum...

POS Receipt format change #2
I would like to have the "Sales Receipt" read "Sales Invoice" or "Invoice" on our transaction receipts. Also on the same receipt I would like to have "transaction #" replaced with "Invoice #". Can anyone help me with this? I have been able to edit the "Sales Receipt" portion in an xml editor but have not been able to change the "Transaction #" as there must be some other coding that needs to change. Thank you in advance. Robert Hi Robert, I have already put the solution on this newsgroup kindly search the receipt f...