Formatting Pivot Tables and Pivot Data in Excel

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

Thnaks in advance for help.

I'm having a problem with several Pivot Tables that is driving me up the wall. I have read similar posts but no solutions that work for me.

I have a Pivot Table with source data in a SQL Server Database. Some of the columns are dates formatted mm/dd/yyyy (ex. 01/01/2007). No blanks, nulls, or other data in the columns.

Excel thinks these are text, so I can't sort or filter correctly by date.

How can I make Excel recognize these as dates. Is there somewhere in the import process where I can do this? Do they need to be in a different format for Excel to recognize them?

I want Excel to automatically recognize these dates on import.

Is it possible to change the default # formatting for a pivot table? Each time I create a new one I go into number & select the format I'd like which is 95% of the time the same thing. I'd like to set it to that style permanently and only have to change it 5% of the time.

Thanks for your help!

Hi All,

I am pretty unfamiliar with pivot tables, but am trying to create what I think is a fairly straightforward pivot table, but am having trouble with formatting the headers the way I'd like. My Pivot table currently looks like the one below.

Region Data Total
Region1 Sum of Market Cap ($m) 4,544
Count of Name 51

But I'd like SUM of Market Cap and Count of Name to be column headers, so that I can have all data for a region on the same row. (The pivot table will need to be copied daily into a PPT presentation). Is this possible?

BTW, using Excel 2003 in this example.

Edit: The forum software removes extra spaces, so I'm not sure how to post a mini-table. Hopefully you can still get the general idea of what is going on. 3 columns with 2 rows of data for each region. I want to keep the 3 columns, but have the labels in column B be the column headers instead of row labels. Hope this makes sense.

Hello,

In Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.

Hi All,
I'm having a devil of a time trying to preserve the formatting on a pivot table that I made in Excel 2003. As near as I can tell, I've done the appropriate things --- check the preserve formatting box, uncheck autoformat --- but my formatting is not preserved when I pivot.

Adding to the mystery is the fact that some formatting is preserved but not all. I'm guessing either I'm missing something obvious or this is just an unruly, rogue pivot table that refuses to be formatted.

Any insight would be tremendously appreciated. I've attached the workbook.

Cheers,
Tim

Hi Everybody
I make & use pivot tables a lot.
Whenever I make one with numbers, I get formatting for a number as below
1234567.78(no commas and 2 decimal places)
I normally want the formatting as
1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).

Now I can double click the field, goto number--> number and then apply this formatting.

My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?
Requesting help
Regards
Anetsurfer

Hi All,

I have a pivot table in excel 2007.

I'd like to use copy-paste special-values over the top of it to get rid of the pivot table and data link, but keep the formatting, before sending the workbook in an email.

When I do this though, excel strips out all of the pivot tables formatting like bolded column headings and colors, and lines delineating the sections of the table.

Is there a way to turn a pivot table into just values in a workbook but keep the formatting provided by the pivot table?

Thanks,

Dave

Hey guys,

I have a spreadsheet, and on one of these sheets I have approximately 11,000 records. At this point, I have created a pivot table based on this data. As I want to see some trends over time, and one of these columns is a date value, I have created a pivot table where I have 2 row labels and 1 column label.

From this table, I have now created a stacked column graph. It looks great, but I am hoping to tweak its formatting just a tad. At the moment, each column is spaced an equal distance apart. I am able to change the gap percentage for a series, and the gap between all columns is removed. This is half of what I want to do.

I am wishing to reduce the gap between the columns for the 1st row label, but keep a small gap between the groups of columns for the second row. I hope that makes sense.

How would I go about doing this in Excel? May am is to do this programmatically, but once I figure out how to do it manually, I can record a macro and go from there.

Thanks alot for your help!

Hi -
Is it possible to easily convert pivot table data into a "regular" format? Someone has sent me a pivot table, but for what I need to do, I really need just a spreadsheet with the data so I can sort and filter it for my needs and write some formulas to call on the data.

I was given something like in the first screen shot he
pivot_1.jpg

And I want to convert it easily to a "regular" view like this:
pivot_2.jpg

For the sample above, I had to copy and paste things to make it work, but there must be a simpler way. I did a Google search and a forum search, and I keep getting directed to this article:
http://j-walk.com/ss/excel/usertips/tip068.htm Unfortunately, I followed the steps but it didn't help.

I figure there must be a way to do this, but I am not wording my searches correctly to find the steps. Thanks for whatever help you can provide.

Every time I refresh my pivot table the formatting changes. I have gone into Table Options and unchecked "Auto Format Table" and checked "Preserve Formatting"....but it still changes formatting every single time.

What is going on here? How do I stop the PT from reformatting on every refresh?

My PT is based on one a coworker uses...that does not change formatting every single time. Is there a way to tell what table/report my coworker's PT is based on? I'm assuming that may be why mine keeps reformatting, if my PT is not based on the same table / report.

Thanks in advance for your help!

What might would cause the formatting of a pivot table to change when I go to pivottable options and refresh the data? For instance it changes the borders I have set and changes the fill colors. Some fill colors change to black which I never even messed with in formatting my tables.

Hi All

I am using excel 2010.

I have created a pivot table and added conditional formatting to the cells that encompass the pivot table.

On updating the pivot table, or even filtering, all the conditional formatting is lost.

Is there a workaround for this?

Thanks
Stu

FRiends

Please go through the attach file where I have mange to do the conditional formatting in pivot table. Result is I am getting color codes as required, now I want some formula which will count & sum based on color coding of conditional formatting.

Thanks.

Mangesh

Hi,

I have a Pivot table and would like to format the way the drill down option works.
Basically when someone double clicks on the pivot table data i want to change the default font size and style.

I am using Excel 2010 -

Thanks in advance

Having trouble with Excel 2007 and pivot table format retention.

I have selected the checkbox in pivot table options to tell it to 'preserve cell formatting on update', but it simply won't, hehe. I have columns of data (not in the 'values' section, but in the 'row labels' section) that are formatted to 'currency' or 'date' or 'general' (text); I can highlight the entire column and choose to 'center' the text and it will do so, but upon refreshing the table, everything reverts to right-justified.

If I pick one single cell and center it then refresh, however, it will stay centered. More than one cell at a time will fail, and none will stay centered.

Also, formatting the date column to show mm/dd/yy works, but upon refreshing it reverts to m/d/yyyy, even though the source data is mm/dd/yy! And unlike other fields, the 'field settings' for this particular field does *not* show the 'number format' button (lower left of pop-up window) when chosen. Good grief.

There seems to be no rhyme or reason as to whether it will or will not preserve formatting - what the heck? Any ideas as to how to convince it to actually do this?

EDIT: Never mind, solved it myself; 'enable selection' was the key.

I have a workbook with multiple pivot tables on a page. The rest of the page has other content. When I hit refresh on the pivot table, it is causing a loss of formatting in the pivot table itself in some ways, but also in other cells on the worksheet. Things like cell borders, shading, font size, etc. Also, the loss of formatting is inconsistant. For example:

The page title loses all formatting
Pivot Table 1 loses cell borders.
Pivot Table 2 loses cell borders and changes all shading from green to grey.
Yet changed font colors in certain fields remain totally unaffected.

Does anyone have any ideas why it could be doing this? I need to find out before I go much farther. I have completed this workbook once already and am recreating due to other bugs that developed.

Thanks

i want to retain the data and format, but get rid of the pivot capacity before sharing the spreadsheet. right now if a copy/paste special, i can get the data, but not the formats, any suggestions?

Is there a way to maintain the formatting I have done to a pivot table's source data? For instance, if I highlight a field in the source data, is there a way to keep this highlight through any pivot tables that are created?

Thank you!

I am working on a large set of data and producing a range of pivot tables.

I am also changing the result field say from qty sold to value sold and generally playing around with the data.

Each time I change the pivot table the totals revert back to 2 decimal places. Is there a way of fixing this or setting the default decimal places for pivot tables. Alternatively is this always just driven by the formatting in the underlying spreadsheet?

Owen

Hi,

I have a VBA question,

I'm trying to ensure the formatting of a pivot table (Excel 2003) by using a refresh VBA code activated by the user.

I have a code which can do this but it needs to select the sheet where the pivot table is.

the code I currently have is:

Code:

Sheet2.Select
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Sum of Variance'", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 36
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Ops/SMC'[All;Total]", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 47
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Column Grand Total'", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 16

as you can see it uses "Pivotselect" and then "Selection" which means as in my code above the sheet2 needs to be selected.

if I want to copy a cell I might write sheet2.cells(1,1).copy rather than select then selection.copy, is there a similar change I can do to my code above (pressumably subsituting PivotSelect for something else?) so that I can run is successfully from any sheet without it needing to select sheet 2?

THanks in advance for your help,

Andy

Have conditional formatting set-up in a pivot table to grey out all cells with zero values and flag red values greater than 1.....how can i preserve it even when new data is added to the source (more rows-columns added to pivot)?

also have another question : when i add some data and delete from the source related to the pivot the " deleted item" still shows when I click "Show All".....anyway to remove this??? esp I have been experimenting with a lot of data and a lot of extra mess has been added to pivot - have to uncheck it (does not show in the graphing) but shows when u click Show all....nuisance

Hi guys, hope you can help.

I have a sheet containing all of my pivot table data which is exported from our internal phone system. It contains stuff like handling time of calls, average call durations etc but the data is stored in seconds. For instance 8162 seconds is 6hrs 52 minutes. I get this figure by editing the cell formula to read =8162/60/60 and then changing the Cell Format to Custom and hh:mm:ss.

My question is I have over 2000 lines of this data, which increases by 2000 every month, and the above method of finding my hh:mm:ss is impractial to do as there dozens to do per line (meaning editing potenially 30,0000 individual cells.) is there a way that I can convert all of the time cells to this format and formula?

Sorry if I'm not being very clear but I appreciate any help you may be able to offer.

Many thanks,

Brian

I'm trying to copy a pivot table's values and design format into another workbook using 2007. I don't want to link to or copy over the source data. I thought I could copy\paste\paste special\values but that is not doing the trick. Any suggestions?

I need to format a pivot table calculated field to a %. When I right click on the field name and go to field settings the "number" option is not listed as it is on the other fields. I can manually format the whole column but then whenever I refresh the pivot table it reverts back to a #. Is there any fix for this?
Thanks

Somehow I lost the ability to format *all* the subotals in a Pivot table *at once*.

Normally you can just place the cursor at the left of a subtotal row in a pivot table, and it turns into a black arrow pointing to the right. If you click it, it'll select not only that particular subtotal row, but *all* subtotal rows. That allows you to, for example, format all subtotals as bold, or highlight them with the same color.

Well, I can't do that anymore! I did not change any option in Excel, it just stopped working.

Any ideas? I use Excel 2003, BTW. Thanks,

Hi

I've written macros to create, format, and select a style (background colours, font colours etc.). Then I wrote a macro to copy the pivot table values and format (number formatting) across to a new worksheet. However I'm struggling to copy across the styles (colour backgrounds) across? I want to use the new sheet as a static report. Any ideas?

Thanks!

Hello - I am running Excel 2007, although this question probably also relates to prior versions of Excel. I have noticed that the "Number Format" button is only available for some fields within a pivot table. This can be frustrating because, for the columns that you cannot specify a format for, the user is forced to reformat after each drill of the pivot table.

I cannot identify a rhyme or reason as to when the Number Format button is available. Can anyone provide any insight on this?

Many thanks,
Brian

I have created a Pivot table with General number format. When I reopened the file all of the numbers in the source data had converted to time. I reformatted the data in the source and the pivot table results but the pivot tables retained the time format in the drop downs where you can select data. Because I was working in whole numbers all of my choices show as 00:00:00. Does anyone know how to reformat the dropdown values without recreating the workbook?

I have a pivot table with a filter. The report is formatted differently than the default formatting (i.e. column width made smaller, color shading for columns, light grey dotted lines for cell borders, wrapping text). The option "Preserve Cell Formatting on Update" and "Classic Pivot Table Layout" is used.

When I change the value of the filter, the Pivot Table format does not change.

Then, when selecting "Show Report Filter Pages...:", the worksheets are created correctly for each person but the formatting is not totally preserved (cell width is not preserved, the color coding, wrapping, and cell borders are preserved). I would have to re-format all of the tabs every time I run this daily report.

How do I get the worksheets generated from the "Show Report Filter Pages" option to use the same column width as the original? I'm stumped!

MSExcel Office 2007

Hi,

I have a query that is doing my head and cannot solve, and I hate that!

I have a pivot table based on a certain data set. When I double click on a cell in the data section to extract the data into a new worksheet, the format of the data in the new sheet is completely different to the format of the sheet containing the source data.

The main frustration of this is that the column widths are completely different to the source data (and all different) which means that column headers and cell data are being displayed on two lines within the same cell, as I have 'wrap text' checked for all cells. This of course also means that the row heights are all now completely different as well.

This problem is the same on every report, with the exact same formatting and column widths being used, so it's as if it is a default data extraction auto format. I just cannot find where to change it.

Any help would be massively appreciated, as I am fed up of having to re-format all data taken out of a pivot into something readable.

Regards

Marcus

Greetings all.

I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.

I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.

Thoughts?

EB

This has been bugging me for quite some time in excel....

when using pivot tables I tend to lose any formatting that the colums have when the data is refreshed or I use any of the drop down arrows to isolate a selection.

My time column for example I need in h:mm AM/PM format, but this is lost when I filter the pivot table.

any ideas? ... thanks.

I have a Pivot table that I really enjoy using, but month after month I seem to have to go through the same numerous steps it took me the first time when I created this table, but with the new "updated" data to create my new pivot table. Is there any way to save a pivot table as a template, and simply input new "updated" data into the template.

I have heard of an add on called AutoFormat for Excel Pivit Tables, but I do not think that does what I need to do.. any information would be helpful, and you can e-mail me, and I will actually send you screen shots/ excell file that I am working with along with the new "updated" data.

Thanks All!

Hi there,

Created a Pivot Table.
1)The Column header text label appears in White Font on a White backround.
2) Headers length is quite long. Prefer the header outputs in a 90 degree alignment.

I make these changes, refresh and they do not maintain. The Pivot Table OPTIONS menu shows Preserve Formatting as checked. I have tried to deselct and reselct this options without success.

Thanks in advance for the help.
Ryan

So I have a very wonky problem and have been looking for a fix for this issue and havent located it yet. I registered specifically to ask this question.

I work for a large retailer and have built a large spreadsheet to work as a functional dashboard using data that is prepared in a number of formats using pivot charts and slicers. The problem is that some of these graphs appear unstable. Basically some of them when they update format via slicer or pasting a chart format will break their reference to the pivot table that drives the pivot chart and instead remaps the chart to pivot table 1. I usually create my pivot tables based off of just copying and pasting the pivot table next to it and wondered if the chain of tables may affect it and created a pivot table from the root data table and it had no impact on the problem.

Does anyone know why pasting a chart format or using a slicer would cause a pivot chart to change the pivot table data reference? The problem is I can build each chart manually instead of pasting the format but those charts where pasting the format is an issue also have problems with slicers so when users use the file the charts corrupt and start showing information from another table. IE my pivot chart will reference pivot table 12, but when i slice or paste a format the chart then references pivot table 1. The chart i copied the format from wasn't even referencing pivot table 1.

Any ideas?

Excel 2010

Hi Forum

When I conditional format the subtotal column in a pivot table it goes and applies the same conditional format to the other columns being subtotaled.

See attached screen shot, the subtotal column for 2013 (column V) should be conditionally formatted not the other columns (columns M to T).

this conditional formatting is applied via VBA code however if i apply it manually it does the same thing.

VBA code used is as follows:

Please Login or Register  to view this content.

Basicly for cell V10 this is:

=IF($V$10<$L$10,TRUE,FALSE) (True being Red or down on last years)
and
=IF($V$10>$L$10,TRUE,FALSE) (True being Green or up on last years)

Once I have this working for one column i will also do similar on other columns but that is another story...

Thanks,

-Ollie

Hello,

This has never happened before... Usually, when I click on a pivot table number, it opens the information in its own sheet, showing what the amount represents.
For some reason, this time, when I double click that cell and it opens up the data but is changing the format type? See new post #3.

Dos anyone know why that is happening or how to fix it?

thanks
babs