How to add units in Excel

You can watch a video tutorial here.

Excel provides several ways in which cells can be formatted. Preset formats are available for commonly used formats such as numbers, date, time, and currency. Suppose you have a list of heights and weights and you would like the units to be displayed with the numbers for better clarity. You can either define a custom format or concatenate the unit to the numbers. Formatting the numbers only changes the display and the numbers can still be used for calculation. Concatenating or joining the unit to the numbers changes them to text and they cannot be used for calculation.

Option 1 – Format the cells

Step 1 – Open the Format Cells window

  • Select the cells to be formatted
  • Right-click and select Format Cells  from the context menu

OR

Go to Home > Number  and click on the arrow to expand the menu

OR

Go to Home > Cells > Format > Format Cells

OR

Press Ctrl+1

Step 2 – Define the format

  • Select Custom from the Category
  • Define the format:
    • 0.00 “in”
  • Click OK

Step 3 – Check the result

  • The numbers are displayed with the units

Option 2 – Concatenate the unit

Step 1 – Create the formula

  • Select the cell where the result is to appear
  • Type the formula using cell references:
    • = Weight (Pounds) & “ lbs”
  • Press Enter

Step 2 – Copy the formula

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Check the result

  • The numbers are displayed with the unit

Leave a Comment