Sunday, April 03, 2011

Tip to Stop Zeros Showing for Empty Excel Values

If we are not careful our Excel spreadsheets can appear cluttered and clumsy. One often irritating feature is when formulas that reference other cells display '0' instead of remaining blank when there is nothing to display.
What do I mean? Imagine your formula references A1 but the cell doesn't have a value. Rather than making our formula return blank, Excel actually shows '0'.

Take a look at the example below:

Where those cells show '0' it would look much more professional if instead it did not display anything
when the referenced cells contained no data.

The solution is easy but usually overlooked.

Essentially this means "If the cell is anything other than blank, display the value, otherwise return blank". We return blank by just using "" which is an empty string.
This is very simple (when you know how) but these tiny details can make a big difference to the usability, elegance and professionalism of your Excel spreadsheets!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter


Digg Facebook Technorati Delicious StumbleUpon Reddit BlinkList Furl Mixx Google Bookmark Yahoo Add to Technorati Favorites TwitThis

Post a Comment