Have you ever been frustrated with Microsoft Excel when you’ve been working with a cell but want to display the cell’s units, such as 5 kg, 5 bags, or 4 punnets, but Microsoft Excel keeps converting the cell to text? Well, actually, there is a way to create a custom format in Microsoft Excel that allows you to display the units of the value and at the same time treat the value as a number.

Then, how do we do it…

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

Let’s do this as an example…

First click on cell D4, in a new worksheet and then use the shortcut key combination [Ctrl] + [1] to open the Format Cells dialog box, and then select the Number tab. In the category list box, choose the last option that says Custom. Ok, so what we want to do is create a custom post. Now when we are creating a custom format, the format needs to be entered as follows:

positive format; negative format; zero format

What you’ll notice in the example above is that each format is separated by a semicolon. Okay, let’s say in our cell we want to display the unit of kilograms with each value you enter. In the Type text box, you would type the following:

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

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

So, let’s test this custom format by writing the example shown above. Once you’ve done this, simply hit the OK button at the bottom of the Format Cells dialog. Now that the custom formatting has been applied, enter a value of 5 in the cell and press the [Enter] wrench. You will see at this point that the value is displayed as –

5.00kg

If you now type the value of minus 10 into the cell and press Enter, you should see the value displayed as:

-10.00kg

Why not try this same process to create other different drive types? You can use this technique for any unit value you need. One thing to note though, if you are going to use this technique in a template for other users, be sure to train your users not to type in unit values, in fact they must allow Microsoft Excel to enter the units. in the cells for them.

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