Google Sheets: Creating Simple Formulas

Google Sheets is a powerful spreadsheet tool that allows you to create and edit sheets with ease. It also boasts real-time collaboration features, making it perfect for teamwork. But where Sheets truly shines is in its formula capabilities. Formulas can automate calculations, saving you time and effort. This article will teach you how to create basic formulas in Google Sheets, including those for addition, subtraction, multiplication, average, and count.

What is Cell Referencing in Google Sheets

Cell referencing in Google Sheets is a way to refer to the data in a particular cell or a range of cells in formulas. There are two types of cell references: relative and absolute1.

Relative References

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B21.

Absolute References

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula. An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both. For example, =$A$1

Using Cell References in Google Sheets

When a formula includes a cell address, it’s employing a cell reference. Formulating with cell references allows for easy numerical value updates without rewriting.

How to Create Formulas in Google Sheets

Step 1: Select your Cell

Formulas always reside within cells. Choose the cell where you want the calculated result to appear.

Step 2: Put Equal Sign (=)

Every formula starts with an equals sign (=). This tells Google Sheets you’re about to craft a magical instruction.

Step 3: Referencing the Magic (Cell References)

Formulas often rely on values from other cells. Use cell references (e.g., A1, B2) to tell the formula where to find its ingredients.

Step 4: Mathematical Mastery (Basic Operators)

Google Sheets supports all the basic mathematical operators you know and love:

+ (addition)

– (subtraction)

* (multiplication)

/ (division)

Example: In cell C1, type =A1 + B1 to add the values from cells A1 and B1.

Adding the Cells

Step 1: Select the Cells

If you want to calculate the addition of some particular numbers, then first you need to select the cell that will show you the output and then enter the formula. The alternate method for adding the number within the cell address is to use the formula (=SUM R1+C1), where R1 and C1 are the rows and columns.

Select the Cell

Step 2: Start with “=” and Type Cell Address

After selecting the particular cell that will hold the output type the formula starting with the “=” and enter the cell addresses according to the rows and columns, remember to enter the operator that you want the number to perform.

Enter the = sign >> Enter Cell References

Subtracting the Cells

Step 1: Select the Output Cell

Select the cell in which you want to make the output of the values that are subtracting.

Select the Cell

Step 2: Type “=” and Enter Operator (-) and Enter Cell Address

For subtracting the values using cell reference enter the subtracting formula in the cell, start with “=” and enter cell address. Also, enter the “-” operator between the cell address.

Type ‘=’ >> Enter Cell Reference

Multiplying the cells

Step 1: Select the Cell

Select the cell which will reflect the output of the multiplied values.

Select the Cell

Step 2: Enter “=” and Enter Operator (*) and Cell Address

As always start with the “=’ sign and enter the operator “*” between the cell references.

Enter the ‘*’ operator >> Enter Cell Reference

How to create a formula using the point-and-click method

Step 1: Open Google Sheets

Go to Google Sheets and select a sheet in which you want to perform calculations.

Open Google Sheets >> Select a Sheet

Step 2: Select the Output Cell and Type “=”

After opening the sheet in which you want to perform the calculations select the cell which will contain the output of the reference cells. Now in the selected cell type the “=” sign as you are going to perform the calculations.

Select the Output Cell >> Type “=”

Step 3: Click on the Reference Cell, Type Operator and Click second reference cell

Now take your pointer to the cell that you want to refer to and click on it, then enter the operator you want to perform and choose the second reference cell. Your cells will be calculated.

Click on first reference cell >> Enter Operator >> Click on second Reference cell

How to Find the Average of Numbers in Google Sheets

Step 1: Open Google Sheets and Select a Document

Go to Google Sheets and select a sheet in which you want to perform calculations.

Open Google Sheets >> Select a Sheet

Step 2: Select the Cell and Type ‘=AVERAGE’

To find the average between two cell numbers select an output cell and type “=AVERAGE” in it.

Select the Cell >> Type ‘=AVERAGE’

Step 3: Give the Cell Reference

Now give the cell reference to the AVERAGE function to calculate the average of the numbers.

For example: ‘AVERAGE (B1:B6)’

Give Cell Reference

How to use COUNT Function in Google Sheets

Step 1: Open Google Sheets and Select a Document

Go to Google Sheets and select a sheet in which you want to perform calculations.

Open Google Sheets >> Select a Document

Step 2: Select a Cell and Type ‘=COUNT (range)’

The COUNT function is used to count the number of cells in the range passed in the function. So, to use it select a cell and type the function ‘COUNT’ along the range passing in it.

For example: ‘=COUNT (A2:A4)’.

Type ‘=COUNT (RANGE)’

Conclusion

Creating simple formulas in Google Sheets helps you in your lengthy calculations and makes it easier. You can simply create your formulas in Google Sheets by starting with the ‘=’ sign as it indicates that you want to create a formula. There are many formulas used in Google Sheets and some of the basic formulas of Google Sheets are AVERAGE, SUM, COUNT, etc.

Google Sheets Formulas – FAQs

How can I find the minimum value in the range in Google Sheets?

You can use the ‘MIN’ function for finding the minimum value in range in Google Sheets.

How can I find the maximum value in the range in Google Sheets?

To find the maximum value in range you can use the ‘MAX’ function in Google Sheets.

How can join text in Google Sheets?

You can use the ‘CONCATENATE’ function to join text in Google Sheets.

Can I automate formulas in Google Sheets?

Yes, you can automate formulas in Google Sheets with macros or Apps Script.