+ Reply to Thread
Results 1 to 3 of 3

Show blank instead of zeroes

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    Ukraine
    MS-Off Ver
    2013
    Posts
    23

    Show blank instead of zeroes

    Hello.

    I have a formula: SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,$A8,Sheet1!$C:$C,O$1)-SUMIFS('Sheet2'!U:U,'Sheet2'!$G:$G,$A8))

    I want the result to be shown in all cases, except when it is =0.

    Right now I did it this way: IF(SUMIFS(...)=0,"",SUMIFS(...))

    But there are probably hundread of such formulas, and even bigger and complex ones, and this file is already working very slowly. Is there an easier and less resourcefull way of doing this?
    I read this page: http://office.microsoft.com/en-us/ex...005199879.aspx

    But I'm not sure if other ways are less resourcefull. For example I think that I can use Conditional Formatting, but it's not the best option.
    The way to turn it off in Excel at all is not an option.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,318

    Re: Show blank instead of zeroes

    Try one of...

    Hide Zero’s
    • Highlight range >> Ctrl + 1 >> Number tab >> Category: Custom >> Type: 0;;;@
    • Office Button >> Excel Options >> Advanced >> Display Options for this worksheet: Uncheck Show as zero
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Show blank instead of zeroes

    Please Login or Register  to view this content.
    Your formula will be much quicker if you don't use the whole column, but a range in it.

    e.g.
    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1