Learning to use Google Sheets formulas will make your everyday tasks at the office much easier. Formulas allow users to manipulate data to get results quickly and accurately.
From simple addition and subtraction to extracting stock prices, formulas can do pretty much everything.
While there are many inbuilt formulas available for use within Google Sheets (SUM, VLOOKUP, COUNTIF, etc.), learning how to create your own formulas helps a lot in carrying out daily tasks.
To create your own formula, you first need to understand its structure.
In this article, we’re going to cover the structure of a formula, how to create your own formula, and use it.
What’s a formula?
A formula is an equation that can take multiple inputs and manipulate them according to the given instructions. The instructions can contain different forms of mathematical operations including addition, subtraction, multiplication, division, and more complex calculations as well.
A very basic formula can be the addition of two cells: =A1+A2.
Formulas are written in the cells where we want the result to appear. If we want to see the result of our formula in cell A3, we’ll write it in that cell.
Over here, there are two inputs, A1 and A2. The instruction is to add the contents of both the cells and produce a result. The equals sign (=) exists before the start of every formula to indicate that the cell is equal to the following expression.
Formulas can also contain different combinations of cell references and numbers. That means we can aslo write formulas like A1+10*20.
In the above example, we’ve used two cell references and the addition operator.
Let’s look at both of them in greater detail.
Cell reference explained
Cell referencing means to go to the address of a cell, then access its contents directly. When we reference a cell, we tell the spreadsheet to go to that specific cell and pick up its contents.
In the above example, we gave the instruction to pick up the contents of cell A1 and add them with the contents of cell A2.
The benefits of using cell references are as follows:
- You type in the value once and just reference the cell everytime you have to use it. This means that you don’t have to manually enter a long, complicated value again and again. This saves you time and eliminates the chance of entering a wrong value.
- If you change the contents of the refrenced cell, the change will apply everywhere you’ve used the reference. Let’s say you have a referenced cell in a 100 different places in the spreadsheet. Once you make the change, it will be applied in all those 100 different places automatically.
Operators are symbols that tell the spreadsheet what needs to be done to the inputs. We’re going to look at arithmetic operators here. To learn more about the other operators, read this amazing guide.
Arithmetic operators are used for mathematical calculations and include Addition (+), Subtraction (=), Division (/), Multiplication (*), and Exponents(^).
The order of operations will follow the BEDMAS rule.
Now that we’ve covered the structure, let’s create a formula step by step with pictures to follow.
How to create formula in Google Sheets
Open a blank spreadsheet and fill some cells with dummy data like below.
- Select a cell to enter the formula
- Start the formula with an equals sign (=)
- Type the address of the first cell
- Add any operator
- Type the address of the second cell
- Hit enter
You’ll now see a total in the cell you wrote the formula in.
To edit the formula, click the cell with the result and make your changes in the formula bar.
If you want to remove the formula, click the cell again and erase the text in the formula bar with the backspace key.
To practice, make your own variants and use multiple operators to learn the BEDMAS rule. In another cell, type the following formula: =A1*A2-A3/A4^A5 and see if you can understand how the result was calculated.
Create formula through point-and-click
You can also create formulas by clicking cells. But first, you have to manually type the equals sign. Then, click the first cell you want to reference and type an operator. Click the second cell you want to reference and press enter.