Skip to the page
Year 10 · ICT · Summer 1 · Spreadsheets

Named ranges and formatting

PracticalPresent ▶

What this lesson covers

20.1b Named ranges and functions

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

20.3 Display, format and print

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

You will learn to

  • Create a named range and use it in formulae.
  • Format numbers as currency and percentages, with a set number of decimal places.
  • Explain that a number format changes how a value looks, not the value itself.
  • Apply conditional formatting with a rule, such as 'over budget'.

Success criteria

  • I can name the price list and use the name in a VLOOKUP.
  • I can format a column as euros with 2 decimal places.
  • I can make cells turn red by themselves when they go over budget.
The syllabus points for these sections
  • Create and use named cells and named ranges.
  • Explain what a function and an argument are, and nest one function inside another.
  • Use SUM, AVERAGE, MAX, MIN, INT, ROUND, ROUNDUP and ROUNDDOWN.
  • Use COUNT, COUNTA and COUNTIF, including comparisons and wildcards.
  • Recognise the Spanish names of these functions and why the exam uses English ones.
  • Switch between formulae view and values view, and make every cell fully visible.
  • Format cells: bold, italic, shading, borders, alignment, wrap text, hide and show.
  • Apply number formats and conditional formatting.
  • Set up the page to print formulae and values clearly.
  • Avoid the common ways spreadsheet marks are lost.

Key words

Say each word, then read what it means. Every one is in the glossary too.

Cell
One box in the grid, where a column and a row meet.
Cell reference
The address of a cell: column letter then row number, such as C3.
Range
A block of cells written first:last, such as C3:C8.
Formula
A calculation in a cell that starts with =.
Function
A ready-made formula with a name, such as SUM or VLOOKUP.
Argument
One input to a function, written inside its brackets and separated by commas.
Nested function
A function used inside another function, such as ROUND(AVERAGE(...),2).
Absolute reference
A reference like $I$7 that stays fixed when the formula is copied.
Named range
A name, such as TripCost, given to a cell or range and used in formulae.
Lookup
A function that finds a value in a table and returns a matching value.
Exact match
A lookup that only accepts the identical value (FALSE in VLOOKUP).
Criteria
The condition COUNTIF uses to decide which cells to count.
Wildcard
A symbol standing for unknown characters: * for any number, ? for exactly one.
Conditional formatting
Formatting that changes automatically when a cell meets a rule.
Formulae view
A display mode that shows the formulae in cells instead of their results.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

20.1b Named ranges and functions

Named cells and named ranges

You can give a cell or a range a name, such as TripCost for I7 or MealTable for H3:I5.

A name works like an absolute reference. It does not move when the formula is copied.

Names make formulae easier to read and check. =TripCost+E3-C3 tells you what it does.

To create one, select the cell or range, type the name in the name box (left of the formula bar) and press Enter. Most spreadsheets also have a define name option.

e.g.Example
I7 named TripCost. H3:I5 named MealTable. F3: =TripCost+E3-C3 → 40.00 + 6.50 − 46.50 = 0.00 E3: =VLOOKUP(D3,MealTable,2,FALSE) → 6.50 Copied down, both still point at I7 and H3:I5.
!Watch out

Names cannot contain spaces or start with a number. Use exactly the name the question gives. A different spelling is a different name and loses the mark.

Functions, arguments and Spanish spreadsheets

A function is a ready-made formula with a name, like SUM. It goes after the = sign, and its inputs go in brackets.

Each input is an argument. Arguments are separated by commas. =ROUND(C10,1) has two arguments: C10 and 1.

A function can go inside another one. This is a nested function: =ROUND(AVERAGE(C3:C8),2) gives 31.83.

In Spanish Excel, functions have Spanish names. Arguments are separated by ; because the comma is the decimal sign. The exam uses English names, so learn those.

English (exam)Spanish Excel
SUMSUMA
AVERAGEPROMEDIO
MAX / MINMAX / MIN
INTENTERO
ROUNDREDONDEAR
ROUNDUPREDONDEAR.MAS
ROUNDDOWNREDONDEAR.MENOS
COUNTCONTAR
COUNTACONTARA
COUNTIFCONTAR.SI
LOOKUPBUSCAR
VLOOKUPBUSCARV
HLOOKUPBUSCARH
XLOOKUPBUSCARX
IFSI
TRUE / FALSEVERDADERO / FALSO
e.g.Example
English: =VLOOKUP(D3,$H$3:$I$5,2,FALSE) Spanish Excel: =BUSCARV(D3;$H$3:$I$5;2;FALSO) English: =ROUND(31.8333,1) Spanish Excel: =REDONDEAR(31,8333;1)
!Watch out

If the exam computer runs a Spanish spreadsheet, the printout shows Spanish names. Ask the exams officer early whether the software can be set to English. In any written answer, use English names and commas.

SUM, AVERAGE, MAX and MIN

Each takes one or more arguments: ranges, cells or numbers, separated by commas.

They use only numbers. They ignore empty cells and text. A 0 is a number, so it is included.

=SUM(C3:C8,E3:E8) adds two ranges: 191.00 + 40.00 = 231.00.

Try the toy: it holds a café order. Click D2, change the quantity to 5 in the formula bar and press Enter. Watch every result that depends on it update. Switch on Show formulae to see what is typed in each cell.

FunctionWhat it doesExampleResult
SUMadds all the numbers`=SUM(C3:C8)`191.00
AVERAGEthe mean: total ÷ how many numbers`=AVERAGE(C3:C8)`31.83
MAXthe largest number`=MAX(C3:C8)`46.50
MINthe smallest number`=MIN(C3:C8)`0.00
e.g.Example
=AVERAGE(C3:C8) Step 1: add 46.50 + 30.00 + 46.00 + 0.00 + 45.00 + 23.50 = 191.00 Step 2: count the numbers: 6 Step 3: 191.00 ÷ 6 = 31.8333…, shown as 31.83 with 2 decimal places
!Watch out

AVERAGE ignores a blank cell but counts a 0. If David's cell were empty instead of 0.00, the average would be 191.00 ÷ 5 = 38.20.

There is something to try for this step in the IGCSE library.

INT and ROUND

INT (integer) has one argument: a number. It removes the decimal part, rounding down to a whole number.

ROUND has two arguments: the number, then how many decimal places to keep. If the next digit is 5 or more it rounds up. Below 5 it rounds down.

A negative number of places rounds to tens, hundreds and so on.

Formula (C10 = 31.8333…)ResultWhy
`=INT(C10)`31the .8333 is removed
`=ROUND(C10,0)`32next digit 8 is 5 or more, so round up
`=ROUND(C10,1)`31.8next digit 3 is below 5, so round down
`=ROUND(C10,2)`31.83next digit 3 is below 5
`=ROUND(C10,-1)`30rounds to the nearest 10
?Why?

ROUND changes the value stored in the cell. Formatting to 2 decimal places only changes what you see. A cell formatted to show 32 still holds 31.8333 and gives different totals later.

!Watch out

INT always goes down, even for negative numbers. =INT(-2.5) gives −3, not −2.

ROUNDUP and ROUNDDOWN

Both take the same two arguments as ROUND: the number, then the number of decimal places.

ROUNDUP always rounds away from zero. ROUNDDOWN always rounds towards zero. The next digit does not matter.

Use ROUNDUP when you must have enough: buses, boxes, tickets. Use ROUNDDOWN when you cannot go over: how many items you can afford.

Formula (C10 = 31.8333…)Result
`=ROUNDUP(C10,1)`31.9
`=ROUNDDOWN(C10,1)`31.8
`=ROUNDUP(C10,0)`32
`=ROUNDDOWN(C10,0)`31
e.g.Example
130 pupils go on the trip. Each bus has 55 seats. 130 ÷ 55 = 2.36… =ROUND(130/55,0) → 2 buses. 2 × 55 = 110 seats, so 20 pupils are left behind. =ROUNDUP(130/55,0) → 3 buses. Everyone travels. Each pupil has €20 for souvenirs at €3.50 each: 20 ÷ 3.5 = 5.71… =ROUNDDOWN(20/3.5,0) → 5 souvenirs. A 6th would cost €21.

COUNT and COUNTA

COUNT counts the cells in a range that hold numbers.

COUNTA counts the cells that are not empty: numbers, text, anything.

Both take one or more ranges as arguments. Neither counts empty cells.

FormulaResultWhy
`=COUNT(C3:C8)`6six amounts (0.00 is a number)
`=COUNT(A3:A8)`0names are text, not numbers
`=COUNTA(A3:A8)`6six names
`=COUNTA(A3:A10)`8also counts the words Total and Average
?Why?

To count pupils, count names with COUNTA. To count how many payments were entered, count numbers with COUNT.

!Watch out

Choose the range carefully. Including a heading or the total row changes the count.

COUNTIF

COUNTIF counts the cells in a range that meet one condition.

Argument 1 is the range to check. Argument 2 is the criteria: the condition, in double quotes.

Criteria can be a value ("10A"), a comparison (">0"), or text with wildcards. * means any number of characters. ? means exactly one character.

To use a cell in the criteria, join it on with &: ">"&I7 means "greater than the value in I7".

FormulaResultCells counted
`=COUNTIF(B3:B8,"10A")`2Ana, Carmen
`=COUNTIF(F3:F8,">0")`4Bruno, David, Elena, Félix
`=COUNTIF(C3:C8,">=45")`3Ana, Carmen, Elena
`=COUNTIF(G3:G8,"Paid")`2Ana, Carmen
`=COUNTIF(A3:A8,"*a")`2Ana, Elena (names ending in a)
`=COUNTIF(F3:F8,">"&I7)`1David (46.50 is more than 40.00)
!Watch out

A comparison without quotes (>0 instead of ">0") is an error. Criteria are not case-sensitive, so "paid" also matches Paid.

20.3 Display, format and print

Show formulae, not only values

Normally cells show values: the results. Formulae view shows what is typed in each cell.

Most spreadsheets have a show formulas option, often on the formulas tab or in the view settings.

In formulae view, widen columns until every formula is fully visible. A cut-off formula cannot be marked.

Adjust row heights and column widths, and wrap text in long headings, so nothing is hidden. Hide or show rows and columns only when the task says.

E3   =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
E4   =VLOOKUP(D4,$H$3:$I$5,2,FALSE)
...  (same pattern down to E8)
F3   =$I$7+E3-C3
F4   =$I$7+E4-C4
...  (same pattern down to F8)
G3   =IF(F3=0,"Paid",IF(F3<=10,"Nearly","Chase"))
...  (same pattern down to G8)
C9   =SUM(C3:C8)
E9   =SUM(E3:E8)
F9   =SUM(F3:F8)
C10  =AVERAGE(C3:C8)
?Why?

In formulae view numbers lose their formatting, so 46.50 shows as 46.5. That is normal. The examiner wants to see the formulae, not the formatting.

Format cells: text, shading, borders, alignment

Typical tasks: make headings bold or italic, add shading (a fill colour), add borders, and set alignment.

Align text to the left and numbers to the right, so the decimal points line up.

Merge and centre a title across the top of the data, for example A1:G1.

Apply exactly what is asked, to exactly the cells named.

?Why?

Consistent formatting is a house style (topic 14). Examiners check that every cell in the stated range has the format, not only the first one.

Number formats

A number format changes how a value looks. It does not change the value.

Decimal places: 31.8333 shown to 2 decimal places is 31.83. Currency adds a symbol: €31.83, or 31,83 € in Spanish style.

Percentage: the value is shown multiplied by 100, with a % sign. 0.25 shows as 25%.

Use the symbol asked for: euro (€), pound (£) or dollar ($). The $ in a currency format has nothing to do with the $ in an absolute reference.

e.g.Example
Share of the cost Bruno has paid: =C4/($I$7+E4) → 30.00 ÷ (40.00 + 7.50) = 30.00 ÷ 47.50 = 0.6315… Percentage format, 0 decimal places → 63% Percentage format, 1 decimal place → 63.2%
!Watch out

Do not multiply by 100 and also apply the percentage format. You would get 6315.8%. The format does the × 100 for you.

Conditional formatting

Conditional formatting changes a cell's format automatically when its value meets a rule.

Select the range, choose the rule (for example "greater than"), type the value, then pick the format.

Because it is a rule, the format updates when the data changes. Colour added by hand does not.

e.g.Example
Rule 1: F3:F8, cell value greater than 20 → red fill. David (46.50) and Félix (22.50) turn red. Rule 2: G3:G8, cell value equal to Paid → green text. Ana and Carmen turn green. If Félix pays €5 more, F8 becomes 17.50 and the red disappears by itself.
!Watch out

Apply the rule to the whole range asked (F3:F8), not to one cell. "Greater than 20" does not include 20. Use "greater than or equal to" if 20 should count.

Page layout for printing

Set the orientation: portrait (tall) or landscape (wide). Formulae printouts are wide, so landscape usually fits better.

Use fit to page to scale the printout to 1 page wide, or to the number of pages the task says. Set a print area if only part of the sheet must print.

For formulae printouts, switch on gridlines and row and column headings. Then the examiner can see which cell holds each formula.

Add a header or footer with your name, centre number and candidate number. Check the print preview before you print.

?Why?

A formula like =$I$7+E3-C3 means nothing on its own. With headings, the examiner sees it sits in F3 and can check what I7 holds.

!Watch out

"Fit to 1 page wide" is not "fit to 1 page". Squeezing a long sheet onto one page can make the text too small to read.

How spreadsheet marks are lost

Most lost marks come from the evidence, not from wrong thinking.

Before each printout, ask: is everything visible, is it on the right number of pages, and is my name on it?

MistakeHow to avoid it
Formula cut off in the printoutwiden columns in formulae view
No row and column headings on a formulae printoutswitch them on in page setup
Typed number instead of a formula or cell referencealways point to the cell that holds the value
Missing $, so copied formulae breakcheck the last cell you copied into
Wrong range (includes a heading or total)click the formula and check the coloured outline
Sorting one column on its ownselect the whole list before sorting
Named range spelt differently from the questioncopy the name exactly
No name, centre number or candidate numberheader or footer on every printout

Study it

Open the study page and go one step at a time.

ICT topic 20

Spreadsheets

Step by step, with exam questions and a quiz.

ICT topic 16

Graphs and charts

Step by step, with exam questions and a quiz.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.