Google Sheet is a powerful tool for organizing large amounts of data in tabular format. Some of the tabular data can be empty and you are searching for the solutions to explicitly filled that empty field with some default value in Google Sheets then this article will guide you.
There are various cases where we set default values for cells. Some of them are :
1 Set a Default Value if the reference cell is Empty or Blank.
Suppose reference Cell is A1, Destination Cell is B2. You want to set the value as "#N/A" if Reference Cell A1 is empty.
Formula Look like this:
2 Set a Default Value if the reference cell using Find and Replace Feature
Using Find and Replace features of a google sheet we can set a default value.
First, select the column(s) whose you want to set a default value.
Goto toolbar and then select Format > Number > Plain Text
Again Goto toolbar and then select Edit > Find and replace
In Find field, type: ^\s*$ and in replace field, type desire default value. In my case, I am using 0.
^indicates from the beginning of the string.
$indicates the end of the string.
\sindicates any whitespace character. This includes spaces, tabs, and newlines.
*is the zero-or-more repetition of.
As a whole, ^\s*$ pattern indicates "match any sequence of non-whitespace characters, including the empty string"
Check the box "Match the case" and "Search Using the regular expression"
Click on Replace All button
Click on the Done button
3 Set a Default Value if the reference cell using Google App Script
Using Google App Script we can create a set default value. Here, for example, we have set 0 default values if the cell value is blank.
Google App Script look like this:
When you execute this script. Then Just select all the cells to whom you want to set a default value. Then click on Default > Zero in the toolbar. Now the default value(0) is set to the blank field.
The main logic is located in green color. If you want to set '#N/A' instead of 0 then just replace the green line with the below code.