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 final data

 

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

 

add units to numbers initial data

 

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.

 

add units to numbers 1

 

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

 

add units to numbers 2

 

As a result, all values in the selected range now have the unit “kg” after the number.

 

add units to numbers 3

 

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 to numbers final data

 

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"

 

add units to numbers 4

 

  1. Drag the formula down the column.

 

add units to numbers 5

 

  1. Repeat the same process for Column H, but in cell H2, enter the formula:
=F2&" m"

 

add units to numbers 6

 

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.

 

google sheets add units to numbers 1

 

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

 

google sheets add units to numbers 2

 

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

 

google sheets add units to numbers 3