Handling Zeroes in Excel

Much Ado About Nothing

It’s amazing how passionate some people can be about zeroes. I have known people who hate, with a passion, to see zeroes displayed in their reports. They will sometimes use some formula acrobatics to avoid having a zero displayed.

I usually make their day when I tell them that there is a sheet level option you can change to stop all zeroes displaying on a sheet.

The instructions vary slightly between versions

Excel 2010

Click the File tab and click Options

Excel 2007

Click the round Office button (top left) and click the Excel Options button

Both Excel 2007 + 2010

Click the Advanced option on the left and scroll two thirds down and look for the section as shown in the image below. Untick the option Show a zero in cells that have a zero value and click OK.

Excel Display Zeroes option

Excel 2003

Click the Tools menu and select Options then click the View tab and untick the Zero values option and click OK.

Now all zero cells will appear blank.

Excel 2003 Display Zeroes

Custom format

If you only want to stop zeroes displaying in a range and not the whole sheet you can use a Custom Format like.

#;#;

See image below – this works in all versions

Custom Format to stop zeroes displaying

 

Please note: I reserve the right to delete comments that are offensive or off-topic.