Custom cell format
To define a custom format, follow these steps:
1. Enter sample text for the format in a cell, and then select that cell (Excel then displays the sample text in the new format, which helps see the effects of your changes).
2. Do one of the following:
- Right-click on the selection and choose Format Cells... in the popup menu:
- On the Home tab, in the Number group, click the dialog box launcher:
- Press Ctrl+1.
3. In the Format Cells dialog box:
- On the Number tab, in the Category box, select the Custom item:
- In the Type list box, select the custom format you want to base your new custom format. Excel displays the details for the type in the Type text box.
- If the details for the type extend beyond the Type text box, double-click in the Type text box to select all its contents, issue a Copy command (for example, press Ctrl+C), and then paste the copied text into a text editor, such as Notepad. (For a shorter type, you can work effectively in the Type text box. For a longer type, it's easier to have enough space to see the whole type at once.)
- Enter the details for the four parts of the type, separating the
parts from each other with a semicolon:
- If you write formats with conditions such as [< 1000] and [≥ 1000], the format for the calculated condition is applied,
- If you write two formats, the first applies to positive and zero values and the second applies to negative values
- If you write three formats, the first applies to positive values, the second to negative values, and the third to zero
- If you write four formats, they apply to positive, negative, zero, and text values, respectively:
<positive>; <negative>; [zero]; [text]
- If you're working in a text editor, copy what you typed and paste it into the Type text box. Check the sample text to make sure it seems to be correct.
- Click the OK button.
Codes for custom number formats
|General||Displays the number in general format.|
|#||Displays only significant digits and does not display insignificant zeros.|
|0 (zero)||Displays insignificant zeros if a number has fewer digits than the zeros in the format.|
|?||Displays a space for insignificant zeros on either side of the decimal point so that the decimal points align when formatted in a fixed-width font.
It can also be used for fractions with different numbers of digits.
|E-, E+, e-, e+||Scientific notation.|
|$, -, +, /, (, ), :, space||Displays this character.|
|*||Repeats the next character to fill the full width of the column:
|_ (underscore)||Leaves a space equal to the width of the next character.|
|“text”||Displays text enclosed in double quotes.|
|[color]||Displays characters in the specified color – black, blue, cyan, green, magenta, red, white , or yellow .|
|[condition value]||Custom criteria for each number format section.|
See also this tip in French: Format de cellule personnalisé.