Format cells to hide numbers, but still use them for calculation

Excel

An easy way to hide numeric contents, but still have it available for calculation is to give them a custom format of nothing,

There are four custom formats, each separated with a semi-colon (;).

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

For example, this formatting,

#,##0.00_);[Red](#,##0.00);0.00;”Comment: “@

will show positive numbers in black with two decimals and a blank space at the right to adjust for the paren “)” used with negative numbers. Negative numbers will be red with two decimals and enclosed in parenthesis. Zero (0) numbers will use two decimals in black. Typing text in the cell produces “Comment: text you type“.

To make a number disappear, but still be there for calculation, use the formatting,

;;;

This custom format creates no format for positive, negative, zero, or text values. So, nothing appears.

Beware! If you format a cell to disappear, then create a new formula that references the hidden cell, the result from the new formula will also be hidden since it copies the format from the cell’s used in the formula. It’s easy to fix though. Just reformat the new formula to show using the numeric format you need.

Share the power...