• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table: How to change data format of multiple value fields at once

Good day Bing


You could just use find and replace in your source data and then refresh the pivot tables.
 
Good day Bobhc:


Many thanks for your quick response.


Sorry that I did not explain it clearly.


What I am looking for is to change the number format in Pivot Table value field (Field Setting => Number Format)


For example:

Source table:

Region 2011 2012 2013

EU 0,351 0,2 1,232

NA 1,2 2,1235 3,5


I'd like to have the following format in Pivot table

Region 2011 2012 2013

EU $0,3 $0,2 $1,2

NA $1,2 $2,1 $3,5


Currently I have to format it column by column in Pivot. I am wondering if there is any solution to format all columns at once.


Thanks
 
Hi,


Are you looking in the bleow format.


Row Labels Sum of EU Sum of NA

2011 $351 $12

2012 $2 $235

2013 $232 $35

Grand Total $585 $282


If So after pivottable please select sum of EU under Values and then select value field settings then number format then currency then select $ english (U.S.).


Thanks,

Suresh Kumar S
 
Hi Suresh, thanks for you reply.


My question is not about simple SUM or Format , but to change "NUMBER FORMAT" for "MULTIPLE VALUE FIELDS" "ALL AT ONCE"


In my above example, I have value fields ( columns) 2011, 2012 and 2013 in the pivot table. I want to change the number format of these 3 data fields from number ( e.g 1.234,01) to currency ( e.g. $1.234,01) ALL AT ONCE.


The standard way that I know is to change one filed at one time (content menu =>field setting => number format), which will take a lot time if I need to change the format of 30 columns.


Now as workaround, I am using conditioning format based on the pivot table header. For example, if a column header is ended with € (e.g. 2011 Sales€), then € format, if it is ended with % (e.g. 2011 GM%), then percentage format. With this solution, I just need to create conditioning format ( with all possible conditions and format) in one cell and paste the format to the entire pivot table.


This solution works completely fine in the pivot table. The only problem is that when I copy and paste the data from pivot table to another location, all conditioning format will get lost ( pasting format also doesn't help)


I recalled that I saw YouTube clip few years ago with a solution. But I could not find it anymore.


Cheers


Bing
 
You must log in or register to reply here.
Back
Top