Changing the Number Format in a Pivot

[excel__novice]

Hi All

Might be a simple query, but I'm trying to change the number format of a field in an excel pivot but the option to change number formats in the 'Value Field Settings' box is not appearing.

I am using the number as a 'Column Label' and it is coming up as a number not %. Can I not change this to a % because I'm not using it's not a 'value field'?

Has anyone seen something similar before?

[Paul]

Hi Novice, welcome to the forum.

If the data is truly numeric, you should be able to right-click one of the numbers (one of the column labels) and choose 'Field Settings', then in the lower left click 'Number Format' and set it to Percentage.

[excel__novice]

Hi Paul

Thanks for your reply! So quick!

I've tried this and when I right click on the numbers as normal, the option to change number formats doesn't appear under the 'Subtotals & Filters' section. What do you mean by 'truly numeric'? In my data dump I have two columns which are % - I am using them both as column labels, one has come through as a % however the second hasn't..

[Paul]

Try posting your workbook (less any sensitive data)...

[JosephP]

does one column have missing data? if not then if you change the format of the source data (eg to a number format not percent) do any of the cells remain percentages? if they do then the data field is treated as text

[excel__novice]

Hi Paul & Joseph

There's no missing data which is impacting it. I've tried taking a print screen to see if that can help - excel.jpg - does this help explain my issue?

[JosephP]

not really-would be better to see the source data. you didn't answer my question about formatting the raw data?