You can do this with conditional formatting. Click on Format , then
Conditional Formatting. Your formula would say =A1=0 if you want to
conditionally format cell A1. On this same screen after entering your
formula, click on Format, and change Font to white. Then if the cell value is
0 in cell A1, the font is white in cell A1, the same as your background so
you can't see the 0. You're basically telling Excel, if there is a zero in
cell A1, make the font white. To copy this conditional formatting to other
cells throughout your worksheet without affecting your formulas, us the
Format Painter. Click on the cell containing the conditional format, then
click on the Format Painter paint brush. The cell will turn to a blinking
dotted border. Then click and drag the blinking cell to all cells you want to
format to hide the zeros. Because this can replace other formats in other
cells, like bold borders or font size/font color, only drag to cells with
common formats. It will not, however, replace formulas in other cells. And
this is the beauty of the Format Painter. It only copies and pastes
formatting, not formulas. If, because of the complexity of your spreadsheet,
it is impossible to click and drag the Format Painter to all your cells that
could contain a zero you want to hide, you'll have to individually
conditionally format each cell that could possibly contain a zero. Don’t drag
a cell reference that is locked to a particular cell such as =$A$1=0, or all
the conditional formatting will be based on the value in Cell A1. You can use
the F4 key to remove the $ to unlock a cell as to row and column. Obviously
the formula =A1=0 should only apply to cell A1. So to conditionally format
Cell B2, the formula would be =B2=0, etc. There are no If statements in
conditional formatting, so the formulas are different than standard cell
formulas. You can use And and Or quite creatively in conditional formulas to
do amazing things.
Here's an example: =OR($B$38<1,$B$38="") I use this formula to turn a cell
red if it is zero or blank. I set the Pattern to Red and left the font as
black. This is a cell that can't be zero or my spreadsheet will give
incorrect pricing, so it turns red to remind user to enter a value. This
obviously only affects cell B38. As a side note, if you try this instead,
=If($B$38<1,OR $B$38="") it won’t work. Again, conditional formatting
formulas are different than financial calculation formulas.
You cannot do any financial calculations with conditional formatting, it is
as the name of the feature implies, for conditional formatting a cell as to
Font, Border, or Pattern (color). You can conditionally format any cell in a
spreadsheet based on the value in any other cell on the same spreadsheet. You
can't, however, conditionally format a cell based on a cell in another
worksheet in the same workbook or another workbook. However, this can be
accomplished by using a formula to bring the value from another spreadsheet
in the same or any other workbook, into the active spreadsheet and then do a
Conditional Format based on the value in that cell.
Probably more than you needed to know for your project, but a useful lesson.
Gary
