Google Sheets Variables in a Formula (3 ways)

If you need variables in your google sheet, you have 3 options. The best option is to create a named range in your sheet that refers to a cell containing your variable. If you don’t want to store the variable in the sheet, you can use a named function or an Apps Script function. Which option you choose depends on how easily you need to access your variable, and if you need to update it.

Using Named Ranges for sheet variables

The easiest and most user friendly option for simple variables within a google sheet is to use a Named Range. A named range will simply give a cell (or a range of cells) within your sheet a friendly name (of your choosing). You can then use this name within any other formula calculations in the sheet. Updating the value of the named cell will update the value used in all other formulas and calculations in that sheet.

This is the best choice if your sheet variables are likely to remain as simple numbers or text and not evolve into something more complex, like a function.

Named ranges are available on Windows, ChromeOS, Mac etc, however you can only access them and not create new ranges on Android. And the feature is unavailable on iPad and iPhone.

Here’s how to create a named range for a single cell that acts like a variable.

1. Pick a cell to store your variable. I like to add a new sheet tab called ‘Variables’ to keep them separate.
2. Select the cell that will hold your variable value.
3. Select Data > Named Ranges from the menu.
4. The Named Ranges toolbar should appear on the right of your screen.
5. Click ‘+ Add a range‘.
6. Enter a name for your variable (names have a few rules, but in brief, they can’t contain spaces or punctuation)
7. Click Done.

Once you’ve entered your named range, you can refer to it in cell formulas by simply entering the name you chose. For example, in the cell below, we want our result to be our variable name. If we type the first few letters of our variable name (MyVariable1) it appears in the selection list. To see this in action, check out our sheet variables example in Google Sheets.

Using Named Functions for sheet variables

As well as named ranges, Google Sheets has recently added a feature that allows you to define your own functions, called Named functions. You can use a named function to return a simple value. This then acts like a variable. Whilst they are a little more complex to create, named functions allow you to add a description. Also, a function can perform more complex tasks than a simple named range.

To create a named function that returns a variable:

1. Select Data > Named functions
2. The Named Ranges toolbar should appear on the right of your screen.
3. Enter a name for your function, such as MyFunction1
4. Enter a description for your function (we used ‘returns 100’ as that is what our function will do).
5. Skip the argument placeholders.
6. Enter equals followed by the variable value you would like returned, i.e. =100
7. Click ‘Next‘
8. Click ‘Create‘ if everything looks good in the preview window

You can then use your function in any formula, just like any of the in-built sheets functions. You can see the benefit of using a function to store your variable when you go to use it. The drop-down selection list within sheets contains your function description. Handy, if you have a lot of variables in your sheet.

Use Apps Script to create a custom function which returns a variable

You can use Apps Script to write simple functions that return a value. The benefit of this approach is that Apps Script functions allow you to unlock the full power of JavaScript in your Google Sheets. This approach is the most complex, but offers the most versatility.

To use Apps Script to write a simple function to return a variable value, first click Extensions > Apps Script in the menu.

This will open a new Apps Script window, inside the editor window that opens paste the following:

/* this function returns 1 */
function get_var1() {
  return 1;
}

This is a simple function with a comment at the top. get_var1() will return the value 1. You can see it in use below and in our example sheet.

Using Apps Script ‘Sheet Properties’

In our testing, we also investigated the use of ‘Script Properties‘. In the Apps Script editor, under Settings, you have the option to associate named properties with a script, a user of a script or a document. You can then set and update these variables within Apps Script code. We tried using Apps Script to create functions to retrieve and set the value of a user level property. Whilst this worked, the issue arose that immediate cells containing functions to retrieve these values would only refresh after being deleted and re-created.

You should only use Script properties in Apps Script functions that trigger based on sheet actions or schedules. Their purpose is to store the state of execution of an Apps Script program, not to act as data within a sheet. There are better ways to simulate sheet variables mentioned above!