Custom formats in Excel

Ok, so here's the scenario, you want to enter a value like 20Kg (or 44 lb) into an Excel cell. All goes well until you try to multiply the value. Instant disaster. Your number isn't a number it's a piece of text and you can't multiply or add text. You can't divide or minus text or find the 4th power of it either - you get the idea.. it's basically a useless entry - pretty but useless.

SO, how do you format a cell so it displays the Kg after the number but so it still treats the entry as a number? I'm so glad you asked!

The answer is to create a custom number format. Click the cell or a range of cells and choose Format > Cells > Number tab. Select the Custom entry and type something like 00.00 "kg" in the text box and click Ok. Now, enter the numbers again as 20 or 40 etc and the kg will appear automatically after the number courtesy of the format - and the number, well it's still a number so you can add, multiply, divide and do all sorts of other wonderful things to it as you like.

You can also add the text before a number. So, for all those lovely Canada dollars I get paid by the folk at Hub Digital Living where I write a monthly Office column, I type a Custom format like "CAN" 00.00 in my sheet where I record my payments. It's all so easly when you know how.. and now - you do too..