How to Apply Custom Format Cells in Excel (17 Examples)

To apply a custom format in Excel:

Select the cell or range you want to format.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Click Custom from the Category.
- In the Type field, select the format that you created.
- Hit OK.

A custom format in Excel is a user-defined way of displaying numeric values or text in cells. Whether dealing with financial figures, percentages, or large numbers, custom formatting helps to present data in specific formatting needs.

In this Excel tutorial, you will learn about basic concepts of custom formatting and how custom format works; also, you will explore various examples of applying custom format cells in Excel.

Microsoft Excel offers many built-in formats for numbers, currency, percentages, accounts, dates, and times. However, there are times when you require something extremely specialized. You can develop your own custom format if none of the built-in Excel formats fit your needs.

Watch Video – Apply Custom Format Cells in Excel

How Does Custom Format Work in Excel Cells?

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

In this order, it consists of 4 sections of code. In the following image, you can see that semicolons separate these codes.

Now, let’s see these formatting syntaxes:

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

Formatting Guidelines and Considerations

Supposedly, you can create multiple 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 another 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

How to Custom Format Cells in Excel: 17 Examples

You will learn many examples of Custom Format, such as controlling decimal places, displaying a thousand separators, rounding numbers, adding units, text, and text with text, showing currency symbols and percentages, converting decimals into fractions, and many more practical usages of applying custom format cells. It is important to mention that, throughout this tutorial, you will see numbers, texts, and dates as the source data, and the destination headings are named after the corresponding format code.

Here are 17 practical examples of applying custom format cells:

Controlling Decimal Places of Numbers

A period (.) represents the decimal point’s location. The number of decimal places required is determined by zeros (0). Some format examples: 0 or # – shows the nearest integer with no decimal places; 0 or #.0 – shows 1 decimal place; 00 or #.00 – shows 2 decimal places.

To control decimal places of numbers using custom Format Cells:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Under Category, select Custom.
- Write the format code in the Type field: #.000
- Hit OK.

As a result, the selected cell will be formatted with three decimal places.

Repeat the steps and type different format codes individually to display other decimal places.

Showing Thousand Separators in Excel Cells

You can use a comma (,) in the format code to generate a custom number format with a thousand separator. Some format examples include #,### – display a thousand separators and no decimal places; #,##0.000 – display a thousand separators and 3 decimal places.

To show a thousand separators in Excel cells:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- From the Category section, select Custom.
- Write the format code in the Type field: #,###
- Press OK.

As a result, you will see that cells are formatted like the following image.

Repeat the steps and type the corresponding format code to display the other format.

Rounding Numbers with Custom Format of Cells

You can use commas with any numeric placeholders like pound symbol (#), question mark (?), or zero (0) in a format code when rounding large numbers.

To round numbers, follow the steps:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- Use the following format code in the Type Box: #,###
- Hit OK.

Finally, you will see the effect of the newly created format in the selected cells.

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.

Select the source data and press Ctrl+1.
Select Custom from the Category section in the Format Cells.
In the Type field of the Format Cell dialog box:
- For thousands, write #,
- Or, For millions, type #,,
Click OK.

After rounding each section with the intended formatting code, you will get an output like the following image.

Adding Units with Custom Cell Formatting

The numbers can be scaled by units such as thousands and millions. Besides, K and M can be added to the format codes; for example, thousands Indicator: #.000,\K, millions Indicator: #.000,,\M. Follow these steps:

Select cells for which you want to apply a custom format.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, write the format code: #,###.000\K
- Hit OK.

As a result, you will see an output like the following image.

You can repeat the steps for applying other format cells.

Adding Text in Numbers With Custom Format

You can also show text and numbers in a single cell. 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.

To add text in numbers, follow the following steps:

Select cells for which you want to apply a custom format code.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, write the format code: #.00" Increase"; -#.00" Decrease"; 0
- Hit OK.

Thus, you will see the newly created format in your desired cells.

Adding Text Within Text in Excel Cells

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. To do that, follow these steps:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom Under the Category section.
- In the Type box, type the format code: General; General; General; "American novelist "@
- Click OK.

Thus, you will see the intended effect of the format code.

Displaying Currency Symbol in Cells

You can simply insert the dollar symbol ($) in the relevant format code to make a unique number format. For example, the format $#.00 will display 7000 as $7000.00. On most common keyboards, there are no additional currency symbols available. However, you can include a currency symbol by turning the NUM LOCK on and then typing the ANSI code using 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 display the currency symbols in Excel cells, follow these steps:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Under Category, select Custom.
- Press NUM LOCK.
- In the Type field, type the format code: € #,###
  To insert the € sign, press Alt+0128.
- Click OK.

As a result, you will see that the (€) sign is added in the selected cells.

You can repeat the steps and type different format codes as presented in column headers to display different currency formats.

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 the ALT key while typing their four-digit ANSI codes.

Displaying Percentages with Custom Format

You can display a number as a percentage of 100 and use the percentage symbol (%) in your specific custom format. To present percentages as integers, you must first convert them to decimals; the format code will be #%. If you want to show percentages with two decimal points, use #.00% as the format code. Follow these steps:

Select the range for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- Write the format code in the Type field: #%
- Click OK.

As a result, you will see the intended output like the following.

You can repeat the steps for the rest of the ranges to get the result.

Converting Decimal Numbers into Fractions

Numbers can be written as mixed fractions, like 11 1/3. The custom codes you can apply for mixed fractions: # #/# –presents a fraction remaining of up to one digit, # ##/## – presents a fraction remaining of up to two digits, # #/5 to display decimal integers as fifths.

To convert decimal numbers into fractions using custom format:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, write the format code: # #/#
- Click OK.

Thus, you will see mixed fractions in the selected cells.

You can repeat the steps and type different format codes like the following image.

Note: Instead of the pound marks (#), you can use question mark placeholders (?) to return the 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.

Creating A Scientific Notation in Excel Cells

You can insert the block letter E in your number format code if you want to display numbers in Scientific Notation. For example, use #E+# to display 7,000,000 as 2E+6, insert #0.0E+0 to display 7,000,000 as 0E+6, and use 00E+00 to display 7,000,000 as 00E+06. Follow these steps:

Select the desired cells for creating scientific notation.
Open the Format Cells dialog box by pressing Ctrl+1.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, write the format code: #E+#
- Click OK.

As a result, you will see scientific notation is applied within the selected cells.

So, repeat the same steps for other notations to get the output, like the following.

Showing Negative Numbers with Custom Format

To construct a custom format for negative numbers, 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, use #.00; (#.00).

To show negative numbers with the custom format, follow the following steps:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category.
- In the Type field, insert the format code: #.00; (#.00)
- Click OK.

Finally, you will see that the selected cells have new formatting, like the following image.

You can follow the same steps and type different format codes for other columns.

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

Displaying Dashes with Custom Format of Cells

Zeros are displayed as dashes in accounting format. You can also do this in custom 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);”-“.

Follow these steps to display dashes with the custom format:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- In the Category section, select Custom.
- In the Type field, write the format code: 0.00;(0.00);"-"
- Click OK.

Finally, you will see the intended formatting in the selected cells.

You can follow the same steps and type different format codes to show dashes in another column.

Including Indents with Custom Format

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. For example, to indent from the left boundary, you can use _( and for indenting from the right boundary, use _). You can also use the following format code 0.00_);(0.00); 0_);_(@ or, to add an 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:

Select cells in the range you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom in the Category section.
- Write the format code in the Type box: 00_);(0.00); 0_);_(@
- Click OK.

Finally, you have indented positive integers and zeros from the right and text from the left.

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:

Adding Different Font Colors with Custom Format

You can change font color with a customs number format, which includes eight major colors, 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.

Typically, you can 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 with the custom format:

Select cells for which you want to create custom formatting.
To open the Format Cells dialog box, press Ctrl+1.
In the Format Cells dialog box:
- Select Custom from the Category section.
- Write the format code in the Type field: [Green]$#,##0.00;[Red] -$#,##0.00;[Black] "-";[Blue] @
- Click OK.

Finally, you can see an output like the following image.

Repeating Characters in Excel Cells

In your bespoke Excel format, insert an asterisk * sign before the character to complete the column width with a repeated character. 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: #*=

So, the quickest way 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 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.

To repeat characters with the custom format, follow the steps:

Select cells for which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, write the format code: *0#
- Click OK.

Thus, you will see an output like the following image.

So, repeat the steps and type another format code to add repeated characters in the other column.

Changing Alignment with Custom Format

After the number code, you can type an asterisk (*) and space to align the numbers left in the cell; for example, #,###*. Taking it a step further, you could use a custom format to align numbers to the left and text inputs to the right using the following: #,###* ; -#,###* ; 0* ;* @

To change alignment with custom Excel formats:

Select the cells in the range in which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type field, the format code: #,###* ; -#,###* ; 0* ;* @
- Click OK.

Finally, you can see an output like the following image.

Applying Conditional Formatting with Custom Format

You can also apply conditional formatting in Excel cells with the help of a custom format. For example, you can show numbers that are less than 10 in blue font color, and numbers that are greater than or equal to 10 in red color, using the format code: [Blue][<10]General;[Red][>=10]General

To apply conditional formatting with custom format:

Select the cells in the range in which you want to create custom formatting.
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Select Custom from the Category section.
- In the Type box, type the format code: [Blue][<10]General;[Red][>=10]General
- Click OK.

Finally, you can see that the custom format is applied and some highlighted cells in the selected area.

Download Practice Workbook

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

Custom Formatting Cell.xlsx

Conclusion

So, hopefully, this article has provided detailed guidance and offers many examples of applying custom format in Excel cells. You can use these custom format codes and modify them based on your requirements. If you have any questions, feel free to ask us. Also, feel free to leave comments in the section below.

Frequently Asked Questions

How do I apply a custom temperature format in Excel?

You can use a format code like 0"°C" to apply a custom temperature format. The format code will display the temperature followed by the degree Celsius symbol (10°C).

Can VBA be used to automate custom formatting in Excel?

Yes, you can use VBA to apply custom formatting. For example, you can write a VBA script that utilizes the NumberFormat property to set custom formats for specific ranges or cells.

<< Go Back to Custom Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

18 Comments

Moe Aug 9, 2022 at 12:12 AM

Great article and excellent guide, keep up the good work!
- Reply
  Bhubon Costa Aug 11, 2022 at 12:44 AM
  
  Thank you MOE, for your generous appreciation!
Moe Aug 9, 2022 at 12:16 AM

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
- Reply
  Rafiul Haq Aug 10, 2022 at 11:47 PM
  Thank you MOE for your wonderful question. To do this you need to apply the following Custom Cell Format.
```
[<=1000000]  #,"K";[>1000000]   #,,"M"
```
Wayne Erfling Dec 2, 2022 at 11:32 PM

It would be nice to include a section on locale, either the “obsolete” LCID, such as

[$-409] – where 409 is really just hex for 1033

or the locale name

[$-en-US]

which I believe is the preferred method now.

The [$…..] notation allows for provisioning the currency symbol as well, so
[$USD -en-US]

will show $2,750.22 AS USD 2,750.22

Finding anything like Microsoft documentation on this field is very difficult, so I’m certain at least somebody finding your page would be thinking about this section of the custom format.
- Reply
  Tanjima Hossain Dec 5, 2022 at 12:18 PM
  
  Hello ERFLING,
  
  Thanks for your valuable suggestion. But you can take a look at Section 7 of this article which may align with this requirement.
  
  Thanks
  Tanjima Hossain
  ExcelDemy
Fred Jan 14, 2023 at 2:35 AM

Hi, great stuff!

How can I create a format that has a decimal with 2 spaces if content has decimal digits, and no decimal separator if it’s an integer?

Basically #.00 if has decimals, and # otherwise.

The closest I get is #.##, but that wont give me 2 spaces of decimals if there only is one (so I’d like to fill it out with a 0 in that case), and integers will have just the decimal dot at the end which looks terrible.
- Reply
  Bishawajit Chakraborty Jan 17, 2023 at 4:18 PM
  
  Thank you, Fred, for your wonderful question.
  
  Firstly, go to the Home tab.
  
  You can keep the decimal places of any number at any place by using the Increase Decimal and the Decrease Decimal icons here.
  
  Bishawajit, on behalf of ExcelDemy
Gilles Laurence Jan 20, 2023 at 1:13 AM

Hi

When I write $#,### in the custom section I get the following result $1000000. How do I get a result that would show $1,000,000. Windows was set up in French. I wonder if that is the problem.

Thank you for your help.

Gilles
- Reply
  Rafiul Haq Jan 24, 2023 at 1:33 AM
  
  Thank you, Gilles Laurence, for your comment. I am replying on behalf of ExcelDemy. The custom number format works perfectly on our end. The French number system uses a comma for a decimal separator and a space for thousand separators. You can change the thousand separators from the Advanced tab on the Excel Options to solve this.
Karl Jan 27, 2023 at 1:17 PM

I would like excel to always return 8 Alpha numeric with a fill of 0 on the left side of the data string.
e.g.

1111A -> 0001111A
11111A -> 0011111A
1B -> 0000001B

Anyone can help me? I knowhow to do it with a formula =right(“0000000″&A1,8). This will require me using a separate cell. I would like the modification to happen in the original cell itself. I am sure the Excel has this function I just cant figure it out.

Thanks for any help.
- Reply
  Arin Jan 30, 2023 at 1:12 PM
  Hi KARL,
  Thanks for your comment.
  To always return 8 digits with a fill of 0 on the left side of the data you can use VBA code. Follow the steps given below to do that.
  Steps:
  • Firstly, go to the Developer tab >> click on Visual Basic.
  
  • Then, it will open Microsoft Visual Basic for Applications.
  • Now, open Insert >> select Module.
  
  • Next, a Module will open then type the following code in the opened Module.
```
Sub Alphanumeric()
For Each cell In Selection
    If Len(cell.Value) < 8 And Len(cell.Value) <> "" Then
        cell.Value = WorksheetFunction.Rept("0", 8 - Len(cell.Value)) & cell.Value
    End If
Next
End Sub
```
  • Finally, Save the code and go back to the worksheet.
  
  • Then, select the cell or cell range to apply the VBA.
  • Here, we selected the range B5:B7.
  • Next, open the Developer tab >> select Macros.
  
  • After that, select Alphanumeric and click on Run.
  
  • Thus, Excel will always return 8 Alphanumerics with a fill of 0 on the left side of the data string.
  
  We hope this will solve your problem. Please let us know if you face any further problems.
  
  Regards,
  Arin Islam,
  ExcelDemy
JeteMc Feb 7, 2023 at 8:44 PM

Thank You!
- Reply
  Shamima Feb 13, 2023 at 3:45 PM
  
  Hello, JeteMc!
  
  Welcome, JeteMc. To get more helpful information stay in touch with ExcelDemy.
  
  Regards
  ExcelDemy
deepak Feb 17, 2023 at 12:42 PM

How to show 100 in value 25=100/4 by using formula custom cell format , without change the original value
- Reply
  Afia Kona Feb 19, 2023 at 12:22 PM
  Dear Deepak,
  Thank you for your comment.
  To show 100 in value 25=100/4, you need to apply the following Custom Cell Format.
```
00/4
```
  Best,
  Afia Aziz Kona
Anton Jan 4, 2024 at 12:32 PM

Can I set negative numbers color with hex (ie ‘#FF6600’) instead of the name of the color (ie ‘[RED]’)?
Best,
Anton
- Reply
  Md. Abdur Rahim Rasel Jan 4, 2024 at 4:27 PM
  
  Dear Anton!
  Thanks for reaching out and sharing your problem with us.
  No, you can not set the color of negative numbers using hex values directly in the custom number format in Excel. The custom number format in Excel has predefined color names such as [Red], [Green], etc., but it doesn’t support specifying colors using hex values.
  Again, thank you for being with us.
  Regards
  Md. Abdur Rahim Rasel
  Exceldemy Team

How to Apply Custom Format Cells in Excel (17 Examples)

How Does Custom Format Work in Excel Cells?