How to Set Default value for cell in Google Sheets ?

👤 Diwas Poudel 🕒 06 Nov 2021 📁 FIX

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:

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

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

=Switch(B2,"",”Default Value”,B2)

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.

Step:

1 First, select the column(s) whose you want to set a default value.

2 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.

3 Again Goto toolbar and then select Edit > Find and replace

4 In Find field, type: ^\s*$ and in replace field, type desire default value. In my case, I am using 0.

Explanation: ^\s*$

^ indicates from the beginning of the string.
$ indicates the end of the string.
\s indicates 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"

5 Check the box "Match the case" and "Search Using the regular expression"

6 Click on Replace All button

7 Click on the Done button

find-replace-google-sheet

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:

const onOpen = () => {

SpreadsheetApp.getUi().createMenu("Default")

.addItem("Zero", "zeroSelection")

.addToUi()

}

const zeroSelection = () => {

var range = SpreadsheetApp.getActiveSheet().getActiveRange();

range.setValues(range.getValues().map(row => row.map(cell => !cell ? 0 : cell)))

}

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.

cell => !cell ? '#N/A' : cell)

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.

appscript-editor

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.