How to Define Variables in Google Sheets [Complete Guide]

Hi, in this article, we will learn how to define variables in Google Sheets. Google Sheets comes with plenty of customization options to format your sheet in your preferred manner. Similarly, you can create your variable in Google Sheets for referencing any cell.

If you are not aware of the variables in Google Sheets, then here is the complete guide on how to define variables in Google Sheets.

Why do we use Variables in Google Sheets?

As we all know, in Google Sheets every cell has a specific name or range that we use in formulas to reference them. Variables are the substitute for this cell reference name. You can create any name for your cell and then use it in the formulas.

Variables are mostly useful when working on a huge project containing a large amount of data with complex calculations. It becomes difficult to reference any cell with the cell name in these complex equations therefore we use variables instead of cell names for referencing. It helps the user to recognize any cell very easily.

Let me show you in the next section how to create and use variables in Google Sheets.

How to Define Variables in Google Sheets

In this tutorial on how to define variables in Google Sheets, we will teach you how to create a variable in Google Sheets then will tell you how to use them to define variables in Google Sheets with the help of a couple of examples.

Use Case #1

Step 1

Let’s suppose you have such type of data as described in the following example that you have two different values of working hours and rate per hour, and you have to find the net amount in the following cell.

Step 2

To find the total earnings amount, you will simply multiply both values by giving their cell address as I have given in the following screenshot.

Step 3

You will get your result when you press the Enter key after writing the formula as can be seen the result in the following picture.

Now, let me define how to use a variable in Google Sheets.

Step 4

First, place your cursor on the working hour’s value cell by clicking on it as I have placed in the following picture.

Step 5

When you click on the cell, if you keep an eye on the left corner of the rows and columns, you will see a small box with the cell address or cell name as directed in the following picture. If you click on it, it will turn into edit mode as you can see in the following picture.

Step 6

Now, you can give any name to this cell according to your desire as I have given “W_H” for the following cell as it represents working hours.

Step 7

After giving a name to working hours, similarly, place the cursor to the rate per hour value and change the name of the cell, as I have given “R.P.H” for the rate per hour value cell as shown in the following figure.

Step 8

The benefit of giving names to those cells is that you can use these names as a variable for the cell reference while applying a formula too. As you can see in the following picture, I have written “=W_H*R.P.H” instead of the cell address. Let’s see in the next step what we will get.

Step 9

The result is in front of you which is the same as we got with the help cell address that’s the role of variable in Google Sheets through which you can create your cell reference and can use it in formulas.

Use Case # 2

Step 1

In this example, we have a selling record for two different stores that sell cakes. Although the unit price of the cake is $5. Let’s find the total number of sales made by both stores with the help of variables.

Step 2

First, let’s change the name of the cells one by one with the variables. As you can see in the following screenshot, for the sales of Store 1, I am naming the cell “Store1“.

Step 3

Similarly, now put your cursor on the second cell, and let’s change its name to “Store2” as it represents the sales of Store 2 as directed below.

Step 4

Similarly, the last value is the unit price of the cake so after placing your cursor on its cell, change its cell name by “UP” as can be seen in the following picture.

Step 5

Now, to find the total amount, I am applying the following formula, first I will sum both stores’ sales then multiply it by the unit price of the cake, and I will use the variables instead of cell names in my syntax as you can see in the following picture.

Step 6

You can see the result in the following screenshot, in this way too, you can perform any calculations with the help of variables where you don’t want to give the cell address or when it is difficult to use cell range then make a variable for the specified cell and use it to your formulas in Google sheets.

Frequently Asked Questions

Can I Use Defined Variables in Calculated Fields in Google Sheets?

How to make changes with variables in Google Sheets?

As we have learned in the above tutorial on how to define variables in Google Sheets, to create a variable in Google Sheets, we need to create a cell name and need to give a cell reference. Later, you might need to change these names and cell paths. If you have committed a mistake with creating a variable and want to make changes to your variables, then follow the following instructions that may be helpful to you.

Step 1

If you want to make changes to your variables in Google Sheets, then look at this small arrow along the name box. When you click on this small arrow, a small drop-down menu will open where you will see the “Manage named ranges” option.

Step 2

When you open the “Manage named ranges” option, you will see all your variables in the right panel as can be seen in the following picture. If you want to edit any of them, click on it, and a pencil icon will appear through which you can Edit your variables in Google Sheets and make changes.

Step 3

If you want to change the name of your variable in Google Sheets, then you can change it with the following highlighted name box.

Step 4

If you want to change the cell address for your variable, then you can change it from the following highlighted option.

Step 5

Once you have completed making changes to your variables you will have to click on the “Done” button to save these changes.

How to remove temporary variables from Google Sheets?

Creating a variable in Google Sheets can be very helpful in some cases, but after completing the task, it might be useless, or you may need its original form. If so, then don’t worry because once you have created a variable in Google Sheets, Google Sheets allows you to edit and remove it from your Google Sheets. Below are the steps to remove a variable in Google Sheets.

Step 1

As you can see in the following picture, all your variables are listed below, Select the variable that you want to remove, and when you click on it, a small edit icon will appear as directed in the following picture. Click on it to remove this variable.

Step 2

When you open your variable in the Edit mode, you will see a “Bin” button along the name box as highlighted in the following picture.

Step 3

When you click on the “Bin” button, a small pop-up will appear in front of you for the confirmation of removing the variable. If you are sure to remove this variable, then click on the “Remove” button.

Conclusion

I hope that now you have understood the variables in Google Sheets with the help of the above article on how to define variables in Google Sheets.