How to quickly fill cells with 0 (zero) – Excel Quick Tip

Moments ago an active member of our facebook Excel community Muhammed Bilal Asmat asked this question:

Okay for example there is a very large table consists of rows and columns. Some of the cells contains values and some are blanks. So I want to convert cells containing values to zero. The way I am doing right now is to go to each cell which contain value and den I change it to zero other cells which are blank should remain blank. It took me about 2 hours to do it any simple way. Hope u understand what I want to say.

 U will not believe I am working for more than a year n each month end I spend 2 to 3 hours to make the values null. Waiting.

WOW… seriously?

Here is an easy way to do it using wildcards in Excel with Find and Select feature

Method 1 – Using Excel Find

Step 1: Select the range using mouse or have an active cell within the range and hit Ctrl+A

Step 2: Hit Ctrl+F and in the find what field type asterisk (*) symbol by pressing and holding Shift key on the keyboard and then press 8. (Usually it is 8 but if your keyboard is different then it might be on some other key, have a look 🙂 )

Step 3: Click Find All button. Excel will find all the cells that contain any values at all.

Step 4: To select all the results click on the first result and hit Ctrl+A you will notice that all the cells with values will be selected leaving the blanks unselected.

Step 5: Click Close button but be careful not to make any inadvertent click just yet. Hit F2 key. This will put you in edit mode in the first cell. Type 0 or any digit or anything you like and instead of hitting just Enter hit Ctrl+Enter. Hitting Ctrl+Enter will put the same value in all the selected cells at once. In our case it is 0.

Following animation shows all five steps carried out:

Method 2 – Using Excel Replace – Easier than first method

Step 1: Select the range with mouse or with a shortcut key Ctrl+A if active cell is inside range.

Step 2: Hit Ctrl+H combo. This will open the same dialogue box as in the method 1 but now with the replace tab.

Step 3: In find what field put in asterisk symbol as discussed in step 2 of method 1. And in replace with field type the value with which you want to replace the original content. In our case it is 0.

Step 4: Click Replace All button.

Step 5: Rejoice! you have just saved months of time in your future life 🙂

Following animation shows how to do it:

Bonus Tip – Fill blank cells with “0” (zero)

Most of the we want to fill blank cells with zeroes as opposed to the situation Asmat mentioned. The reason is blank cells create many problems especially in reporting if you don’t know how to handle them. So the easiest way is to have zeroes in blank cells. But in large set of data it is quite a challenge to do so. But here again Excel is no stopping to amaze us. Follow these steps to fill blank cells with zero or anything you like:

Step 1: Select the range using mouse or having an active cell within range and hitting Ctrl+A

Step 2: In the home tab > Editing Group > Find and Select drop down > click go to special

Step 3: From the dialogue box select blank option and click OK. Immediately all the blank cells are selected. Don’t make any clicks or movements that deselect the data.

Step 4: Hit F2 key to enter edit mode and hit 0 key on the keyboard and instead of pressing just Enter key hit Ctrl+Enter and it will put 0 in all the blank cells at once.

Watch the following animation to kill any confusion:

….. Coolness! 🙂

So here you have it. One of the secrets revealed that are used by Excel PROs to tame the wild-big data. How are you using Excel’s Find, Replace and GO TO Special feature? Enlighten us with your great ideas in the comment box below.