An Excel cell number format controls how a value looks in a worksheet, not the value Excel stores or uses in calculations. For example, a cell containing 0.25 can display as 25%, while its underlying value remains 0.25. To change a format in Excel for Microsoft 365 on Windows, select the cell, then choose a format from Home > Number, or press Ctrl+1 to open Format Cells.
What a cell number format changes
Excel keeps a cell’s contents separate from its number format. A numeric value can be shown as a decimal, currency amount, percentage, date, time, or another presentation without changing the stored number. This lets a worksheet show readable results while preserving values that formulas can calculate with.
Suppose A1 contains the number 12.5. A General format may show 12.5; a format with two decimal places can show 12.50; and a currency format can show a currency symbol and two decimal places. The stored value is still 12.5 in each case. A formula that refers to A1 uses that value, not the symbol or trailing zeroes visible on screen.
This distinction also explains a common misconception: applying a format is not the same as converting a value. Formatting a number as a date does not turn it into a separately stored date object. Excel dates are serial numbers displayed with date formats, and times are fractions of a day. A format may make a value easier to read, but it cannot make text into a usable number or correct an incorrect value.
How to apply a number format
For everyday changes, select one or more cells and use the Number Format list in the Home tab’s Number group. Common choices include General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, and Text. Some options also have buttons nearby for increasing or decreasing displayed decimal places.
For more control, open the Format Cells dialog. On Windows, select cells and press Ctrl+1, or right-click and choose Format Cells. Choose the Number tab, select a category, adjust its options, and select OK. For a custom format, choose Custom and enter a format code. On Excel for Mac, the Format Cells dialog is also available from the Format menu; Control+1 is a common shortcut, though keyboard settings can affect shortcuts. Excel for the web supports many common formats, but the desktop app is the more dependable choice when you need to create or edit a complex custom format.
Formatting applies to the selected cells, so check the selection before changing it. To apply one format to a whole column, select the column header. To format nonadjacent cells, hold Ctrl on Windows while selecting them. On Mac, use Command while selecting additional ranges. A cell’s format can also be copied with the Format Painter, but that copies appearance rather than changing the underlying data.
What the built-in categories do
Built-in categories provide practical presets and expose settings that would otherwise require a custom code. The exact symbols and examples shown can depend on your regional settings, including the currency symbol and whether a decimal point or comma separates parts of a number.
- General lets Excel choose a display that fits the value. It may show many digits or use scientific notation for very large or small numbers.
- Number lets you set decimal places and choose a thousands separator. Negative-number display options are also available.
- Currency and Accounting show a currency symbol. Accounting aligns symbols and decimal points down a column, while Currency places the symbol nearer the number.
- Percentage displays a value multiplied by 100 and adds a percent sign. A stored value of 0.25 appears as 25% with zero decimal places.
- Date and Time display serial values as calendar dates or times. Choose a style that makes the intended day, month, year, or time clear.
- Fraction and Scientific display numbers as fractions or in scientific notation, which can be useful for measurements or very large and small values.
- Text displays cell contents as text. It is useful for identifiers that should not be treated as quantities, but setting this format after typing a number may not convert the existing value.
Custom formats and their building blocks
A custom number format lets you control display details without writing a formula. In Format Cells, open Number > Custom and enter a code in the Type box. For example, #,##0.00 displays a number with a thousands separator and two decimal places. The zero placeholder requires a digit; the # placeholder shows a digit only when needed. The ? placeholder reserves space for insignificant digits, which can help align decimals or fractions.
Custom formats can contain up to four sections separated by semicolons, in this order: positive numbers; negative numbers; zero values; text. For example, #,##0.00;[Red](#,##0.00);-;@ shows positive numbers with two decimal places, negative numbers in red parentheses, zero as a dash, and text as entered. The @ symbol represents the text in the cell. If a format has fewer sections, Excel applies its rules for the sections provided rather than treating each possible type separately.
To display a unit as literal text, put it in quotation marks in the format code. For example, 0.0 "kg" displays 3.5 as 3.5 kg while keeping the stored value numeric. A code such as 00000 can display the number 73 as 00073. This is helpful for fixed-width codes when the values are still safe to store as numbers.
Custom formats can also include conditions and colors, such as a condition in square brackets. Use them carefully: a display rule does not change the value, and not every code works the way a reader might expect across different regional settings. For frequently reused codes, record what the format means so another person can interpret the worksheet.
Dates, percentages, and leading zeroes
Dates are a frequent source of confusion because Excel stores valid dates as serial numbers. Applying a date format to a number changes its appearance, but the displayed date depends on the serial value and workbook date system. A time is stored as a fraction of one day: for instance, one half represents noon when displayed as a time. When showing durations longer than 24 hours, an elapsed-time code such as [h]:mm can show total hours rather than restarting the hour display at each new day.
For percentages, remember that the percent sign changes the display scale. If a cell contains 0.08, Percentage format displays 8%. If it contains 8, the same format displays 800%. Decide whether your input is a decimal fraction or a whole-number percentage before applying the format; otherwise, the display may be off by a factor of 100.
Leading zeroes are more complicated when the value is an identifier rather than a quantity. A custom format such as 00000 can display a numeric 73 as 00073, but the underlying value remains 73. If an identifier is longer than 15 digits, Excel’s numeric precision limit means digits may be changed when entered as a number. Store long account, tracking, or product codes as text from the outset instead of relying on a number format to preserve them.
Why a number format may seem not to work
If changing a format has no visible effect, the cell may contain text rather than a number. This often happens when data is imported, when an apostrophe is used before a value, or when the value contains characters Excel cannot interpret under the current regional settings. A format can change the appearance of numeric values, but it does not by itself convert text such as “12.5” into the number 12.5. Check alignment, warning indicators, and the formula bar, then use an appropriate conversion method if the data needs to participate in calculations.
Other common issues include:
- Too many or too few decimals: the format may round what is displayed. The underlying value can still contain more precision, so calculations may differ from what the rounded display suggests.
- Dates appearing as numbers: a date may be in General format. Apply a date format to check whether the stored serial is a valid date for the workbook.
- Unexpected dates or times: the value may be wrong, or a number may simply have been given a date format. Formatting alone cannot determine the intended meaning.
- Missing leading zeroes: a General format will not preserve them for numeric values. Use a suitable custom format for short numeric codes, or store identifiers as text.
- Hash marks in a cell: Excel may show #### when a column is too narrow to display a formatted number or date. Widen the column first; this usually does not mean the value has been deleted.
Because formatting can hide detail, avoid treating what you see as proof of the exact stored value. Select the cell and inspect the formula bar, or temporarily apply General or a suitable number format. Do not enable the workbook’s precision-as-displayed setting merely to make calculations match rounded displays: that option can permanently discard stored precision.
Choosing a format that suits the data
Choose a number format based on what the value represents and what readers need to compare. Use Currency or Accounting for monetary amounts, Percentage for rates stored as decimal fractions, and a clear date format for calendar dates. For identifiers, choose Text or a carefully designed fixed-width display instead of implying that the values can be added or averaged.
Consistency matters in tables. Use the same decimal places for comparable measurements, the same currency presentation for comparable amounts, and an unambiguous date style where day and month order could be confused. A format that shows 04/05/2026 can mean different dates in different regional conventions; a style that spells out the month can reduce that ambiguity.
Also consider whether rounding is appropriate for the audience. A report might display dollars to two decimal places or a percentage to one decimal place, but that is a presentation choice. If a decision depends on the exact underlying value, inspect the value or provide enough displayed precision rather than assuming that a rounded cell tells the whole story.
Number formats versus formulas and conditional formatting
A number format changes how a value is presented. A formula calculates a value, and conditional formatting applies visual rules such as a fill color based on a condition. These tools can be used together, but they are not interchangeable. For example, a custom format can show a negative number in red without changing it; conditional formatting can color a cell when it meets a rule; and a formula is needed if the actual result must be changed.
Likewise, a format that appends a label such as kg does not add text to the cell’s stored value. That is useful when calculations should still use the number. If the label must become part of a text result for export or concatenation, a formula or a data-conversion step may be more appropriate. Choose the method that preserves the type of data your later calculations and systems require.
Frequently Asked Questions
Does changing an Excel number format change the cell value?
No. It changes the value’s display. Formulas continue to use the stored value unless you edit or convert the cell contents.
How do I create a custom number format in Excel?
Select the cells, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, enter a code in Type, and select OK. For example, 0.00 displays two decimal places.
Why does Excel show a date as a number?
Excel represents dates as serial numbers. If a cell is in General or Number format, you may see the serial instead of a calendar date. Apply a date format, and verify that the underlying number is the intended date.
How can I keep leading zeroes in Excel?
For a short numeric code, use a custom format such as 00000 to display a fixed number of digits. For identifiers that must remain exact, especially those longer than 15 digits, store them as text before entering or importing them.
Why does my percentage look 100 times too large?
Percentage format multiplies the displayed number by 100. If the cell contains 8, Excel displays 800%; if it should represent 8%, enter 0.08 or convert the existing data appropriately.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Schedule an Email in OutlookTo schedule an email in Outlook, write your message, open the menu beside Send, and choose Schedule send…
- Can You Password Protect a Sheet in Excel? Here’s HowYes. In Excel for Microsoft 365 on Windows, go to Review > Protect Sheet, choose which actions users…
- How to Insert a Line in Word Under Text: Easy MethodsTo put a line under text in Word, select the text and press Ctrl+U on Windows or Command+U…
- How to Add a Calendar Drop-Down in ExcelTo add a usable date drop-down in Excel for Microsoft 365 on Windows, put a date list on…
- How to Track Changes in a Word Document and Review EditsTo track changes in a Word document, open it in Microsoft Word, select Review > Track Changes, then…
- How to Schedule an Email in Outlook on Any DeviceTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.