How To Reset A Drop List To Default Values In Worksheet Excel?

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 make full that empty field with a default value in Google Sheets, this article volition assistance.

We set default values for cells in a variety of situations. Some of them are :

i Set a Default Value if the reference cell is Empty or Blank.

Suppose reference Prison cell is B2, Destination Jail cell is C2. You desire to ready 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)

two Set a Default Value if the reference cell using Observe and Supercede Feature

Using Find and Supercede features of a google canvass we can set up a default value.

Step:

one First, select the column(s) whose y'all want to set a default value.

ii Goto toolbar and and then select Format > Number > Plainly Text

If you don't desire to treat numbers as text, you can skip steps 1 and ii. I did it for the sake of simplicity and safety. In addition, when a number is formatted as text, information technology is left-aligned in the cell rather than correct-aligned.

3 Again Goto toolbar so select Edit > Find and replace

4 In Find field, type: ^\due south*$ and in replace field, blazon want default value. In my case, I am using 0.

Explanation: ^\s*$

• ^ indicates from the beginning of the string.
• $ indicates the stop of the cord.
• \s indicates any whitespace character. This includes spaces, tabs, and newlines.
• * is the zero-or-more repetition of.

As a whole, ^\southward*$ pattern indicates "match whatever sequence of non-whitespace characters, including the empty cord"

five Cheque the box "Lucifer the case" and "Search Using the regular expression"

6 Click on Supplant All button

vii Click on the Done button

3 Set a Default Value if the reference prison cell using Google App Script

Using Google App Script we tin create a set default value. Here, for example, we have set 0 default values if the cell value is blank.

Google App Script look similar this:

  const onOpen = () => {

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

    .addItem("Nothing", "zeroSelection")

  .addToUi()

}

const zeroSelection = () => {

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

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

}

When y'all execute this script. And then Merely select all the cells to whom y'all want to gear up a default value. And so click on Default > Zero in the toolbar. At present the default value(0) is prepare to the bare field.

The chief logic is located in green color. If y'all desire to prepare '#N/A' instead of 0 then just supervene upon the greenish 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.

• Then click on the Edit icon and and then Run icon
• And then come up dorsum to your google sheet. O And select your desired jail cell to supersede default value based on condition.
• And so click on Default > Zero from the menu. This is run the currently created script and use to the google canvas.

Source: https://ourtechroom.com/fix/set-default-value-for-cell-googlesheets/

Posted by: hickmanwithilt1989.blogspot.com

Share this post