+ Reply to Thread
Results 1 to 7 of 7

must not show 0

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    94

    must not show 0

    Hi hope this is the correct forum. I have a simple formula that returns a value, sometimes 0. What is the setting / formula to use if i want the 0 value to show blank cell?

    Thanks!
    Last edited by Schalk; 03-16-2022 at 09:37 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: must not show 0

    Apply a Custom Format to the cell with the formula:

    General;-General;;

    The two semicolons at the end will make zero values display as blanks.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-15-2011
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    94

    Re: must not show 0

    That works thanks, but what if when the value is not 0 and I want to to show a currency like $23.00. Can this be done?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,321

    Re: must not show 0

    Alternatively, with a formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,420

    Re: must not show 0

    In File...Options...Advanced you have the ability to untick the 'Display Options for this worksheet...Show a zero in cells that have zero value'
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: must not show 0

    Quote Originally Posted by Schalk View Post
    That works thanks, but what if when the value is not 0 and I want to to show a currency like $23.00. Can this be done?
    You can modify the format string that I gave you - just change the General to what you want.

    Hope this helps.

    Pete

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    East Asia
    MS-Off Ver
    Office 365
    Posts
    311

    Re: must not show 0

    another approach is to use Conditional Formatting

    - highlight the cells you want this to apply to
    - go to Conditional Formatting > New Rule
    - format only cells that contain > cell value equal to zero
    - format > number > custom > use the format from Pete_UK's post

    A slightly longer way to set it up, but it means you can change the formatting of non-zero data more easily
    <<< Please click * to thank valued contributors

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Show Tasks as Lines and Show Time Points' bars on X-Axis
    By pjvyas in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 09-29-2019, 05:14 AM
  2. Replies: 2
    Last Post: 05-29-2017, 11:03 AM
  3. Replies: 2
    Last Post: 08-14-2014, 06:03 AM
  4. [SOLVED] One TextBox Show Column and the other show Cell address Solved by : ragulduy
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-26-2014, 05:17 AM
  5. [SOLVED] how to add counter in msgbox to show remain times the message will show vba code
    By sspreyer in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-25-2013, 06:46 AM
  6. Replies: 0
    Last Post: 06-15-2011, 10:36 AM
  7. Replies: 1
    Last Post: 04-11-2008, 02:02 AM

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