In this Excel tutorial, you will learn how to apply the number format. You can access number formatting options from the toolbar, ribbon, and number format dialog box.
We have also described custom number format codes for your better understanding. Moreover, we also added steps to clear number formats if you do not want any number format applied to your data.
While preparing this article, we used Microsoft 365 for applying all operations, but they are also applicable in all Excel versions.
Excel Number Formatting is a valuable tool to present your data meaningfully. You can alter how your data appears by applying various number formatting options making your data clearer and more readable.
What is Number Format?(Apply, Customize & Clear)
Number Format defines how to display a number in Excel. Number formats mainly helps to express numerical information in an easily understandable way. For showing different type of numerical data, you need different format. Like for currency data, you need to add dollar ($) or euro (€) sign in front of the number so that you can differentiate between a normal numeric entry and a currency entry.
In Excel, you can apply different number formats. In the image below, you can see the overview of all available number formats.
Click the image to see the full view.
Why Using Number Formats Is Important?
Number formats make numerical data understandable and accessible. The most important advantage of number formats is they only change the appearance of the number not the value in the cell. When you use a number format, you inform your spreadsheet what kinds of data are kept in a cell. So, using a number format is helpful for better representation of data.
How to Apply Available Number Formats in Excel?
There are three different ways of applying available number formats in Excel. You can apply number formats from the Toolbar, Top Ribbon, and Number format dialog box. Now, let’s show you the steps of applying these methods.
1. Applying Number Format from the Toolbar
You can avail yourself of number format options from the toolbar. Select the cell where you want the format, go to the Home tab, and choose any formats from the Number group.
As default, the General number format applies, but other than that, we can also apply Accounting Number Format, Percentage Style, and Comma Style.
Also, you can avail two tools Increase Decimal and Decrease Decimal for increasing or decreasing numbers after decimal points.
2. Changing the Number Format from Top Ribbon in Excel
Yes, you can avail several format options from the ribbon as well. Click the drop-down on the Home tab under the Number group to see the options.
Read More:
3. Applying Number Format from Dialog Box
You can apply your desired number format from the dialogue box. In the Home tab, click on the Number Format symbol in the Number group to open a dialog box named Format Cells.
Now, from the Format Cells group, select your desired number format by clicking on any Category and selecting the format.
Read More:
How to Add Custom Number Format Codes in Excel?
You can add custom number format to your data using number format codes. Let’s see how to create custom number format codes.
First, you need to understand how Excel interprets the number format before creating it. Excel number format usually consists of 4 section codes that are separated by semicolons in this order:
Here, you can see a format code where,
- Format for positive number (formats number with a thousand separator and two decimal points)
- Format for a negative number (same as a positive number with an added parenthesis)
- Format for 0 value (adds a dash for 0 values)
- Formats for text value (Applies Arial font)
Notes:
- A number format may have up to four sections, but only one is necessary.
- The first section is automatically applied to positive numbers, the second section is automatically applied to negative numbers, the third section is automatically applied to zero values, and the fourth section is automatically applied to text.
- If you want to skip a section, place a semicolon in the appropriate spot without including a format code.
Digit and Text Placeholders
The table below contains a predefined list of formatting codes that you can use to create an infinite number of custom Excel number formats. The most general and practical applications of these format codes are described in the following hints.
Now, here the image shows the output of applying every format code.
Read More:
- How to Display Long Numbers in Excel
- How to Enter 16 Digit Number in Excel
- How to Enter 20 Digit Number in Excel
How to Clear Number Formats in Excel?
If do not want any number format in your numerical data, then you need to clear the formats. Since General is the default number format, changing the number format to General will remove all types of formatting.
To do this, click General from the Number group in the Home tab.
Frequently Asked Questions
1. What is the special format in Excel?
Excel comes with four special number formats that offer automatic number formatting:
- A format for social security numbers
- A format for phone numbers
- A format for zip codes
- A format for zip codes plus four digits
2. How do I show 1000000 as 1M in Excel?
Choose Custom from the Number tab’s drop-down menu in the Format Cells dialog box, then type #,, “Million” where it says General. A space separates the second comma and the double quotation mark, and the value shifts from 1000000 to 1,000,000.
3. How do you write 1 to 10000 in Excel?
Enter 1 in cell A1, then in cell A2 insert the formula
=A1+1
Copy this formula and paste it down to A10000.
Conclusion
You can work with number formats in Excel with the help of this article. The number formats help you to present numerical data accurately for efficient analysis, communication, and decision-making. You can apply Excel number formats from the ribbon, toolbar, and dialog box. Customize your data with currency symbols, decimals, percentages, and scientific notation to present precisely and clearly.
Excel Number Format: Knowledge Hub
<< Go Back to Learn Excel
