How to Set Default value for cell in Google Sheets ?


👤 Diwas Poudel    🕒 Nov 6 2021    📁 Fix    📜 0 comment

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.

x
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)

or

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

or

=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

format-number-plain-text-googlesheet

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.