Excel Using Custom Formats

(Last Updated On: 2020-11-24)

Number Formatting is an Excel function many People use instinctually.
Every time you right-click that cell in Excell and choose Format Cell to choose a Category under the Number Tab you are doing Number Formatting.

But not everybody really knows what this means. If you choose to change the number format from General to Number do you Actually change the Data? What happens if you change the Number to Text? Does your Data Change?

Well No. Your Data is exactly still the same no difference.

What is Number Formatting

Number Formatting is the VIEW you see in Excel. This is the look and the feel of the cell this makes the whole sheet or workbooks affectiveness either increase or decrease. A Cell showing 6% compared to 0.06 give a much different look and feeling to the data even though they mean exactly the same thing.

Available Number Formats

The below detail can all be found from the Office.com support Article.

General

The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits)

Number

Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Currency

Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Accounting

Also used for monetary values, but it aligns the currency symbols and decimal points of numbers in a column.

Date

Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Time

Displays date and time serial numbers as time values, according to the type and locale (location) that you specify. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Percentage

Multiplies the cell value by 100 and displays the result with a percent (%) symbol. You can specify the number of decimal places that you want to use.

Fraction

Displays a number as a fraction, according to the type of fraction that you specify.

Scientific

Displays a number in exponential notation, replacing part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. You can specify the number of decimal places that you want to use.

Text

Treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers.

Special

Displays a number as a postal code (ZIP Code), phone number, or Social Security number.

Custom

Each one of the above-mentioned formats is basically a carefully planned Custom format. It allows you to Modify and Existing Number Format.

So that annoyance of why does Excel not have the Number Format you require is a thing of the past you can build it yourself.

Structure

The Structure is split into four distinct sections each separated by a Semicolon. They are always in the same order. Positive, Negative, Zero and Text.

It is possible to not fill in one or more sections. In this case, the Section not filled in will use Section 1 (Positive) as the format applied to it. If you don’t want to format on a Specific section simply leave the section blank by putting the Semicol there.

An example would be: [Blue];;; with three semicolons.
This will make all Positive number Blue and blank out all other Values\Text.

Appose to: [Blue]; with one semicolon.
Which will make all values\text except negative Values blue. Negative Values will then be blanked out.

Or lastly: [Blue] with no semicolon.
All Values\Text will be Blue.

Characters that display natively

These characters appear in number format without needing special handling. As you type these characters they will display.

CharacterComment
$Dollar
+-Plus, minus
()Parentheses
{}Curly braces
<>Less than, greater than
=Equal
:Colon
^Caret
Apostrophe
/Forward slash
!Exclamation point
&Ampersand
~Tilde
 Space character

Escaping characters

Certain Characters require special handling to display. The escape character in custom number formats is the backslash (\). This has to be placed in front of the character to be displayed. These characters are normally the Place holders.

Placeholders

Placeholders have special meaning in custom number format codes.

CharacterPurpose
0Display insignificant zeros
Leading Zeros will still be displayed
#Display significant digits
Leading Zero’s will be Remove
?Display aligned decimals
.Decimal point
,Thousands separator
*Repeat digit
_Add space
@Placeholder for text

Colors

The following 8 colors can be specified by name in a number format:
[black] [white] [red][green] [blue] [yellow] [magenta] [cyan].
Color names must appear in brackets.

Colors by index

Except for the colors mentioned above, it is possible to specify colors by index. [Color1],[Color2] up until [Color56]. See below Grid of all Colors.

Conditionals

Custom number formats can also be written with up too two conditions.
These conditions must be in Square Brackets Separate from the color.
Example: [Condition1];[Condition2]

[Red][<40]0 ” Fail”;[Blue][>=40]0 ” Pass”

If your condition to become more complicated than two conditions you can look at Conditional Formatting which handles formatting much easier.

Hide all content

You can actually use a custom number format to hide all content in a cell. The code is simply three semi-colons and nothing else ;;;

********************************************************

If you liked what you read Please Share.
I’d love it if you followed me on YouTube and Facebook.

Also, feel free to subscribe to my posts by email.
Donations for the site can be made here.

Thanks for reading.
Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *