How To How To Show Kilograms and Millilitres Symbols In A Microsoft Excel Cell?

Ever been frustrated with Microsoft Excel when you have been working with a cell but you want to show the units of the cell such as 5 Kgs, 5 Bags or 4 Punnets but Microsoft Excel keeps turning the cell into text. Well, there is in fact a way of creating a custom format in Microsoft Excel that lets you show the units of the value whilst still keeping the value being treated as a number.

So, how do we do it…

Well, first off we need to get into the Format Cells dialog box in Microsoft Excel as this is the dialog box that allows you to create a custom format but before you do that make sure that you have selected the cell that you want to apply the format to.

Lets do this as an example …

First click on cell D4, in a new worksheet and the use the shortcut keystroke [Ctrl] + [1] to bring up the Format Cells dialog box and then choose the Number tab. In the categories list box, choose the last option which says Custom. Okay so what we want to do is to create a custom entry. Now when we are creating a custom format the format must be entered as follows –

Positive Format; Negative Format; Zero Format

What you will notice in the example above is that each format is separated by a semi-colon. Okay so lets say in our cell we want to show the unit of kilograms with each value you enter. In the Type text box you would write the following –

#,##0.00 “kg”; -#,##0.00 “kg”; 0.00 “kg”

In the example you will notice that our units are in double quotes. The double quotes are there to identify that they are in fact text elements. The # symbol simply says if there is a number it will be shown or if there is not a number nothing be shown. The 0 symbol says that if there is a number, show the number but if there is no number in that position place a zero in its place. The minus symbol in the negative format means that even if you type the number in with brackets around the value, the application will convert the brackets to a negative symbol at the start of the cell.

So, let us try this custom format by typing in the sample shown above. Once you have done this simply press the OK button at the bottom of the Format Cells dialog box. Now the Custom format has been applied enter a value of 5 into the cell and press the [Enter] key. You will see at this point that the value is shown as –

5.00 kg

If you now type the value of minus 10 into the cell and press enter, you should see the value shown as –

-10.00 kg

Why not try this same process for creating other different unit types. You could use this technique for any unit value that you require. One thing to note though, if you are going to be using this technique in a template for other users, make sure that you train your users not to type the Unit Values in, they in fact need to let Microsoft Excel put the units into the cells for them.

Creating Custom Formats in Microsoft Excel gives you a greater amount of power to customise your templates and spreadsheets to suit your own business environment and it also gives a much greater air of professionalism when you can see the custom format like kilograms next to the value instead of in a corresponding cell.