Custom Formatting replace "MyText" to Blank

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,972
Office Version
  1. 2019
Platform
  1. Windows
Hello Excel Gurus,

Is it possible to replace a value with a blank using Excel's custom formatting? Meaning I have text from PivotTables "Values" as when nothing is selected it shows (All) .
I would like to replace the text "(All)" with that of an empty cell. I have access to Custom Formatting within this cell but not sure if it is possible to achieve it or not. Or perhaps if I could shift the color to white text when (All) is typed in?

From this :

1605080835738.png


To this :

1605081005527.png


Will appreciate any help.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
310
Office Version
  1. 2007
Platform
  1. Windows
The best way to do this is:
Select cell "(All").
With right click go to Format Cells.
In the list Category select Custom.
In the field "Type" type three semicolon signs ( ;;; ), and press OK.
This is special custom format to hide values.
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,972
Office Version
  1. 2019
Platform
  1. Windows
The best way to do this is:
Select cell "(All").
With right click go to Format Cells.
In the list Category select Custom.
In the field "Type" type three semicolon signs ( ;;; ), and press OK.
This is special custom format to hide values.
But this will remove all values in a cell, whereas I would like to keep all values other than the one in brackets. meaning (All)
Thanks.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
310
Office Version
  1. 2007
Platform
  1. Windows
OK, try this.
When you creating formatting rule, select "Format only cells that contain".
Then:
Format only cells with: "Cell value, equal to, (All).
Click button "Format", go to the tab "Number" and do as I said before.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,972
Office Version
  1. 2019
Platform
  1. Windows
OK, try this.
When you creating formatting rule, select "Format only cells that contain".
Then:
Format only cells with: "Cell value, equal to, (All).
Click button "Format", go to the tab "Number" and do as I said before.
I think you mean Conditional Formatting? I rather come to live with (All) as I made other adjustments and was not trying to opt for condiitional formatting. so I am marking your first post as solution.
But thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,454
Messages
5,601,764
Members
414,471
Latest member
cchunyadi

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