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.
Cross Sheet Formulas
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").
Text Values in Comparison Formulas
There are a few changes in how text values behave in comparison formulas in next gen Spreadsheets. These changes have been put in place to align some functionality more closely with other spreadsheet products.
- Classic: non-numeric text values compare as if they are zero.
- Next Gen: non-numeric text values compare as if they are infinity.
For example, take the expression of =”no” > 0. Classic is FALSE because "no" is treated like zero, but in next gen this evaluates to TRUE because “no” is treated like infinity.
Here are some example formulas and what the results would be in classic compared to next gen.