Variables in google sheets

Are you looking to add a variable to a formula in a cell in google sheets?

You can add variables in google sheets using a custom formula in the app script. 

Use cells as variables in google sheets

If you want to just reuse a few values in different calculations like filtering, searching, etc, then you can define them in a separate cell in google sheets instead of variables.

For example, you have a lot of data in many sheets. You want to search for a list of data if they exist in those sheets.

To do this, you can write these lists of values in a separate table and use the vLookup to search for these values in every sheet. You can change the table cells on the fly and the result will update automatically. 

So you can use cells as variables in google sheets.

Use custom formula as variable in google sheets 

Here is an example:

Let’s say I want to have the below formula in google sheets.

=if(x < 0, x+1, x)

Here x is a custom function. I want to pass row number to x and it should do the calculation.

To do this, first, create a custom formula in the app script and then use that here.

1. Click on Extensions > App script to open the app script editor

2. Copy and paste the below formula

function x(row_number) {
 
 return SpreadsheetApp
          .getActiveSheet()
          .getRange("Sheet1!A"+row_number) // Get Rows from column A in sheet1
          .getValue();
}

3. Click on the save button to save the code

4. Now go to your sheet and in a cell type

 
=if(x(1) < 0, x(1)+1, x(1))

5. Press enter to execute the custom formula. 

Here is a demo:

This is how you will define custom functions and use them as variables in google sheets. 

Note:- Google sheets do not support adding named variables in a formula. You can only use a cell or range as a reference in a formula.

Wrapping up

We discussed how to use variables in google sheets. If it’s a simple calculation like search and filter etc, you can use cells as variables. For complex ones, you can use an app script to define custom formulas and then use them as variables in your function.

Appendix

[1] App script in google sheets – Link

Further Reading

New to google sheets ? Start here