Units such as RM, kg or % should be added with a cell format, not typed into the cell. That way the cell stays a real number and still works in formulas, charts and sorting. In practical tasks this appears in instructions like “show currency with two decimal places”.
This lesson sits after choosing a chart in the module on spreadsheet modelling and charts.
What is the difference between a value and its display?
A cell has two parts: the stored value and the display format. The stored value is what formulas use. The format only decides how it looks.
If you type 12.5kg, the spreadsheet cannot read it as a number, so it keeps it as text. If you type 12.5 and apply a format that shows “kg” after the number, the cell still holds 12.5 and still adds up.
How to format correctly, step by step
- Type only the number, with no unit, no RM sign and no percent sign unless the program converts it for you.
- Select the cells that share the same unit.
- Apply the right format: currency, number with decimal places, percentage, or a custom format for a unit such as kg.
- Put the unit in the column heading when a custom format is not required, for example “Weight (kg)”.
- Check alignment: numbers align right by default. A left-aligned entry may be text.
Worked example
A pantry list (invented) records weights: flour 12.5, rice 8 and sugar 4.25, all in kg.
Correct entry: B2 = 12.5, B3 = 8, B4 = 4.25, with a format showing two decimal places and the heading “Weight (kg)”. Then =SUM(B2:B4) gives 12.5 + 8 + 4.25 = 24.75.
Currency check: if B6 holds 1250 and is formatted as currency with two decimals, it displays as RM 1,250.00 while the stored value is still 1250.
Rounding check: B7 holds 2.678 and is formatted to two decimals, so it displays 2.68. The formula =B7*10 gives 26.78, because the stored value is still 2.678, not 2.68.
The mistake to watch for
A common slip is to type the unit into the cell.
Mistaken entry: B3 = 8kg (typed as text)
=SUM(B2:B4) now gives 12.5 + 4.25 = 16.75, because the text cell is skipped. The total looks plausible but is 8 too small.
The correction is to retype B3 as 8 and use a format or heading for the unit. As a quick test, compare the total against a hand sum of the visible values.
A second slip is typing 52 into a cell that is already formatted as a percentage and expecting 52%. Some programs convert it, others show 5200%, so always check what is displayed.
Check yourself
1. B2 shows 0.075 and is formatted as a percentage with one decimal place. What is displayed?
Show answer
7.5%. 0.075 × 100 = 7.5.
2. A cell holds 3.456 and is formatted to two decimal places. What is displayed, and what does =B2*100 give?
Show answer
It displays 3.46. =B2*100 gives 345.6, because the stored value is still 3.456.
3. Prices 4.50, 3.00 and “2.50RM” (typed as text) are in B2:B4. What does =SUM(B2:B4) give, and how do you fix it?
Show answer
It gives 4.50 + 3.00 = 7.50, because the text is skipped. Retype the third cell as 2.50 and format the column as currency. The total then becomes 10.00.
Where this leads next
Once values are clean numbers, you can test the rules in the model properly: test a model with boundary inputs. The ICT practical task and evidence checker includes a prompt to confirm that units are formatted, not typed.
When the same type of slip returns in different tasks, a teacher in online one-to-one ICT tuition can build a short routine of checks that fits how you work.