In Excel, value formatting changes how a stored number, date, or text value appears in a cell; it usually does not change the underlying value used in calculations. To apply a format in Excel for Microsoft 365 on Windows, select the cells, open Home > Number Format, and choose a category such as Currency, Date, or Percentage. For more control, press Ctrl+1 to open Format Cells and select a format or create a custom one.
What value formatting means in Excel
People often use “value formatting” to mean the number format applied to a cell. A cell can contain a numeric value such as 0.25 while displaying it as 25%, or contain a date serial number while displaying it as 2-Nov-2026. Formatting changes the presentation, not the stored number that Excel uses in arithmetic. If a cell contains 0.25, for example, percentage formatting displays 25% but does not turn the value into 25.
That distinction matters when checking results, exporting data, or sharing a workbook. A displayed value may be rounded even though Excel retains more precision. A cell showing $12.35 could contain 12.345, for instance. A formula that refers to that cell normally uses the stored value, not the rounded display. If the calculation itself must use rounded numbers, use an appropriate rounding formula or change the source data; changing the number format alone is not enough.
How to apply a format
Select one cell or a range, then choose a format from the Number Format drop-down on the Home tab. Common options include General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, and Text. The exact choices visible in the drop-down can depend on the current selection and Excel interface.
For detailed settings, open the Format Cells dialog with Ctrl+1 on Windows. On Mac, use Command+1. You can also right-click the selection and choose Format Cells. In the dialog, the Number tab offers categories and settings such as decimal places, thousand separators, date types, and negative-number styles. The same dialog also contains controls for alignment, font, borders, fill, and protection; those affect other aspects of appearance or worksheet behavior rather than the cell’s number format.
To format a whole column or range consistently, select it before choosing the format. Excel applies the format to the selected cells, including blank cells, so values entered later into those cells may use that format. Applying a format to an entire column can be convenient for a data-entry area, but avoid doing so unnecessarily in very large workbooks when a smaller range would do.
Choosing among common number formats
Pick a format that makes the value’s meaning easy to read without implying false precision. Useful choices include:
- Number: For counts, quantities, and measurements. Set decimal places to match the useful precision and use a thousands separator for long values.
- Currency or Accounting: For monetary amounts. Currency places the symbol close to the amount; Accounting aligns currency symbols and decimal points in a column, which can make financial tables easier to scan.
- Percentage: For proportions. Excel displays 0.25 as 25%, and 1 as 100%. Choose the decimal places that suit the data.
- Date and Time: For dates and time values that Excel recognizes as numeric date/time entries. Choose a readable style, such as a long date for reports or a compact date for a table.
- Text: For entries that should be treated as text, such as product codes or identifiers with leading zeros. The timing of applying this format matters, as explained below.
General is Excel’s default format. It lets Excel decide how to display many values, and long numbers may appear in scientific notation or with fewer visible decimal places when space is limited. If a column has a specific purpose, setting a suitable format can make the sheet more predictable and readable.
Custom formats and useful examples
When built-in options do not provide the exact appearance you need, select Custom in the Format Cells dialog and enter a format code. A custom number format changes display while keeping a numeric value numeric. For example, the code 0.00 displays two decimal places, while #,##0 displays whole numbers with a thousands separator. These are presentation choices; they do not automatically round or otherwise alter the stored values.
Some practical custom formats are:
0.0%displays a proportion such as 0.256 as 25.6%.00000displays the number 42 as 00042. The stored value remains 42, so this is useful for visual consistency but not a substitute for storing an identifier as text when its digits must remain literal.yyyy-mm-dddisplays a recognized date in a year-month-day layout, which is often easy to sort and scan.0.00;[Red]-0.00;0.00displays positive numbers with two decimals, negative numbers in red with a minus sign, and zero with two decimals. The semicolons separate sections for positive, negative, and zero values.
Custom formats can include up to four sections separated by semicolons, generally for positive numbers, negative numbers, zero, and text. They also support conditions and colors, but complicated codes can be hard for another person to maintain. If a format is difficult to understand at a glance, document its purpose or use a simpler built-in option. Date-format letters can also be context-sensitive: for example, “m” can represent a month or, in a time context, minutes.
Formatting dates, percentages, and identifiers correctly
Excel stores recognized dates as serial numbers and displays them using a date format. That is why changing a date cell to General or Number can reveal a number rather than a calendar date. It does not mean the date has disappeared; it means the display format changed. Conversely, text that merely looks like a date may not behave as a date in sorting or calculations. If Excel does not recognize an imported date, changing its number format may not fix the underlying text.
Percentage formatting is another frequent source of confusion. A stored value of 0.08 displayed as a percentage appears as 8%; formatting a stored value of 8 as a percentage displays 800%. The percentage format scales the displayed number by 100. When entering data, typing a percent sign, such as 8%, makes Excel interpret the entry as a proportion of 0.08. Check a few cells before applying a percentage format to a whole column.
For codes such as 00481, phone numbers, or account identifiers, consider whether the entry is a quantity or a label. A numeric format such as 00000 can show leading zeros, but the underlying number has no zeros at the front. If the identifier must be preserved exactly when copied, exported, or used as a key, storing it as text is often safer. To make future entries text, format the destination cells as Text before entering or importing the values. Applying Text formatting after Excel has already converted an entry does not restore lost leading zeros.
Number formats versus conditional formatting
A number format gives cells a consistent display, such as two decimal places or a date style. Conditional formatting changes appearance when a rule is met—for example, highlighting overdue dates or values below a target. In Excel for Microsoft 365 on Windows, select the cells and choose Home > Conditional Formatting, then select a rule type such as Highlight Cells Rules. Conditional formatting is useful when the appearance should respond to the cell’s value; it does not replace choosing an appropriate number format.
These features can work together. A sales column might use Currency or Accounting formatting for all amounts, while a conditional-formatting rule highlights unusually low results. If a cell looks different from its neighbors, check both its ordinary cell format and any conditional-formatting rules that apply. A rule can make a cell’s font, fill, or other supported appearance differ even when its underlying number format is the same.
Common formatting problems and fixes
- The cell shows hashes: A row may be too narrow to display a formatted number or date. Widen the column or choose a shorter format. Hash marks do not usually mean the value is damaged.
- A number appears as a date: The cell may have a date format. Select a more suitable category such as Number or General and check the result.
- Formatting seems to do nothing: The content may be stored as text rather than as a number or date. A number format changes display, not the data type. Check alignment and whether calculations recognize the entry, then convert or re-import the data appropriately.
- Leading zeros disappear: Excel may have interpreted the entry as a number. Apply Text formatting before entry, import the column as text where the import options allow it, or use a custom display format when the value is genuinely numeric and only needs a visual prefix.
- A displayed total does not match visible rounded figures: Excel may be calculating with extra decimal places. Increase the displayed precision to inspect the values. Do not assume that a two-decimal display means the stored values were rounded.
- Different computers show a different date layout: Date conventions and regional settings can affect how dates are interpreted or displayed. A clear format such as
yyyy-mm-ddcan reduce ambiguity, but confirm that the underlying entries are real dates.
When troubleshooting, first select a problem cell and inspect the formula bar and Format Cells dialog. The formula bar helps reveal the stored content, while the dialog shows the selected number format. If the cell contains a formula, formatting changes its displayed result, not the formula itself.
How to choose a format for a workbook
Think about the audience and what decisions the sheet supports. A data-entry sheet may need clear instructions and consistent formats; a report may prioritize readable totals; an exported data table may need values represented in a way another system can interpret. Before choosing, consider:
- Whether the value is a quantity, a proportion, a date, money, or an identifier.
- How many decimal places are meaningful, rather than simply how many Excel can display.
- Whether leading zeros, signs, or units must be visible.
- Whether the workbook will be copied, imported, or opened in another spreadsheet program.
- Whether the format could mislead—for example, showing a precise-looking amount when the source data is only an estimate.
For shared workbooks, use familiar formats and clear column headings. A heading such as “Rate (%)” or “Amount (USD)” helps explain what the displayed numbers mean, especially if the file might be viewed with different regional settings. Keep in mind that formatting is not validation: a cell formatted as Date can still contain an invalid or unrelated value. Data validation and suitable formulas may be needed when entries must follow specific rules.
Frequently Asked Questions
Does Excel formatting change the actual cell value?
Ordinary number formatting changes how a value is displayed, not the stored value used in calculations. Formatting a number to show fewer decimals does not by itself round the underlying number.
How do I format a number as a percentage without changing its value?
Select the cell and choose Percentage from Home > Number Format. Excel displays the stored proportion multiplied by 100, so 0.2 displays as 20%. Check that the original number is a proportion before applying the format.
Why does Excel show a date as a number?
Excel stores recognized dates as serial numbers. If a date cell is formatted as General or Number, that serial value may appear. Apply a date format to display it as a calendar date again.
How can I keep leading zeros in Excel?
For identifiers, format the cells as Text before entering or importing the values. A custom format such as 00000 can add visible zeros to numeric values, but it does not make those zeros part of the stored number.
What is the difference between a number format and conditional formatting?
A number format controls the regular display of values, such as currency symbols or decimal places. Conditional formatting applies appearance rules based on values or conditions, such as highlighting cells above a threshold.
Related
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
- Cash Flow Format in Excel: Build a Simple Statement
- CAGR Return in Excel: Calculate Investment Growth
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis: Methods, Tools, and ExamplesExcel data analysis means organizing spreadsheet data, checking it for errors, and using formulas, PivotTables, charts, and other…
- Conditional Formatting in Excel: Questions AnsweredIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
- Excel Data Tables: What They Do and How to Use ThemIn Microsoft Excel, a Data Table is a What-If Analysis tool that recalculates a formula for different values…
- Jira Merge Table Cells: Steps and LimitationsYes—Jira’s current rich-text editor can merge adjacent cells in a table when the Merge cells command is available.…
- Freeze Panes in Excel: Keep Rows and Columns VisibleIn Excel for Microsoft 365 on Windows, select the cell immediately below the rows and immediately to the…
- Google Sheets: How to Merge Columns Without Losing DataTo combine values from columns, put the result in a new column: with a first name in A2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.