How to create & edit Google Sheets formulas: key functions and examples

How to reference other cells in Google Sheets formulas

When creating formulas in Google Sheets, it's often necessary to reference data from other cells. This is what cell references are for:

• Single-cell reference. Refer to individual cells like E2 to use its value in your calculations.
• Referencing multiple cells. In formulas like =SUM(E2,E4,E8,E13) or =SUM(E2:E13), you're using either non-adjacent or adjacent cells.
• Use named ranges. You can even assign a name to a cell range, like Sales2024, and use it in formulas like =SUM(Sales2024). You will learn more about named ranges in this article of ours.

How to reference other sheets in Google Sheets formulas

Google Sheets formulas can calculate data not only from the same sheet they are written on but also from other sheets. This is particularly useful when working with large datasets spread across multiple tabs.

This is how you reference data from another sheet:

1. Begin your formula as usual by typing =SUM(
2. Then navigate to the other sheet by clicking its tab.
3. Select the cell or range you want to reference.

   The formula will automatically update to =SUM(Sheet2!E2:E13), where Sheet2 is the name of the sheet.

4. Hit Enter to confirm.

How to navigate to a certain cell reference

If you want to change just one of the existing cell references, enter the cell and go to the required reference using either your mouse or arrows on your keyboard.

As you navigate the formula, Google Sheets will help you by highlighting both the referred cell & the function argument you're currently on:

Example 1. Using direct values

You can enter values directly into a formula as arguments:

=SUM(40,50,55,20,10,88)

Example 2. Using cell references

Arguments can also be cell references or ranges:

=SUM(E5,E8,E13)
=SUM(E2:E13)

Example 3. Using nested functions

When the values you need to calculate depend on other Google Sheets formulas, you can simplify things by using nested functions. Thus, instead of creating two separate formulas in different cells, you will efficiently combine them into one formula.

Let me show you. For example, in cell D15, you could calculate the average sale amount with

=AVERAGE(E2:E13)

And in D16, you could round the result with

=ROUND(D15, -1)

But instead, with a nested function, you can perform both calculations in one formula:

=ROUND(AVERAGE(E2:E13),-1)

Here, the AVERAGE function is used as an argument in the ROUND function. This Google Sheets formula calculates average sale amount first, then rounds the result:

This approach makes your calculations more compact (since you don't need to use two cells) and reduces the complexity of your spreadsheet.

How to copy formulas in Google Sheets to adjacent cells

One of the most common tasks is copying formulas to adjacent cells. 

Google Sheets makes this easy by suggesting you auto-fill the whole column once you enter one formula:

Google Sheets will automatically adjust all cell references based on their new rows so that your calculations apply to the corresponding data.

But if you reject this suggestion, you may use the drag-and-drop functionality later:

1. Select the cell with the formula you want to copy.
2. Hover your mouse over the small blue circle in the bottom-right corner of the cell (this is called the fill handle).
3. Click and drag the fill handle over the adjacent cells where you want to copy the formula:

How to copy formulas in Google Sheets to non-adjacent cells

If you need to copy a formula to non-adjacent cells or even across sheets, follow these steps:

1. Select the cell containing the formula you want to copy.
2. Press Ctrl+C (or Cmd+C on Mac) to copy the formula.
3. Select the cells where you want to paste the formula (you can use Ctrl/Command and click to select multiple non-adjacent cells).
4. Press Ctrl+V (or Cmd+V on Mac) to paste the formula into the selected cells.

This way is ideal to copy formulas across multiple non-adjacent cells or even other Google sheets.

Advanced Find & Replace: view all Google Sheets formulas

With Advanced Find and Replace, you gain a lot more control over your data, especially when it comes to Google Sheets formulas. This extension lets you search, navigate, and export formulas across your entire Google Sheets file.



This is what you can do with the add-on:

• Search for all formulas. Whether in the current or across all sheets, the tool will easily locate all or specific formulas. You can search for function names, specific ranges, and parts of your formulas, or simply find all formulas whatsoever:
• Navigate through found formulas. Once the tool locates the formulas, you will have their complete list in a tree view. Use it to jump directly to their cells, saving you from scrolling through long sheets:
• Export a list of formulas and their locations. For easier analysis or documentation purposes, you can export the list of found formulas along with their exact cell locations to another sheet or a Google Sheets file:

All this makes Advanced Find & Replace perfect if you work with large datasets and need more flexibility when reviewing and managing complex formulas.

1. SUM — adding values

The SUM function is one of the most basic but useful functions in Google Sheets. It adds together the values in a specified range.

I already used it earlier to total all sales:

=SUM(E2:E13)

You can also add numbers based on one or several conditions, for example, calculate the total value of products costing over $100. The syntax of such formulas is a bit more complex, so jump to this article if you want to learn all about it: SUMIF in Google Sheets with formula examples

2. AVERAGE — calculating the mean

The AVERAGE function calculates the average of a set of numbers.

I also already used it earlier to calculate the average sale amount:

=AVERAGE(E2:E13)

3. COUNT & COUNTA — counting cells

COUNT counts how many cells in a range contain numbers. COUNTA counts all non-blank cells:

=COUNT(E2:E12)
=COUNTA(E2:E12)

There's also COUNTIF function that counts the number of cells that meet a certain condition, like all cells greater than 50 or those containing a specific word or phrase. The function is a bit more complicated so feel free to learn about it in this blog post: Google Spreadsheet COUNTIF function with formula examples

4. IF, AND, OR — conditional logic

The IF function helps you create logical tests and return values based on whether a condition is true or false:

=IF(D2>100,"Above expected","Below expected")

This formula checks if the value in D2 is greater than 100. If it is, the result will be Above expected, otherwise, Below expected:

You can combine it with AND or OR, and even nest multiple IFs for more complex conditions, like this:

=IF(AND(D2>100,E2>200),"Above expected","Below expected")

To learn more about this, visit this article: Google Sheets IF function — usage and formula examples

5. CONCATENATE — joining text

When you want to join text from multiple cells, CONCATENATE comes in handy:

=CONCATENATE(B2,": ",C2)

This formula will join values from B2 and C2, with a colon and a space in between:

You can also use the & operator for a simpler approach. You will find this in the following article: Google Sheets CONCATENATE function to combine text from cells

6. VLOOKUP — searching for data

The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from another column.

=VLOOKUP(B16,B1:E13,4,false)

This formula looks for Violet in column B and returns the corresponding value from column E:

This function, however, has its limits & requires a learning curve. So here's a complete guide for you to get used to: Google Sheets VLOOKUP with examples

7. Unique Google Sheets functions

Google Sheets offers several unique functions that you won't find in Excel. They include: 

• ARRAYFORMULA: apply a formula to an entire range of cells at once.
• IMPORTRANGE: import data from another Google Sheets file.
• QUERY: perform SQL-like queries a.k.a. an advanced filter on your data.

These functions are widely used for many spreadsheet tasks, and we've covered them in detail in another article: Must-know Google Sheets functions not available in Excel