Google Sheets is a powerful tool for tabularly organizing large amounts of data. Some tabular data may be empty, and if you're looking for a way to explicitly fill that empty field with a default value in Google Sheets, this article will help.
We set default values for cells in a variety of situations. Some of them are :
1 Set a Default Value if the reference cell is Empty or Blank.
Suppose reference Cell is B2, Destination Cell is C2. You want to set the value as "#N/A" or "0" if Reference Cell B2 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
If you don't want to treat numbers as text, you can skip steps 1 and 2. I did it for the sake of simplicity and safety. In addition, when a number is formatted as text, it is left-aligned in the cell rather than right-aligned.
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.
How to Create Script in Google Sheet to set Default Value ?
- Goto Tools > Script Editor
There paste above mention script. As shown in the below screenshot.
- Then click on the Edit icon and then Run icon
- Then come back to your google sheet. O And select your desired cell to replace default value based on condition.
- Then click on Default > Zero from the menu. This is run the currently created script and apply to the google sheet.