Excel: Formatting as you Type
Published on 17th March 2006
While it certainly doesn't feel like a new season yet, we hope to put a spring in your step with a tip for all users of Excel. We will also explain some of the codes you see in the Format cells dialog box.
You have probably noticed that Excel knows when you have typed in a date and converts the format of the cell to Date format. IE. if you type 17-3-6 it will get converted to 17/03/2006.
You can use this for other formats too!
Typing a % after a value makes the cell have the Percentage format (you probably have seen that happen too.)
If you start a cell with a pound sign (£) the cell will get the Currency format, with a comma to show thousands (£1,010). Many people prefer this to the clicking the Currency button because a) the pound sign is not put on the left hand side of the cell with a gap to the number and b) if you don't type any pennies, no decimal places are shown.
Starting the cell with an apostrophe ' ensures the cell will be formatted for text. This is really useful when you want to type in a phone number. 01256323223 - Excel will see this as a number and drop the insignificant zero resulting in 1256323223. Type '01256323223 instead will format the cell to text (and the apostrophe is hidden). You may also get this problem with invoice, purchase and other code numbers.
If you'd rather show fractions instead of decimals, type zero and a space followed by the fraction. IE. "0 3/5" will result in 3/5. NB: This fraction is a numerical value and you can refer to the cell to do calculations.
Sometimes, however, you do have to resort to the Format Cells dialog box. When choosing your format, the codes the dialog box shows if you want to choose a custom format can be a bit confusing, so here's what they mean.
- 0 = at least one digit, no decimal places.
- 0.00 = at least one digit plus two decimal places.
- #,##0 = at least one digit, no decimals, a comma to denote thousands, millions etc. (the # shows where digits will be used if the number is large enough).
- 0.00;-0.00 = at least one digit plus two decimal places; negative numbers have a - in front.
- 0.00;[Red]-0.00 = at least one digit plus two decimal places; negative numbers have a - in front and are coloured red.
- @ = text format
- _-£* #,##0_-;-£* #,##0_-;_-£* "-"_-;_-@_- = pound sign is on the left of the cell, with the number on the right. No decimals but commas to denote thousands; Negative numbers are the same (the minus is displayed). Zero is shown as £ -. Text is shown with a space to the left. (This is the Accountancy style you get when you click the Currency button).