Add formulas to a spreadsheet
You can perform many different types of calculations by using functions to create formulas in Google Sheets.
Create a formulaDesktop
- Open a spreadsheet.
- Type an equal sign (=) in a cell and type in the function you wish to use. The new version of Sheets also has function autocomplete, offering suggestions for functions as you type.
- A function help box will be visible throughout the editing process to provide you with a definition of the function and its syntax, as well as an example for reference. If you need more information, click the “Learn more” link at the bottom of the help box to open a full article.
Function help box shortcuts
- F1 will minimize and maximize the help box.
- Shift + F1 will hide the help box.
- F1 or Shift + F1 will make the help box appear if it is currently hidden.
- In the Sheets app, open the spreadsheet.
- Touch the cell where you want to add the formula.
- Touch the Formula icon to the left of the text box at the bottom of the screen. If you want to type in the formula manually or the cell already has content in it, skip to step 7.
- Choose a type of function to choose from.
- Choose the function you want to use in your formula.
- A description of the function will show as you’re working on it to guide you through formula creation.
- To use the content of other cells in your formula equation, type the names of the cells into the formula.
- Choose from the symbols below the text box to add to the formula. For example, to subtract the contents of one cell from another, touch the - (minus) symbol.
- To finish the formula, touch the Checkmark .
Note: Adding formulas is only available on desktop and Android devices at this time.
Additional features for creating formulasSimple ways to select and edit ranges
- When editing a formula, the range-selection signal (a grey bracket) will appear next to your cursor where you’re likely to need a range in the formula. When you see the signal, you can move the keyboard arrows around your sheet to select a range.
- Toggle this mode on and off using the keyboard shortcuts F2 or Ctrl + E. If range-selection mode is turned off, using the arrow keys will move your cursor inside the input box instead of selecting a range.
- Clicking within the sheet with your mouse remains an option for selecting a range at any point when editing a formula.
Range replacement improvements
- When you have the text of a range highlighted in your formula, use F2 or Ctrl + E to enter range-selection mode and easily make adjustments to the range.
- If you press Shift + F2 or Shift + Ctrl + E while editing the text of a range, you can easily make adjustments to all occurrences of that range in the formula.
A function used in the same cell with another function is called a nested function. When functions are combined, Google Sheets will calculate the innermost function first. The nested function is contained in parentheses and is used as one of the components of the surrounding function.
For example, let's say you want to calculate the absolute value of a sum of several numbers in the cell range A1:A7. To calculate the sum of these numbers, you would enter '=SUM(A1:A7)' into a cell.
To calculate the absolute value of this sum, you need to nest the sum formula within absolute value formula. To calculate both formulas in a single cell, enter '=ABS(SUM(A1:A7))' into the cell. Note that the =SUM() function is performed first and is used as a component in the =ABS() function.
When you reference other cells in a formula, we highlight those cells in contrasting colors to help you more easily build a formula. When you click on the cell that contains a completed formula, you'll also see these cells highlighted.