Replace blanks with zero in #Excel

Following the techniques used in the previous post , we can perform many Excel magics for different scenarios.  On top of my mind is to replace blank cells with zero.

Excel tip - Replace blanks with zero.png


The first task is to select all blank cells in the range.  We can do so by using the following shorcuts:

Ctrl+A => Ctrl+G => Alt+S => k => Enter

(Note: select any cell in the range first)

With all the blank cells selected, the trick is to enter “0” or any values or texts or even formula and then confirm by pressing CTRL+Enter.

As simple as this! 😎

Another approach doing the same task is Find and Replace.  Simply

  1. Select the range with blanks
  2. Ctrl+H to open Find and Replace dialog box
  3. Leave it blank in “Find what”; enter 0 in “Replace with”
  4. Replace all

Done Done Done Done 😁


Watch it in action on my YouTube channel:

I hope you like it.  If you do, please give a thumb up, share and subscribe. 😉


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.