Hi AHubbard,
No, what you are looking is not doable via available Custom Formatting options.
You can modify the Pivot Calculated formula to some extent but this would not meet your requirement as I have understood.
Let me know incase this is not what you are looking for.
Requirement:
Pivot Table Output 1234.567 to appear as 1234567. (You can add formatting $ etc. as required later). You want this without modifying the Input Sheet which contains values as 1234.567.
I guess you had a typo on the question "Normally I'd just divide it by 1000 to move the decimal point back". Hope you meant
Multiply.
1234.567*1000=1234567 (move the decimal three spaces to the right)
Solution:
The solution I have is building a custom column with the desired formulars using 'Calculated item field'
feature of Pivot Tables in Excel.
Steps and screenshots as below:
1. Select the Pivot Table
2. Under Pivot Tools
On the Options tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
3. In Insert Calculated Field
Name: Value Formatted (Can be anything you want)
Formula: = Value*1000 [Double Click on the 'Value' field from the list, which you want to modify then append "*1000" to the end]
4. Click OK [to Directly Add the new Calculated field]
5. Remove the auto calculated field, Rt. click Remove 'Sum of Value'
6. Now you are left with the desired output.
7. Also note that the Custom Formula fields will be available for your future usage as well from the 'Pivot Table Field List' for this workbook.
References:
Create a formula in a PivotTable report
How to modify a calculated field in a pivot table
Other recent topics
