How to format cells in Excel
A cell format changes how a value looks, not the value itself. 1234.5 can show as $1,234.50 and 46027 as 1/5/2026. This guide covers the formats you will use most and how to build your own.
In short: select the cells and press Ctrl+1. In the Format Cells dialog, pick a category on the Number tab or choose Custom and type a format code such as #,##0.00.
The Format Cells dialog
Ctrl+1 is the shortcut Microsoft lists for opening Format Cells in Excel for Windows. You can also right-click the selection and choose Format Cells, or open the number format box on the Home tab and click More Number Formats. The dialog has six tabs:
| Tab | What it controls |
|---|---|
| Number | Number, currency, accounting, date, time, percentage, text and custom formats |
| Alignment | Left, right, center, wrap text, text direction, center across selection |
| Font | Typeface, size, color, bold, italic |
| Border | Lines around and between cells |
| Fill | Background color and pattern |
| Protection | Locked and Hidden, which only take effect once the sheet is protected |
Number formats you will use most
The built-in categories cover most needs. Keep in mind the difference between what is stored and what is shown:
| Category | Stored value | Shown as |
|---|---|---|
| Number, 2 decimals, thousands separator | 1234.5 | 1,234.50 |
| Currency | 1234.5 | $1,234.50 |
| Short date | 46027 | 1/5/2026 |
| Percentage, 1 decimal | 0.125 | 12.5% |
| Text | 00123 | 00123 (stays text) |
To Excel, a date is a serial number, and the format turns it into something readable. That is why you can do math with dates, as shown in date functions. And because a format only changes the display, Excel keeps calculating with 1234.5 even when the cell shows $1,234.50. To actually cut decimals, use ROUND.
Custom formatting in Excel
Under Number > Custom, type your own code into the Type box. A few codes worth knowing:
| Format code | Value | Shown as |
|---|---|---|
#,##0.00 | 1234.5 | 1,234.50 |
$#,##0.00 | 1234.5 | $1,234.50 |
0 "pcs" | 25 | 25 pcs |
00000 | 2134 | 02134 (ZIP code with leading zero) |
mm/dd/yyyy | 46027 | 01/05/2026 |
dddd, mmmm d, yyyy | 46027 | Monday, January 5, 2026 |
mmm yy | 46027 | Jan 26 |
[h]:mm | 1.5 | 36:00 |
#,##0.00;[Red]-#,##0.00 | -80 | -80.00 in red |
How the placeholders work: 0 always shows a digit, padding with zeros if needed. # shows only digits that are really there. Literal text goes in double quotes. A format can have up to four sections separated by semicolons: positive numbers, negative numbers, zero and text.
Hours over 24 with [h]:mm
When you add up working hours, the normal time format rolls over after 24 hours, so 36 hours show as 12:00. The square brackets in [h]:mm tell Excel to keep counting the hours. More in calculating time.
Merge cells or center across selection?
For a heading above several columns, many people reach for Home > Merge & Center. It looks fine but causes trouble later: merging keeps only the content of the upper-left cell, sorting and filtering ranges with merged cells cause problems, and selecting a single column suddenly grabs its neighbors.
The better option: select the cells, press Ctrl+1, and on the Alignment tab set Horizontal to Center Across Selection. The text looks exactly as centered, but the cells stay separate.
The same tab has Wrap text, which breaks long text inside the cell instead of letting it spill into the next one. For a line break at a specific spot, press Alt+Enter while typing. If you were searching for a formula to merge cells, you probably want to join their text: that is a job for =A2&" "&B2 or TEXTJOIN, not for formatting.
Cell styles and Format Painter
Home > Styles > Cell Styles holds ready-made combinations such as Heading 1, Input, Calculation, or Good, Neutral and Bad. If input cells always get the same style, anyone opening the file can see where to type. Create your own with New Cell Style.
The brush icon Format Painter in the Clipboard group copies the format of one cell to others. Double-click it to keep it active until you press Esc. For lists that grow, an Excel table is often easier, because new rows pick up the same format automatically.
Hide the gridlines in Excel
Forms, dashboards and printouts look calmer without the gray helper lines. On the View tab, in the Show group, clear the Gridlines check box. This applies to the current sheet only and leaves any borders you added yourself untouched.
Common formatting problems
A number turns into a date
Type 1/5 or 1-5 and Excel often reads it as a date. Once the date format is in the cell, it stays for later entries too. Reset the format to General or Number with Ctrl+1.
#### instead of a number
The column is too narrow for the formatted number or date. Double-click the right edge of the column header to fit it. With dates, #### can also mean the value is negative.
Text format stops a formula from calculating
If a cell is formatted as Text, a formula typed into it stays as plain text. Set the format to General, click the cell, press F2 and then Enter.
Format instead of rounding
Two decimals on screen do not mean Excel calculates with two decimals. A total can therefore differ by a cent from the sum of the values you see.
Format Cells in German Excel
| English | German |
|---|---|
| Format Cells (Ctrl+1) | Zellen formatieren (Strg+1) |
| Custom, Type | Benutzerdefiniert, Typ |
#,##0.00 | #.##0,00 (comma and period swapped) |
dd.mm.yyyy | TT.MM.JJJJ (T for Tag, J for Jahr) |
[Red] | [Rot] |
| Center Across Selection | Über Auswahl zentrieren |
| Cell Styles | Zellenformatvorlagen |
| Gridlines | Gitternetzlinien |
Format codes are translated automatically when a workbook opens in another language version, so you do not have to rebuild files for colleagues abroad.
Which Excel versions support this?
The Format Cells dialog, custom number formats, Center Across Selection and cell styles are part of every current desktop version of Excel for Windows, from Excel 2016 to Microsoft 365.
FAQ
What is the shortcut for Format Cells in Excel?
Ctrl+1 on Windows. The right-click menu gets you there as well.
How do I keep a leading zero?
Use a format code made of zeros, such as 00000 for five-digit ZIP codes. The value stays a number. For phone numbers or IDs you never calculate with, the Text format is the simpler choice.
How do I hide what is in a cell?
Use the custom format ;;; (three semicolons). All four sections are empty, but the value still shows in the formula bar, so this is not real protection.
Can a format change color based on the value?
Simple colors for negative numbers work with [Red] in the format code. For anything beyond that, such as "sales below target in red", use conditional formatting.