Number Formatting in Excel
Excel can handle lots of different format types of numbers, but you must regularly choose what type of number format you wish to be expressed on the cells containing those numbers. If you don't, you may suffer problem when calculating functions or expressing the data in a specific way.
Examples:
- Date
- Time
- Percentage
- Monetary value
- fraction
- text
Date
Dates can be formatted in lots of different ways:
From short format: (01/01/00) or long format (1st Jan 2000). However these are still thought of as numbers by Excel. For instance "42831" is actually the date 06/04/2017, whereas 0 would be 01/01/1900. Excel cannot process dates before the 01/01/1900. Generally typing a date into a cell will change the number format on the cell automatically, however you can swap back and forth between number formats see the difference or the underlying numerical figure. However it is good practice to assign entire columns/rows the same number format, so that any data added after this point will be automatically converted without manual intervention.
To change a cell's formatting select the cells you wish to change and use the number format box shown in the image
Currency/Accounting
There are two numbering formats used for Currency: Currency and Accounting format.
Currency: This option will display the currencies symbol next to the value
Accounting: This option will display the currency's symbol to the far left of the cell away from the value.
Percentage
Percentages are displayed as a number below 1: for instance 0.5 would be 50%, 0.05 would be 5%. So when entering percentages if you don't wish to use the "%" symbol every time, you enter a value you can then change it in the number format section to convert it to a percentage afterwards
Time
Times are a little strange as they do use a number format that then turns into a time however It is almost impossible to predict what number would need to use to produce the time you want. Therefore I suggest using the following format when entering times: HH:MM:SS (hours, minutes & seconds). We can also change how the time is displayed in 12 or 24 hour format by venturing into the "more number formats..." menu found in the bottom of the number format drop down menu.
Large/High numbers - Comma Separators
When you are using numbers larger than 1000 it can become quite hard to read especially if you have a lot of 0's on the end. Therefore we can use a thousands separator to add a comma between each thousand:
1000 -> 1,000
10000 -> 10,000
100000 -> 100,000
1000000 -> 1,000,0000
Further number format/Changing display preferences
By default every cell is assigned to "General" which has essentially no special formatting rules upon it. If you type something it will leave it as typed unless it's very similar to another type of format such as: Date or time etc. However once data has been entered Excel will try to guess what format it should have. I have shown above this a few examples of where we can change the format, however you are able to edit these formats to be viewed in a specific way which can be found at the bottom of this drop down menu as "More Number formats": for more information on how to write a custom display format for numbering formatting follow this Guide here. These include change the symbol of currency, the display type of a date or many other useful things.
