How to Set Default value for cell in Google Sheets ?


👤 Diwas Poudel    🕒 Oct 13 2021    📁 Fix    📜 0 comment

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:

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

or

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

or

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

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

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)