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):

=IF(SUM((C3:H3))>=1,SUM((C3:H3)),””)

Nifty, hmm?

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

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!

Advertisements

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

  1. Anonymous says:

    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.

  2. robin says:

    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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: