Cells and formulas
- A spreadsheet is a grid of cells. Each cell has a reference made of its column letter and row number, such as B3. A range such as A1:A10 means all the cells from A1 to A10.
- Formulas start with an equals sign: =B2+C2, =B2*C2 or =A1/2.
- Functions: =SUM(A1:A10) adds up a range, =AVERAGE(B2:B20) finds the mean, =MAX() and =MIN() find the largest and smallest values, and =COUNT() counts cells with numbers.
- =IF(B2>=50,"Pass","Fail") shows "Pass" if B2 is 50 or more, and "Fail" if it isn't.
Copying formulas
- Fill a formula down to copy it. Relative references change as they are copied: =A1*2 becomes =A2*2 in the next row.
- Absolute references, written with dollar signs such as $B$1, stay the same when copied, for example for a fixed price or rate.
Charts and formatting
- Bar charts compare categories, line graphs show change over time, and pie charts show parts of a whole. Give every chart a title and labelled axes.
- Sorting puts data in order; filtering shows only the rows that match a rule.
- Conditional formatting changes a cell's colour automatically, such as turning marks below 50 red.
- Data validation stops wrong data being entered, such as allowing only numbers from 1 to 100.
Modelling
- A spreadsheet model uses formulas to represent a real situation, such as the budget for a school trip.
- "What if" questions change the input values to see how the results change, such as the cost per student if more students go.
Key terms
- Cell
- A single box in a spreadsheet.
- Cell reference
- The column letter and row number of a cell, such as B3.
- Range
- A group of cells, such as A1:A10.
- Formula
- A calculation in a cell, starting with an equals sign.
- Function
- A built-in formula, such as SUM or AVERAGE.
- Relative reference
- A cell reference that changes when a formula is copied.
- Absolute reference
- A cell reference that stays the same when copied, such as $B$1.
- Conditional formatting
- Changing a cell's appearance automatically, based on its value.
- Data validation
- Rules that stop incorrect data being entered.
- Model
- A spreadsheet that represents a real situation.
Quick questions on Spreadsheets and data
- What is a cell reference?
- The column letter and row number of a cell, such as B3: Columns are letters and rows are numbers.
- What must every spreadsheet formula start with?
- An equals sign: For example: =B2+C2.
- Which function adds up a range of cells?
- SUM: =SUM(A1:A10) adds A1 to A10.
- What does =AVERAGE(B2:B6) do?
- Finds the mean of the values in B2 to B6: AVERAGE finds the mean.
- What does =MAX(A1:A10) find?
- The largest value in A1 to A10: MIN finds the smallest value.
- What does an absolute reference, such as $B$1, do?
- Stays the same when the formula is copied: Use it for a fixed value, such as a price.
- Cell A1 holds 5 and cell B1 holds 3. What does =A1*B1 give?
- 15: * means multiply.
- What does =IF(C2>=50,"Pass","Fail") show if C2 is 40?
- Fail: 40 is less than 50.
Spreadsheets and data: questions and answers
- Which chart is best for showing parts of a whole?
- A pie chart. Line graphs show change over time.
- What is conditional formatting?
- Changing a cell's appearance automatically, based on its value. For example, turning low marks red.
- What is data validation?
- Rules that stop incorrect data being entered. For example, only allowing numbers from 1 to 100.