Excel formulas and functions help students organize data, perform calculations, and answer questions using spreadsheets. This cheat sheet gives a quick reference for writing formulas, choosing common functions, and reading cell references. Students need these skills for computer science, math, science labs, business projects, and data analysis tasks.
It is designed to make spreadsheet work faster, clearer, and less error prone.
Key Facts
- Every Excel formula begins with an equals sign, such as =A1+B1.
- A cell reference names a cell by column letter and row number, such as C4.
- A range uses a colon between two cell references, such as A1:A10 for all cells from A1 through A10.
- The formula =SUM(A1:A5) adds all values in cells A1 through A5.
- The formula =AVERAGE(B2:B8) finds the mean of the values in cells B2 through B8.
- The formula =MAX(C1:C20) returns the largest value in the range, and =MIN(C1:C20) returns the smallest value.
- The formula =IF(A1>=60,"Pass","Retry") returns one result when a condition is true and another when it is false.
- Use A$1 for an absolute reference that does not change when copied.
Vocabulary
- Formula
- A formula is an expression that begins with = and calculates a result using values, cell references, operators, or functions.
- Function
- A function is a built-in Excel command, such as SUM or AVERAGE, that performs a specific calculation.
- Cell Reference
- A cell reference is the address of a cell, written with a column letter and row number, such as B7.
- Range
- A range is a group of cells identified by a starting cell and ending cell, such as D2:D12.
- Relative Reference
- A relative reference changes when a formula is copied to another cell, such as A1 becoming A2 when copied down one row.
- Absolute Reference
- An absolute reference uses dollar signs, such as 1, to stay fixed when a formula is copied.
Common Mistakes to Avoid
- Forgetting the equals sign makes Excel treat the entry as text instead of a formula, so A1+B1 will not calculate unless it is written as =A1+B1.
- Using commas instead of a colon for a continuous range is wrong because =SUM(A1,A10) adds only A1 and A10, while =SUM(A1:A10) adds all cells between them.
- Copying a formula without locking a needed reference can give incorrect results because relative references change position when copied.
- Mismatching parentheses causes formula errors because every opening parenthesis must have a matching closing parenthesis, such as =AVERAGE(A1:A5).
- Typing numbers as text can break calculations because values stored as text may not be included correctly in formulas like SUM or AVERAGE.
Practice Questions
- 1 Cells A1 through A4 contain 6, 9, 12, and 15. What result does =SUM(A1:A4) return?
- 2 Cells B1 through B5 contain 10, 20, 30, 40, and 50. What result does =AVERAGE(B1:B5) return?
- 3 Write an Excel formula that returns "Yes" if the value in C2 is greater than or equal to 100 and "No" if it is not.
- 4 Explain why 1 is useful when copying a formula across many rows or columns.