In todays podcast, a custom number format that shows positive values and zeros, but hides the negative values. Episode 553 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Now, in yesterday's netcast I showed how to use a formula; the IF function to make sure that any negative values are not shown and I wanted to point out that there's a non formula way of doing that, I went back to the original formula from the podcast from two days ago and I'm going to select all these cells, some are negative and some are positive, I'm going to go to format cell and on the number tag you'll see this is currently just formatted as a number, I'm going to go down to the custom type, now in the custom type we have a number format of 0 and many people don't realize it but you're actually allowed to enter for different number formats in this box and each one is separated by a semicolon, basically if you would enter all four it becomes the number of positive, the number of negative, the number of 0 and then what to do if there's text, but if you only enter two; the first zone is what to do if it's positive or zero, the second zone is what to do if it's negative, so what I want to do is I want to say my format is zero semicolon, that says if the number is positive or zero just show it as a regular number but if it's negative show it as nothing, when I click OK sure enough all the positive numbers show up but all of the negative numbers show up blank.
Great way to not have to change your formula simply use a number format and you'll hide all the negative numbers.
Hey thanks for stopping by; we'll see you next time for another netcast from MrExcel.
Now, in yesterday's netcast I showed how to use a formula; the IF function to make sure that any negative values are not shown and I wanted to point out that there's a non formula way of doing that, I went back to the original formula from the podcast from two days ago and I'm going to select all these cells, some are negative and some are positive, I'm going to go to format cell and on the number tag you'll see this is currently just formatted as a number, I'm going to go down to the custom type, now in the custom type we have a number format of 0 and many people don't realize it but you're actually allowed to enter for different number formats in this box and each one is separated by a semicolon, basically if you would enter all four it becomes the number of positive, the number of negative, the number of 0 and then what to do if there's text, but if you only enter two; the first zone is what to do if it's positive or zero, the second zone is what to do if it's negative, so what I want to do is I want to say my format is zero semicolon, that says if the number is positive or zero just show it as a regular number but if it's negative show it as nothing, when I click OK sure enough all the positive numbers show up but all of the negative numbers show up blank.
Great way to not have to change your formula simply use a number format and you'll hide all the negative numbers.
Hey thanks for stopping by; we'll see you next time for another netcast from MrExcel.
