How to Custom Format Cells in Excel (17 Examples)

For numbers, currency, percentages, accounts, dates, and times, Microsoft Excel offers a myriad of built-in formats. However, there are times when you require something extremely specialized. If none of the built-in Excel formats fit your needs, you can develop your own. This tutorial covers the fundamentals of Excel number formatting as well as thorough instructions on how to custom format cells in Excel. You’ll learn how to display the required number of decimal places, change font color or alignment, display a currency sign, show leading zeros, round numbers by thousands, and much more.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Basic Concept of Custom Formatting of Cells in Excel

You can change the format of the number inside the cell using the Custom format option. Because Excel interprets date and time as general numbers, this is essential. You can change the format of a cell using the Format Cell option.

  • General
  • Number
  • Currency
  • Accounting
  • Date
  • Time
  • Percentage
  • Fraction
  • Scientific
  • Text
  • Special
  • Custom

You can utilize the required format type under the custom option.

To customize the format, go to the Home tab and select Format cell, as shown below.

Basic Concept of Custom Format Cells in Excel

Note: you can open the Format Cells dialog box with the keyboard shortcut Ctrl + 1.


How Number Format Works in Excel

To develop a custom format in Microsoft Excel, you must first understand how the number format is recognized by Microsoft Excel.

In this order, it consists of 4 sections of code. these codes separated by semicolons.

How Number Format Works in Excel

Let’s see these formats:

How Number Format Works in Excel

  1. For Positive numbers (display 3 decimal places and a thousand separator).
  2. In the case of the negative numbers (enclosed in parenthesis).
  3. For zeros (display dashes instead of zeros).
  4. Text values format.

Formatting Guidelines and Considerations

Supposedly, you can create an endless number of custom number formats in Excel by applying the formatting codes mentioned in the table below. The following hints will show you how to utilize these format codes in the most usual and practical ways.

Format Code Description
General number format
# Digit placeholder which does not show extra zeros and symbolizes optional digits.
0 Unimportant zeros are represented in a digit placeholder..
? A digit placeholder, which leaves a place for them but does not show them, hides unimportant zeros.
@ Text placeholder
(. )(Dot) Decimal point
(,) (Comma) Separator for thousands. After a digit placeholder, a comma represents the numbers multiplied by a thousand.
\ The character that comes after it is shown.
” “ Any text wrapped in double-quotes will be shown..
% The percentage indication is presented after multiplying the values input in a cell by 100.
/ Specifies fractions as decimal numbers.
E Specifies the format for indicating scientific notation.
(_ ) (Underscore) Bypasses the following character’s width. 
(*) (Asterisk) Continue with the next character until the cell is entirely filled. It’s typically paired with other space character to adjust alignment.
[ ] It is used to make use of conditional formatting.

Characters that Display by Default

Some characters appear in a numerical format by default, while others require specific treatment. Without any special handling, the following characters can be used.

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

17 Examples to Use Custom Format of Cells in Excel

Custom formatting in Excel is a highly powerful tool, and once you figure out how to use it correctly, your options are nearly limitless.

Therefore, we will show you various examples of custom format cells in Excel. The objective of this lesson is to walk you through the most important components of Excel number formatting so you can master custom number formatting.

1. Control the Number of Decimal Places with Custom Format of Cells in Excel

A period (.) represents the decimal point’s location. The number of decimal places required is determined by zeros (0). Here are some format examples are shown below.

  • 0 or # – shows the nearest integer with no decimal places.
  • 0 or #.0 – shows 1 decimal place.
  • 00 or #.00 – showing 2 decimal places.

To create these custom excel formats follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Control the Number of Decimal Places to Custom Format Cells in Excel

 Step 2:

  • Press Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • Type the format code #.000 in the box.

Control the Number of Decimal Places to Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Control the Number of Decimal Places to Custom Format Cells in Excel

Step 4:

  • Repeat the steps and type different format codes to display different formats.

Control the Number of Decimal Places to Custom Format Cells in Excel


2. Show Thousand Separators with Custom Format of Cells

Include a comma (,) in the format code to generate a custom number format with a  thousand separator. Here are some format examples are shown below.

  • #,### – display a thousand separators and no decimal places.
  • #,##0.000 – display a thousand separators and 3 decimal places.

To show thousand separators follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Show Thousand Separators with Custom Format Cells in Excel

Step 2:

  • Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • We will type the format code #,### in the Type Box.

Show Thousand Separators with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Show Thousand Separators with Custom Format Cells in Excel

Step 4:

  • Repeat the steps and type different format codes to display different formats.

Show Thousand Separators with Custom Format Cells in Excel


3. Round Numbers with Custom Format of Cells in Excel

If a comma is contained by any numeric placeholders  (pound symbol (#), question mark (?) or zero (0) ), Microsoft Excel divides thousands by commas, as illustrated in a previous way.

Follow the instructions below to make your own bespoke Excel formats.

To round numbers follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Round Numbers with Custom Format Cells in Excel

Step 2:

  • First, press Ctrl+1 to open the Format Cells dialog box.
  • Now, under Category, select Custom.
  • Type the format code #,### in the Type Box.

Round Numbers with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Round Numbers with Custom Format Cells in Excel

But, if there is no digit placeholder after a comma, the number is scaled by a thousand, two consecutive commas by a million, and so on.

Step 4:

  • Type the format code (#,) for thousands separator and (#,,) for the millions in the Type Box.
  • Click OK to save the newly created format and see the results.

Round Numbers with Custom Format Cells in Excel


4. Add Units with Custom Cell Formatting

To indicate that the numbers are scaled by units such as thousands and millions, add K and M to the format codes.

  • Thousands Indicator: #.000,\K
  • Millions Indicator: #.000,,\M

Step 1:

  • Select cells for which you want to create custom formatting.

Add Units to Custom Format Cells in Excel

Step 2:

  • Press Ctrl+1 to open the Format Cells dialog box.
  • Select Custom from the Category.
  • Type #,###.000\K in the Type Box

Add Units to Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Add Units to Custom Format Cells in Excel

Step 4:

  • Repeat the steps to all cells.

Add Units to Custom Format Cells in Excel

Note: Include a space between a comma and a backward slash to make the number format more readable.


5. Add Text in Number with Custom Format of Cells

Another example of showing text and numbers in a single cell can be seen here. For positive numbers, add the phrases “increase” and “decrease”; for negative values, add the words “decrease.” Simply double-quote the content in the relevant section of your format code:

#.00″ Increase”; -#.00″ Decrease”; 0

To add text in numbers follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Add Text in Number with Custom Format Cells in Excel

Step 2:

  • Firstly, press Ctrl+1 to open the Format Cells dialog box.
  • Secondly, select Custom under Category
  • Then, Type the format code #.00″ Increase”; -#.00″ Decrease”; 0 in the Type Box.
#.00" Increase"; -#.00" Decrease"; 0

Add Text in Number with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Add Text in Number with Custom Format Cells in Excel


6. Add Text within Text in Excel

You can combine some specific text with text typed in a cell. Just type the additional text in double-quotes before or after the text placeholder (@) in the fourth part of the format code.

For example, use the following format code to replace the text in the cell with another text, such as “American novelist” before each name of a writer. To do that, follow these steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Add Text in Text with Custom Format Cells in Excel

Step 2:

  • Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • In the Type box, type the format code. The code is,
General; General; General; "American novelist "@

Add Text in Text with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Add Text in Text with Custom Format Cells in Excel

Related Content: How to Format Text Using Excel VBA (12 Methods)


7. Include Currency Symbol with Custom Format of Cells in Excel

Simply insert the dollar symbol ($) in the relevant format code to make a unique number format The format $#.00, for example, will display 7000 as $7000.00.

On most common keyboards, There are no additional currency symbols available. However, you can use this method to enter popular currencies:

  • Firstly, to include currency symbol, turn the NUM LOCK on.
  • To type the ANSI code, use the numeric keypad.
Symbols  Names  Codes
€ (EUR) Euro ALT+0128
¢ Cent Symbol ALT+0162
¥ (JP¥) Japanese Yen ALT+0165
£ (Sterling) British Pound ALT+0163

To include currency symbols, we will follow these steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Include Currency Symbol with Custom Format Cells in Excel

Step 2:

  • Press Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • For Euro currency, type the format code € #,### in the Type Box.

Include Currency Symbol with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Include Currency Symbol with Custom Format Cells in Excel

Step 4:

  • Repeat the steps and type different format codes as presented in column headers to display different currency formats.

Include Currency Symbol with Custom Format Cells in Excel

Note: Other unique symbols, such as copyright, and trademark, can be accepted in a specific Excel number format. These characters can be typed in by holding down the ALT key while typing their four-digit ANSI codes.


8. Display Percentages with Custom Format

If you want to represent a number as a percentage of 100, use the percent symbol (%) in your specific custom format.

  • In order to present percentages as integers, you must first convert them to decimals.: #%

To show percentages with two decimal points: #.00%

Step 1:

  • Select cells for which you want to create custom formatting.

Display Percentages with Custom Format Cells in Excel

Step 2:

  • Press Ctrl+1 to open the Format Cells dialog box.
  • Select Custom under Category.
  • Type the format code #% to show the percentage with no decimal place in the Type Box.

Display Percentages with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Display Percentages with Custom Format Cells in Excel

Step 4:

  • Repeat the steps to the rest of the cells to get the fill result.

Display Percentages with Custom Format Cells in Excel


9. Convert Decimal Number into Fractions

Numbers can be written as 11 1/3. The custom codes you apply in Excel decide which way the fraction is displayed.

  • # #/# –presents a fraction remaining of up to one digit.
  • # ##/## – presents a fraction remaining of up to two digits.
  • Include it after the slash in your number format code scale fractions to a certain denominator. Such as using the fixed base fraction format # #/5 to display decimal integers as fifths.

Step 1:

  • Select cells for which you want to create custom formatting.

Convert Decimal Number into Fractions with Custom Format Cells in Excel

Step 2:

  • Firstly, press Ctrl+1 to open the Format Cells dialog box.
  • Select Custom under Category.
  • Then, type the format code # #/# to show fraction remainder up to 1 digit in the Type Box.

Convert Decimal Number into Fractions with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Convert Decimal Number into Fractions with Custom Format Cells in Excel

Step 4:

  • Repeat the steps and type different format codes.

Convert Decimal Number into Fractions with Custom Format Cells in Excel

Notes:

  • Instead of pound marks (#) use question mark placeholders (?) as illustrated in the above screenshot to return number at some distance from the remainder.
  • Add a zero and a space before the fraction in a General Cell formation To enter 5/7 in a cell, for example, type 0 5/7. When you type 5/7, Excel interprets it as a date and changes the cell format.

Similar Readings


10. Create A Scientific Notation with Custom Format of Cells in Excel

Insert the block letter E in your number format code if you want to display numbers in Scientific Notation.

  • #E+# displays 7,000,000 as 2E+6.
  • #0.0E+0 – displays 7,000,000 as0E+6.
  • 00E+00 – displays 7,000,000 as 00E+06.

Follow these steps to learn!

Step 1:

  • Select cells for which you want to create custom formatting.

Create A Scientific Notation with Custom Format Cells in Excel

Step 2:

  • Open the Format Cells dialog box by pressing Ctrl+1
  • Under Category, select Custom.
  • Type the format code #E+# to show scientific notation with no decimal places.

Create A Scientific Notation with Custom Format Cells in Excel

Step 3:

  • Click OK to save the newly created format and see the results.

Create A Scientific Notation with Custom Format Cells in Excel

Step 4:

  • Repeat the steps to all cells.

Create A Scientific Notation with Custom Format Cells in Excel


11. Show Negative Numbers with Custom Format

In the beginning, We learned about the four code portions that comprise a number format in Excel:

Positive; Negative; Zero; Text

For negative numbers, to construct a custom format you’ll need at least two code parts: one for positive numbers and zeros, and another for negative numbers.

Simply include negative values in the second portion of your custom code to show them in parentheses. For example 

#.00; (#.00)

To show negative numbers follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Show Negative Numbers

Step 2:

  • Ctrl+1 to open the Format Cells dialog box.
  • Select Custom, under Category.
  • Type the format code in the Type box to show the negative values in parenthesis. The code is,
#.00; (#.00)

Show Negative Numbers

Step 3:

  • Click OK to save the newly created format and see the results.

Show Negative Numbers

Step 4:

  • Repeat the steps and type different format codes as presented in column headers to display negative numbers.

Show Negative Numbers

Note: To add an indent in the positive values section to align positive and negative integers at the decimal point. For example: 0.00_); (0.00)


12. Display Dashes with Custom Format of Cells

Zeros are displayed as dashes in Excel Accounting format. You can also do this in your own number format.

The third section of the format code determines the zero layouts, as you recall. So, enter “-” in the third section to make zeros appear as dashes. For example 0.00;(0.00);”-“

Step 1:

  • Select cells for which you want to create custom formatting.

Display Dashes

Step 2:

  • Firstly, press Ctrl+1 to open the Format Cells dialog box.
  • Secondly, under Category, select Custom.
  • Then type the format code 00;(0.00);”-” in the Type box.
00;(0.00);"-"

Display Dashes

Step 3:

  • Click OK to save the newly created format and see the results.

Display Dashes

Step 4:

  • Repeat the steps and type different format codes as presented in column headers to show dashes.

Display Dashes


13. Include Indents with Custom Format of Cells in Excel

You can indent information within a cell if you don’t want the contents to ascend against the cell side-line. Apply the underscore (_) to generate a space to add an indent.

The following are some of the most frequently used indent codes:

  • For indenting from the left boundary, use the following formula: _(
  • For indenting from the right boundary, use the following formula: _)

You can use the following format code :

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

Or, to add indent on both sides of the cell: _(0.00_);_((0.00);_(0_);_(@_)

Follow these instructions for indenting positive integers and zeros from the right and text from the left.

To indent positive numbers and zeros from the right and text from the left follow the following steps.

Step 1:

  • Select cells in range you want to create custom formatting.

Include Indents

Step 2:

  • Ctrl+1 to open the Format Cells dialog box.
  • Select Custom under Category.
  • Then, type the format code in the Type box.
00_);(0.00); 0_);_(@

Include Indents

Step 3:

  • Click OK to save the newly created format and see the results.

Include Indents

Include two or more indent codes in a row in your custom number format to move values away from the cell borders. The picture below shows how to indent cell contents by 1 and 2 characters:

Include Indents


14. Change Font Color with Custom Format of Cells

It’s one of the most basic things you can do with a customs number format, which included eight major colors, is to change the font color for a specific value type. Simply Choose one of the color names in the appropriate part of your custom format code to determine the color.

Use the format code.

[Green]General;[Red]General;[Black]General;[Blue]General

You can also display the currency sign, two decimal places, a thousand separator, and show zeros as dashes by combining color codes with the required number formatting:

[Green]$#,##0.00;[Red] -$#,##0.00;[Black] “-“;[Blue] @

To change font color follow the following steps.

Step 1:

  • Select cells for which you want to create custom formatting.

Change Font Color

Step 2:

  • Firstly, press Ctrl+1 to open the Format Cells dialog box.
  • Secondly, under Category, select Custom.
  • Then, type the format code [Green]$#,##0.00;[Red] -$#,##0.00;[Black] “-“;[Blue] @ in the Type box to change the font color.
[Green]$#,##0.00;[Red] -$#,##0.00;[Black] "-";[Blue] @

Change Font Color

Step 3:

  • Finally, click OK to save the newly created format and see the results.

Change Font Color

Note: The color code must be the section’s first item.

Related Content: Uses of CELL Color A1 in Excel (3 Examples)


15. Repeat Characters with Custom Format in Excel

Enter an asterisk (*) well before the character to complete the column width with a repeated character in your bespoke Excel format.

You can add leading zeros in any numeric format by inserting *0# before it.

Or, you can use this number format to insert after a number. There are too many equality signs to occupy the cell: #*=.

To repeat characters follow the following steps:

Step 1:

  • Select cells for which you want to create custom formatting.

Repeat Characters

Step 2:

  • Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • Type the format code *0# in the Type box to add zeroes.

Repeat Characters

Step 3:

  • Click OK to save the newly created format and see the results.

Repeat Characters

Step 4:

  • Repeat the steps and type different format codes as presented in column headers to add repeated characters.

Repeat Characters

Note: The quickest method to enter phone numbers, zip codes, or social security numbers with leading zeros is to use one of the predefined Special formats. You can even construct your own number format. Use this format to show international six-digit postal codes, for example, 000000. Use the following format for social security numbers with leading zeros: 000-00-000.


16. Change Alignment with Custom Cell Formatting

After the number code, type an asterisk (*)  and space to align the numbers left in the cell. For example, “#,###* “.  You don’t need double quotes in a genuine format code; they’re simply used to signify that an asterisk is followed by a space.

Taking it a step further, you could use this custom format to align numbers to the left and text inputs to the right:

#,###* ; -#,###* ; 0* ;* @

To change alignment with custom excel formats follow the following steps.

Step 1:

  • Select the cells in range you want to create custom formatting.

Change Alignment

Step 2:

  • Press Ctrl+1 to open the Format Cells dialog box.
  • select Custom under Category.
  • Type the format code #,###* ; -#,###* ; 0* ;* @ in the Type box to change the alignment.
#,###* ; -#,###* ; 0* ;* @

Change Alignment

Step 3:

  • Finally, click OK to save the newly created format and see the results.

Change Alignment


17. Apply Conditional Formatting with Custom Format of Cells in Excel

Show numbers that are less than 10 in blue font color, and numbers that are greater than or equal to 10 in red color, use this format code:

[Blue][<10]General;[Red][>=10]General

Step 1:

  • Select the cells in range you want to create custom formatting.

Apply Conditional Formatting

Step 2:

  • Firstly, Press Ctrl+1 to open the Format Cells dialog box.
  • Under Category, select Custom.
  • In the Type box, type the format code.
[Blue][<10]General;[Red][>=10]General

Apply Conditional Formatting

Step 3:

  • Finally, click OK to save the newly created format and see the results.

Apply Conditional Formatting


Conclusion

To conclude, I hope this article has provided detailed guidance to create custom format cells in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

4 Comments
  1. Great article and excellent guide, keep up the good work!

  2. I have a question though, can I create a custom format that rounds numbers less than a million to #, /K and numbers above a million to #,, /M ?

    Thanks

    • Thank you MOE for your wonderful question. To do this you need to apply the following Custom Cell Format.

      [<=1000000]  #,"K";[>1000000]   #,,"M"

Leave a reply

ExcelDemy
Logo