Sign Flip Any Number Format in Pivot Table

kyleswanson

New Member
Joined
May 6, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am wondering if there is any easy way to take any number format in an Excel Pivot Table and essentially flip the sign. Preferably, any positive numbers would show up in parentheses (accounting format) and any negative numbers would appear to be positive without parentheses. In the case of percentages, would prefer the sign flip with a "-" sign instead of the parentheses.

Example:
Old Number: 30
New Number after applying sign flip edit: (30)

Another Example:
Old Number: 2.7%
New Number: -2.7%

As you can see, there are quite a few custom formats that I would like to apply this sign flip too, so wondering if these is a simple edit that can be made to any given format to sign flip it.

If there is no easy all inclusive solution, here are a few formats that would be awesome to know how to sign flip:

_($* #,##0,_);_($* (#,##0,);_($* "-"_);_(@_) – accounting thousands $
_(* #,##0,_);_(* (#,##0,);_(* "-"_);_(@_) - accounting thousands
_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) - accounting
0.0% - percentage with 1 decimal point

1620328183968.png



Thanks and really appreciate any insight on this!!

Kyle
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
The way the custom number formats work is that they have 4 positions separated by semi-colons which represent the following:-
Positive ; Negative ; Zero ; Text
The default for percent is effectively
0%;-0%
All you need to do is switch the sign around
-0%;0%

20210507 Custom Number Format percent.xlsx
ABCD
5SettingFormatPostiveNegative
6Default Format10%-10%
7Custom Format-0%;0%-10%10%
Sheet1


1620353590623.png
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
For the 30 and negative 30, I am pretty sure brackets is already a custom format, so use that custom format and create an new one swapping what is in position 1 (positive) with position 2 (negative)
 

Forum statistics

Threads
1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top