Excel: Make a Zero Not Show 30

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.


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..
Excel make zero not show part 1  Dockery Design

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:
Excel make zero not show part 2  Dockery Design

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.

30 thoughts on “Excel: Make a Zero Not Show

  1. Reply dockerydesigns Jan 31,2010 10:00 am


    Its always difficult to integrate a new excel trick. However, making the zero disappear from an excel spreadsheet sum is easy, and very, very effective.

  2. Reply Battaglini@ymail.com Feb 22,2010 2:41 pm

    [ bunch of expletives ].

    Why didn’t you post this information when I needed it? Now everyone thinks I’m a dolt.

  3. Reply Ulysses Sittner Feb 23,2010 1:08 am

    Interesting article. Were did you got all the information from…

  4. Reply Ronna Chiaminto Feb 23,2010 4:39 pm

    Such a usefule blog…wow !!!!

  5. Reply Sweating Cure Feb 25,2010 5:41 pm

    I am a frequent reader of your blog and just wanted to inform you that I really like your articles.

  6. Reply Chelsea Meleski Dec 23,2010 2:27 pm

    For design work like in your portfolio, I could earn $300-$2000 Per Day! Sign me up for a consultation call!

  7. Reply physical therapist salary/a> Mar 26,2011 4:09 am

    You made some decent points there. I appeared on the web for the problem and located most people will go together with with your website.

  8. Reply monster beats outlet Mar 31,2011 11:21 am

    Since the Monster dre beats headset can not simulate the sound of the rear, monster beats there is no diffraction effects of the human ear, the sound field in front of largely distributed in the range of 180 degrees,dr dre beats all the sound elements (such as musical instruments) are evenly distributed within this range, from near and far from uniform. Beats by dre The reason to draw such a map,Monster beats bydre not to say that the only way the sound field distribution is correct, but to facilitate discussion of a hypothetical,dr dre beats assume that the distribution of various musical instruments is indeed the case,beats by dre and through the sound system is the ideal way to restore Beats by dre sale.

  9. Reply Chassidy Vink Apr 1,2011 7:58 am

    Hi and many thanks for the comment you still left on my blog, i’ve also bookmarked your internet site and can return as i uncovered the data within your internet page just like a very good source to my very own pup regards Eddie

  10. Reply Stacy Taglialatela Apr 7,2011 4:17 am

    I’ve bookmarked, Dugg, and I joined the RSS subscription. Thanks! .

  11. Reply Andrew A. Sailer Apr 10,2011 5:39 pm

    you have a great blog here! would you like to make some invite posts on my blog?

  12. Reply Cheap Christian Louboutin Apr 11,2011 9:24 pm

    I had issues together with your website on my browser and needed to refresh the page a couple of times?-

  13. Reply Christian Louboutin Apr 12,2011 12:37 am

    Thanks to the Fresh Staff to get a good 12 months of assessments.I often adore the very best and Worst l ist, however I consider th is 12 months you might have m issed some monumental f*ckups for the ??Worst??? l ist. Logos like ANZ, Frigidaire, Packard Bell and Tropicana all of which got a unanimous bollocking from your Brand Newbians. To the ??Best??? , I would have integrated:Apollo Meiji Woolworths FICOJack inside the box or AVID.

  14. Reply ubuntu themes Apr 12,2011 3:26 pm

    thanks! :) :) :) :)

  15. Reply Robbie Laffitte Apr 12,2011 4:00 pm

    You have a lot of useful pointers on this page.

  16. Reply Kary Mendicino Apr 14,2011 8:03 pm

    Oh, this is quite interesting. Actulally, I found your blog on google search. I will tell my friend about your blog later.

  17. Reply Sara Howlin Apr 15,2011 2:28 am

    Thank you very much for your post! I am very interested in your points.

  18. Reply Cheap Christian Louboutin Apr 15,2011 3:10 am

    The new Zune browser is surprisingly good, but not as good as the iPod’s. It works well, but isn’t as fast as Safari, and has a clunkier interface. If you occasionally plan on using the web browser that’s not an issue, but if you’re planning to browse the web alot from your PMP then the iPod’s larger screen and better browser may be important.

  19. Reply list foods high in potassium Apr 15,2011 6:14 am

    I’ve bookmarked, Dugg, and I joined the RSS subscription. Thanks! .

  20. Reply Sherita Magri Apr 15,2011 3:55 pm

    You make a lot of good points on this blog. Keep up the great work.

  21. Reply autos rent Apr 16,2011 12:49 am

    Zune and iPod: Most people compare the Zune to the Touch, but after seeing how slim and surprisingly small and light it is, I consider it to be a rather unique hybrid that combines qualities of both the Touch and the Nano. It’s very colorful and lovely OLED screen is slightly smaller than the touch screen, but the player itself feels quite a bit smaller and lighter. It weighs about 2/3 as much, and is noticeably smaller in width and height, while being just a hair thicker.

  22. Reply Humberto Lagrasse Apr 18,2011 5:19 pm

    A thoughtful insight and ideas I will use on my blog. You’ve obviously spent a lot of time on this. Congratulations!

  23. Reply Angelo Welander Apr 21,2011 8:05 am

    Just wanted to give you a shout from the valley of the sun, great information. Much appreciated.

  24. Reply Chas Suran Apr 24,2011 4:56 pm

    Howdy blogger, thank you for providing this article.. I found it first-class.

  25. Reply Korey Anstey Apr 25,2011 3:02 pm

    Hmm, thanks for sharing, I’ve never thought of that before. Hey, can you credit the original source that you read when publishing this? Thanks :)

  26. Reply Joline Pasquarelli Apr 26,2011 3:29 am

    Brilliant idea! I couldn’t agree more.

  27. Reply Austin Bergsten Apr 26,2011 5:24 am

    Don’t jump to conclusions. Maybe there is another great idea.

  28. Reply 快打旋風 Apr 27,2011 6:16 am

    do you have twitter or facebook?I want to be your fans in hurry.3$4

  29. Reply Bench Saw Apr 28,2011 6:24 pm

    RSS Feeds on your blog sometimes does not work.-*,,-

Leave a Reply