Lesson 1: Formula foundations
This course uses English function names and comma argument separators, as in Microsoft 365 for Windows. Some regional settings use semicolons. A formula starts with =; cell references identify the input cells and ranges.
Sample data: enter 12, 8, 10, and 10 in cells B2:B5. The examples below use that range.
References, operators, and core formulas
A relative reference such as A1 shifts when copied. An absolute reference such as $A$1 stays fixed. Mixed references $A1 and A$1 lock only the column or row. Press F4 while editing a reference to cycle through these forms.
| Function | Purpose and syntax | Example and result |
|---|---|---|
| SUM | Adds numbers. SUM(number1, [number2], ...) | =SUM(B2:B5) returns 40. |
| AVERAGE | Arithmetic mean; ignores empty cells and text in references. | =AVERAGE(B2:B5) returns 10. |
| COUNT | Counts cells containing numbers. | =COUNT(B2:B5) returns 4. |
| COUNTA | Counts non-empty cells, including text and errors. | =COUNTA(A2:B5) counts every filled cell in that range. |
| COUNTBLANK | Counts empty cells in a range. | =COUNTBLANK(A2:A10) returns the number of blank rows. |
| MIN | Returns the smallest numeric value. | =MIN(B2:B5) returns 8. |
| MAX | Returns the largest numeric value. | =MAX(B2:B5) returns 12. |
| PRODUCT | Multiplies numbers. PRODUCT(number1, ...) | =PRODUCT(B2:B3) returns 96. |
| SUBTOTAL | Calculates a filtered-list summary. Function number 9 means SUM. | =SUBTOTAL(9,B2:B5) returns 40 and respects filtered-out rows. |
Copy a starter formula: =SUM(B2:B5)
Build and audit a formula
- Select the output cell and type
=. - Enter a function name, then select its arguments with the mouse or keyboard.
- Close the parenthesis and press Enter.
- Copy the formula down and check whether each reference should move or stay fixed.
Use Evaluate Formula to step through a complex expression. Use Trace Precedents to identify its inputs. Avoid hard-coded totals when a range formula can update automatically.
Progress is stored in this browser only.