Zero in Excel

Zero is an interesting number in Excel (and in mathematics in general) and causes lots of problems for a ‘nothing’ number. Below some quick tips on dealing with zero in Excel.

YouTube Dealing with Zeros in Excel

Hide/ Show Zero in Excel- Change the options

If you prefer to have all zeros (numbers, percentages etc) hidden in your spreadsheet you can change your options to reflect this.

1. Under Excel Options,
2. in Advanced, about half way down you can
3. untick the ‘Show a zero in Cells that have zero value’.

So if you have it switched on or off you will see the following with the zeros (note that even the 0% disappear).

If your zeros are hidden, you can show them again by ticking the ‘Show a zero in Cells that have zero value’.

Note however that this is not the only way to do this. So if you have received a spreadsheet with zeros hidden and the above doesn’t work, then look at the other options.

Hide/ Show Zero in Excel- Conditional Formatting

Another option is to use Conditional Formatting. This would be used in the case where you only want to hide certain cell’s zeros.

So below we only highlight cells I10 to I14 and:

1. Go to the HOME tab
2. Click Conditional Formatting
3. Click Highlight Cell Rules
4. Click Equal To
5. Put a 0 in the ‘Format cells that are EQUAL TO:,and
6. Choose Custom format.

In the custom format box you would choose the FONT tab and change the colour to match the background colour (in this case white). In effect the zero will still be there but it will be coloured white which is the same colour as the background so it looks hidden.

As a result, only those cells will ‘disappear’ while all the other zeros will be shown (see below).

The risk with making zeros disappear

The one problem with making zeros disappear is that users may think the cell is empty and accidentally overwrite the cell number or formula.

As per Spreadsheet Good Practice module we advise that it is very clear where users can and can’t work in cells. We suggest you rather replace the zero’s with a dash. It is different enough from a number to be more transparent, but it shows that there is something in that cell.

Make zero a dash in Excel- Accounting Format

We find that we are using the Accounting Format regularly as it shows zeros as a dash and we can control whether we want the currency symbol to appear or not.

As shown below in the red box, we feel the dashes are clearer for presentation purposes while showing that there is something in that cell.

To make a zero appear as a dash in Excel you:

1. Highlight the cells you want to change
2. Click on Format Cells
3. Choose the Accounting format
4. Set your number of decimals and choose the currency symbol (which includes an option of None if you don’t want one)
5. Click OK.

Make zero a dash in Excel- Custom Format

The above method works well, but you may want a bit more customization. This is where you can create your own custom format (and this is useful if you want to start showing zero % as a dash).

In order to use the Custom Format to make zeros a dash:

1. Highlight the relevant cells
2. Click on Format Cells
3. Choose the Custom option
4. In the Type box, use # ##0;-# ##0;- (note the semi colons between the options)
5. click OK

Hide/ Show Zero Percent in Excel- Conditional Formatting

For percentages you can do the same thing as shown above using conditional formatting.

So highlight cells the cells and:

1. Go to the HOME tab
2. Click Conditional Formatting
3. Click Highlight Cell Rules
4. Click Equal To
5. Put a 0 in the ‘Format cells that are EQUAL TO
6. Choose Custom format.
7. In the Custom Format box, choose the FONT tab and
8. change the colour to match the background colour (in this case white).

Note we have the same concern as with numbers. If the contents is hidden, users may accidentally overwrite formula.

Hide/ Show Zero Percent in Excel- Custom Format

You can hide any zero percents using the custom format option in a similar way to making zeros a dash.

In this case you would use the custom format code of

0.0%;-0.0%;  (note that after the last semi colon it is blank).

Note we have the same concern as with numbers. If the contents is hidden, users may accidentally overwrite formula.

Make zero percent a dash in Excel

To make a zero percent (0%) appear as a dash you need to use the Custom Format option.

As shown below, any non zero will show the % but a zero shows a -.

The custom format would be

0.0%;-0.0%;-

Dealing with leading zeros

Another common problem with zeros is leading zeros, that is where a zero appears in front of a number e.g. 083 123 4567. Sometimes you want them to stay and sometimes you want them to go away. In a separate post we deal with these options.

For some help with dealing with zeros (including leading zeros) have a look at the Data Cleanup in Excel course.

Related

Excel bullets in cell