Ravi wants to refer to all the cells from D2 down to D8 together. Which of these writes this range correctly?
D2:D8
Priya clicks the letter C at the top of her spreadsheet. What does she select?
The whole of column C
Why would you double click a cell that already contains data?
To edit the data already in the cell
A student selects a cell and presses Backspace. What happens?
The data in the cell is deleted
Which action selects several cells that are next to each other?
Click and drag across the cells
What do people mainly use spreadsheets for?
To organise, process and analyse data
What is a cell in a spreadsheet?
The box where a column and a row meet
Which statement about spreadsheets and jobs is correct?
They are used in many different jobs
Jay wants to type the number 25 into cell B3. What should he do?
Click cell B3 and type 25
Why are the columns of a sheet labelled with letters and the rows with numbers?
So every cell has its own reference
A football coach records each player's goals and fitness in a spreadsheet. How does this help him?
He can analyse the data to pick a team
A spreadsheet file has three tabs at the bottom of the screen. What does this mean?
The file contains three sheets
Which term describes a group of cells that are next to each other?
Range
What is meant by 'sheet'?
A page within a spreadsheet, shown as a tab at the bottom of the screen
Which term describes the parts of a sheet that run up and down and are labelled with letters?
Column
What is meant by 'cell reference'?
The column letter followed by the row number, which identifies one cell
What is meant by 'row'?
A part of a sheet that runs across and is labelled with a number
Which of these is another name for a cell reference?
Cell address
Dev clicks the number 4 on the left of a spreadsheet. What does he select?
The whole of row 4
A pupil is working on a sheet called Sales and wants to switch to the sheet called Costs. What should she do?
Click the Costs tab at the bottom
Which symbol must every spreadsheet formula start with?
An equals sign
Which symbol is used for multiplication in a spreadsheet formula?
The asterisk
A student types =83-3 into a cell and presses Enter. What is shown in the cell?
80
What is the result of the formula =(10+5)*4?
60
Cell A4 holds 97 and cell C2 holds 78. What does the formula =A4+C2 show?
175
Which formula divides the number in B2 by the number in B4?
=B2/B4
A formula uses cell references instead of typed numbers. What is the main advantage?
The answer updates automatically when the data changes
In cell D2 the formula is =B2*C2. The formula is copied down to D3. What is the formula in D3?
=B3*C3
In a snack shop sheet, B2 holds the price per item and C2 holds the quantity sold. Which formula gives the total sales in D2?
=B2*C2
Which key do you press to finish entering a formula into a cell?
Enter
Why does every spreadsheet formula start with an equals sign?
So the entry is calculated instead of shown as text
What is the result of the formula =20+5*4?
40
Which formula adds the value in B12 to the value in B7 and then divides the answer by the value in C9?
=(B12+B7)/C9
A sheet works out total sales for the first row in D2. The same total is needed for rows 3 to 13. What is the best way to work out the rest?
Copy the formula from D2 down to D13
Which term describes a calculation typed into a spreadsheet cell, such as =53+32?
Formula
What is meant by using brackets in a spreadsheet formula?
The part of the calculation inside the brackets is worked out first
What is the name for the maths symbols such as + and * that tell a spreadsheet which calculation to carry out?
Operators
A formula has been typed into a cell and Enter has been pressed. Where can the formula still be seen?
In the formula bar
Which symbol is used for division in a spreadsheet formula?
The forward slash
A student types the equals sign in a cell, then clicks on cell B2. What is added to the formula?
The cell reference B2
Why is =SUM(J4:J34) usually better than writing =J4+J5+J6+ ... +J34?
It is much shorter and easier to check
Cell A1 contains Sun and cell A2 contains day. What does =CONCATENATE(A1,A2) show?
Sunday
Cell A2 contains jOHN smith. What does =PROPER(A2) show?
John Smith
Cells A1 to A5 contain 4, 9, 2, 7 and 5. What does =AVERAGE(A1:A5) show?
5.4
Cells B1 to B5 contain the payments 12, 30, 8, 45 and 20. Which function gives the lowest payment?
=MIN(B1:B5)
A teacher wants the highest score from the test scores in cells C2 to C31. Which function should be used?
=MAX(C2:C31)
Cells A1 to A6 contain 12, Apple, 8, an empty cell, Pear and 5. What does =SUM(A1:A6) show?
25
Cells A2 to A30 of a register contain the names of members. Which function counts how many names are in the list?
=COUNTA(A2:A30)
The price each customer paid is in column L, with the heading Price in L1. Which function counts how many customers paid a price?
=COUNT(L:L)
Which of these entries is a function rather than a formula?
=SUM(A1:A5)
The sales totals for 12 items in a school snack shop are in cells D2 to D13. Which function shows the shop's total revenue?
=SUM(D2:D13)
The ratings from a holiday survey are in cells H2 to H50. Which function shows the average rating?
=AVERAGE(H2:H50)
Cells A1 to A6 contain 12, Apple, 8, an empty cell, Pear and 5. What does =COUNT(A1:A6) show?
3
Cells A1 to A5 contain 4, 9, 2, 7 and 5. What does =MAX(A1:A5) show?
9
Which term describes a ready-made calculation that is built into a spreadsheet and can work on a group of cells at once?
Function
What is meant by the function SUM?
It adds together the numbers in a range
Which function adds up the numbers in a range and then divides by how many numbers there are?
AVERAGE
What is meant by the function COUNTA?
It counts the cells in a range that are not empty
Which function changes text so that the first letter of each word is a capital and the remaining letters are lowercase?
PROPER
What is meant by a range in a spreadsheet?
A group of cells from a first cell to a last cell
In =CONCATENATE(A1,A2), what does the comma do?
Separates the cells the function should use
In the function =AVERAGE(L:L), what does L:L mean?
Every cell in column L
What is meant by the function CONCATENATE?
It joins the contents of several cells into one piece of text
In =SUM(A1:A5), what do the brackets show?
Which cells the function should work on
In the function =MAX(C2:C31), which part tells the spreadsheet what to do?
MAX
Cells A1 to A5 contain 10, 20, Apple, 30 and an empty cell. What does =AVERAGE(A1:A5) show?
20
In an IF function, what is the correct order of the three parts?
The test, then the value if true, then the value if false
Cell A1 holds 15 and cell B1 holds 15. Which test is true?
A1=B1
A pass is given only when a value is more than 10. Cell A1 holds 10. Is a pass given?
No, because 10 is not greater than 10
The snack shop reorders an item when its stock in B2 falls below 5. Which test should the IF statement use?
B2<5
The discount rule uses =IF(C2>10,D2*0.9,D2). Cell C2 holds 12 and cell D2 holds 30. What is shown?
27
A travel agent bonus uses the IF statement =IF(Q7>7,1,0). What does cell Q7 hold?
The average rating of the agents
A quiz marks the answer typed in B2. It shows 1 when the answer is 7 and 0 for anything else. Which IF statement should be used?
=IF(B2=7,1,0)
A student types =IF(A1>10,Pass,Fail) and the spreadsheet shows an error. What is the mistake?
The text values are missing their speech marks
A shop's spreadsheet lists only the quantity sold and the price of each product. What should be added before deciding which products are best sellers?
A column that calculates the total sales for each product
Cell A3 contains the word White. What does the formula =IF(A3='White','Yes','No') show?
Yes
The snack shop puts =IF(D2>20,'Yes','No') in cell E2 and copies it down to E13. What does this do?
Checks each row's sales total against 20
A spreadsheet checks whether two customer numbers match with =IF(B2=C2,'Match','No match'). B2 holds 145 and C2 holds 146. What is shown?
No match
A shop manager wants the spreadsheet to work out which products to reorder. Why is an IF function useful here?
It applies the same reorder rule to every product
Which term describes the spreadsheet feature that checks a condition and then shows one of two different results?
IF function
What is meant by the test in an IF function?
The condition that the spreadsheet checks
What is meant by a comparison symbol in an IF function?
A symbol such as =, > or < that is used to compare two values
Which term describes the result that a spreadsheet shows when the condition it is checking is met?
Value if true
What is meant by the value if false in an IF function?
The result a cell shows when the test it is given is false
In the structure =IF(test, value if true, value if false), which symbol separates the three parts?
Comma
In an IF function, what can the value if true and the value if false be?
Text, numbers or a calculation
In an IF function, which values are written inside speech marks?
Text values such as Yes
A student types IF(D2>20,'Yes','No') into a cell and no decision is worked out. What is missing from the formula?
An equals sign at the start
Why do people often present data in a chart instead of a long table of values?
A chart shows the data as a picture, making it easier to compare
A column chart shows how many books each year group read. The columns are labelled and the scale is clear, but readers cannot tell what the chart is about. What is missing?
A title
A line graph shows values from 0 to 50 on the vertical axis, but no label says what those numbers measure. Why is this a problem?
The reader cannot tell what the values represent
A chart of house prices has a vertical axis that starts at 200,000 instead of 0. Why can this mislead the reader?
A small rise can look like a huge rise
A student records the temperature in a greenhouse every hour for one day. The teacher wants to show the pattern across the day. Which chart is best?
A line graph, as it shows how a value changes over time
A class votes for a new after-school club. The teacher wants to show each club as a share of the total votes. Which chart is best?
A pie chart, as it divides a total into parts
A gardener measures the height of five different plants once. The heights are not shares of a whole. Which chart best compares the five heights?
A bar chart, as the plants are separate categories
A chart has already been created from the data in a spreadsheet. Where do you change the colour of the bars and add axis titles?
In the chart editing options
A chart has been created from the results of a pivot table. What happens to the chart when a filter is applied to the pivot table?
The chart changes to show only the filtered data
A bar chart shows the favourite sport of each year group, but the bars are not labelled. Why is this a problem?
The reader cannot tell which sport each bar shows
A line graph has a vertical axis showing 0, 37, 74, 111 and 148. Why is this a poor scale?
The values are awkward to read from the graph
Why should the column headings be included when you select the data for a chart?
So the chart labels each set of values with its name
A student wants a pie chart showing how the total holiday spending is split between two groups. Which values should the pie chart use?
The total spending of each group
Which term describes the chart type that is used to compare separate categories, such as the number of bookings for each holiday type?
Bar chart
What is meant by 'pie chart'?
A chart where a circle is divided into slices to show parts of a whole
What is the name for the text placed at the top of a graph to tell the reader what the graph shows?
Title
What is meant by 'line graph'?
A graph that shows how values change over time
What is the name for a graph problem where the y-axis does not start at zero, so a small rise looks like a huge increase?
Misleading scale
A chart has been created from spreadsheet data. Which setting is used to change it from a column chart into a pie chart?
Chart type
What is meant by the scale on a graph?
The set of numbers marked along an axis
What is meant by an axis label on a graph?
Text that says what an axis measures
A student selects their data and inserts a pivot table, leaving the default choice to place it on a new sheet. Where does the pivot table appear?
On a new sheet, on a tab of its own
A student puts Holiday Type in Rows and Gender in Columns. What does this create?
A two-dimensional table comparing two sets of data
A student adds Tickets to the Values area. Which summary should be chosen to give a total for each genre?
SUM, which adds the values together
A student wants to know how many customers visited each day, using a column of customer names. Which summary should they choose?
COUNTA
Two groups of different sizes are being compared. Why is AVERAGE of Price Paid fairer than SUM?
A bigger group has a bigger total even if each person pays less
A pivot table of tickets per genre has a Grand Total row showing 105. What does this number mean?
The total tickets sold across all of the genres
A student wants to find which genre has the best average star rating. Which setup gives the answer?
Row: Genre, Value: Star Rating, Summarise by AVERAGE
Why does a pivot table save time compared with writing formulas and functions to summarise the same data?
It groups the data and works out the totals, averages or counts
A pivot table has Holiday Type in Rows, Gender in Columns and COUNTA of Holiday Type in Values. How does a student find the most popular holiday type with men?
Look down the M column for the highest count
A student has a table of data with headings and wants to make a pivot table. What should they do first?
Select all of the data, including the headings
A pivot table counts the tickets sold on each day. Monday 24, Tuesday 31, Wednesday 18, Thursday 27. Which day sold the fewest tickets?
Wednesday
A student creates a pivot table from a table of data. What happens to the original data?
It stays the same on its own sheet
In the pivot table editor, which area is used to narrow down which records are summarised, for example to show only one year group?
Filters
Which of these best describes what a pivot table does?
It groups and summarises the data
A spreadsheet collects dates of birth from many people. Why is validation useful here?
It stops the same date being written in different ways
A cell must only allow the choices Yes or No. Which type of rule is needed?
A list of items rule
A column should allow only the single letter Y to be typed. Which text rule is needed?
Text is exactly Y
A column holds the ages of members of a club. Which number rule allows only sensible ages?
Number between 0 and 120
A hotel booking form has an arrival date. The date must not be in the past. Which rule is needed?
Date is after today
A hotel booking form has a box for the customer's email address. Which rule only allows a proper email address?
Text is a valid email
A rule is set to Reject the input. What happens when a user types data that breaks the rule?
The data cannot be entered at all
A drop-down validation rule has been added to a cell. How does the cell change?
A small arrow on the right of the cell
A validation rule that is not a drop-down is added to a cell. How does the cell look after the rule is added?
No different from before
A Notes column holds sentences written by staff. No entry may include the word absent. Which rule should be used?
Text does not contain absent
A spreadsheet records how many tickets each pupil has sold. Only numbers above 0 should be allowed. Which rule fits best?
Number greater than 0
Which term describes a spreadsheet tool that sets up rules to check data as it is entered?
Data validation
Which term describes a validation rule that turns a cell into a menu of set choices such as Single,Double,Family?
List of items
What is meant by a number rule in data validation?
A rule that only allows numbers that meet a condition
Which term describes the setting where incorrect data is still allowed but the cell is marked as invalid?
Show a warning
What is meant by a date rule in data validation?
A rule that only allows dates that meet a condition
Which text rule accepts any entry that includes a particular word or phrase?
Text contains
Which term describes data such as dates that are typed in many different ways by different people?
Inconsistent data
In a data validation rule, what is meant by the criteria?
The type of rule, such as text or number
Which term describes data that can never be true, such as an age of -10 or a date of 31 February?
Impossible data
A hotel booking form must accept bookings of between 1 and 6 guests. Which rule keeps the entries sensible?
Number between 1 and 6
Why is formatting added to a spreadsheet of data?
It makes the data clearer and easier to read
The text in one column is cut off because the column is too narrow. Which action shows all of the data?
Dragging the line between the column letters
A student needs to format columns A, C and E at the same time. What should they do?
Click A, hold Ctrl and click C and E
A student wants every letter in the sheet to use a different style of lettering. Which formatting option do they change?
Font
A student selects a cell and changes the text size to 18. What does this change?
The letters in the cell are shown larger
A student needs to select exactly the cells from A1 to D20 before adding borders. Which action does this?
Click A1, hold Shift and click D20
A sheet lists product codes, quantities and prices. Which column should be formatted as currency?
The column of prices
A student applies bold and a fill colour to a cell holding the number 12.5. What has changed?
How the number looks on screen
A student has selected a table of data and wants a line around every cell, including the edge of the table. Which Borders option should they choose?
All borders
A student gives the heading row a fill colour that is different from the rest of the sheet. Why does this help?
The headings look different from the data below
Which term describes making the data in a cell darker so that it stands out from the rest?
Bold
What is meant by shading?
Filling a cell or group of cells with a chosen colour
What is the name for the lines drawn around the edge of a cell or group of cells?
Borders
What is meant by alignment?
Moving the data to the left, centre or right of a cell
Which term describes keeping the headings on screen at the top of a sheet while you scroll down through lots of data?
Freezing the top row
What is meant by currency format?
A format that adds a pound sign and shows numbers to two decimal places
Which formatting tool changes the colour of the letters shown in a cell?
Text colour
A column of short product codes would look neater in the middle of each cell instead of at the left edge. Which formatting should be applied?
Set the alignment to centre
A student wants the headings in row 1 to stand out from the data below them. Which formatting is best?
Bold text and a different fill colour
Pass and Fail rules cover a column, and one cell in that column contains Merit. What happens to that cell?
It keeps its normal formatting and gets no colour
A cell is green because it contains Pass. The value is changed to Fail, and a Fail rule also exists. What happens?
The cell turns red automatically
A Position column holds 1, 2, 3, 10 and 11. Which rule colours only the cells holding 1?
Is equal to 1
A green rule already covers a column and you want to add a red rule to the same cells. What should you do?
Add another rule for the same range of cells
A teacher wants to spot every score below 40 in a long list of results. Which use of conditional formatting is best?
Fill cells below 40 with a chosen colour
In a self-marking quiz, column C shows Correct or Incorrect. Which rule colours the wrong answers red?
Text contains Incorrect, with a red fill
A student wants every cell in column D that contains the word Late to be filled amber. They select column D and open the conditional formatting settings. What should they do next?
Choose Text contains and set an amber fill
A student selects only cell B2 and adds a conditional formatting rule. Which cells does the rule format?
Only cell B2
A Text contains rule looks for Pass and fills the cell green. Which cell will turn green?
The cell showing Passed
A Cost column holds values from 5 to 200. Which rule colours only the cells whose value is above 100?
Is greater than 100
Which term describes a feature that automatically changes how cells look when their values meet a rule you set?
Conditional formatting
What is meant by the Text contains rule?
A rule that formats any cell whose text includes the given word
What is the name for a rule that formats a cell only when its value is exactly the same as the number you set?
Is equal to
What does it mean that the Text contains rule is not case sensitive?
It matches pass, Pass and PASS in exactly the same way
What does the Fill colour setting do when you build a conditional formatting rule?
It sets the background colour applied to cells that match the rule
Which term describes formatting that stays the same whatever value is stored in a cell?
Normal formatting
Which setting decides what a conditional formatting rule checks for, such as text or a number?
The type of rule you choose
In conditional formatting, what is a rule?
A test that a cell must meet for formatting to appear
In a self-marking quiz, an IF statement puts the word Correct in a cell. Which part colours that cell green?
A conditional formatting rule that looks for Correct
A coach records how many laps each player runs each week and colours every value over 20 in green. What is the main benefit of this rule?
Strong weeks stand out so patterns are easy to spot