MS Excel: How To Define An Excel Custom Number Format

Added On: Jul 1, 2023

Last Update: Aug 17, 2023

Excel provides to define the custom format of numbers using the Format cells dialogue box and some other ways. MS Excel provides many built-in formats not only for numbers but also percentages, accounting, dates, currency, and times.

Excel’s built-in formats are more than one and if you are comfortable using that formats then you can also use a custom method to define your own formats of a particular category. Excel Number Format is a very important tool and if you learn then you will have many options that you can apply to your worksheet.

In this blog you will go to learn how to show the required number of decimal places, change the alignment or font color, display a currency symbol, round numbers by thousands, show leading zeros, and much more.

How to Access the Custom Formatting Menu

Select a cell to apply to the format.
Right click on cell and click on Format Cells option from the list or use shortcut ctrl+1 (on windows) and command+1 (On Mac/apple).

In the Format Cell dialogue box

Now, Select the Number option from the category available on the left of the dialogue box or you can also choose other options i.e. Number, Date, Percentage, etc. according to your requirements.
After selecting a category, now you have to choose a formatting style from multiple options available on the right side of the dialogue box.

Or
You can define your custom format of numbers. Click on the built-in formats present on the right side.
Click on the Type text box to edit the pre-define format.
Once you are done with the process, then click on the OK button to apply the formatting to the selected cell.

Custom Integer, Decimal & Currency Formatting

Excel user can also use Custom Number Format for decimals, integers, or currency. To define custom number format for different types few characters are used, which are defined below:

0: This is used to force the display of a digit in its place.
#: This is used to show a digit if it adds to the accuracy of the number (but don't display if a leading zero or a zero at the end of a decimal).
. (dot): This is used to define the decimal points.
[colour]: This is used to define color of the font.

Other characters can also be used at the beginning or end or middle like ",", "$", "£", "+", "-", "(" and ")" to make more readable numbers or to denote currency ($) or positive or negative values.

Excel provides facilities to replace a number with your chosen characters e.g. replace Zeros with the text "NIL".

In Excel you can apply format (one text, two text, three text) on the cell if that contains a numeric value and it will depend on the number (positive or negative).

One Text Format: This format can be applied to all numbers.
Two Text Format: This format is separated by Semi-Colon and the first format is applied to Positive numbers including the value Zero and the Second format is applied to Negative numbers.
Three Text Format: This format is separated by Semi-Colon and the first format is applied to Positive numbers including and Second format is applied to Negative numbers and the last or third format is applied to value Zero.

Decimal & Currency Formatting Examples

Excel Custom Percentage Formatting

Excel also provide custom formatting for percentage. Some characters are used to define custom percentage formatting i.e. "0", "#" and ".", these characters are used in the same way as used for a custom number format.

Excel Percentage format display the numbers with % symbol and multiply numbers with 100.

Percentage Formatting Examples

Excel Custom Date & Time Formatting

Excel provides a feature to define the custom format of Date and Time according to the requirements. Some characters are used to define custom Dates & Times, explained in the below table.

Characters	Explanation	Example
d	Represents day of the month or day of week	d = one or two digit representation (e.g. 1, 12) dd = 2 digit representation (e.g. 01, 12) ddd = abbreviated day of the week (e.g. Mon, Tue) dddd = full name of the day of the week (e.g. Monday, Tuesday)
m	Represents Month (when used as in date)	m = one or two digit representation (e.g. 1, 12) mm = two digit representation (e.g. 01, 12) mmm = abbreviated month name (e.g. Jan, Dec) mmmm = full name of a month (e.g. January, December)
y	Represents Year	yy = 2-digit representation of year(e.g. 99, 08) yyyy = 4-digit representation of year(e.g. 1999, 2008)
h	Represents Hours	h = one or two-digit representation (e.g. 1, 20) hh = two-digit representation (e.g. 01, 20)
m	Represents minute (when used in a time)	m = one or two digit representation (e.g. 1, 55) mm = two-digit representation (e.g. 01, 55)
s	Represents Seconds	s = one or two digit representation (e.g. 1, 45) ss = two-digit representation (e.g. 01, 45)
AM/PM	Represents 12-hour clock time	"AM" or "PM"