What are the most useful spread­sheet formulas in Google Sheets?

With spread­sheet formulas, Google Sheets lets you perform simple arith­met­ic tasks and display the results. Formulas always begin with an equal sign and can also contain functions.

What are spread­sheet formulas in Google Sheets?

Formulas in Google Sheets make it possible to go beyond just dis­play­ing and storing data in spread­sheets. With spread­sheet formulas, you can get more out of your data and even carry out extensive arith­met­ic op­er­a­tions. This means that in­di­vidu­al values do not remain static, but can, for example, be compared, added or sub­trac­ted within the spread­sheet itself.

What is the dif­fer­ence between formulas and functions?

The terms functions and formulas are often used in­ter­change­ably in Google Sheets, but they aren’t actually the same. While both serve similar purposes and share many sim­il­ar­it­ies, they differ in certain aspects. Although both are used to connect cells and the values they contain with other cells and values, a function is a pre-defined operation that contains one or more formulas. While functions are often the more user-friendly method, formulas may be the better choice for simple and targeted cal­cu­la­tions. While formulas can also contain functions, functions often go far beyond simple arith­met­ic tasks.

How are Google spread­sheet formulas struc­tured?

Formulas in Google Sheets spread­sheets are not always struc­tured in the same way. However, there are three things that all formulas need to have:

• Each formula must begin with an equal sign.
• Each formula has to include cell ref­er­ences. These are used to specify which cells the program should include in its cal­cu­la­tion. Each cell is unique, and the cell reference is created by combining the column (rep­res­en­ted by a letter) and row (rep­res­en­ted by a number). You can reference multiple cells in a formula.
• Every formula needs operators to indicate which cal­cu­la­tion the formula should perform.

Formulas in Google Sheets can also contain functions and fixed values.

How to use spread­sheet formulas in Google Sheets

If you want to use formulas in a Google spread­sheet, follow the steps below:

1. Open your spread­sheet.
2. Click an empty cell.
3. Enter the equal sign (=).
4. Enter the first cell reference.
5. Enter the operator.
6. Enter the second cell reference.
7. You can add ad­di­tion­al operators and cell ref­er­ences as necessary. The outcome of the cal­cu­la­tion will appear directly above the cell. Press [Enter] to insert the cal­cu­lated result into the cell.

Al­tern­at­ively, you can use the formula bar located above the columns to write your formula. The cells in the formula will be high­lighted re­gard­less of the method you choose.

How formulas work in Google Sheets

The best way to il­lus­trate how formulas in Google Sheets work is to show a few simple examples. In the examples below, we have a spread­sheet con­tain­ing sales data, product numbers and the price of the products.

Example using addition and sub­trac­tion

In the first example, we’re going to add the values in cells C4, C9 and C11 together. To do this, follow the steps here:

1. Click any empty cell. In our example, we’ve selected C19.
2. Start with the equal sign, followed by C4, a plus sign, C9, another plus sign and finally C11.
3. Confirm your entry with [Enter].
4. The total sum of the three values should now be displayed in cell C19. The result is £206.54.

If you want to subtract values from each other, you can use a similar structure and simply exchange the operator. In this example, we want to calculate the dif­fer­ence between C4 and C10. When we press [Enter], we get £67.49.

Example using mul­ti­plic­a­tion

If you want to perform mul­ti­plic­a­tion or division, the process is similar. To il­lus­trate this, we’ve expanded our example table to include the number of units sold. To calculate the amount of sales made with product 331-994 on January 4, 2024, follow the steps below:

1. Click cell E11.
2. Enter the equal sign.
3. Type the following formula after the equal sign: C11*D11. Press [Enter].
4. You’ll now see the result (£875.00) directly in the cell.

How to use functions in a Google Sheets spread­sheet

Since functions often offer sig­ni­fic­antly more options, it can be useful to integrate them into formulas you create. In the next example, we want to determine how many products with the serial number 331-773 were sold during a specific period of time. To do this, we can use the SUM function. Here’s how:

1. Click in cell D17.
2. Enter an equal sign to start.
3. Insert the SUM function and add an open bracket behind it.
4. Enter the cell ref­er­ences D2, D6, D10, D11 and D14 and separate them with a plus sign. You can also use a comma to separate the cell ref­er­ences.
5. Close the bracket.
6. Press [Enter]. The result should be 53.

How to display formulas after de­term­in­ing results

Normally, after you use a formula, only the result is displayed. However, if you want to un­der­stand how a certain value was obtained, you can have the formula displayed af­ter­wards instead. Here’s how:

1. Click on View at the top of the screen.
2. Now select Show next to the eye icon. It’s the first item in the drop-down menu that appears.
3. Place a check in front of Formulas. You can, of course, change this back at any time.

Overview of the most important formulas for Google Sheets

Here’s a list of the most useful spread­sheet formulas for Google Sheets: