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.
Understanding Excel Formulas & Functions Reference
A spreadsheet is a grid, but it behaves more like a small program. Each formula creates a relationship between cells. When a value changes, Excel recalculates cells that depend on it.
This makes one worksheet useful for repeated tasks such as tracking experiment results over several weeks or updating a class budget. A good spreadsheet separates input cells from calculation cells. Put raw measurements, prices, or scores in one clear area.
Put results in another area. This layout makes mistakes easier to find and prevents someone from typing over an important calculation.
Excel follows an order when it calculates. It handles work inside parentheses first. It then deals with powers, multiplication, and division before addition and subtraction.
This order can change an answer when a formula has several operations. Parentheses show Excel exactly which part should be done first. Functions work like built in tools.
Some functions summarize data, while others make decisions, count entries, round values, or search for matching information. The most useful function is not always the shortest one.
Choose a function that matches the meaning of the data. A total is useful for costs, while an average is more useful for a set of repeated measurements.
Copying formulas is one of Excel's biggest time savers. Relative references shift as a formula moves to a new row or column. This is useful when every student score needs the same calculation or every item in an inventory needs the same price rule.
A locked reference stays fixed during copying. It is useful for a single tax rate, conversion factor, or grading threshold used throughout a table. Mixed references lock only the row or only the column.
They are helpful in multiplication tables and models where headings across the top or down the side must remain fixed. Before filling a formula down a column, check the first copied result carefully. One wrong reference can produce a whole column of believable but incorrect answers.
Error messages are clues, not just failures. A divide by zero error often means a formula tried to divide by an empty cell or a zero value. A name error can mean Excel does not recognize a function name or a reference was typed incorrectly.
A value error often appears when text is used where a number is expected. Check whether numbers were entered as text, especially after copying data from a website. Use the formula bar to inspect what a cell actually contains.
It helps to test a formula with a few simple values whose answers you can predict. In school projects, label units clearly and keep a short note explaining important formulas. A spreadsheet is easier to trust when another person can follow the logic without guessing.