How to Add Units to Numbers in Excel & Google Sheets

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.

1. Select the range of cells where you want to add units (E2:E10), and in the Ribbon go to the Home tab, and click the Format Cells icon.

 

 

2. In the Format Cells window, choose Custom category, enter 0.0 “kg” (or any other unit you need) in the Type box, and 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.

1. In the new column (G), cell G2, enter the formula:

=E2&" kg"

 

 

2. Drag the formula down the column.

 

 

3. Repeat the same process for Column H, but in cell H2, enter the formula:

=F2&" m"

 

 

As a result, helper Columns G and H now have values from Columns E and F with units.

Note: If you want to delete columns without units, first copy and paste as values Columns G and H, to prevent a #REF! error.

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:

1. Select a range of cells with numbers (E2:E10), and in the Menu, go to Format > Number > More Formats > Custom number format.

 

 

2. In the Custom number formats box, enter 0.0 “kg”, and click Apply.

 

 

3. 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.