In one of our previous blogs, we learned various ‘Format Cells’ options available in Excel. One of the formatting options was the Excel ‘Number’ Format. Using the ‘Number’ formatting tab, we can change and format the display of text, numbers, date, and time in Excel. We can even create our own custom formatting in Excel. In this blog, we would unlock each of the Excel ‘Number’ format features.
Let us now recall how to open the ‘Format Cells’ dialog box. There are three ways to open it.
- Right-Click on the Cell to Format > Click ‘Format Cells’ option
- Home tab > Number group > Drop-Down button > More Number Format button
- Home tab > Number group > Format Cells dialog box launcher
- Keyboard shortcut – Ctrl + 1 or Alt + H + FM
The first tab in this dialog box is where the ‘Number’ formatting options are available. There are multiple categories in this – General, Numbers, Percentage, Date, Time, etc. In this blog, we would unlock each of these categories in detail.
Excel Number Format – General Category
By default, all the cells in a new excel workbook have the formatting as ‘General’. The cells set as ‘General’ do not have any specific formatting.
When you type any simple text or a number in a cell that has a ‘General’ formatting, the cell does change its formatting from ‘General’ to any other.
Also Read: Formatting – Format Cells Feature in Excel
However, when you type any date/time or percentage (eg. 16%) in a cell with ‘General’ formatting, the cell would automatically change its format to Date or Time or Percentage respectively.
Below is the demonstration of the same.
Excel Number Format – Number Category
The number format category helps to give different formatting to the numbers in Excel. It is important to note that it only determines the display of a number in a cell.
The default settings are like below:
There are three sections in the ‘Number’ category in Excel.
Decimal Places – The decimal places input box controls how many decimal places should the excel cell display. It is set as ‘2’ by default. You may increase it further to a maximum of 10 decimals or may decrease it to 0 which represents no decimal.
In the below screenshot, you can how the decimal places formatting gives the result.
Also, as mentioned earlier, this setting only changes how the number would be displayed and not the number itself.
The second section is Use 1000 separator (,). This option is unticked by default.
The excel inserts a thousand separator to the number if you tick this checkbox. Refer to the screenshot below:
The third section of the Number Formatting is how the negative number should be displayed in the cell. For Example, you can show the negative numbers with a minus sign (but in black color) or you can use the other one where negative numbers are shown in red color, prefixed by a minus sign. It does not change the number itself, but only changes how it should get displayed in a cell.
Also Read: All About Custom Excel Number Format
Refer to the below screenshot.
Excel Number Format – Currency Category
Similar to the ‘Number’ category is the ‘Currency’ category of the cell formatting. It helps us to display an amount prefixed with the currency symbol that you can choose from the list of available options.
Below is the screenshot for your reference:
The ‘Decimal Places’ and the ‘Negative Numbers’ sections work in a similar way as the ‘Number’ category.
From the ‘Symbol’ section dialog box, you can choose the available currency symbols for the amount.
Excel Number Format – Accounting Category
There is not much difference between the ‘Currency’ category and the ‘Accounting’ category. Both these categories are preferred when you want to enter any amount or monetary value in the cell.
However, unlike the ‘Number’ category formatting, when the cell formatting of the amount column is set as ‘Accounting’, the decimal places and the currency symbols in the cells align itself.
Refer to the screenshot below:
Date and Time Categories
The date and time categories, as the name suggests, format the cell property as a date or as a time format.
Different countries have a different date and time format. Some follow a period (dot) as separator while some follow dash (-) or forward-slash (/). Some countries write the year first, then month and then date while others have some other format.
To take care of all these geography-specific requirements, excel has provided with an option to select the location (country), and then you can select the date/time format from the list (based on the selected location).
The date and time formats that begin with an asterisk (*) symbol gets updated according to the operating system date and time formats, and rest does not.
Percentage Category
When you type any number in a cell that is formatted as ‘Percentage’, the Excel would automatically suffix the ‘percentage’ symbol (%) after that number.
This category controls the decimal places for percentages.
Fraction Category
The ‘Fraction’ category converts the decimal into a fraction in Excel. Let us take one example to understand this better. Type 0.25 in a cell in Excel and then open the ‘Format Cells’ dialog box (Ctrl+1). Select the ‘Fraction’ category and click ‘OK’. You would notice that excel would change the decimal number into a fraction.
Where the fraction has two digits in the denominator, in that case, you need to select the option ‘Up to two digits’ and where there are three digits in the denominator, then select ‘Up to three digits’. If you select an incorrect option, then the excel would give the fraction that is the nearest one.
For Example: To convert the number 0.99 into a fraction, you need to select the ‘Type’ as ‘Upto three digits’, then it would return the correct fraction 99/100. If in case you select the type as ‘Up to two digits’, then, it would return the nearest fraction 98/99 (with two digits in the denominator).
Text Category
Where any cell formatting is set as ‘Text’, it would show the value inside the cell as a normal text even though it has a number. The cell would display exactly what you type.
This means that the cell containing a number, but formatted as a text, would not consider that number as a number and therefore, would not perform any mathematical operation on it.
The below image is a small example explaining the above.
Special Category
These are some of the additional features.
The United States users use it for entering the Zip Code, Phone Numbers, and Social Security Number in its specific format.
Custom Category
In addition to the above specific categories and limited formatting options, the excel has provided with a category that enables you to create your own number, date, time, percentage, etc. formatting. This is popularly called by the name of Custom Formatting in Excel. We have an exclusive blog on the ‘Custom Number Formatting in Excel’. Click here.
This brings us to the end of this blog. Share your views and comments in the comment section below.