Excel Custom Formats

Excel has several built-in formats for number, currency, percentage, accounting, dates and times. You can also create your own formats if required.

To create a custom format, press Ctrl + 1 or right click > Format Cells

Then select Custom.

Let us understand the Excel Number Format

Excel understands the format in 4 different sections, separated by semicolons, in the order:

POSITIVE; NEGATIVE; ZERO; TEXT

Let us look at an example:

#,##0.000; (#,##0.000); “_”; [Blue]

1. Format for positive numbers (display 3 decimal places and a thousands separator).
2. Format for negative numbers (same as positive numbers, but enclosed in parenthesis).
3. Format for zeros (display underscore instead of zeros).
4. Format for text values (display text in blue font color).

Keep in Mind

1. Custom format only changes the visual representation of the cell — not the underlying value.
2. It is not mandatory to include all four sections. If a custom format contains just 1 section, that format will be applied to all number types — positive, negative and zeros. If a custom number format includes 2 sections, the first section is used for positive numbers and zeros, and the second section — for negative numbers. A custom format is applied to text values only if it contains all four sections.
3. Type General to use the default format for any section.
4. Skip the code section to hide the value(s). For example General; ; ; General will only show positive numbers and words.

Digit and Text Placeholders

Excel formatting — broad tips

You can create an infinite number of number formats. Some common implementations are given.

Control decimal places

The location of the decimal point in the number format code is represented by a period (.). The required number of decimal places is defined by zeros (0).

0 or # — display the nearest integer with no decimal places.
0.0 or #.0 — display 1 decimal place.
0.00 or #.00 — display 2 decimal places, etc.

There is a difference between 0 and # in the integer part of the format code.

If the format code has only pound signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point. For example, if you type 0.75 in a cell with #.00 format, the number will display as .75. If you use 0.00 format, the number will display as 0.75.

Show thousands seperator

To create an Excel custom number format with a thousands separator, include a comma (,) in the format code.

#,### — display a thousands separator and no decimal places.
#,##0.000 — display a thousands separator and 3 decimal places.

Round numbers by thousand, million, etc.

Microsoft Excel separates thousands by commas if a comma is enclosed by any digit placeholders — pound sign (#), question mark (?) or zero (0). If no digit placeholder follows a comma, it scales the number by thousand, two consecutive commas scale the number by million, and so on.

For example, if a cell format is #.00, and you type 9000 in that cell, the number 9.00 is displayed.

Text and spacing in custom Excel number format

To display both text and numbers in a cell, do the following:

To add a single character, precede that character with a backslash (\).
To add a text string, enclose it in double quotation marks (“ “).
For example, to indicate that numbers are rounded by thousands and millions, you can add \K and \M to the format codes, respectively:

To display thousands: #.00,\K
To display millions: #.00,,\M

Display leading zeros

Typing 000006 will show 6 in Excel. If you need numbers consisting of 6 digits, use the following format code: 000000

Display zeroes as dashes or blanks

General; -General; “-” will show “-” instead as 0.

Add indents with custom Excel format

Cell contents will ride up right against the cell border. But you can indent information within a cell. To add an indent, use the underscore (_) to create a space equal to the width of the character that follows it.

The commonly used indent codes are as follows:

To indent from the left border use _(
To indent from the right border use_)
Most often, the right indent is included in a positive number format, so that Excel leaves space for the parenthesis enclosing negative numbers.

For example, to indent positive numbers and zeros from the right and text from the left, you can use the following format code:

0.00_);(0.00); 0_);_(@

Or, you can add indents on both sides of the cell:

_(0.00_);_((0.00);_(0_);_(@_)

The indent codes move the cell data by one character width. To move values from the cell edges by more than one character width, include 2 or more consecutive indent codes in your number format.

Change font color

There are colors available:

[Black]
[Green]
[White]
[Blue] [Magenta]
[Yellow]
[Cyan]
[Red]
[Green]General;[Red]General;[Black]General;[Blue]General

YouTube:
https://youtu.be/ztq-ERfoImk

GitHub:
https://github.com/havishmad/excel_custom_formats