Numbers, text and formats
7 minutes · 4 steps · free, no sign-up
Sheets treats numbers and text very differently, and mixing them up is the cause of many "why won't it add up?" moments. In this lesson you will spot a number that is really text, fix it, and then change how numbers are displayed without changing their value.
- Tell a number from text by how it lines up
- Fix a number that was typed as text
- Add up a column with SUM
- Format values as currency and as a percentage
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceWhat you will use
=SUM(B2:B4) adds the numbers in a range (text is ignored)
$ button currency format
% button percentage format
0.15 as % shows as 15%The key ideasSummary: Numbers, text and formats
A real number is stored as a number and lines up on the right of its cell. Text lines up on the left. If a value that should be a number sits on the left, Sheets thinks it is text, and functions such as SUM will skip it without any warning.
Formatting only changes how a number is displayed. A cell showing 15% still holds 0.15, and a cell showing a currency still holds the plain number. That is why you can format first and calculate afterwards.
Watch out forCommon mistakes
- Typing units into the cell.
2 poundsor5kgis text. Type the plain number and put the unit in the heading instead. - Typing 15 and expecting 15%. A percentage of fifteen per cent is the number
0.15. If you format15as a percentage you get1500%. Type the percent sign yourself, or type 0.15 and then format. - Trusting what a cell looks like. Formatting can hide the real value. Click the cell and look in the formula bar to see what is actually stored.
Next stepTry it in the real Google Sheets
In Google Sheets the format menu is Format → Number, and the toolbar has quick buttons for currency, percent and decimal places. Dates are numbers too, which is why they can be added and subtracted. You will meet dates in the Intermediate level.