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.

How to set default value in Google Sheet

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.