What is Formatting?
Formatting, in MS Excel, is to change the data’s type such that it can suit the needs of the user. The numbers entered can be formatted according to the following available options
- Number
- Currency
- Accounting
- Short Date
- Long Date
- Time
- Percentage
- Fraction
- Scientific
- Text
Why Use Number Formatting?
Formatting should be used for if the user wants to make the sheet specific to his/her needs and work-friendly according to the organization he/she is working for.
How to Use Number Formatting?
Number
If the user wants to enter a number in the cell that does not have any other connotation, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Number’.
Consider a range of cells as follows
After formatting as ‘numbers’ these cells will look like
Currency
If the user wants to enter currency in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Currency’. The currency according to the settings of the computer will appear. Any number entered in the cell will now have the particular currency symbol.
Using the same example as the last one, these cells will now look like
Accounting
If the user wants to enter currency in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Accounting’. The currency symbol according to the settings of the computer will appear. The difference between ‘Currency’ and ‘Accounting’ formats is that the latter aligns the currency symbols and the decimal points. This difference is more observable when we use the data we used above in this formatting.
Short Date
If the user wants to enter date in the short form in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Short Date’. The date according to dd/mm/yyyy format will appear.
For example, if we have entered a date as ‘6/9/18’, using the short date formatting, it will now look like
Long Date
If the user wants to enter date in the long format in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Long Date’. The date according to the long format like 6th September 2018 will appear.
Time
If the user wants to enter time in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Time’. The time in hh:mm:ss format will appear. For example, if we enter our previous data as 6pm, 7am and 8 pm, then they will look like this in the ‘time’ format.
Percentage
If the user wants to enter percentage in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Percentage’. The percentage symbol will appear. For the data used before, this will look like
Note that these numbers appear as percentages in 1000 because, according to Excel, any number – for example 67- is 67/1. Which makes it 6700/100 and thus 6700%.
Fraction
If the user wants to enter fraction in a cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Fraction’. The value will now appear as a fraction.
Using a slightly different example for fractions, let us look at three numbers 67.5,78.6 and 89.7. These numbers in fraction formatting will look like
Scientific
If the user wants to enter a scientific value in the cell, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Scientific’. The value will appear as a scientific equation. For the examples used above this will look like
Text
If the user wants to enter text in the cell but it has already been formatted to something else, select the cells and then click on the arrow of the dropdown list in the ‘Number’ group of the ‘Home’ tab. A list of available formats will appear. Choose ‘Text’. The formatting will disappear to display only the text entered.
The same value in the example for ‘Scientific’ will appear as follows in ‘Text’ formatting, thus giving us back the numbers as they initially were.
