To edit a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom on the Number tab, change the code in the Type box, and select OK. This changes how the values look—not the values stored in the cells—so you can display numbers as dates, percentages, phone numbers, or other patterns without changing the underlying data.
Before editing, decide what you want the cells to show and whether the format should apply to one cell or a whole range. It also helps to know the data type: a number, date, or text value may respond differently to the same format code. If a format is important, make a note of its existing code before replacing it so you can restore it if needed.
What You’ll Need
- A workbook open in Excel for Microsoft 365 on Windows, with the cells you want to format.
- A clear idea of the display you want, such as two decimal places, a date with a month name, or a fixed number of leading zeros.
- Optional: the format code you intend to use. You can also edit the existing code directly in Excel’s Format Cells dialog.
Step-by-Step
- Select the cells to format. Click one cell, or drag across a range. To apply the format to a whole column, click its column letter; for nonadjacent cells, hold Ctrl while selecting each cell or range. Check that you have not selected a header or unrelated data that should keep its current appearance.
- Open Format Cells. Press Ctrl+1 on Windows. Alternatively, go to Home and select the small dialog box launcher in the lower-right corner of the Number group. In the dialog, open the Number tab if it is not already selected. On Excel for Mac, Command+1 opens Format Cells; the dialog layout may differ slightly, but the Number and Custom choices serve the same purpose.
- Choose Custom. Select Custom in the Category list. The Type box shows the format code for the active cell or selection. If Excel has a built-in format that is close to what you want, you can choose it first and then customize the code. The Sample area previews how the selected cell’s value will appear.
- Inspect the current code before changing it. Click in the Type box and note or copy the existing code if you might want to restore it. Excel may show a code that applies to the active cell even when you selected multiple cells with different formats. Check the preview and, after applying, confirm the result across the whole selection.
- Edit or replace the code. You can modify the current code, or select the text in the Type box and enter a new one. For two decimal places, use
0.00. For a thousands separator and two decimals, use#,##0.00. For whole numbers with leading zeros to a minimum of five digits, use00000. The zero placeholders display zeros when a digit is missing; the number sign placeholders do not force extra zeros. - Use sections when values need different appearances. A custom number format can have up to four sections, separated by semicolons: positive numbers; negative numbers; zero values; text. For example,
#,##0.00;[Red]-#,##0.00;0.00;@displays positive numbers with two decimals, negative numbers in red with a minus sign, zero with two decimals, and text as entered. You do not have to include all four sections; the meaning depends on how many you provide. - Check the preview and apply the format. Select OK to apply the code, then look at several cells in the range, including a negative value or zero if those occur in your data. The preview is useful, but it may show only the active cell’s value. If the result is not right, reopen Format Cells and revise the Type box rather than changing the stored data.
- Save the workbook and verify the stored values if necessary. A custom format affects display only. Select a formatted cell and inspect the formula bar to see the underlying value. For example, a number displayed as
00042may still be stored as the number 42. If you need the zeros to become part of the actual text, a number format alone is not enough; convert the data to text using a suitable method before relying on it as an identifier.
Here are a few other useful patterns to try in the Type box. Use 0% to show a value such as 0.25 as 25%, or 0.0% to show one decimal place. Use mmm d, yyyy for a date such as Nov 3, 2026, and h:mm AM/PM for a time with an AM or PM marker. To show a number with a label, enter a format such as 0 "items"; the word appears in the cell but is not added to the underlying value. Check the sample before applying any pattern, especially when working with dates and times.
Common Mistakes
Assuming the format changes the value. Number formats only control display. A value displayed as a date, currency amount, or padded code can still be a regular serial number or numeric value underneath. This matters when sorting, exporting, doing calculations, or using the data in another application.
Using a zero placeholder when you meant an optional digit. In a format code, 0 forces a digit to appear, while # displays a digit only when one exists. For example, 0000 pads a number to four digits, but #### does not pad it. Use ? when you want a space reserved for an insignificant digit, often to align decimals or fractions.
Forgetting that phone numbers and IDs may be text. A format such as 000-000-0000 can display punctuation for a numeric value containing ten digits. It cannot restore digits that Excel already removed from a value entered as a number, and it will not necessarily transform text that looks like a number. For identifiers that must preserve leading zeros, importing or storing them as text is often safer.
Confusing month and minute codes. In date and time patterns, m can represent a month or a minute, depending on its position. Near hours or seconds it can mean minutes; near a year or day it usually means months. If a date or time looks unexpected, test a short pattern such as mmm d or h:mm and check the preview before applying it broadly.
Typing literal words or punctuation without quoting them. Some characters have meaning in format codes. Put text you want displayed in quotation marks, as in 0 "kg", and use the preview to confirm the result. Do not add a currency symbol or label unless it is appropriate for the data; a custom display does not perform a currency conversion or attach a real unit to the value.
Tips
Build a format from the simplest working pattern. First make the number of decimal places correct, then add a thousands separator, color, punctuation, or a label. This makes it easier to spot which part of a code causes an unexpected result. For instance, test 0.00 before extending it to a multi-section format for positive and negative values.
Use the Sample area as a quick check, but test more than one kind of value when the selection contains mixed data. A format can make a positive number look right while displaying a negative number or zero in an unhelpful way. If you set separate sections, decide deliberately how negative values and zeros should appear rather than accepting a default that could hide important information.
Keep a note of reusable custom codes in a workbook documentation sheet or a trusted team reference. Excel stores custom formats in the workbook, so a format that exists in one file may not automatically be available in another. When sharing a workbook, open it on the recipient’s version of Excel if possible and check that the display is still clear.
To remove a custom appearance, select the cells and choose a built-in format from Home > Number Format, such as General, or use Format Cells to select another category. General may not restore the original display exactly if the value was changed or entered differently, so use Undo promptly if you only just applied the wrong code.
Troubleshooting
The format does not change the display. Reopen Format Cells and check that the correct cells are selected and that the code is in the Type box under Custom. Confirm the cell contains a number or date rather than text. A numeric-looking text value may not respond to numeric format codes. Also check whether the worksheet is protected or the cell is part of a table or imported data range with other formatting controls.
The date appears as a number. Excel stores dates as serial numbers. Apply a date format from the Date category or use a custom pattern such as mmm d, yyyy. If the value remains unchanged, it may be text rather than a real Excel date. Changing its number format will not convert text into a date; convert or re-enter the value as a date first.
The cell shows ######. This often means the column is too narrow to display the formatted value, especially after adding decimals, a longer date, or a currency symbol. Widen the column by dragging its right boundary, or double-click that boundary to AutoFit. If widening does not help, check whether the value is a negative date or time, which some date formats cannot display as expected.
The zeros or punctuation are missing. Confirm that the code uses the right placeholders and that the value has not already lost information. A custom format can show leading zeros for a numeric value, but it cannot recover a zero that was discarded from the underlying data. If you entered a long ID as a number, Excel may also have changed its precision; formatting does not recover digits that were lost.
The code is rejected or the preview looks wrong. Check for unmatched quotation marks, misplaced semicolons, and characters that need to be treated as literal text. Try a simpler code first, then add one feature at a time. If you are editing a code with color or condition sections, ensure each section is separated correctly. In Excel for the web, the available formatting controls can vary; if you cannot find or edit the needed Custom code, open the workbook in the desktop app and use Format Cells.
Frequently Asked Questions
Will editing a custom number format change my formulas or calculations?
No. A custom number format changes how a cell is displayed, not the value used in calculations. A cell showing 25% might contain 0.25, and a cell showing five padded digits might contain an ordinary integer.
Can I create a custom format for just one cell?
Yes. Select one cell before opening Format Cells and apply the code. You can also select a range or an entire column to apply the same display pattern to multiple cells.
How do I show leading zeros in Excel?
For numeric values, apply a custom format with the required number of zero placeholders. For a minimum of six displayed digits, use 000000. If the zeros must be part of the stored identifier, keep the value as text instead of relying only on formatting.
Can a custom format display words with a number?
Yes. Put the literal word in quotation marks in the format code, for example 0 "boxes". The word is only a display label; the cell still contains a number, which can remain useful for calculations.
Where are custom formats saved?
Custom formats are saved with the workbook, not as a universal setting that is guaranteed to appear in every workbook. If another file or computer does not show the format in its Custom list, copy a formatted cell or enter the code again in that workbook.
Can I edit a custom number format in Excel for the web?
Excel for the web includes number-format controls, but the available options can differ from desktop Excel. If you cannot access the Custom category or the code you need, open the workbook in Excel for Microsoft 365 on Windows or Mac and edit it through Format Cells.
Related
- How to Delete Borders in Excel Without Clearing Data
- How to Delete Blank Lines in Excel Safely
- Delete Blank Rows in Excel Quickly: Safe Ways to Clean
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change Your Calendar View in Outlook: A Step-by-Step GuideTo change your calendar view in Outlook, open Calendar and choose Day, Work week, Week, or Month from…
- Sunday Service in Google Sheets: Planning and SchedulingYes. Google Sheets can serve as a shared planning sheet for a Sunday church service: use it to…
- How to Create Inbox Rules in Outlook on Windows and WebTo create an inbox rule in Outlook, open the rules settings, choose a condition such as who sent…
- Google Sheets Date Format: Formulas, Examples, and TipsTo show a date in a particular format with a Google Sheets formula, use TEXT: if A2 contains…
- COUNTIFS Syntax in Google Sheets: Examples and TipsGoogle Sheets uses =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) to count rows that meet two or more conditions at…
- Change the Print Area in Excel: Steps and FixesTo change the print area in Excel for Microsoft 365 on Windows, select the cells you want to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.