How to format cells in Excel (number, text, scientific notation, etc.)

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 365, 2021, 2019, 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?

Excel Format basics

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:
Excel Number Format box

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:
To see the actual cell value, select a cell and look at the formula bar.

How to format cells in Excel

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.

4 ways to open the Format Cells dialog

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:

  1. Press Ctrl + 1 shortcut.
  2. Right click the cell (or press Shift+F10), and select Format Cells… from the pop-up menu.

    Right click the cell, and then click Format Cells…

  3. Click the Dialog Box Launcher arrow at the bottom right corner of the Number, Alignment or Font group to open the corresponding tab of the Format Cells dialog:
    To open the Font, Alignment or Number tab of the Format Cell dialog, click the corresponding Dialog Box Launcher arrow on the ribbon.
  4. On the Home tab, in the Cells group, click the Format button, and then click Format Cells…
    One more way to open the Format Cells dialog in Excel.

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.

Format Cells dialog in Excel

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:

Number tab - apply a specific format to numeric values

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.

Excel Number format

For numbers, you can change the following options:

  • How many decimal places to display.
  • Show or hide the thousands separator.
  • Specific format for negative numbers.

By default, the Excel Number format aligns values right in cells.
Applying Excel Number format

Tip. Under Sample, you can view a life preview of how the number will be formatted on the sheet.

Currency and Accounting formats

The Currency format lets you configure the following three options:

  • The number of decimal places to display
  • The currency symbol to use
  • The format to apply to negative numbers

Tip. To quickly apply the default currency format with 2 decimal places, select the cell or range of cells and press the Ctrl+Shift+$ shortcut.

The Excel Accounting format provides only the first two of the above options, negative numbers are always displayed in parentheses:
Excel Accounting format options

Both Currency and Accounting formats are used to display monetary values. The difference is as follows:

  • The Excel Currency format places the currency symbol immediately before the first digit in the cell.
  • The Excel Accounting number format aligns the currency symbol on the left and the values on the right, zeros as displayed as dashes.

Accounting and Currency format in Excel

Tip. Some of the most often used Accounting format options are also available on the ribbon. For more details, please see Accounting format options on the ribbon.

Date and Time formats

Microsoft Excel provides a variety of predefined Date and Time formats for different locales:
Date formatting options

For more information and the detailed guidance about how to create custom date and time format in Excel, please see:

Percentage format

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.

Note. If you apply the Percentage format to the existing numbers, the numbers will be multiplied by 100.

For more information, please see How to show percentages in Excel.

Fraction format

This format lets you choose from a variety of the built-in fraction styles:
Excel's built-in fraction styles

Note. When typing a fraction in a cell that is not formatted as Fraction, you may have to type a zero and a space before the fractional part. For example, if you type 1/8 is a cell formatted as General, Excel will convert it to a date (08-Jan). To input the fraction, type 0 1/8 in the cell.

Scientific format

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:
Using the Scientific notation format in Excel

To quickly apply the default Excel Scientific notation format with 2 decimal places, press Ctrl+Shift+^ on the keyboard.

Excel Text format

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.

Special format

The Special format lets you display numbers in the format customary for zip codes, phone numbers and social security numbers:
Special format options for zip codes, phone numbers and social security numbers

Custom format

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.

Alignment tab - change alignment, position and direction

As its name suggests, this tab lets you change text alignment in a cell. Additionally, it provides a number of other options, including:

  • Align the cell contents horizontally, vertically, or centered. Also, you can center the value across selection (a great alternative to merging cells!) or indent from any edge of the cell.
  • Wrap text into multiple lines depending on the column width and the length of the cell contents.
  • Shrink to fit - this option automatically reduces the apparent font size so that all data in a cell fits in the column without wrapping. The real font size applied to a cell is not changed.
  • Merge two or more cells into a single cell.
  • Change the text direction to define reading order and alignment. The default setting is Context, but you can change it to Right-to-Left or Left-to-Right.
  • Change the text orientation. A positive number input in the Degrees box rotates the cell contents from lower left to upper right, and a negative degree performs the rotation from upper left to lower right. This option may not be available if other alignment options are selected for a given cell.

The below screenshot shows the default Alignment tab settings:
The default Alignment settings

Font tab - change font type, color and style

Use the Font tab options to change the font type, color, size, style, font effects and other font elements:
The Font tab options

Border tab - create cell borders of different styles

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.

Tip. To hide gridlines in a certain range of cells, you can apply white borders (Outline and Inside) to the selected cells, as shown in the screenshot below:
The Border options to hide gridlines in the selected cells

For more details, see How to create, change and remove Excel cell border.

Fill tab - change the background color of a cell

By using the options of this tab, you can fill cells with different colors, patterns, and special fill effects.
The Fill tab options

Protection tab - lock and hide cells

Use the Protection options to lock or hide certain cells when protecting the worksheet. For more information, please check out the following tutorials:

Cell formatting options on the ribbon

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.

Fastest way to apply the default Excel number formats

To quickly apply one of the default Excel formats in terms of number, date, time, currency, percentage, etc., do the following:

  • Select a cell or a range of cells whose format you want to change.
  • Click the little arrow next to the Number Format box on the Home tab, in the Number group, and select the format you want:

A quick way to apply the default Excel formats

Accounting format options on the ribbon

Apart from changing the cell format, the Number group provides some of the most used Accounting format options:

  • To apply Excel Accounting number format with the default currency symbol, select a cell(s), and click the Accounting Number Format icon The Accounting Number Format icon.
  • To choose the currency symbol, click the arrow next to the Accounting Number icon, and select a required currency from the list. If you want to use some other currency symbol, click More Accounting Formats… at the end of the list, this will open the Format Cells dialog with more options.

Click the Accounting format icon arrow, and select a currency symbol from the list.

  • To use the thousands separator, click the icon with a comma The thousands separator icon.
  • To display more or fewer decimal places, click the Increase Decimal or Decrease Decimal icon, respectively. This option can be used for Excel Accounting format as well as for Number, Percentage and Currency formats.

Displaying more or fewer decimal places

Other formatting options on the ribbon

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:
A quick way to add cell borders

Excel formatting shortcuts

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)

Excel number format not working

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:

  • The cell is not wide enough to display the data in the selected format. To fix it, all you need to do is increase the column width by dragging the right boundary. Or, double-click the right boundary to automatically resize the column to fit the largest value within the column.
  • A cell contains a negative date or a date outside the supported date range (1/1/1900 through 12/31/9999).

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:
A number of hash symbols appear in a cell if the cell is not wide enough or contains an invalid date.

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!

Table of contents

61 comments

  1. ❤️👌👌👌

  2. Hi, I have work sheet in exel example:
    1.2
    3.5
    6.30
    9.40
    2.50
    Now i want to convert or change automatically in exel
    A.2
    C.E
    F.CJ

    THAT MEANS I WANT TO USE 1=A, 2=B, 3=C AND .=.

    • Hi! Use the MID function to extract all the characters from the cell value one by one.
      To get the code of the letter corresponding to the digit, add 64 to the digit.
      Use the CHAR function to convert code number into alphabet.
      Use the ISNUMBER function to convert only numbers to letters.
      Use the TEXTJOIN function to combine letters into a text string.
      I believe the following formula will help you solve your task:

      =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(A1:A20),1)), CHAR(MID(A1,ROW(A1:A20),1)+64), MID(A1,ROW(A1:A20),1)))

  3. How can I modify the number of numbers that appear after the decimal point in an Excel file, and make this change permanent and not for specific cells?

    • Hi! You can change the default number of decimal places in your numbers using Windows Control Panel - Regional Settings: Region and Language > Additional Settings.

      • hi! I followed the steps and set the default number to 0, but when using Excel, the default number remained 2.

  4. How to convert scientific values to ex: 4.123456781E-05 to 0.0004123456781 in Analysis for Office

  5. Hi! How do I create a custom number format that will show this 00:09:93:7A:35:3F instead of this number 0009937A353F?
    Thanks.

  6. When I go to write a figure with a point Double Zero, then double zero could be visible in the Excel cell.

    For example,
    If I write 745.00, it shows 745 in the Excel Cell.
    How can I fix this ?

  7. On one sheet of my workbook, all format options are shown as 50%2. It is only affecting one column. Any solution please?

    • I don't really understand your question. But all the information you need on how to change the format of the cells is in the article above.

  8. i want to type 5-9 but it is coming in the format 5-may which is not what i want, how can i go About it

  9. I have a number 412345654Q. I want to add spaces such as SSN but it wont since it has a letter at the end. How do I format that column to
    show it as 412 345 654Q
    Any help would greatly be appreciated!

  10. How do I make a number in hundreds shown in thousands? E.g 645 to convert/show as 645,000. I tried number formatting but the 000 always gets added before 645 -making it to 00645, when I try to customize.

  11. Hi

    Can you please let me know how to add any new currency in Accounting Number Format, I don't want to change my default currency, but for easy access, I want to add a new Currency which I use from time to time

    Regards
    Kamal

  12. Hi , How I can use TEXT and TODAY function together , I am using =TODAY() for current date and i want to use date in this format 2023-01-25.
    but at run time my excel is giving me date like 43221.

  13. is there any way to show a blank cell if the cell contains 0 (zero)?
    inside the formula, we can use IF to return a blank cell using " "
    but I'm looking for constant values in cells
    also to show all zero values as a blank cell, we can use FILE>OPTION>ADVANCED>DISPLAY OPTIONS>SHOW A ZERO IN CELLS THAT HAVE ZERO VALUE
    but i want not to show zeros in certain cells only,,,,(advanced option doesn't display any zero value)
    thanks

  14. In excel if i ener 356 no. I want this to show in diffrent column like in a 3 in b 5 and in c 7 . Can i do this in excel . And if i put somewher 1,6,9,7,5 then it show me possibilites of no. Like 16975 and 96715

  15. when i m changing of type of number like general to accounting then type of font automatically changed. please suggest what is the problem.

  16. Hi i have customer code 11E24 but this is shown as scientific notation how i can solve this
    I already format cell

  17. DOES ANYONE KNOW HOW TO PUT 'SHRINK TO FIT' AND 'WRAP TEXT' BACK ON THE TOOLBAR IN EXCEL 22 FOR MAC. OR ALTERNATIVELY IS THERE A FONT THAT I CAN SWITCH TO THAT HAS SIZE 6 AS ONE OF THE STANDARD SIZES WITHOUT HAVING TO GO THROUGH THE SHRINK TO FIT OPTION
    THANK YOU

  18. I am trying to create a custom format of data.

    The number comes in this format :

    31026055A but I need it in this format 310-26-055A.

    I created a custom format ###-##-### but it doesn't work with the character at the end. What can i use for this?

  19. I'm trying to figure out how to get rid of the dash that Excel puts in place of a 0 in the Accounting format, while retaining the spacing of the $ and the numbers.

    Thank you for your help!

    • Hello!
      To turn zeros and dashes into spaces, leave only the currency sign in the third section of the number format. You can add space to align characters in a cell. Read more about it in this guide: Custom Excel number format.

      _-* #,##0.00 $_-;-* #,##0.00 $_-;$" ";_-@_-

      I hope my advice will help you solve your task.

  20. i am trying to get the average sales per month and my formular isnt updating the rest of the columns. please help.

  21. How to write 3-57 in excell, it shows March 57. Pls help

    • Hi Premika,

      First, set the cell's format to Text, and then type 3-57 in it.

  22. hello, What formulas can i use for number format if you have a cell or column that 1 through 28 b-c = d thanks

  23. Hi,
    Can any one help me to convert scientific notion to the general number for 6.15E+11.
    I tried it is giving 615000000000. But this not the right answer.

  24. How do you format a scientific number to a general number? I have occurrences in a spreadsheet and every time I click in the cell I can see the general number on the formula line but in the cell the scientific number still appears.

    • Hi Leslie,

      Just set the Number format for that cell:

      1. Select the cell(s), and then open the Format Cells dialog box (Ctrl+1).
      2. On the Number tab, select Number under Category.
      3. Specify how many decimal places you want to show in a cell and click OK.

  25. hI. I AM IMPORTING DATES OF BIRTH TO AN EXCEL DOCUMENT AS TEXT BECAUSE THE CVS SPREADSHEET I AM IMPORTING TO IS SET UP THAT WAY. WHEN I CHANGE THE FORMAT OF A CEL TO TEXT AND TYPE 1/1/2000 IT CONVERTS THE CEL BACK TO A DATE CEL AND I CANNOT UPLOAD MY DATA. CAN YOU HELP?

  26. nice blog too informative. looking and reading your points its so impressive. doing more blog like this. i really appreciated doing like this.

  27. how to convert 100 excel files into the general category format with 1 click?
    please help me with a code

  28. -602.11
    How do to removed from excel

  29. 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!

  30. nice blog too informative. looking and reading your points its so impressive. doing more blog like this. i really appreciated doing like this.

  31. Unfortunately still I'm helpless.... Can anyone of u consider my problem above? plsease

  32. 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

  33. 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.

  34. how to covert Hijri date to Gregorian date. please give me correct formula for excel 2010. Example : 12/10/1437 output in Gregorian ????

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)