In Excel for Microsoft 365 on Windows, select the cells and press Ctrl+1 to open Format Cells, then choose Number > Custom. On a Mac, press Command+1. There is no separate built-in shortcut that takes you directly to the Custom category; you still select it in the dialog.
What the shortcut opens
Ctrl+1 is a shortcut to the Format Cells dialog, not a command that applies a particular number format. The dialog contains settings for number display, alignment, font, borders, fill, and protection. To create or edit a custom number format, use its Number tab and select Custom in the Category list. The code box shows the selected format and allows you to enter a new one.
The shortcut works on selected cells, so select the cells before opening the dialog. You can select a single cell, a range, or a whole column. If a cell is active within a contiguous data region, Ctrl+Shift+Arrow keys can extend the selection to an edge; use those only when you intend to select that full span.
On Windows, another route is Home > Number Format dropdown > More Number Formats, then Number > Custom. The dialog launcher in the Number group on the Home tab also opens Format Cells. These routes are useful if you do not remember the shortcut or are using a device without a full keyboard.
What a custom number format changes
A custom format changes how a cell value is displayed, not the value stored in the cell. For instance, formatting the number 42 with 00000 makes it appear as 00042, while the underlying value remains 42. A calculation using that cell still uses 42. This distinction is useful for reports and identifiers, but it matters when sharing data with other systems.
Formats can control decimal places, separators, symbols, dates, times, colors, and text appearance. They do not round or convert the stored value merely because fewer digits are displayed. If a cell contains 3.456 and the format shows two decimal places, it appears as 3.46, but calculations continue to use 3.456 unless a formula or another operation changes the value.
Custom formatting is therefore not the same as entering a formula or changing the cell’s contents. It is often a good choice when you want a consistent presentation while keeping numeric values available for sorting, totals, and calculations.
How to enter a custom format
- Select the cells you want to format.
- Press Ctrl+1 on Windows, or Command+1 on Mac.
- In Format Cells, open the Number tab and select Custom in the Category list.
- Choose a suitable existing code, or type a code in the Type box. The Sample area previews how the active cell will look.
- Select OK. Reopen the dialog if you need to adjust the code or apply it to a different selection.
Excel generally adds a newly created custom format to the workbook’s available formats, so it can be selected again in that workbook. Availability and the list of built-in formats can vary with regional settings and Excel version. If a code is not accepted, check that its punctuation and quoted text are entered exactly as intended.
In Excel for the web, use the Home tab’s number-format controls and the option for more number formats if it is available in your interface. Keyboard behavior can depend on the browser and keyboard layout, so the ribbon is the dependable route there. The desktop Format Cells dialog provides the full custom-format workflow described above.
Understand the parts of a format code
Excel uses placeholders to decide how digits appear. The most common are 0, #, and ?. A zero placeholder displays a digit even when that position would otherwise be empty; a number sign displays a digit when one exists but does not force an extra zero; and a question mark reserves space for an absent digit to help align values. For example, 0.00 displays two decimal places, while #,##0.## permits up to two decimal places without forcing trailing zeroes.
A semicolon can divide a code into sections. The sections, in order, apply to positive numbers, negative numbers, zero, and text. For example, #,##0.00;[Red](#,##0.00);-;@ displays positive numbers with two decimals, negative numbers in red parentheses, zero as a dash, and text as entered. You do not have to define all four sections; if a section is omitted, Excel applies its default behavior for the remaining values.
Text that should be displayed literally can often be enclosed in quotation marks in the format code. For example, 0.0" kg" adds a space and kg after a number without changing the number itself. Some characters have special meaning, so quotes or a backslash may be needed to display them literally. Color names such as [Red] can be used in a section, but custom formats do not provide the full range of conditional formatting rules.
Useful examples for everyday spreadsheets
- Show leading zeroes: use
00000to show a five-digit appearance. The value 42 displays as 00042. - Show thousands separators and two decimals: use
#,##0.00. The value 1234.5 displays as 1,234.50 in a region that uses a comma for grouping and a period for decimals. - Show a percentage: use
0.0%. A stored value of 0.125 displays as 12.5%. The percent sign format scales the displayed number; it does not rewrite the stored value. - Add a unit label: use
0.0" kg"so 2.5 appears as 2.5 kg while remaining numeric. - Display a readable date: use
mmm d, yyyyto show a date such as Jan 5, 2026, subject to the workbook’s regional settings. - Show elapsed hours: use
[h]:mmfor durations that can exceed 24 hours. Without brackets, an hour format can cycle through the time of day rather than show a total duration.
For phone numbers, a pattern such as (000) 000-0000 can display a ten-digit numeric value in a familiar layout. This is only a presentation solution: country codes, extensions, variable lengths, and leading zeroes may make text storage more appropriate. For IDs, postal codes, or account references, consider storing the data as text when arithmetic is not meaningful and exact characters must be preserved.
Custom formats versus changing the data
Custom number formats are best when the content is genuinely numeric and you only need a different display. A numeric value with a currency format can still be summed; a duration formatted as hours and minutes can still be used in calculations. A formatted ID may look right in the worksheet, but another program that reads the underlying value may not see the displayed zeroes or punctuation.
That difference becomes important when exporting or copying data. A CSV file stores text values, not Excel’s workbook formatting instructions, so a value displayed as 00042 may be exported as 42. If those zeroes are part of the identifier itself, store the identifier as text or transform it deliberately before export. Likewise, do not use a custom format as a substitute for cleaning values that contain inconsistent spaces or characters.
Use formulas or data conversion when the value itself must change. Use a custom format when the numeric value should stay intact and only its on-screen appearance needs to change. This distinction helps avoid surprises in lookups, imports, and reports.
Windows, Mac, and web differences
For Excel on Windows, Ctrl+1 is the standard shortcut for Format Cells. In Excel for Mac, the corresponding shortcut is Command+1. The key sequence opens the dialog; it does not apply a saved custom code automatically. If your keyboard layout or system settings intercept a shortcut, use the ribbon route instead.
The dialog’s appearance and the route to it can differ slightly between Windows and Mac, but the core operation is the same: open the cell-format settings, go to Number, and choose Custom. On Mac, the Format menu or the Home tab’s number-format controls are alternatives when the shortcut is inconvenient.
Excel for the web has a more limited interface than the desktop apps, and browser shortcuts may compete with Excel shortcuts. Use the Home tab’s number-format menu to look for additional number-format options. If you need to create or manage a more involved custom code and the web interface does not expose the needed controls, open the workbook in the desktop version of Excel.
Common problems and how to fix them
- The number still looks unchanged: confirm that the intended cells were selected and that the code is in the Type box under the Custom category. Check the Sample preview before selecting OK.
- Leading zeroes disappear after export: the format changes display, not the stored value, and CSV does not retain Excel formatting. Store identifiers as text if the zeroes are part of the data.
- A date code shows an unexpected result: Excel stores dates as serial values, and format letters are interpreted by context. In date and time codes,
mcan mean month or minute depending on nearby time components. Use a clear pattern such asmmm d, yyyyfor a date orh:mm AM/PMfor a time. - A duration resets after 24 hours: use an elapsed-time format such as
[h]:mmrather than a clock-time hour format. - The display differs on another computer: decimal and grouping separators, date conventions, and currency symbols can depend on regional settings. Test the workbook under the settings and export process your recipients will use.
Also remember that a display format does not prevent Excel from storing a value with more precision than is shown. If you need the rounded number itself for later calculations, rounding must be handled in the data or by an appropriate formula, not just by showing fewer decimal places.
Frequently Asked Questions
What is the shortcut for custom number format in Excel?
Press Ctrl+1 on Windows or Command+1 on Mac to open Format Cells. Then choose Number > Custom. There is no dedicated default shortcut that opens the Custom category directly.
How do I open Format Cells without a shortcut?
Select the cells, then use the Home tab’s Number Format dropdown and choose More Number Formats, or open the Format Cells dialog launcher in the Number group. The exact menu presentation can vary by platform.
Can I make Excel always show leading zeroes?
For a fixed-width numeric display, apply a code such as 00000 to five-digit values. If the zeroes are part of an ID that must remain intact outside Excel, store the ID as text instead of relying only on formatting.
Does custom formatting change the cell’s actual value?
No. It changes how Excel displays the value. A percentage format, for example, can show 0.25 as 25%, while the stored number remains 0.25.
Why does my custom format not appear in a CSV file?
CSV files do not preserve Excel workbook formatting. They contain exported values, so a custom display such as padded zeroes, colors, or date presentation may not carry over as it appears in the worksheet.
Related
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
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 Clear Cache in the Outlook Desktop App SafelyTo clear Outlook’s local mailbox cache in classic Outlook for Windows, close Outlook, rename the account’s offline data…
- Data Validation Excel Shortcut and How to Use ItIn Excel for Microsoft 365 on Windows, press Alt, then A, V, and V—one key at a time—to…
- How to Add a Drop-Down in Excel Using Keyboard ShortcutsTo add a drop-down in Excel for Microsoft 365 on Windows, select the cell or cells, press Alt+D,…
- How to Change View Size in Outlook Email on Any ScreenTo change the size of an email in Outlook, adjust message zoom in classic Outlook for Windows, or…
- How to Check Duplicate Values in ExcelTo check for duplicate values in Excel, select the cells you want to inspect and choose Home >…
- Excel New Sheet Shortcut: Add a Worksheet in SecondsIn Excel for Microsoft 365 on Windows, press Shift+F11 to insert a new worksheet. The new sheet is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.