Back in Episode 767, the pod dealt with the problem of using =IF(ISBLANK(A2),"",A2) to hide zero values in the results table. Today, Rene and Denis both write in with a far simpler solution. Episode 783 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Now, we're going to go back and address an issue that we talked about in podcast 767.
I got 2 notes -- one from Rene, one from Dennis -- and they said, you know, hey, you missed the really obvious thing, and I'm great at missing the obvious thing.
Back in 767, we had a data set that looked like this and, on another worksheet, they had set up a formula that was basically copying that data, and so I'll just choose =B2 here, and the original data set is fairly sparse.
So, what we're getting is, of course, lots of places the numbers are showing up.
So, there's the 9, the 9, but, everywhere else, we were getting a 0, and, in that question, they said that they had rewritten this to put “” instead of 0s.
Someone said, well, you know, that's the long way around.
Why don't you just go to TOOLS, OPTIONS, and on the VIEW tab, there's a setting that says we're going to show ZERO VALUES.
If we uncheck that, then, sure enough, the data on the copied worksheet, where all the formulas are, looks just like it did on the original sheet.
All the places where there is no value, and we're getting a 0, we're just simply getting a blanket to appear there, and the beautiful thing is that there's not any text in there.
So, if we would build a formula that would refer to a couple of cells, it's going to work perfectly, or if we would build the AUTOSUM formula out there, it's going to work perfectly as well all the way through.
Now, the one downside to this is it affects every cell in your spreadsheet.
So, if you had another section where you really wanted 0s to appear, well, they're not going to appear, but, in many cases, that TOOLS, OPTIONS, and VIEW, uncheck zeros is a great way to go.
Thanks to Renee and Dennis for sending that in -- I'll be sending out an Excel master pin to both of them -- and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, we're going to go back and address an issue that we talked about in podcast 767.
I got 2 notes -- one from Rene, one from Dennis -- and they said, you know, hey, you missed the really obvious thing, and I'm great at missing the obvious thing.
Back in 767, we had a data set that looked like this and, on another worksheet, they had set up a formula that was basically copying that data, and so I'll just choose =B2 here, and the original data set is fairly sparse.
So, what we're getting is, of course, lots of places the numbers are showing up.
So, there's the 9, the 9, but, everywhere else, we were getting a 0, and, in that question, they said that they had rewritten this to put “” instead of 0s.
Someone said, well, you know, that's the long way around.
Why don't you just go to TOOLS, OPTIONS, and on the VIEW tab, there's a setting that says we're going to show ZERO VALUES.
If we uncheck that, then, sure enough, the data on the copied worksheet, where all the formulas are, looks just like it did on the original sheet.
All the places where there is no value, and we're getting a 0, we're just simply getting a blanket to appear there, and the beautiful thing is that there's not any text in there.
So, if we would build a formula that would refer to a couple of cells, it's going to work perfectly, or if we would build the AUTOSUM formula out there, it's going to work perfectly as well all the way through.
Now, the one downside to this is it affects every cell in your spreadsheet.
So, if you had another section where you really wanted 0s to appear, well, they're not going to appear, but, in many cases, that TOOLS, OPTIONS, and VIEW, uncheck zeros is a great way to go.
Thanks to Renee and Dennis for sending that in -- I'll be sending out an Excel master pin to both of them -- and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
