A detailed primer for anyone new to working with formulas in spreadsheets.
A spreadsheet is a grid. Columns are labeled with letters (A, B, C... through XFD — that's 16,384 columns in modern Excel). Rows are numbered (1, 2, 3... through 1,048,576). A cell is the intersection of a column and row — B5 is column B, row 5. A range is a group of cells, written as two corners: A1:D10. A workbook contains multiple sheets (tabs), referenced with an exclamation mark: Sheet2!A1. You can also reference cells across different workbooks, though this requires both files to be open.
Click a cell and start typing with =. As you type a function name, a tooltip shows the expected arguments in order. Press Tab to autocomplete. Press F2 to edit the active cell without using the mouse. Press Esc to cancel. When a formula references other cells, Excel draws colored borders around those cells matching the color of the reference in the formula bar — this is the fastest way to verify you selected the right range before pressing Enter.
When you copy a formula to another cell, relative references shift automatically. The fill handle (small green square in the bottom-right corner of a selected cell) is the fastest way to copy: drag it down or across, or double-click it to auto-fill down to match the adjacent column's height. For non-adjacent cells, Ctrl+D fills down from the top cell, and Ctrl+R fills right from the leftmost cell.
Instead of remembering that B2:B50 contains your sales data, you can name that range "SalesData" and write =SUM(SalesData) instead. Named ranges make formulas readable and reduce errors when ranges change — update the named range once, and every formula that references it updates automatically. Create them from the Formulas tab or by typing directly into the name box left of the formula bar.
Spreadsheets distinguish between numbers, text, dates, and Boolean values (TRUE/FALSE). This matters because functions behave differently based on type. =SUM("5", 5) returns 5, not 10 — the text "5" is ignored. Dates are stored as sequential numbers (January 1, 1900 = 1), which is why you can subtract two dates to get the number of days between them. The ISTEXT, ISNUMBER, and ISDATE functions help identify types in messy datasets.
#DIV/0! — Dividing by zero or an empty cell.#N/A — A lookup couldn't find the value. Not always a bug; check if the value genuinely exists.#VALUE! — Wrong data type (e.g., adding a number to text).#REF! — A referenced cell was deleted or moved.#NAME? — Unrecognized function name (typo, or the function isn't available in your version).#NUM! — Numeric overflow or impossible math operation.#SPILL! — An array formula is blocked by existing data in the spill range.#NULL! — Intersection of two ranges that don't intersect. Rare in modern use.How formulas are structured in Excel and Google Sheets — from the ground up.
Every formula starts with =. This tells the spreadsheet "what follows is a calculation, not just text." Without it, SUM(A1:A10) is just a string of characters in a cell. With it, =SUM(A1:A10) becomes a working formula.
Functions are the verbs of formulas: SUM, AVERAGE, IF, VLOOKUP. They're always uppercase (the sheet auto-capitalizes them) and always followed by parentheses. Everything between the parentheses is the function's input — its arguments.
Arguments are the values you give a function to work with. They go inside the parentheses and are separated by commas (,) in most regions, or semicolons (;) in some European locales. Arguments can be cell references, numbers, text strings, or other functions.
By default, references are relative — when you copy a formula down a column, A1 becomes A2, then A3. Add dollar signs to make references absolute:
A range is a rectangular block of cells, written as top-left:bottom-right. A1:C10 means all cells from A1 down to C10. Ranges can span rows (1:1), columns (A:A), or multiple sheets (Sheet2!A1:B10).
These return TRUE or FALSE and are used inside IF statements, conditional formatting, or FILTER functions:
You can put one function inside another. The inner function runs first:
If you find yourself nesting more than 3-4 levels, consider breaking the problem into helper columns or using LET for readability.
In current versions, many functions automatically spill results into adjacent cells. A single =SORT(A1:A50) fills all cells below with sorted values — no special keystroke needed.
Deeper concepts that go beyond the basics — for when you're comfortable with formulas and want more control.
An array formula operates on multiple values at once rather than a single value. In modern Excel and Google Sheets, functions like SORT, FILTER, and UNIQUE automatically spill results into adjacent cells. You don't need to select a range beforehand — the output range is determined dynamically. If something blocks the spill range, you get a #SPILL! error. Older Excel versions required Ctrl+Shift+Enter to create array formulas (sometimes called CSE formulas), but this is no longer necessary in current versions.
LET assigns names to intermediate calculations within a single formula. Instead of repeating the same sub-calculation three times, you name it once and reference the name. This makes formulas shorter, faster, and easier to debug. LAMBDA goes further — it lets you define a custom function with parameters that you can reuse across the workbook without VBA or macros. For example, =LAMBDA(x, x*1.1) defines a function that adds 10% to its input. Combined with named ranges, LAMBDA functions become part of your permanent formula toolkit.
Some functions recalculate every time any cell in the workbook changes — not just when their own inputs change. These are called volatile functions. NOW(), TODAY(), RAND(), OFFSET(), INDIRECT(), and CELL() are all volatile. In large workbooks, too many volatile functions can slow everything down because they force cascading recalculations. If performance is an issue, audit your volatile functions first — replacing OFFSET with INDEX often resolves it.
A circular reference happens when a formula refers to its own cell — either directly (=A1+1 in cell A1) or through a chain of dependencies. By default, Excel flags these with a warning because they create an infinite loop. However, you can enable iterative calculation in settings, which tells Excel to stop after a set number of iterations. This is sometimes used intentionally for financial models that need to converge on a value, but it makes the calculation order unpredictable and can hide errors.
When you format a range as a Table (Ctrl+T), Excel automatically assigns a name to the table and each column. Instead of =SUM(B2:B100), you can write =SUM(Table1[Sales]). This is called a structured reference. It's self-documenting, automatically expands when you add rows, and is much easier to read months later. Google Sheets doesn't have this exact feature, but named ranges provide a similar benefit.
All three of these tools share the same fundamental model: a grid of cells arranged in rows and columns, where each cell can hold a value or a formula. Formulas begin with an equals sign and can reference other cells, ranges, or data from other sheets. Functions like SUM, IF, and VLOOKUP behave nearly identically across platforms — the syntax you learn for one transfers to the others with minimal adjustment. Collaboration features vary (Google Sheets is browser-native with real-time co-editing; Excel supports co-authoring through Microsoft 365; LibreOffice Calc is offline-first), but the formula engine underneath is remarkably consistent. All three can import and export common formats like .csv and .xlsx, making it practical to start work in one tool and finish in another.