SQA Higher Administration and IT Spreadsheets: a complete overview of functions and formulae, managing and linking workbooks, and charts and printing

What spreadsheets actually demand at Higher

Spreadsheets are one of the IT applications you must use confidently in Higher Administration and IT. The SQA expects a range of functions, formulae and features, some of them complex, used to process and analyse data accurately, plus the ability to manage larger workbooks, link and move data between applications, and present and print results well. You meet this in two ways: in the assignment you build a working spreadsheet, and in the question paper you explain what a function does, justify a chart choice, or describe a print option.

This guide walks through the whole area, then sets out how it is examined. Each topic has a matching dot-point page with practice questions; this overview ties them together.

Functions and formulae

The calculation backbone is cell references and functions. A relative reference (B2) changes when copied; an absolute reference ($B$2) stays fixed, needed for a single rate used by every row. Logical functions, IF and nested IF, make a sheet show different results depending on the data. Conditional functions, COUNTIF and SUMIF, count or total the cells that meet a condition. Lookup functions, mainly VLOOKUP, pull matching data (a price, a name) from a table. Statistical functions (SUM, AVERAGE, MAX, MIN, COUNT, ROUND) summarise figures, while named ranges make formulae clearer and conditional formatting highlights cells that meet a rule. Choosing the right function, and the right reference type, is what makes a spreadsheet accurate and efficient.

Managing and linking workbooks

Real spreadsheets span many sheets and files. You must manage worksheets and workbooks: naming and colour-coding tabs, moving, copying, hiding and protecting sheets, and using 3-D references across sheets. Consolidation combines figures from several sheets onto a summary sheet with linked formulae, so totals appear in one place and update automatically. Dynamic links connect data across applications (a spreadsheet table linked into a report) so it stays current, with the trade-off that the link breaks if the source moves. Importing and exporting (CSV, text, database) moves data between systems without re-keying it.

Charts and printing

Finally, data must be presented and output clearly. Charts turn figures into a visual: a pie for proportions, a column or bar to compare, a line for a trend, each with a title, labels and legend. Printing has options that matter: the formulae view (with gridlines and headings) to check calculations, fit to page to make a large sheet readable, print selection to print only what is needed, and orientation and page breaks for layout.

How spreadsheets are examined

A typical SQA profile:

• Explain or write a function. What IF, COUNTIF, SUMIF or VLOOKUP does, or a simple formula.
• Justify a choice. Why an absolute reference, or which chart type suits the data.
• Describe a feature. Consolidation, a dynamic link (with an advantage and disadvantage), or a print option.
• Apply it. The assignment requires you to actually build and format the spreadsheet correctly.

Check your knowledge

A mix of recall and explanation questions covering spreadsheets. Attempt them, then check against the solutions.

1. What does an absolute cell reference (for example $B$2) do when a formula is copied? (2 marks)
2. Write an IF formula showing "Pass" for 50 or more in C2, otherwise "Fail". (2 marks)
3. What does the COUNTIF function do? (1 mark)
4. What does VLOOKUP do? (2 marks)
5. What is meant by consolidating data? (2 marks)
6. Which chart type suits a trend over time? (1 mark)