Search This Blog

Friday, June 20, 2008

Little excel tips: display no value for zero, automatically add date

So, you're totalling a formula up (such as a sum) and you do not want the value to display as 0 (i.e., zero). In my case, some people will use the spreadsheet online, while others will print it out. For those who print it, I want the total column to appear blank (but it is not!) so that they can manually print in their calculations (sure, they should be using it online, but....)

To display a blank value instead of zero, use this bit of code modified for your function (in my example, sum) and values (my case, a range of cells C3 throughH3):


Nifty, hmm?

Now, to add the date, each time a spreadsheet is opened or saved:
In a cell:

Ah, but you only want the year and month, e.g., June-01
Right click on the cell, format> on the number tab, choose> Date Now choose the date as you would like it to format. Need the time? Throw that in, too!


Anonymous said...

Nice. But, unfortunately, if you are charting the data the "" blank still looks like 0 to the chart code. I'm looking for something the chart code would treat like a blank cell.

Unknown said...

Hi there,
Thanks for letting me know. I hadn't thought about what happens if you take the data and pull it through to a graph or chart. I think there are a few ways around this using either some scripting or a filter.

Try this:
Select your chart > Tools > Option > Chart, Plot Empty Cells

(choose: not plotted)