Formulas are what make spreadsheets more than just a grid for storing data. They let you calculate, transform, and summarize information automatically. Understanding how formulas work — and how to avoid common mistakes — is a foundational skill for anyone working with spreadsheet data.
What Is a Formula?
A formula is an instruction that tells a spreadsheet to perform a calculation. Formulas begin with an equals sign (=). Everything after the equals sign is evaluated and the result is displayed in the cell.
For example: =10+5 displays 15. =A1+A2 adds the values in cells A1 and A2.
Mathematical Operators
Spreadsheet formulas use standard mathematical operators:
+addition-subtraction*multiplication/division^exponentiation (raising to a power)
Calculation order follows standard mathematical rules: operations in parentheses are evaluated first, then exponentiation, then multiplication and division, then addition and subtraction. When in doubt, use parentheses to make the intended order explicit.
Cell References
Instead of typing values directly into formulas, you reference cells. This means your formula updates automatically when the data in those cells changes.
There are three types of cell references:
- Relative references (e.g.,
A1) — adjust automatically when you copy a formula to another cell. If you copy a formula one row down, all row numbers in the formula increase by one. - Absolute references (e.g.,
$A$1) — do not change when you copy the formula. The dollar signs lock the row, the column, or both. - Mixed references (e.g.,
$A1orA$1) — lock either the column or the row but not both.
Suppose you have a tax rate in cell B1 and a list of prices in column A. To calculate tax for each price, you would write a formula like =A2*$B$1 in column B. The absolute reference to $B$1 ensures that when you copy the formula down the column, it always refers to the tax rate cell — not a different cell each time.
Copying and Filling Formulas
One of the most useful spreadsheet features is the ability to copy a formula across many rows or columns at once. When you fill a formula down a column, relative references adjust row by row. This is how you apply the same calculation to an entire dataset without retyping the formula for each row.
Avoiding Hard-Coded Values
A hard-coded value is a number typed directly into a formula rather than referenced from a cell. For example, =A1*0.13 hard-codes the tax rate. If the rate changes, you must find and update every formula that contains it.
A better approach: put the tax rate in a clearly labelled cell and reference that cell in your formulas. This makes your spreadsheet easier to maintain and audit.
Common Formula Mistakes
- Circular references. A circular reference occurs when a formula refers to its own cell, directly or indirectly. Most spreadsheet software will warn you, but circular references can cause incorrect results or infinite loops.
- Wrong range. Accidentally including or excluding rows from a sum or average is a common error. Always verify that your formula covers the intended range.
- Mixing data types. Formulas that try to add text values to numbers will produce errors. Check that your data is consistent.
- Unintended relative references. When copying formulas, relative references shift. Verify that copied formulas still reference the correct cells.
Checking Your Results
Never assume a formula is correct just because it produces a number. Spot-check results manually. For totals, verify that the sum matches what you would expect. For averages, check that the result is plausible given the data range.
Key Takeaways
- Formulas begin with
=and calculate results from values or cell references. - Relative references adjust when copied; absolute references stay fixed.
- Avoid hard-coded values — reference cells instead.
- Watch for circular references, wrong ranges, and unintended reference shifts.
- Always verify formula results against expected values.
- spreadsheet formulas
- cell references relative absolute
- spreadsheet formula errors