Imagine being able to set default values for cells in Google Sheets without using any code with Google Apps Script. It would save you a lot of time and effort, right? Well, good news: it’s possible!
Setting Default Cell Values
The key to making this technique work is to use array literals to create a formula that propagates to the adjacent cell. It might sound like an abstract concept, but we’ll show you a concrete example.
In a blank sheet, write the value “Input” in cell A1. In cell B1, type this formula:
=IF(ISBLANK(C1),{"Input",100},"Input")Your sheet will look like this:
Try typing 200 in cell C1, over the value of 100.
Cell C1 will display 200, but cell B1 will now show a #REF! error.
Now, delete the value you just typed in cell C1. The error message disappears and the default value of 100 is displayed again.
Finally, hide column B so that the #REF! error is never visible, and you’ll have a default value of 100 set for cell C1.
🎩 Hats off to my friend Scott Ribble for showing me this ingenious solution.
Advanced Default Values without Hidden Column
However, the previous method has one drawback: it requires a hidden column.
But fear not, we can use a clever circular formula to overcome this.
In a new blank sheet, add this formula to cell A1:
=IF(ISBLANK(B1),{"Input",100},"Input")Initially, you might see this error message regarding a circular error (i.e., a formula referring to itself):
That’s a problem, but we solve it by enabling iterative calculations and limiting them to only one iteration in the menu:
- Click on “File.”
- Go to “Settings.”
- Navigate to “Calculation.”
- Enable iterative calculation and set the “Max number of iterations” to 1.
- Leave the threshold at 0.05 as it doesn’t apply in this case.
Now, you can enter any value in cell B1, and if you delete it, the default value of 100 will be displayed.
How Does It Work?
The IF function checks if cell B1 is empty.
If it is empty, it displays the array literal:
{"Input",100}which shows “Input” in cell A1 and the value 100 in cell B1.
However, if cell B1 already contains a value, the output of the IF function is simply the string “Input” in cell A1.
Note: Default values are not limited to numbers. They can be text, an image, or even another formula.
Example: Checkbox with Default Values
You can use default values to check or uncheck checkboxes. Here’s an interesting illustration of how to create a “Select All” checkbox in Google Sheets using default values.
Thanks to one of the readers in the comments below for sharing this solution.
Default Value Formulas to Mimic Radio Buttons
Another interesting use case for these default values is to mimic radio buttons using formulas and checkboxes.
Column A contains array literal formulas that ensure a user can only select one checkbox at a time.
Now that you know these tricks, you can save time and energy by setting default values for cells in Google Sheets. Enjoy!
For more tips and tutorials on Google Sheets and other topics related to online marketing, visit Crawlan.com.
