*Read this article to learn how to create and edit formulas in Google Sheets. By the end, you'll understand how they work, and gain a solid knowledge of key Google Sheets functions like SUM, IF, VLOOKUP, and more.*

Formulas are the backbone of any spreadsheet. You need them to calculate & analyze your data, and even automate some tasks. Let me introduce you to a world of formulas and functions. I'll start with the basics and gradually move toward essential functions every Google Sheets user should know. Feel free to make a copy of the practice spreadsheet to follow the article along.

## How to create formulas in Google Sheets

Google Sheets formulas are a combination of operators and functions that perform calculations. Every formula starts with an *=* sign, followed by a function like *SUM*, *AVERAGE*, or *IF*.

Here’s how you create formulas in Google Sheets:

- Click the cell where you want to display the result.
- Type =, followed by the function name. For example, SUM will add the values in the range.
Once you enter the function, you'll see its short description, required arguments and the purpose of each:

Tip. Google Sheets will suggest a list of all suitable functions starting with the letters you've typed. You can find a complete list of Google Sheets functions here.

- Continue by adding the formula contents: cell references or ranges. The easiest way to do that is to type them manually or select them with your mouse:
`=SUM(E2:E13)`

- Press Enter to calculate and display the result.

### 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.

Here’s how you reference data from another sheet:

- Begin your formula as usual by typing
`=SUM(`

- Then navigate to the other sheet by clicking its tab.
- 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. - Hit Enter to confirm.

Tip. You can also manually type the reference as `=Sheet_name!Cell_range`

`Sheet2!E2:E13`

Note. If your sheet name contains spaces, enclose it in single quotes:

`='October sales'!E2:E13`

## How to edit formulas in Google Sheets

So, your formula is created. In future, as your data changes, you may want to adjust those ranges or add more conditions. For example, change `=SUM(E2:E15)`

to `=SUM(E2,E4,E8,E13)`

to total only specific sales.

Here’s how you edit formulas in Google Sheets:

- Double-click the cell containing your formula, or select the cell and press F2 or Enter
- Modify the formula directly in the cell or in the formula bar above your sheet:
`=SUM(E2,E4,E8,E13)`

- Press Enter to apply the changes.

### 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:

## How to use nested functions in Google Sheets

As you've learnt so far, Google Sheets formulas use arguments to perform calculations.

But what you may not know is that those arguments can be not only cell references but also values or even other functions. Nesting functions within formulas can make your calculations more concise. Here's a breakdown of how this works.

### 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

Copying formulas in Google Sheets lets you quickly replicate calculations across different cells. This is useful when working with repetitive data structures, e.g. calculating a 5% tax from each sale.

But depending on how far your intended cells are away from the original cell with the formula (adjacent or non-adjacent), you will need to use different ways for the task.

### 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:

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

Tip. If you're copying formulas down a column for hundreds or thousands of rows, simply double-click the fill handle to autofill the entire column.

Note. To copy the formula to other cells correctly, make sure you use absolute and relative cell references correctly.

### 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:

- Select the cell containing the formula you want to copy.
- Press Ctrl+C (or Cmd+C on Mac) to copy the formula.
- Select the cells where you want to paste the formula (you can use Ctrl/Command and click to select multiple non-adjacent cells).
- 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.

## How to show formulas in Google Sheets

By default, cells in Google Sheets display the results of your formulas. However, it can be incredibly useful to view the actual formulas used in your sheet. For instance, to troubleshoot or understand how the values are calculated. Luckily, there's a built-in feature for that.

To show formulas in Google Sheets, go to *View > Show > Formulas* in the menu:

Tip. You can quickly toggle between showing formulas and results by using the shortcut Ctrl+`

Instantly, Google Sheets will show formulas used in every cell instead of their results:

However, if you’re working with large, complex spreadsheets, you may want to consider using our Advanced Find & Replace add-on.

### 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.

Here’s 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.

## Key Google Sheets formula examples

Now that you've learned how to create, edit, and copy formulas in Google Sheets, let’s take it a step further by exploring the key functions that make your formulas truly powerful. They can automate tasks, save time, and provide valuable insights.

Below, you'll find a list of Google Sheets functions every user should know. These examples will not only show you how to write formulas but also help you understand when and why to use each one.

### 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

Understanding and using these Google Sheets formulas will help you harness the full potential of your tables.

Well, congratulations! 🙂 You’ve now learned how to create, edit, copy, and use key formulas in Google Sheets. With this knowledge, you can build formulas that automate tasks, perform detailed analysis, and make your spreadsheets work for you.

## Practice spreadsheet

Сreate & edit Google Sheets formulas (make yourself a copy)