Be able to display either formulae or values

Topic 20 – Spreadsheets

Learning objective

Be able to display either the formula or the calculated value in a spreadsheet, to understand when each view is required and how it supports the creation, testing and presentation of data models (syllabus 20.1 & 20.2).

Why it matters (linked to AO3 – Evaluate solutions)

  • Confirms that the intended calculation has been entered – essential for auditing.

  • Helps locate and correct common errors such as wrong cell references, missing $ signs or incorrect operator precedence.

  • Allows you to demonstrate the logic behind a result when presenting to others (design & test phases of the Systems Life‑Cycle).

  • Ensures the final output is clean and professional for reports, printing or further analysis.

When to display formulas

  • Checking that the correct calculation is present (design stage).

  • Teaching or demonstrating how a result is derived.

  • Auditing a worksheet for errors, inconsistencies or hidden cells.

  • Documenting the data model for the Systems Life‑Cycle (design & test phases).

When to display values

  • Presenting final results to an audience or client.

  • Using the data in charts, pivot tables or further calculations.

  • Printing, exporting or sharing the spreadsheet.

  • Protecting confidential business logic – hide formulas that reveal proprietary calculations.

How to toggle the view

Microsoft Excel (Windows)

  1. Select the worksheet you want to inspect.

  2. Go to the Formulas tab on the ribbon.

  3. Click Show Formulas or press Ctrl + ` (grave accent).

  4. All cells that contain a formula now display the formula text (e.g., =A2+B2).

  5. Press the same button or shortcut again to return to the normal value view.

Google Sheets

  1. Open the spreadsheet.

  2. From the menu choose View > Show formulas.

  3. The worksheet switches to formula view; repeat the command to hide them again.

Key spreadsheet concepts (syllabus 20.1)

Order of operations

ExpressionResultExplanation
=A2*B2+C2Product first, then additionMultiplication precedes addition.
=A2*(B2+C2)Sum first, then productParentheses force the addition to be evaluated first.

Absolute vs. relative references

  • Relative: A2 – changes when the formula is copied.

  • Absolute: \$A\$2 – never changes.

  • Mixed: A\$2 (row absolute) or \$A2 (column absolute).

Named ranges

  1. Select the cells you want to name (e.g., B2:B10).

  2. In Excel: Formulas > Define Name. In Google Sheets: Data > Named ranges.

  3. Enter a meaningful name, e.g., UnitPrice.

  4. Use the name in a formula: =SUM(Quantity*UnitPrice).

External data sources (syllabus 20.1)

  • Excel: Data > Get Data > From Text/CSV – import a .csv file.

  • Google Sheets: File > Import > Upload – choose “Insert new sheet(s)”.

  • Once imported, treat the data like any other range; you can name the range for easier reference.

Essential functions (syllabus 20.1)

FunctionPurposeExample formula
SUM(range)Adds all numbers in a range.=SUM(C2:C10)
AVERAGE(range)Mean of a range.=AVERAGE(D2:D10)
MAX(range)Largest value.=MAX(E2:E10)
MIN(range)Smallest value.=MIN(E2:E10)
IF(logicaltest, valueiftrue, valueif_false)Conditional output.=IF(A2>100,"High","Low")
LOOKUP(lookupvalue, lookupvector, result_vector)Simple lookup.=LOOKUP(F2,\$A\$2:\$A\$10,\$B\$2:\$B\$10)
VLOOKUP(lookupvalue, tablearray, colindex, [rangelookup])Vertical table lookup.=VLOOKUP(G2,\$A\$2:\$D\$20,4,FALSE)
HLOOKUP(lookupvalue, tablearray, rowindex, [rangelookup])Horizontal table lookup.=HLOOKUP(H2,\$A\$1:\$Z\$5,3,TRUE)
XLOOKUP(lookupvalue, lookuparray, returnarray, [ifnotfound], [matchmode], [search_mode])Modern, flexible lookup (Excel 365/2021).=XLOOKUP(I2,\$A\$2:\$A\$20,\$B\$2:\$B\$20,"Not found")
COUNTIF(range, criteria)Counts cells meeting a condition.=COUNTIF(C2:C20,">=50")
CONCATENATE(text1, text2, …) or TEXTJOIN(delimiter, ignore_empty, …)Join text strings.=CONCATENATE(A2," – ",B2)

Nested functions – building more powerful formulas

Example: calculate a commission that is 5 % of sales, but only if sales exceed £1 000; otherwise return £0.

=IF(SUM(C2:C10)>1000,0.05*SUM(C2:C10),0)

Here SUM is nested inside IF. The same principle can be applied to combine VLOOKUP with IFERROR:

=IFERROR(VLOOKUP(A2,ProductTable,3,FALSE),"Not found")

Manipulating data (syllabus 20.2)

  • Sorting – Data > Sort range… (choose column, ascending/descending).

  • Filtering – Data > Create a filter; use the drop‑down arrows to display only rows that meet criteria.

  • Searching – Use Ctrl + F or the “Find & Replace” dialog to locate specific values or formulas.

  • Removing duplicates – Data > Remove duplicates (helps clean imported data).

Practical example – Sales‑price‑quantity model (expanded)

CellValues view (default)Formulas view
A1Quantity Item 1Quantity Item 1
B1Unit price Item 1Unit price Item 1
C1150=A2*B2
A2Quantity Item 2Quantity Item 2
B2Unit price Item 2Unit price Item 2
C2200=A3*B3
A3Quantity Item 3Quantity Item 3
B3Unit price Item 3Unit price Item 3
C3120=A4*B4
C4470=SUM(C1:C3)
D4Average price=AVERAGE(C1:C3)
E4High/Low flag=IF(C4>400,"High","Low")

Switch to Formulas view to verify each calculation; switch back to Values view for reporting or charting.

Common errors when toggling views

  • Printing formulas by mistake: ensure “Show Formulas” is off before printing or exporting.

  • Accidental editing while in formula view: a stray keystroke can alter the underlying calculation.

  • Absolute/relative reference mix‑ups: missing $ can cause wrong totals when copying – always double‑check after toggling.

  • Hidden rows/columns: they remain hidden in both views; un‑hide them before a full audit.

  • Incorrect operator precedence: use parentheses to enforce the intended order.

Linking this skill to other syllabus sections

Syllabus codeRelated skillConnection to “display formulae/value”
20.1Create a data model (formulae, functions, cell‑referencing)Formula view lets students verify the model they have built.
20.2Manipulate data (sorting, filtering, searching, removing duplicates)After sorting or filtering, showing formulas ensures no reference has been broken.
20.3Present data (formatting, conditional formatting, printing)Switch back to values before applying number formats or printing.
11‑16File management, graphics, layout, chartsCharts use underlying values; hide formulas for a clean visual.
7Systems life‑cycleDesign documentation includes formula view; testing uses value view.
8‑10, 12‑13e‑Safety & data protectionHide formulas that could reveal proprietary calculations before sharing.

Quick “Topic Checklist” for teachers

Use this checklist to confirm coverage of the ICT 0417 syllabus when planning spreadsheet lessons.

Syllabus sectionCovered in this note?Notes / extra resources
20.1 – Create a data modelYesIncludes order of operations, absolute/relative references, named ranges, external data, essential functions, nested formulas.
20.2 – Manipulate dataYesSorting, filtering, searching, removing duplicates – brief tip sheet added.
20.3 – Present dataYesReminder to revert to values before formatting/printing.
21 – Website authoringNoLink to separate note on “Show HTML source vs. rendered view”.
11‑16 – Images, layout, charts, etc.PartialProvide “Further reading” list on chart creation.
7 – Systems life‑cycleYesDesign (formula view) and test (value view) connection highlighted.
8‑10, 12‑13 – e‑Safety & data protectionYese‑Safety reminder included.

e‑Safety reminder

Before exporting or sharing a workbook:

  • Hide any formulas that reveal confidential business logic.

  • Check that personal data (names, IDs, addresses) are not stored in hidden cells.

  • Save the file in a non‑editable format (e.g., PDF) if the recipient only needs to view the results.

  • Consider password‑protecting the workbook if it contains sensitive calculations.

Key points to remember

  • Excel shortcut: Ctrl + ` toggles formula/value view.

  • Google Sheets: View > Show formulas toggles the same.

  • Only cells containing a formula are affected; plain numbers or text stay unchanged.

  • Switching views never changes the underlying data – it only changes what is displayed.

  • Always return to the Values view before formatting, printing or sharing.

Suggested diagram: Screenshot of the Excel ribbon highlighting the Show Formulas button (Formulas tab) and the Google Sheets menu path View > Show formulas.