How to format numbers in Excel
Excel is, at its heart, a programme that manipulates numbers: so getting your numbers to appear as you want is an important skill. The appearance of a number entered in an Excel cell can be changed in many different ways. This blog post shows you how.
You can change the number format via Home > Number > Number Format and choose from the dropdown menu.
Excel 2013 offers 10 formats in this dropdown, Excel 2007 offers 11, although there are more if you select More Number Formats at the bottom of the list. You can even design your own. Even if you choose to stick to the default General format (as we recommend), it’s handy to be familiar with some of the others in order to be able to recognise and adapt them in other people’s spreadsheets.
The default setting displays numeric values as plain numbers, without dollar signs, colour, commas or the like.
Formatting a number as a percentage displays that number as though it were multiplied by 100, and adds a percentage sign to the display. If you format a cell as a percentage, then any number entered will be treated as a percentage, so 30 would be 30% or 0.3. If you format a cell that already contains 30 as a percentage it will become 3000%.
Some people choose to enter percentages as 100 times their actual value with the %age symbol in an adjacent cell and then divide the value by 100 in all subsequent calculations. Make sure you check how any percentages you use have been set up.
Accountants and bookkeepers will be familiar with this format which adds a £ sign (UK version – $ in the USA etc.) to the left edge of the cell and adds two decimal places (to correspond to pence/cents, etc.). Although some familiarity with this is advised, hundreds of pound signs in a spreadsheet in which there is only one currency can be unnecessary. In such cases, we recommend stating the currency prominently in a heading and sticking to unadorned numbers (probably to two decimal places).
This useful style gets its own button:
Comma style adds commas to large numbers and adds two decimal places (so 1000 becomes 1,000.00). If your spreadsheet is mostly populated with integers, the two decimal places are probably unnecessary.
If you liked this article, then you’ll love our most popular course, Microsoft Excel. You’ll be an Excel expert in no time!
Enter Your Voucher Code Here
Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items.