Cells, formulas and functions
How a spreadsheet is organized
A spreadsheet file is called a workbook. Each workbook contains one or more worksheets, shown as tabs along the bottom of the screen.
Every worksheet is a grid. Columns run up and down and are labelled with letters (A, B, C). Rows run across and are numbered (1, 2, 3). The box where a column and a row meet is a cell. Every cell has an address made of its column letter and row number, so the cell in column C, row 4 is C4.
A group of cells is called a range. You write a range with a colon between the first and last cells, so B2:B10 means every cell from B2 down to B10.
Three kinds of data
- Text
- Names, labels and descriptions, such as "Rent" or "Notebooks".
- Numbers
- Amounts you can calculate with. Type them without currency symbols or spaces, then format them afterwards.
- Dates
- Spreadsheets store dates as numbers behind the scenes, which is why you can sort them and subtract one date from another.
By default, numbers line up on the right side of a cell and text lines up on the left. If a number sits on the left, it may be stored as text and will be left out of calculations.
Formulas
A formula tells the spreadsheet to calculate something. Every formula starts with an equals sign. Use + to add, - to subtract, * to multiply and / to divide.
The real power comes from cell references. If B2 holds the number of units sold and C2 holds the price, =B2*C2 calculates the revenue. Change either number and the answer updates instantly. If you typed =120*3.5 instead, you would have to rewrite the formula every time a number changed.
Functions you'll use every day
A function is a built-in formula with a name. You give it values or a range inside parentheses.
| Function | What it does | Example |
|---|---|---|
| SUM | Adds up a range | =SUM(B2:B10) |
| AVERAGE | Finds the average | =AVERAGE(B2:B10) |
| MIN and MAX | Finds the smallest or largest value | =MAX(B2:B10) |
| COUNT | Counts the cells that contain numbers | =COUNT(B2:B10) |
| IF | Shows one result if a test is true and another if it is false | =IF(B2>100,"Over","OK") |
In Excel, the AutoSum button (Σ) on the Home tab writes a SUM formula for you. Google Sheets has a similar Functions button (Σ) in its toolbar. Always check the range it picks before you press Enter.
Copying formulas
When you copy a formula down a column, its cell references move with it. Copy =B2*C2 from row 2 to row 3 and it becomes =B3*C3. These are relative references, and they save a lot of typing. To copy quickly, drag the small square at the bottom-right corner of a selected cell, called the fill handle.
Sometimes a reference needs to stay put. Dollar signs lock it: $F$1 always points to cell F1, wherever you copy the formula. This is an absolute reference. If F1 holds a 13% tax rate, =D2*$F$1 works in every row.
Make it readable
- Bold your headers so every column is clearly labelled.
- Format numbers as currency, percentages or dates instead of typing symbols.
- Freeze the top row from the View menu so headers stay visible as you scroll.
- Widen columns by double-clicking the line between two column letters.