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”.
Select “Custom”
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 ?.??? |
| , | 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.