This is one of those things that I wish (Upon a star) that would just be automatic.

There are so many things in Excel that are automatic and a nuisance at the same time, I think we take for granted the automatic things in Excel that don’t annoy us.  How’s that for your riddle of the day!

I was recently filling out an expense form and I thought – Hey, I got a minute, let’s fancy this puppy up a bit (I say this to myself with a southern drawl accent…when I say these things to myself – it spices up my day a bit).

So on the expense report I added formulas to total the row and then total the column. But when you add the formula it places a 0 or $0 in the column – it’s the default (yuck).

From this…

…to this.

There are a couple ways to deal with this:

• change the formula to something like this: if(sum(e19:m19)=0,””,sum(e19.m19))     
  If you do this, however, you will could get an error if you were adding the column up

• Use Conditional Formatting: 
  The catch here is that sometimes when you set the font to white in conditional formatting, when you print the text will show up. You won’t see it on the screen, but when creating a PDF and/or printing it will show there.

Select “Use a formula…”

Choose white for the text color

It will show you what you have defined but in this case it is to display white text, so it won’t show here.

• cell format: Number -> Custom ->   $0.00;-0;;@,
  seems straight forward and to the point, but not unless you understand the format keys. But this is my preferred way to do this.

I know this is pretty rudimentary, but lately I’ve noticed that those are the things being overlooked these days.