I used to solve this problem with Home, Find & Select, Go To Special, Blanks, OK, 0, Ctrl+Enter. There is a faster way, using Ctrl+H, as shown in today's episode.
Transcript of the video:
The MrExcel Podcast is sponsored Easy-XL.
Learn Excel from MrExcel Podcast Episode 1888: Replace Empty Cells with Zero.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
I have two different solutions of how to solve this problem today and one of them has been on the podcast before but this other one is new.
As you see, we have a lot of empty cells here and before we would create a pivot table from this, we want to fill those empty cells with 0.
So, surprisingly, you can use Find or Replace.
So, Ctrl+H and we just leave Find What as blank and Replace With as 0.
Click Replace All and all of those blank cells become 0.
I thought that was a great trick.
I learn that on the road with one of my Power Excel seminars from someone in the audience.
Previously, I would have done select all the cells, Find and Select, Go To Special, and said I want to select the Blank cells, click OK which selects just those blanks and then 0 and Ctrl+Enter to enter that in all the cells of the selection.
Seems like Ctrl+H is a faster, faster way to go so cool tip there.
All right, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
Learn Excel from MrExcel Podcast Episode 1888: Replace Empty Cells with Zero.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
I have two different solutions of how to solve this problem today and one of them has been on the podcast before but this other one is new.
As you see, we have a lot of empty cells here and before we would create a pivot table from this, we want to fill those empty cells with 0.
So, surprisingly, you can use Find or Replace.
So, Ctrl+H and we just leave Find What as blank and Replace With as 0.
Click Replace All and all of those blank cells become 0.
I thought that was a great trick.
I learn that on the road with one of my Power Excel seminars from someone in the audience.
Previously, I would have done select all the cells, Find and Select, Go To Special, and said I want to select the Blank cells, click OK which selects just those blanks and then 0 and Ctrl+Enter to enter that in all the cells of the selection.
Seems like Ctrl+H is a faster, faster way to go so cool tip there.
All right, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
