Flashcards · 0417 · Topic 20

Spreadsheets flashcards

13 cards on Topic 20, subtopics 20.1 to 20.3. Pick a subtopic or study them all.

Study mode needs JavaScript. Every card is listed below, so you can still cover the answers and test yourself.

All 13 cards

For a quick read-through or printing. Cover the right-hand side to test yourself.

What is a spreadsheet data model?: A spreadsheet set up so that changing an input changes the outputs automatically, so "what if" questions can be tested.
Relative vs absolute cell references: A relative reference (C4) changes when the formula is copied. An absolute reference ($C$4) stays fixed. A mixed reference (C$4 or $C4) fixes only the row or the column.
Why does =D2/$D$7 use dollar signs?: So that when the formula is copied down, every row still divides by the same total in D7, while D2 changes to D3, D4 and so on.
What does =IF(D7>150,D7*0.1,0) return when D7 is 168?: The condition is true, so it returns 168 × 0.1 = 16.8.
What does VLOOKUP do?: It searches the first column of a table for a value and returns the item from a chosen column in the same row. HLOOKUP does the same across rows. Use exact match (FALSE) for codes.
COUNT vs COUNTA vs COUNTIF: COUNT counts cells containing numbers. COUNTA counts non-empty cells. COUNTIF counts only cells that meet a condition, e.g. =COUNTIF(D2:D6,">35").
What is a named range, and why use it?: A meaningful name given to a cell or range, such as VATRate. It makes formulas easier to read and check.

Why select the whole data range before sorting?: If only one column is sorted, the other columns stay where they are and the rows are scrambled.
Sort vs filter: A sort rearranges rows into ascending or descending order. A filter hides rows that do not meet the criteria without deleting them.
What is conditional formatting?: Formatting that changes a cell's appearance automatically when it meets a rule, e.g. shading marks below 40 red.

What does a cell full of hash symbols (####) mean?: The column is too narrow to display the value. Widen the column.
Name five number formats for spreadsheet cells: Integer, a set number of decimal places, percentage, currency, date and time.
Why print a spreadsheet with formulas showing?: It shows the examiner, or a checker, that results come from formulas and cell references rather than typed-in values.