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.
Quick questions on Data manipulation using spreadsheets
- What does every spreadsheet formula start with?
- An equals sign (=): Without it, the cell just shows text.
- What does =SUM(B2:B5) do?
- Adds up the values in cells B2 to B5: B2:B5 is the range B2, B3, B4 and B5.
- What does =AVERAGE(C2:C11) work out?
- The mean of the values in C2 to C11: It adds them up and divides by how many there are.
- What does =IF(B2>=50,"Pass","Fail") show when B2 is 50?
- Pass: 50 is greater than or equal to 50.
- What does =COUNTIF(D2:D20,"Yes") do?
- Counts the cells in D2 to D20 that contain Yes: It counts the cells that match the condition.
- What is an absolute reference?
- A reference that stays the same when a formula is copied, such as $B$1: The $ signs lock the column and row.
- Why use an absolute reference for a VAT rate?
- Every copied formula must use the same cell: Otherwise the reference would move down as the formula is copied.
- What does conditional formatting do?
- Changes a cell's appearance automatically when a condition is met: For example, marks below 50 turn red.
Data manipulation using spreadsheets: questions and answers
- What does data validation do in a spreadsheet?
- Restricts what can be entered in a cell. For example, a drop-down list of options.
- In =VLOOKUP(A2,B2:D50,3,FALSE), what does the 3 mean?
- Return the value from the third column of the table. The first column is where it searches.
- Which chart is best for showing a trend over time?
- A line graph. The line shows how values rise and fall.