Excel Custom Format in Format Cells

This Excel tutorial explains how to use Excel Custom Format in Format Cells to format date, time, number, text, conditional formatting, leading zero, etc.

You may also want to read:

Excel Extract Time from Date Time

Excel Display Time from Date Time

What is Excel Custom Format?

Excel guesses the most appropriate format for the cells when you type something in a Cell. For example, if you type 31/1/2014, Excel guesses it would be a Date, Format is dd/m/yyyy, but it would not guess it is a Text. If you type 123, Excel guesses it is a number format. You can customize the display of the value in Format Cells, for example, change format dd/m/yyyy to dd-mm-yyyy.

While you change the display of a value, it does not change the underlying value. For example, if you change the format of number 0.3333 to 2 decimal places, it will display as 0.33, but the underlying value is still 0.3333. Similarly, if you display a date from dd/mm/yyyy as mm/yyyy, you don’t lose the day.

One common issue due to incorrect format is that a formula displays as text instead of a formula where you visually see the formula with = sign, but not the result value. This occurs when you customize a format for the Cell and then decide to change the Cell to a formula. In that case , go to Format Cells and change the format to “General”. Finally, double click on the formula Cell and then press Enter.

Apply Custom Format

Right click on the cell which you want to change formatting, then choose Format Cells. The below screenshot shows a list of formats that you can choose to display. Since most of them are self-explanatory, this tutorial will only focus on “Custom”.

custom_format_01

Select  “Custom”

text2

Custom allows you to hard code the format you want. Custom comprises 4 sections, separated by semi-colon:

Format for positive value ; Format for negative value ; Format for zero value ; Format for text

If no semi-colon is used, the condition applies to all kind of value

If one semi-colon is used, the first part applies to positive value, second part applies to negative value

If you want to skip one section in the middle, add a semi-colon to indicate the skipped section

Custom Format of value and text

The below tables shows common notations that are to be used in formatting a value or text

Notation Explanation Example
0 Place a dummy 0 if the specified position does not have a number.If there is a number at the specified position, it does not do anythingIf 0 is used in digit and 0 is at position shorter than the digit, rounding happens Format 1234.5 with 0.00 results in 1234.50Format 1234.56 with 0.0 results in 1234.6
# Place the actual number in the specific positionIf there is a number at the position specified with #, it does not do anythingIf # is used in digit and # is at position shorter than the digit, rounding happens Format 1234.56 with #.## results in 1234.56Format 1234.56 with #.# results in 1234.6
. Place a decimal point at the specific location for formatting decimal part and integer part Format 1234.56 with #.## results in 1234.56
? Use ? with . in order to align the decimal point in the same columnIf there is a number at the position specified with ?, it does not do anythingIf ? is used in digit and ? is at position shorter than the digit, rounding happens Format 1234.56 with ?.? results in 1234.6Format the below column with ?.???custom_format_02
, Add thousands separator (comma) to a number- Apply #,### to add comma separator every 3 digits
– Apply #, to divide the number by 1000. For each additional comma, further divide the number by additional 1000
Format 1234567 with #,### results in 1,234,567Format 1234567 with #,, results in 1.234567
[ ] Apply font color to text. Color can be [Black],[Green],[White],[Blue],[Magenta],[Yellow],[Cyan],[Red]You can apply conditional formatting for font color, but only two conditions can be applied
[color1][condition1];[color2][condition2]
Format 1234 with [Red]# results in 1234Apply 1234 with [Red][<=100];[Blue][>100] results in 1234
_ Place a specific character width in front or at the backFor example, if your negative format has a bracket but positive format does not, you may want to leave a character width for positive number at the backTo apply, place an underscore followed by the character Format 1234.56 with (_(#.##_)) results in ( 1234.56 )   (add a space with bracket width in before and after brackets)
“” Add text as part of the format, similar to use & in a formulaAdding of some notations do not require “”, these include $ + ( : ^ ‘ { < = – / ) ! & ~ } > space Format 1234 with +#,### “HKD” results in +1,234 HKD
* Repeat a specified character in prefix or suffix to fill up the whole Cell width Apply 1234 with *0#,### result in
00000000000000000000000001,234
@ Add a space for text Apply abc with abc@@@ results in
abc with 3 spaces at the back
E Display scientific (exponential) notations format.E represents 10, for example, E2 means 10 to the power 2, equal to 100 Apply 1234 with 0.0E results in 1.2E+03 (=1.2 * 10 to the power 3)

Custom Format of date and time

The below table shows Time related formatting. I skipping examples because it is very simple to understand.

Notation Explanation
m Express month in number, from 1 to 12
mm Express month in number, from 01 to 12
mmm Express month in short description, from Jan to Dec
mmmm Express months in long description, from January to December
mmmmm Express month in the first alphabet, from J to D
d Express day in number, from 1 to 31
dd Express day in number, from 01 to 31
ddd Express day in short description, from Sun to Sat
dddd Express day in long description, from Sunday to Saturday
yy Express year in two digits, from 00 to 99
yyyy Express year in your digits, from 1900 to 9999
h Express hour from 1 to 24. If hour exceeds 24, a day will add and hour will be subtracted by 24
hh Express hour from 01 to 24. If hour exceeds 24, a day will add and hour will be subtracted by 24
[h] Convert date and time to hours, allow you to display hour larager than 24
m Express minute from 1 to 59. If minute exceeds 60, an hour will add and minute will be subtracted by 60
mm Express minute from 01 to 59. If minute exceeds 60, an hour will add and minute will be subtracted by 60
[m] Convert date and time to minute, allow you to display minute larger than 60
s Express second from 1 to 59. If second exceeds 60, a minute will add and second will be subtracted by 60
ss Express second from 01 to 59. If second exceeds 60, a minute will add and second will be subtracted by 60
[s] Convert date and time to second allow you to display second larger than 60
AM/PM or a/p Display time in AM/PM or a/p format

Remarks:

Instead of directly applying formats on the cells, you can also create a formula and use Text(value, “format”). However, not all of the above notations are compatible in the Function. I don’t recommend to use Text formula because it converts the value to text.

To know which notation can be used in Text Function, click here for details.

 Outbound References

https://support.office.com/en-us/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?CorrelationId=f9dfc98c-1186-44d5-b23c-5843840ad1ec&ui=en-US&rs=en-US&ad=US

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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