To show a number with a fixed minimum number of digits in Excel, select the cells, press Ctrl+1, choose Number > Custom, and enter a format such as 00000. A value of 73 will display as 00073, while remaining a number in the cell. For a fixed number of decimal places, use a format such as 0.00. Formatting changes how a value looks, not the value Excel stores.
Use a custom number format when you want consistent display, such as five-digit product codes or two decimal places. If the digits are an identifier that must retain leading zeros as actual characters, store them as text instead. The distinction matters for sorting, calculations, and exporting data.
What You’ll Need
- Excel for Microsoft 365 on Windows, with the workbook open.
- The cells or column you want to format, and an idea of the required digit width or decimal places.
- A decision about whether the entries are numbers for calculations or identifiers such as postal codes, account numbers, or product codes.
You do not need a formula for ordinary number formatting. The built-in Custom number format is usually the simplest choice. If you use Excel for the web or Mac, the same principle applies, but the location of some formatting controls and shortcuts can differ.
Step-by-Step
-
Select the cells. Click a cell or drag across the range you want to change. To format a whole column, click its letter at the top of the worksheet. Applying the format before entering data can help make a column consistent, but it does not force Excel to accept a particular number of digits.
-
Open Format Cells. In Excel for Microsoft 365 on Windows, press Ctrl+1. Alternatively, go to Home and click the small dialog box launcher in the Number group. Right-clicking the selection and choosing Format Cells also works. In Excel for Mac, Command+1 opens Format Cells in many current versions; if that shortcut is intercepted by your system, use the Format Cells command from the Home ribbon or the cell context menu.
-
Choose the right kind of digit format. In the Format Cells dialog, select the Number tab, then select Custom in the Category list. The Type box shows the current format code. Custom is appropriate for leading zeros, fixed decimal places, and other display patterns. If you only need a standard count of decimal places, the Number category is also suitable: set Decimal places there and click OK.
-
Enter a zero pattern for a fixed minimum width. In Type, enter
00000for a five-digit display, then click OK. Each zero is a required digit position, so the value 73 appears as 00073 and 4 appears as 00004. A value that is already longer than five digits is not cut off; for example, 123456 remains 123456. The format sets a minimum display width, not a maximum length. -
Use a decimal pattern when you mean decimal places. Enter
0.00to display two digits after the decimal point. For example, 6.4 displays as 6.40, and 6.456 displays as 6.46 because Excel rounds the display to two decimal places. The underlying value remains available for calculations at its stored precision. To show three decimal places, use0.000. -
Check the result in the worksheet. Look at both a short value and a longer one. Confirm that the leading zeros or decimal places appear as intended, and check that the cell still behaves as expected in a calculation. If the entry is an identifier, try selecting it and checking the formula bar: number formatting can show zeros that are not part of the stored value.
-
Apply the format to similar cells. You can select additional cells and repeat the format, or use Format Painter on the Home tab to copy the appearance. If you paste new data into formatted cells, Excel may preserve or replace formatting depending on the paste option. Use Paste Special > Formats when you want to copy formatting without replacing the cell contents.
For a one-off text result rather than a number format, a formula can create a padded text string. With the value 73 in A2, enter this in B2; it returns the text 00073: =TEXT(A2,"00000"). This is useful for building a label or export field, but the result is text and is not directly a numeric value for arithmetic. Use the custom cell format instead when the original cell should remain numeric.
Common Mistakes
-
Typing zeros into the number and expecting Excel to keep them. Excel normally treats an entry such as 00073 as the number 73 and drops the initial zeros. Apply a custom format such as
00000if you need a numeric value displayed with padding, or enter the value as text if the zeros are part of an identifier. -
Confusing digit width with decimal places. The pattern
00000pads the whole-number part; it does not mean five decimal places. A pattern such as0.00controls decimal display. Pick the pattern based on what you want the reader to see. -
Assuming formatting changes the stored value. Showing 6.46 with a two-decimal format does not necessarily change an underlying value of 6.456. Likewise, a displayed 00073 may still be stored as 73. If a calculation or export needs a rounded or text-converted value, formatting alone may not meet that requirement.
-
Using a numeric format for long identifiers. Excel preserves only 15 significant digits in numeric values. A long tracking number or account code can lose precision if treated as a number. Store such codes as text, especially when every digit must remain exact or when the code begins with zero.
-
Entering a format with the wrong number of zeros. Four zeros mean a minimum width of four digits; six zeros mean six. Check the code in the Type box and test it with a short value before applying it to a large range.
Tips
-
Choose text for codes that are not quantities. Postal codes, employee IDs, and serial numbers are usually labels, not values to add or average. Format the cells as Text before entering or importing them, or use an apostrophe before a manually typed entry. The apostrophe marks text and is not displayed as part of the cell value.
-
Remember that zero placeholders and hash marks behave differently. In custom formats,
0displays a zero when a digit position has no value;#displays a digit only when one is present. For fixed-width padding, use zeros. A format such as#####will not reliably add leading zeros. -
Use the Number category for ordinary decimal settings. If you simply need two decimal places and do not need a special pattern, select Number in Format Cells and set Decimal places to 2. The Custom category is more useful when combining requirements or padding whole numbers.
-
Apply the format to the full data range. If new entries will be added, format the whole intended column or the relevant table column. This reduces differences between existing and new rows. Still check imports and pasted data because some paste actions can overwrite the destination format.
-
Keep a clear distinction between display and data. A custom format is ideal when people need to read consistent-looking numbers and calculations should use the underlying numeric values. If another system requires the padded characters themselves, convert or export the values as text and verify the result in that system.
Troubleshooting
The zeros do not appear. Reopen Format Cells with Ctrl+1, select Custom, and make sure the Type box contains the required number of zeros, such as 00000. Also check that the cells contain numbers rather than text. A text entry may not respond to a numeric custom format in the same way. If a cell contains text and must retain leading zeros, store the complete code as text.
The cell shows ######. This usually means the column is too narrow to display the formatted value. Widen the column by dragging the boundary between its column letters, or double-click that boundary to AutoFit. A format with more decimal places or a longer padded width can require extra space.
The value changes when you reduce decimal places. The displayed number may be rounded even though the stored value remains more precise. Check the formula bar or increase the decimal display temporarily to inspect it. If you need calculations to use a rounded value, use an appropriate rounding operation in the calculation rather than relying on the display format.
Zeros disappear after importing or pasting. The incoming values may have been interpreted as numbers, or the paste operation may have replaced the destination format. For codes, set the destination column to Text before importing when possible. If the source is numeric and you only need the worksheet to display padding, reapply the custom format after the import.
Excel changes a very long code. If a value has more than 15 significant digits, Excel may already have changed digits after interpreting it as a number. Formatting cannot restore lost digits. Import or enter the code as text from the start, then compare it with the original source.
The Custom option is missing or hard to find. In the desktop app, use the Format Cells dialog and check the Number tab for Custom. In Excel for the web, open the Home tab’s number-format controls and look for additional number formats; the available editing options can vary. If the web app does not let you create the required custom pattern, open the workbook in the Windows or Mac desktop app, apply it there, and save the workbook.
Frequently Asked Questions
How do I make every number five digits long in Excel?
Select the cells, open Format Cells, choose Custom, and enter 00000. Values shorter than five digits display with leading zeros; longer values are not truncated.
Does formatting a number with leading zeros change its value?
No. A custom format changes the display only. A cell showing 00073 can still contain the numeric value 73. Store the entry as text if the zeros must be part of the actual code.
How do I show a fixed number of decimal digits?
Use the Number category in Format Cells and set the decimal places, or use a Custom pattern such as 0.00 for two places. Excel may round the displayed result without changing the stored precision.
Can I set a maximum number of digits with a custom format?
A simple zero pattern sets a minimum display width, not a maximum. It will not remove extra digits from a longer number. If a strict limit is required, validate the input separately or store a fixed-length identifier as text.
Can I use this method for postal codes and product IDs?
Yes, but first decide whether they are identifiers or numeric values. For a numeric value that only needs padded display, a custom format works. For a code whose zeros and every character must be preserved, use text, especially for long codes.
Will the formatting still be there when I share the workbook?
Custom number formats are saved with the workbook and generally display in Excel on Windows, Mac, and the web. Other spreadsheet programs or data exports may interpret formats differently, so check the file in the destination application if exact presentation matters.
Related
- Find and Replace Hard Returns in Excel Step by Step
- How to Find a Word in Excel with Find and Replace Tool
- How to Find a Table in Excel: Search and Navigation
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Percentage Calculator: Formulas and ExamplesTo use Google Sheets as a percentage calculator, enter the part and the total in cells, then divide…
- Excel Conditional Formatting Question Mark ExplainedIn Excel conditional formatting, a question mark (?) is a wildcard in criteria: it matches exactly one character.…
- Outlook Never Block Sender: Why Mail Still Goes to JunkChoosing Never Block Sender in Outlook adds an address to your Safe Senders list; it does not guarantee…
- Microsoft Excel Snowball Debt Payoff Template SetupYes. You can make a Microsoft Excel snowball debt payoff template by listing debts from smallest balance to…
- How to Open Excel on a Laptop: Windows and MacTo open Excel on a Windows laptop, select Start, type Excel, and choose the Microsoft Excel app; on…
- Excel Dropdown List: How to Select Multiple ItemsExcel’s standard Data Validation dropdown lets you select only one item at a time. To keep several selections…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.