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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,659
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
1,659
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,141,073
Messages
5,704,141
Members
421,328
Latest member
mippy

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