In this article, I will be talking about how you can set the value for a cell as default in Google Sheets.
So, I am just going to get right to it!
Occasionally, a reference cell might be blank and, we need to set a default value to that cell. In this situation, you can apply the following formula:
Reference Cell is Empty
If the cell you are using as a reference is empty, then you can use the following formulas that I am showing you below, to set a value as default for that cell.
=IF(ISBLANK(B2), “Default Value”,B2)
You can see that I have simply applied the features of the IF function to assign default values to cells which would be empty.
I can also follow a similar tactics and, apply the features of the Find and Replace function to set default values to empty reference cells.
Find and Replace Function:
- Firstly, you need to select the cells (range of cells) which have the cells you want to give default values to
- Find the menu at the top of your spreadsheet and, click on the second option which says “Edit”. From the drop-down menu, select the last option “Find and Replace”.
- In the dialogue box, insert “^\s*$” in the field for “Find”.
- Similarly, in the field for “Replace”, insert your preferred default value.
- Click on the check box for “Match the case”
- Moreover, click on the check box for “Search using regular expression”.
- Then, click on the “Replace All” button.
- Lastly, click on the “Done” button.
Breakdown of the Value given for Find
- ^ stands for the beginning of the string
- The “$” is for the end of the string.
- \s means any character which is a space, tab or newline. Basically, any whitespace character.
- * means no more repetition.
Creating a Script in Google Sheets to Set Default Values
This is how you can build a script in your spreadsheet to set default values to cells:
- Find to the “Extensions” option from the top menu bar and click on it.
- From the drop-down menu, click on “Script Editor”.
- On the project page, insert the following:
- Click on the “Run” icon
- Afterward, return to your spreadsheet and, select the relevant cell to replace the default value based on condition.
- Click on the “Default>Zero” from the menu; which is run from the recently created script and can be applied to your spreadsheet.
- Now, all the reference cells which are empty, will display “0”. (In case you prefer a default value other than “0”, then you can replace the “0” in the last line with anything you want; for instance, “N/A” or “Blank”.
I wrap it up here and hope my article on how you can set default cell values has been helpful for you. Best of luck!
Read More Tutorial Articles On: