Number Formatting 2.5 to 2,500

It is fairly straightforward to show 2500000 as 2.5 (format code "0.0,,"). Unfortunately, as far as I know (and I have been hoping for it for a long time), the opposite operation like you describe is not available. I will be happy to learn that it has been added to a recent version of Excel, but am not optimistic that it has.

This is a case where I think your best approach will be to edit the source data for the pivot table to be 2500000, then use formatting to display as 2.5 or 2500 or however you want it displayed. Whether in the source data or after pivoting, the only way I know of to get 2.5 (meaning 2.5 million) to show as 2500 or 2500000 is to actually multiply the value to by 1000 or 1000000.

Please try custom format #,0, to show 2,500

Assuming 2.5 is in cell A1
1. Enter this number in cell B1: 1000000

2. Now copy cell B1 and go to Paste Special, click on Multiply radio button.

Here's one possible solution - it's a bit complicated but might do what you want.

In number formats, the comma (,) displays the number as if it were divided by 1000 - so 39000000 becomes 39000 for example.
Making a number a percentage using the percent sign (%) displays the number as if it were multiplied by 100 - so 4.8 becomes 480% for example.
It is possible to use multiples of these, so two commas (,,) will make 39000000 display as 39, or two percent signs (%%) will make 4.8 display as 48000.
Combining these 'divide by 1000' and 'multiply by 100' options allows any multiple of 10 to be obtained.
In your case, to 'multiply' by 1000000 you don't need the commas, just three % signs, in other words, this custom format:
#,###%%%
If you do that, you'll immediately notice a problem - the three % signs also display, so instead of getting 2,500,000 as you want, you get 2,500,000%%%.
Luckily there's a trick to hide them. Go back to your custom number formatting and put your cursor just before the %%% signs:
#,###¦%%%
With the cursor there, press Ctrl-J to enter a carriage return.
#,###Ctrl-J%%%
The number format will appear to change to just #,### but actually it's on two lines:
#,###
%%%
You now need to turn 'Word Wrap' on in your data cells and adjust the column width to show just the number - in other words, force the %%% to wrap on to the next line down, where you won't see it. If you have rows which are two lines high, or more, just enter another Ctrl-J for each line.

I hope that makes sense!

Please try custom format
0\,0\00 %
click in front of % and press Alt + 0 0 1 0 to put % to next line > OK

Untitled.jpg

Then press Warp text

For Pivots I'd go with MrShorty's approach. If pivot is based on Excel Range and not from Data model (OLAP), then you can add calculated field.
Ex: ='Value Column'*1000
Add calculated field to pivot instead of original value column and format as number with thousand separator and no decimal.

If it's based on Data model, you can add DAX measure to do the same (or perform transformation in PowerQuery).