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

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)

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)

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

Quick “Topic Checklist” for teachers

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

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.