# Excel: Make a Zero Not Show30

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.

## 30 thoughts on “Excel: Make a Zero Not Show”

http://www.dockerydesign.com/blog/index.php/excel-make-a-zero-not-show/

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.

[ 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

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

4. Reply Ronna Chiaminto

Such a usefule blog…wow !!!!

5. Reply Sweating Cure

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

6. Reply Chelsea Meleski

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>

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

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

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

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

11. Reply Andrew A. Sailer

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

12. Reply Cheap Christian Louboutin

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

13. Reply Christian Louboutin

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

thanks!

15. Reply Robbie Laffitte

You have a lot of useful pointers on this page.

16. Reply Kary Mendicino

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

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

18. Reply Cheap Christian Louboutin

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

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

20. Reply Sherita Magri

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

21. Reply autos rent

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

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

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

24. Reply Chas Suran

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

25. Reply Korey Anstey

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

Brilliant idea! I couldn’t agree more.

27. Reply Austin Bergsten

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