Area values that you transfer to Excel / Tables arrive as plain numbers, without a unit. If you simply type the unit along with the value (25 m²), Excel / Tables treats the entry as text – sums, filters and charts will no longer work.
The clean solution is a custom number format. It displays "m²" while the cell value stays a real number.
How to do it
- Select the cells you want to format (in Tables, you only need to select the first row of data; Tables will handle the rest for you after you click “Reload” or “Format -> Refresh”).
- Press Ctrl + 1 to open the Format Cells dialog.
- Go to the Number tab.
- Select Custom from the category list on the left.
Enter the format code you need in the Type field, for example:
0.00 "m²"
- Confirm with OK.
The cell now shows 25.00 m², while the formula bar still shows 25. Formulas such as =SUM(A1:A20) keep working as usual.
Common format codes
| Format code | Input | Display |
|---|---|---|
0 "m²" | 25 | 25 m² |
0.00 "m²" | 25.5 | 25.50 m² |
#,##0.00 "m²" | 1234.5 | 1,234.50 m² |
0.00 "m³" | 12.3 | 12.30 m³ |
The quotation marks matter – they mark the text that should be displayed as it is. The space in front of them creates the gap between number and unit.
Typing the "²" character
- Any keyboard:
Alt+0178using the numeric keypad (³ =Alt+0179) - German keyboard layout:
AltGr+2 - Or simply copy the character from this article: ²
Applying the format to more columns
Use the Format Painter brush on the Home tab. Alternatively, copy a formatted cell and apply it via Home > Paste > Paste Special > Formats.
Good to know
- The format only changes the display. The underlying value stays numeric and can still be processed further.
- The format is stored per cell and is kept in the XLSX file.
- Exporting to CSV drops the formatting, because CSV stores raw values only.
Comments
0 comments
Please sign in to leave a comment.