This tutorial demonstrates how to add units to numbers in Excel and Google Sheets.
Add Units to Numbers
If you have numbers presenting some kind of dimensions (length, width, weight, etc.), you can easily add units to them. Say you have the following data set with Box Weight in Column E and Box Height in Column F and want to show kilograms after numbers (kg) for box weight, and for box height, meters (m).
Note: Before adding units, make sure all values are measured in the same units. In this example, all values in Column E must be in kilograms, and all values in Column F have to be in meters. If any value is in a different unit, you have to convert it first or type in units manually.
Custom Number Format
Display units by creating a custom number format.
- Select the range of cells where you want to add units (E2:E10), and in the Ribbon (2) go to the Home tab, and (3) click the Format Cells icon.
- In the Format Cells window, (1) choose Custom category, (2) enter 0.0 “kg” (or any other unit you need) in the type box, and (3) click OK.
As a result, all values in the selected range now have the unit “kg” after the number.
Now you can repeat the same steps for Column F (Box Height). Just enter 0.00 “m” for meters. Finally, you have added units to numbers in the data set.
Add Units With Formula
Another option for adding units to numbers is to create helper columns and add the formula to join a unit.
- In the new column (G), cell G2, enter the formula:
- Drag the formula down the column.
- Repeat the same process for Column H, but in cell H2, enter the formula:
As a result, helper Columns G and H now have values from Columns E and F with units.
Add Units to Numbers in Google Sheets
In Google Sheets, you can use formulas in exactly the same way as Excel. To set a custom number format, follow these steps:
- Select a range of cells with numbers (E2:E10), and in the Menu, go to Format > Number > More Formats > Custom number format.
- In the Custom number formats box, enter 0.0 “kg”, and click Apply.
- Now repeat the same steps for Column F, with 0.0 “m”. As a result, values in Columns E and F are now formatted with units.