Excel: Make a Zero Not Show

Excel: Make A Zero Not Show

OK, admittedly, not the greatest title for this post. However, this is the main issue I faced today with a clients’ materials.

We built an Excel invoice sheet for one of our great customers The Right Moves. With several equations built into the forms for ease of use and functionality, we felt that this item would be a homerun right out of the gate. However, when they came back with revisions, well… we knew that we might be looking to learn a little something about excel.

Here’s a handy little formula to add up a column or row of figures and only show the result if it’s a non-zero.

=IF(SUM(D4:E4)>=1,SUM(D4:E4),””)

I find it hard to pick out positive numbers in a whole list of values if there are a lot of zero numbers. Look at the two following examples..

Well, if you find that your excel spreadsheets include a whole lot of numbers in use— for instance, if your excel files has you looking at a stock list that goes on for pages and what you’re looking for are numbers hidden within a lot of zero quantity items, it’s really easy to miss those numbers. Why give a faulty report? By using the IF function of Excel you can test the normal formula and only output a result for whatever value you want. ie.

=IF(logical test,if true,if not true)

the example below uses this

logical test : = SUM(D4:E4)>=1

the sum of the numbers between D4 and E4 is greater than equal to 1
if true: sum(D4:E4)

• if the logical test is true then output the sum of D4 to E4
• if not true: “”

output nothing, you could use whatever you want for the “if true” and “if not true”. Text would go in quotes and formulas go without.

Here’s what it looks like:

Much nicer, much easier, and the client agrees wholeheartedly: Much Better! This works great, and you can customize this item to show any character you like (dash, etc.). When using excel, make those zero disappear when you use a sum. Does Excel drive you crazy? Maybe our team has the solution you need. Visit Dockery Design, Raleigh Graphic Design Firm and learn about what we can do for you.