Spreadsheets supports all formulas available in Workbooks as well as some Spreadsheet-specific formulas.
You can type formulas directly into an active cell or enter 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 of Wdesk without needing to type these formulas out.
To automatically calculate the sum of a data set:
- Select the data you want to sum.
- From the Edit toolbar, click Modify .
- Select SUM from the dropdown. This automatically places the value below the selected row. Any changes to the 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 Detail tab.
By clicking Current Sheet, you can see which cells are referenced in the currently selected cell. You can click a specific reference to see the exact values used or click Go to Reference to jump to that reference in your spreadsheet.
If the selected cell is being referenced in another location, you can click Referenced By to view these references. 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 usage.
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 generation 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.
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").