Planning a spreadsheet solution
- Start from the client's requirements: what the spreadsheet must do, who will use it, what data goes in and what information must come out.
- Plan before you build: sketch the layout, list the formulas you need, and write a test plan.
Formulas and functions
- Every formula starts with an equals sign. A cell reference such as B2 points to one cell; a range such as B2:B10 covers several cells.
- Common functions: =SUM(B2:B10), =AVERAGE(B2:B10), =MIN(B2:B10) and =MAX(B2:B10). =COUNT counts cells containing numbers; =COUNTA counts cells that aren't empty; =COUNTIF(C2:C20,"Yes") counts the cells that match.
- =IF(B2>=50,"Pass","Fail") shows Pass when B2 is 50 or more, and Fail otherwise.
- =VLOOKUP(A2,Prices!A2:C50,3,FALSE) looks for A2 in the first column of a table and returns the value from its 3rd column. FALSE means it must be an exact match. XLOOKUP is a newer alternative.
Relative and absolute references
- A relative reference (B2) changes when the formula is copied to another cell.
- An absolute reference ($B$1) stays the same when copied. Use it for a single value that every row needs, such as a VAT rate.
- A named range (such as naming cell B1 'VAT') makes formulas easier to read.
Presenting and checking data
- Formatting: currency, percentages, dates, borders and colours. Conditional formatting changes a cell's look automatically, such as turning marks below 50 red.
- Data validation restricts what can be typed, such as a drop-down list or whole numbers between limits, and shows an error message.
- Sort and filter data to organise it. Charts show patterns: a line graph for trends over time, a bar chart to compare, and a pie chart for proportions.
- Protect cells and sheets so formulas can't be changed by accident. Test with normal, extreme and erroneous data, and check that formulas give the expected results.
Key terms
- Formula
- A calculation in a cell, starting with an equals sign.
- Function
- A built-in formula, such as SUM or AVERAGE.
- Cell reference
- The column letter and row number of a cell, such as B2.
- Range
- A group of cells, such as B2:B10.
- Relative reference
- A cell reference that changes when a formula is copied.
- Absolute reference
- A cell reference that stays the same when a formula is copied, such as $B$1.
- Conditional formatting
- Formatting that changes automatically when a condition is met.
- Data validation
- Rules that restrict what can be entered in a cell.
- Named range
- A name given to a cell or range, used in formulas instead of its reference.