How to Set Default Cell Value in Google Sheets

In this article, I will be talking about how you can set the value for a cell as default in Google Sheets.

So, I am just going to get right to it!

Table of Contents

Occasionally, a reference cell might be blank and, we need to set a default value to that cell. In this situation, you can apply the following formula:

Reference Cell is Empty

If the cell you are using as a reference is empty, then you can use the following formulas that I am showing you below, to set a value as default for that cell.

=IF(B2=””,”Default Value”,B2)

=IF(ISBLANK(B2), “Default Value”,B2)

=Swtich(B2,””,”Default Value”,B2)

You can see that I have simply applied the features of the IF function to assign default values to cells which would be empty.

I can also follow a similar tactics and, apply the features of the Find and Replace function to set default values to empty reference cells.

Find and Replace Function:

Firstly, you need to select the cells (range of cells) which have the cells you want to give default values to
Find the menu at the top of your spreadsheet and, click on the second option which says “Edit”. From the drop-down menu, select the last option “Find and Replace”.
In the dialogue box, insert “^\s*$” in the field for “Find”.
Similarly, in the field for “Replace”, insert your preferred default value.
Click on the check box for “Match the case”
Moreover, click on the check box for “Search using regular expression”.
Then, click on the “Replace All” button.
Lastly, click on the “Done” button.

Breakdown of the Value given for Find

^ stands for the beginning of the string
The “$” is for the end of the string.
\s means any character which is a space, tab or newline. Basically, any whitespace character.
* means no more repetition.

Creating a Script in Google Sheets to Set Default Values

This is how you can build a script in your spreadsheet to set default values to cells:

Find to the “Extensions” option from the top menu bar and click on it.
From the drop-down menu, click on “Script Editor”.
On the project page, insert the following:

How to Set Default Cell Value in Google Sheets

Click on the “Run” icon
Afterward, return to your spreadsheet and, select the relevant cell to replace the default value based on condition.
Click on the “Default>Zero” from the menu; which is run from the recently created script and can be applied to your spreadsheet.
Now, all the reference cells which are empty, will display “0”. (In case you prefer a default value other than “0”, then you can replace the “0” in the last line with anything you want; for instance, “N/A” or “Blank”.