Spreadsheets currently supports most common financial and data-gathering functions. For a list of currently supported formulas, see Supported Formulas.
You can type formulas directly into an active cell or type them into the Formula Bar. Additionally, the Formula Assistant offers suggestions and explanations for formulas as you type. You can click the Formula Assistant icon in the Formula Bar to see all supported formulas.
When highlighting a range of cells, you can see the values for AVERAGE, COUNT, and SUM in the bottom-right corner without needing to enter these formulas.
To automatically calculate the sum of a data set:
- Select the data you want to sum.
- From the Edit toolbar, click SUM .
- This automatically places the value below the selected row. Any changes to the selected data set also updates the sum.
You can also switch between viewing formulas and viewing values in your spreadsheet.
To display all formulas:
- Open the View toolbar.
- Click Show Formulas . Click the button again to show calculated values.
To create a formula that references a cell in another sheet:
- Begin typing your formula.
- Open the sheet that contains the cell you want to reference.
- Click the cell you want to use. This places a reference to that cell in your formula and links your formula to the source cell, meaning this formula automatically updates when the linked cell is edited.
View formula details
The Formula Details panel shows you the cells referenced in a formula, as well as any places the selected cell is referenced.
To view formula details:
- Select the cell that contains your formula.
- Click the Link icon in the right panel to open Link Properties.
- Click the formula icon at the top of the panel to open the Formula Details tab.
The Formula References section shows which cells are referenced in the currently selected cell. The Referenced By section shows other places the selected cell is referenced.
You can click a specific reference to see how the selected cell is being used in a formula, or use Go to Reference to jump to the specific cell.
Scaling in formulas
Formulas rely on your spreadsheet's or cell's "Entered In" and "Shown In" values when making calculations. Any number entered into a cell, including inside a formula, will default to these settings.
To ignore the "Entered In" and "Shown In" settings, you can place the number in quotation marks.
For example, if your spreadsheet is set to Thousands, entering a formula that divides a sum by 2 (=SUM B3:B4/2) will automatically divide the sum by 2,000.
To divide the sum by 2, place the number in quotation marks (=SUM B3:B4/"2").
Advanced formula editor
For complex formulas, you can expand the Formula Assistant to view or change your formulas.
Each part of the formula appears on a new line, and functions will be grouped by indentation. New arguments are separated by a comma, while functions separated by a parenthesis will appear on a new line.
To view the required syntax for a function and how it should be formatted, click the arrow next to your selected formula. Hover over a component to view more information.
To test a sample function in your spreadsheet, click the plus sign to insert the example formula into your sheet.