The tutorial explains the basics of Excel format for number, text, currency, percentage, accounting number, scientific notation, and more. Also, it demonstrate quick ways to format cells in all versions of Excel 2016, 2013, 2010, 2007 and lower.
When it comes to formatting cells in Excel, most users know how to apply basic text and numeric formats. But do you know how to display the required number of decimal places or a certain currency symbol, and how to apply just the right scientific notation or accounting number format? And do you know the Excel number format shortcuts to apply the desired formatting in a click?
By default, all cells in Microsoft Excel worksheets are formatted with the General format. With the default formatting, anything you input into a cell is usually left as-is and displayed as typed.
In some cases, Excel may not display the cell value exactly as you've entered it, though the cell format is left as General. For example, if you type a large number is a narrow column, Excel might display it in the Scientific notation format, something like 2.5E+07. But if you view the number in the formula bar, you will see the original number that you entered (25000000).
There are situations when Excel may automatically change the General format to something else based on the value you input in a cell. For example, if you type 1/4/2016 or 1/4, Excel will treat it as a date and change the cell format accordingly.
A quick way to check the format applied to a certain cell is select the cell and look at the Number Format box on the Home tab, in the Number group:
An important thing to remember is that formatting cells in Excel changes only the appearance, or visual representation, of a cell value but not the value itself.
For example, if you have number 0.5678 in some cell and you format that cell to display only 2 decimal places, the number will appear as 0.57. But the underlying value won't change, and Excel will use the original value (0.5678) in all calculations.
Similarly, you can change the display representation of date and time values the way you want, but Excel will keep the original value (serial numbers for dates and decimal fractions for times) and use those values in all Date and Time functions and other formulas.
To see the underlying value behind the number format, select a cell and look at the formula bar:
Whenever you want to modify the appearance of a number or date, display cell borders, change text alignment and orientation, or make any other formatting changes, the Format Cells dialog is the main feature to use. And because it the most used feature to format cells in Excel, Microsoft has made it accessible in a variety of ways.
To change formatting of a certain cell or a block of cells, select the cell(s) you wish to format, and do any of the following:
The Format Cells dialog will show up, and you can start formatting the selected cell(s) by using various options on any of the six tabs.
The Format Cells dialog window has six tabs that provide different formatting options for the selected cells. To find more about each tab, click on the corresponding link:
Use this tab to apply the desired format in terms of number, date, currency, time, percentage, fraction, scientific notation, accounting number format or text. The available formatting options vary depending on the selected Category.
For numbers, you can change the following options:
By default, the Excel Number format aligns values right in cells.
The Currency format lets you configure the following three options:
The Excel Accounting format provides only the first two of the above options, negative numbers are always displayed in parentheses:
Both Currency and Accounting formats are used to display monetary values. The difference is as follows:
Microsoft Excel provides a variety of predefined Date and Time formats for different locales:
For more information and the detailed guidance about how to create custom date and time format in Excel, please see:
The Percentage format displays the cell value with a percent symbol. The only option that you can change is the number of decimal places.
To quickly apply the Percentage format with no decimal places, use the Ctrl+Shift+% shortcut.
For more information, please see How to show percentages in Excel.
This format lets you choose from a variety of the built-in fraction styles:
The Scientific format (also referred to as Standard or Standard Index form ) is a compact way to display very large or very small numbers. It is commonly used by mathematicians, engineers, and scientists.
For example, instead of writing 0.0000000012, you can write 1.2 x 10-9. And if you apply the Excel Scientific notation format to the cell containing 0.0000000012, the number will be displayed as 1.2E-09.
When using the Scientific notation format in Excel, the only option that you can set is the number of decimal places:
To quickly apply the default Excel Scientific notation format with 2 decimal places, press Ctrl+Shift+^ on the keyboard.
When a cell is formatted as Text, Excel will treat the cell value as a text string, even if you input a number or date. By default, the Excel Text format aligns values left in a cell. When applying the Text format to selected cells via the Format Cells dialog window, there is no option to be changed.
Please keep in mind that the Excel Text format applied to numbers or dates prevents them from being used in Excel functions and calculations. Numerical values formatted as text force little green triangle to appear in the top-left corner of the cells indicating that something might be wrong with the cell format. And if your seemingly correct Excel formula is not working or returning a wrong result, one of the first things to check is numbers formatted as text.
To fix text-numbers, setting the cell format to General or Number is not sufficient. The easiest way to convert text to number is select the problematic cell(s), click the warning sign that appears, and then click Convert to Number in the pop-up menu. A few other methods are described in How to convert text-formatted digits to number.
The Special format lets you display numbers in the format customary for zip codes, phone numbers and social security numbers:
If none of the inbuilt formats displays the data the way you want, you can create your own format for numbers, dates and times. You can do this either by modifying one of the predefined formats close to your desired result, or by using the formatting symbols in your own combinations. In the next article, we will provide the detailed guidance and examples to create a custom number format in Excel.
As its name suggests, this tab lets you change text alignment in a cell. Additionally, it provides a number of other options, including:
The below screenshot shows the default Alignment tab settings:
Use the Font tab options to change the font type, color, size, style, font effects and other font elements:
Use the Border tab options to create a border around selected cells in a color and style of your choosing. If you don't want to remove the existing border, select None.
By using the options of this tab, you can fill cells with different colors, patterns, and special fill effects.
Use the Protection options to lock or hide certain cells when protecting the worksheet. For more information, please check out the following tutorials:
As you have just seen, the Format Cells dialog provides a great variety of formatting options. For our convenience, the most frequently used features are also available on the ribbon.
To quickly apply one of the default Excel formats in terms of number, date, time, currency, percentage, etc., do the following:
Apart from changing the cell format, the Number group provides some of the most used Accounting format options:
On the Home tab of the Excel ribbon, you can find far more formatting options such as changing cell borders, fill and font colors, alignment, text orientation, and so on.
For example, to quickly add borders to the selected cells, click the arrow next to the Border button in the Font group, and select the desired layout, color and style:
If you have closely followed the previous parts of this tutorial, you already know most of the Excel formatting shortcuts. The table below provides a summary.
Shortcut | Format |
Ctrl+Shift+~ | General format |
Ctrl+Shift+! | Number format with a thousand separator and two decimal places. |
Ctrl+Shift+$ | Currency format with 2 decimal places, and negative numbers displayed in parentheses |
Ctrl+Shift+% | Percentage format with no decimal places |
Ctrl+Shift+^ | Scientific notation format with two decimal places |
Ctrl+Shift+# | Date format (dd-mmm-yy) |
Ctrl+Shift+@ | Time format (hh:mm AM/PM) |
If a number of hash symbols (######) appear in a cell after you have applied one of Excel number formats, it's usually because of one of the following reasons:
To distinguishes between the two cases, hover your mouse over a cell with hash signs. If the cell contains a valid value that is too large to fit in the cell, Excel will display a tooltip with the value. If the cell contains an invalid date, you will be notified about the problem:
This is how you use basic number formatting options in Excel. In the next tutorial, we will discuss the fastest ways to copy and clear cell formatting, and after that explorer advanced techniques to create custom numbers formats. I thank you for reading and hope to see you again next week!
12 Responses to "How to format cells in Excel (number, text, scientific notation, etc.)"
how to covert Hijri date to Gregorian date. please give me correct formula for excel 2010. Example : 12/10/1437 output in Gregorian ????
Hello Abas,
Sorry, I don't know how to do this using a formula. Most likely a macro is required. You can try the sample code from this link:
http://www.excelforum.com/excel-programming/572150-gregorian-date-from-hijri.html
Also, you can check out this thread and download a sample workbook with a macro that converts Hijri dates to Gregorian:
http://www.excelforum.com/excel-general/791099-convert-from-hijri-date-to-gregorian.html
Dear,
i try but not working......
any way thanks for cooperation.
i have the vb codes but need sample formula witch is did easy.
Just a tip regarding the Custom number format - if you have a cell or column that requires a certain number of digits, enter that many zeroes in the "Type" box. If the number is less than that number of digits, it will add leading zeroes to the number. For example, type set to ten zeroes (0000000000) and entry of number 123456 will display as 0000123456.
This also works for long number strings too, where Excel tries to display the number in scientific notation. I often work with 12-digit numbers, and entering 12 zeroes in the type makes Excel display the full number in the cell.
Thank you for this tip, Mikey!
You can find a few more here: Custom Excel number format
Hi. please consider my problem. I linked a master sheet (some ranges of cells) with many other sheets (with the same cell references) using arrays. However some functions are not working in the Master sheet within the linked cells where the data from others sheets are automatically updated. For instance, sum function doesn't work with these linked cells. Any ideas?plsease
Unfortunately still I'm helpless.... Can anyone of u consider my problem above? plsease
nice blog too informative. looking and reading your points its so impressive. doing more blog like this. i really appreciated doing like this.
In my Excel sheets, I usuall have a large number of large integer values. Usually, it is a hassle to go "Numbers format", scroll down to "other", check the thousand separator box, and klick twice on the number of decimals, and then press OK. I was very happy to find above the shortcut to set a format with a thousand separator and two decimals, since it will remove several steps from the above, but I still need to press the tool bar button for "fewer decimals" twice. Is there any way to get Excel to have 0 decimals and thousand separator as a default?
BTW, I am a happy customer of Ablebit plugins, the merge tables function has saved me many days of tedious work!
-602.11
How do to removed from excel
Hello,
Please specify what exactly you want to remove from Excel.
I'll try to help.