8.3 Entering and Formatting Data in Spreadsheet Tasks

8.3 Entering and Formatting Data in Spreadsheet Tasks

Key Concept: Learning outcome 4 is worth 10% of PCTN. It tests two practical spreadsheet skills: entering and formatting data (this section) and writing formulas (Section 8.4). AAT publishes an exact list of the formatting skills it expects: bold, italics, underline, merge, fill colour, wrap text, text size and borders for cells, plus thousand separators, accountancy format, percentages and decimals for numbers. It also covers copying and pasting cells and inserting rows and columns.

AAT's review of the October 2023 examiner reports stresses that the assessment uses a spreadsheet style of question rather than spreadsheet software itself. If you have only practised in Excel or Google Sheets, the on-screen tool will look similar but not identical, so use AAT's familiarisation resource on the learning portal before the exam. The skills themselves are the same.

---

The PCTN Formatting Checklist

The specification (learning outcome 4.1) lists these skills:

---

Number Formats in Detail

Number formatting changes how a value looks, not the value itself. This matters in three ways.

1. Decimals are display only. If cell B8 holds 10.6667 and you format it to two decimal places, it shows 10.67, but any formula that uses B8 still uses 10.6667. This is why a spreadsheet total can look a penny different from a hand calculation of the rounded figures.
2. Percentages multiply the display by 100. A cell holding 0.08 formatted as a percentage shows 8%. If a formula such as =D5/B5 returns 0.08, format that cell as a percentage rather than multiplying by 100.
3. Thousand separators and accountancy format aid reading. A figure such as 1250000 is easy to misread, while 1,250,000 is not. Accountancy format lines up currency symbols and decimal points in a column. In common spreadsheet software it also displays a zero as a dash.

---

Copy and Paste: What PCTN Means

The specification says copy and paste cells, not paste values or paste link. The three options behave very differently:

So when a task asks you to complete a column of variances, write the formula once, then copy and paste it down. Each row will calculate its own variance.

---

Inserting Rows and Columns

Inserting a row or column moves the existing data rather than overwriting it, and formulas update automatically. If =SUM(B4:B7) totals four cost lines and you insert a new row inside that range (for example between rows 5 and 6), the formula expands to =SUM(B4:B8). Check totals after inserting, because a row added outside the range (below row 7 in this example) will not be included automatically.

---

Worked Example: Presenting a Budget Report

A PCTN-style task might give you the raw figures for Pennine Pies Ltd's March budget report (Section 7.3) and ask you to present them. A sensible sequence is:

1. Enter "Pennine Pies Ltd: budget report for March" in cell A1, merge A1:F1, make it bold and increase the text size.
2. Enter the column headings in row 3 (Line, Budget £, Actual £, Variance £, A/F, Variance %), make them bold and apply wrap text so the longer headings fit.
3. Apply thousand separators with no decimals to the £ columns.
4. Enter the variance formula in D4, then copy and paste it to the rows below (adjusting the order of subtraction row by row as Section 7.3 explains).
5. Format column F as a percentage with one decimal place.
6. Put a border above the total costs row and fill the significant variance rows with colour.
7. If you realise a cost line is missing, insert a row in the correct place rather than overtyping.

---

Common Exam Traps

• Entering data in the wrong cell. If the task says B6, use B6. Spreadsheet tasks are marked cell by cell.
• Typing words into number cells. Enter 28000 and apply a number format. An entry such as "28000 pounds" or "£28k" is stored as text, and any formula that uses that cell returns a #VALUE! error.
• Multiplying by 100 and formatting as a percentage. Doing both displays 800% instead of 8%.
• Using paste values when the task needs a working formula. The answer may look right, but it will not update, and the task specifically asks for copy and paste.