To format cells as numbers in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Number Format dropdown > Number. This changes how numeric values appear; if a value is stored as text, applying Number format alone will not convert it.
What Excel’s Number format does
Excel keeps a cell’s value separate from its number format. The value is the data Excel uses in calculations, while the format controls how that value is displayed. For example, the underlying value 1234.5 can appear as 1,234.50 with Number format, as $1,234.50 with Currency format, or as 1,235 if displayed with no decimal places. Unless you change the value itself, the stored number remains 1234.5.
Number format is useful for ordinary quantities such as sales, counts, measurements, and totals. In Excel for Microsoft 365, the standard Number format usually displays two decimal places, a thousands separator, and a minus sign for negative values. The exact punctuation used for decimal and thousands separators can depend on your regional settings.
Formatting does not normally change what a formula calculates. If a formula returns 12.678 and the cell displays 12.68, Excel still uses 12.678 in later calculations. That distinction matters when totals appear slightly different from the sum of rounded values shown on screen.
How to apply Number format
Use the ribbon for the quickest method. These steps apply to Excel for Microsoft 365 on Windows:
- Select the cells, range, or column you want to format. To select a whole column, click its letter at the top of the worksheet.
- Open the Home tab and find the Number group.
- Open the Number Format dropdown and choose Number.
- If needed, use Increase Decimal or Decrease Decimal in the Number group to change how many decimal places are shown.
For detailed options, select the cells and open the Format Cells dialog with Ctrl+1 on Windows. Choose the Number category, set the decimal places, and decide whether to use a thousands separator. The dialog also lets you select a negative-number display. Choose OK to apply the settings. In Excel for Mac, Command+1 opens Format Cells; the ribbon route is also available, though the layout may differ slightly from Windows. Excel for the web offers number-format controls on its ribbon, but some advanced Format Cells options are more limited than in the desktop app.
To make the format the default for new entries in a range, format the cells before entering data. You can also copy a formatted cell and use Paste Special > Formats to copy only its formatting. This is useful when you want the destination values to remain unchanged.
Decimal places, separators, and displayed rounding
Number format can make values easier to scan without changing their precision. For instance, a value of 2500000 may display as 2,500,000.00 when the thousands separator is enabled and two decimal places are selected. If you reduce the displayed decimals to zero, Excel shows 2,500,000, but the value is still stored at its original precision.
This display-only rounding is different from rounding the data. If a report must use rounded values in subsequent calculations, apply a rounding formula to the data rather than relying only on the visual format. Otherwise, a column may show values that appear to add to one total while the unrounded underlying numbers produce a slightly different result.
To change decimal places, select the cells and choose Increase Decimal or Decrease Decimal on the Home tab. In Format Cells, the Number category also lets you enter a specific number of decimal places and toggle the thousands separator. A value entered as 0.25 can therefore display as 0.25, 0.2500, or 0, depending on the selected number of decimal places and separator settings; these displays do not mean the value itself has changed.
When Number format does not work
A frequent problem is that numbers imported from a website, text file, or another system are stored as text. Text may look like a number, but Excel can align it differently, show a green error indicator, or leave it out of calculations such as SUM. Changing the cell format to Number changes the appearance setting but does not reliably turn text into a numeric value.
If Excel flags a cell with a green triangle, select it and use the warning button’s Convert to Number command when available. For a column of consistently structured values, another option is to select the cells and choose Data > Text to Columns, then choose Finish. This often converts text-formatted numeric entries without splitting the column, but inspect the results when the source uses unusual separators or mixed data.
A formula can convert a simple text number to a numeric result. With a text value such as 123 in cell A2, enter =VALUE(A2) in another cell; the result is the number 123. Fill the formula down for the remaining rows, then copy the results and use Paste Special > Values if you need to replace the original entries. Conversion can fail or produce unexpected results when the text contains extra spaces, currency symbols, or decimal and thousands separators that do not match Excel’s regional settings.
Also check whether a cell uses the Text format. If it does, changing it to Number and re-entering the value may be necessary. Merely changing the format after an entry does not always make Excel reinterpret what was typed. For a large import, it is safer to inspect representative rows and verify that calculations work before relying on the converted column.
Choose Number, General, or another format
General is Excel’s default format. It displays values in a compact way and may show large or small numbers in scientific notation. Number gives you more control over decimal places and separators, making it a good choice for quantities people need to read. Choose a format based on what the value represents, not just how it looks in the source file.
- Number: Use for counts, measurements, and other quantities where decimal places and separators are useful.
- Currency or Accounting: Use for monetary amounts when a currency symbol is appropriate. Accounting aligns currency symbols and decimal points in a column; the two formats are not interchangeable in every report.
- Percentage: Use when the stored value is a proportion. A stored value of 0.15 displays as 15% under Percentage format.
- Date or Time: Use for date and time values. Excel stores dates as serial numbers, so a date-formatted cell changed to Number may display a serial value instead of a calendar date.
- Text: Use for identifiers that must retain their exact characters, such as product codes or postal codes.
Do not use Number format for every string made of digits. Account numbers, phone numbers, postal codes, and some item codes are labels, not quantities. A value such as 00127 loses its leading zeros if Excel treats it as the number 127. Keep such entries as text, or use a suitable custom display format if the code has a fixed length and you understand that the zeros are only being added to the display.
Custom number formats and fixed-width displays
If the built-in Number option does not match your needs, use a custom format. Select the cells, press Ctrl+1 on Windows (or Command+1 on Mac), open the Number tab, and choose Custom. Enter a format code in the Type box, then select OK. For example, #,##0.00 displays a thousands separator and two decimal places, while 0.00 displays two decimal places without requiring a thousands separator.
Custom formats can also show a fixed number of digits without changing the stored value. The code 00000 displays the number 127 as 00127. This can be useful for consistently formatted codes, but it does not convert the number into text; formulas and exports may still treat it as 127. If leading zeros are part of the actual identifier and must travel with the data, store the identifier as text instead.
Custom formats can define separate displays for positive numbers, negative numbers, zero, and text by separating sections with semicolons. Because format codes can be confusing and may behave differently when data is exported, document the convention and check the result with positive, negative, zero, and text examples before applying a format to a large table.
Common mistakes to avoid
- Assuming the display is the stored value: Two decimals shown on screen do not mean the data has been rounded to two decimals.
- Using Number for identifiers: Excel may remove leading zeros, and very long numeric-looking identifiers can lose precision. Store identifiers as text.
- Applying Percentage to whole-number percentages: If the cell contains 15 and you apply Percentage format, Excel displays 1500%. A proportion of 0.15 displays as 15%.
- Confusing dates with ordinary numbers: Dates are stored as serial values. A number format can expose that serial instead of the date display.
- Expecting number formatting to fix text: Convert text-formatted numbers first, then apply the desired display format.
When checking a result, select the cell and inspect the formula bar as well as the display. The formula bar generally shows the underlying value or formula, while the worksheet cell shows the formatted appearance. For a calculation problem, check both the cell’s format and whether the source entries are truly numeric.
Frequently Asked Questions
How do I format a cell as a number in Excel?
Select the cell or range, then choose Home > Number Format dropdown > Number. Adjust decimal places with the buttons in the Number group, or open Format Cells for more settings.
Why does Excel keep showing a number as text?
The entry may have been imported or typed with a leading apostrophe, spaces, or separators Excel does not recognize. Convert it to a number using the warning menu when available, Text to Columns, or a conversion formula, then check that a calculation includes it.
Does Number format change the value in the cell?
No. Number format changes the display, not the underlying numeric value. To change precision for calculations, use a formula that rounds the value rather than only reducing the displayed decimal places.
How do I keep leading zeros in Excel?
For identifiers that must preserve zeros as part of the data, format the cells as Text before entering or importing them. A custom format such as 00000 can show leading zeros for a fixed-width display, but the underlying value remains numeric.
Why does a date turn into a large number when I choose Number?
Excel stores dates as serial numbers and uses a date format to display them as calendar dates. Applying Number reveals the stored serial value; apply a date format again to show the date in a familiar form.
Related
- Excel Data Validation List: Keep Dropdowns in A-to-Z Order
- Excel Data Validation List Auto Update: How to Set It Up
- Excel Data Validation Lists: Add New Items in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- PowerPoint Change Slide Size: Steps, Options, and TipsTo change slide size in PowerPoint for Microsoft 365 on Windows, open the presentation and select Design >…
- Compare Two Excel Sheets and Find DifferencesTo compare two Excel sheets cell by cell, put a formula on a third sheet such as =IF(Sheet1!A1=Sheet2!A1,"Same","Different"),…
- How to Set Out of Office in the Outlook Mobile AppTo set an out-of-office reply in Outlook mobile, open the app’s Settings, select the email account, choose Automatic…
- Excel Scatter Plot by Third Column: Color Points by GroupYes—color an Excel scatter plot by a third column by making a separate chart series for each category…
- Excel Time Tracker: Build One That Adds UpAn Excel time tracker is a spreadsheet for recording when work starts and ends, subtracting breaks, and totaling…
- How to Find and Replace in One Excel ColumnTo find and replace text in just one Excel column, select the column by clicking its letter, press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.