+ Reply to Thread
Results 1 to 7 of 7

must not show 0

  1. #1
    Forum Contributor
    Join Date
    11-15-2011
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    101

    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
    24,603

    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
    Forum Contributor
    Join Date
    11-15-2011
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    101

    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
    44,086

    Re: must not show 0

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

    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 - RIP 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,464

    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

    RIP - d. 06/10/2022

    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
    24,603

    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
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    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
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

+ 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