Excel Formats - Hide 0 (Zero) Values Easily

You never know when you're going to learn the things you use most.  Mine came mainly from holding the flashlight for my Dad (thanks, Dad. You are AMAZING.) From a career perspective, though, I'd have to say my time as an tech support guy on Excel 1.0 has been nearly as useful as any given year of my many in higher ed.

Excel was a Mac app first.  Did you know that? Yup.

Excel shipped on the Mac before Windows 3.0 shipped on the PC.  (For the record, Win 3.0 - which was really the first adequate GUI on the PC platform, shipped on my birthday in 1990. Coincidence?  One wonders.)  Here's the funny thing. There are fundamentals about Excel that were there in 1989-90, that are useful today, that most people STILL don't know today. Here's one such tip.  

Situation:

  • You have a calculation formula in a range of cells.
  • Some results are POSITIVE.
  • Some are NEGATIVE.
  • Some are ZERO.

Did you know you can make the ZERO results invisible just by formatting?

THE KEY:  Format Cells.

Excel has a nomenclature and structure for number formatting.  The punchline for our immediate purpose, so you'll remember it:

Positive;Negative;Zero;Text

Select a range with a mix of positive, negative and 0 results. Try this:

  • Open Format-Cells.  
  • Scroll to "Custom" at the bottom.
  • Create this custom format:  ;;""  (that's semicolon, semicolon, quote, quote)

Cells with a zero value will display as blank. The cool thing is, they'll still have the numerical value  0.  

I learned that about 25 years ago.  Spreadsheets that show

  • 0.00
  • 0.00
  • some number
  • 0.00

still kinda irk me.  Usually, zero means nothing so don't tie up my brain cells showing me a symbol for nothing. Just Format-Cell-Custom-;;""

 

A Little More Detail Than You Wanted

Positive;Negative;Zero;Text

Excel has a bunch of format operators for any type in this mix.  Commas (#,###), percent formats (%.%%), place-padding (the format "00000" will force those pesky East Coast ZIP codes that start with 0 to show - e.g. 01235.  Google and read up a bit.  Formatting beats custom formats to death 'cause it doesn't affect the underlying value.

Think about that for a sec - you can HIDE negative numbers (format:  ;"";  ) but still use 'em in a calculation elsewhere.  Zowie.

 

Matthew Dunn