Excel Custom Number Formats

Use Excel’s Custom Number Formats to Display Numbers with their Units of Measurement Alongside… e.g 50 Kg

If you have a column of data in Excel where you want to display measurement units such as kilometers (Km), kilograms (Kg), pounds (Lbs), and so on, you can create a custom number format. This custom format allows you to automatically append the measurement unit to the numbers in the cells, eliminating the need to manually enter the unit each time.

To set up a custom format, follow these steps:

1. Select the Cells: Highlight the cells or column where you want to apply the custom format.
2. Open Format Cells Dialog: Right-click on the selected cells and choose “Format Cells” from the context menu, or press Ctrl + 1 to open the Format Cells dialog box.
3. Navigate to Custom Format: In the Format Cells dialog box, go to the “Number” tab and select “Custom” from the list on the left.
4. Enter Custom Format Code: In the “Type” field, enter the custom format code. For example, to display numbers with “Kg” as the unit, you would enter 0 "Kg". This tells Excel to display the number followed by a space and the unit “Kg”.
5. Apply the Format: Click “OK” to apply the custom format to the selected cells.

With this custom format in place, Excel will display the measurement unit alongside the number in each cell. This is particularly useful when you need to reference these values in formulas. For instance, if you have a column of weights in kilograms, you can sum the values without any issues because Excel treats the numbers as numeric values, ignoring the text part of the custom format.

By using custom formats, you ensure consistency and accuracy in your data presentation and calculations, making your spreadsheets more efficient and easier to read.

Watch this short video on my YouTube channel to see how it’s done using 2 examples including kilometres and degrees.

See you again soon, please comment and share if you found useful!
Anita Lund
www.trainers-direct.com.au  Sydney