Here’s some breaking news……Formatting fonts white in order to hide values is not clever. It’s annoying.
Here is an example.
You’ll notice in the formula bar, there is a reference to cell G2. But there doesn’t seem to be anything in G2.
Highlighting column G will reveal that there are indeed values there. They are formatted with white font as to be hidden. I call these white-formatted cells ‘ghost cells’
I suppose some people do this to hide important variables from view in order to protect them. But this tom-foolery not only comes with obvious dangers, it also makes auditing a spreadsheet a hassle.
If you work with one of these geniuses from the ‘Enron School of Spreading Design’ who use the white font trick, here is a technique you can use to quickly reveal these ‘ghost cells’
Step 1: Start Find and Replace and choose Format.Instead of finding an actual value, you can tell Excel to find a format specification.
Step 2: Specify the format you are looking for.
If you are looking for white fonts, you would simply go to the Font tab and choose white under the Color selector.
Step 3: Click Find All then press Ctrl+A on your keyboard.
Pressing Ctrl+A will activate all the cells in the spreadsheet that have the white font.
Step 4: Give the selected cells a different formatting.Once the cells are activated, you can simply apply another formatting.
Now that you’re on to me, I’m modifying my approach. Now using black text, with black fill. Take that, bacon boy.
Very clever.
To catch it, since it’s often constants that are in those cells, I often use the “Go To” functionnality to select all constants. (Hit F5, then click the “Specials” button and select “Constants” from the menu)
Best practice would recommend hiding the column or even better grouping it.
I agree white text cells are a PITA.
However I do use this on occasion if I want to “hide” repeating labels but do need them as values in the cells for pivots and other stuff.
I hide fonts by using custom number format ;; – works like a charm.
Never saw that button on the find and replace options for format. amazing what you don’t see when you are not looking for it. Great post. Thanks!