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.
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 the Format cell, as shown below.
Note: you can open the Format Cells dialog box with the keyboard shortcut Ctrl + 1.
How Does Number Format Work 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 are separated by semicolons.
Let’s see these formats:
- 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 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 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
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.
Step 2:
- Press Ctrl+1 to open the Format Cells dialog box.
- Under Category, select Custom.
- Type the format code #.000 in the box.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes to display different formats.
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 a thousand separators follow the following steps.
Step 1:
- Select cells for which you want to create custom formatting.
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.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes to display different formats.
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.
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.
Step 3:
- Click OK to save the newly created format and see the results.
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 the thousands separator and (#,,) for the millions in the Type Box.
- Click OK to save the newly created format and see the results.
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.
Step 2:
- Press Ctrl+1 to open the Format Cells dialog box.
- Select Custom from the Category.
- Type #,###.000\K in the Type Box
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps for all cells.
Note: Include a space between a comma and a backward slash to make the number format more readable.
Read More: How to Add Text after Number with Custom Format in Excel
5. Add Text in Numbers 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.
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
Step 3:
- Click OK to save the newly created format and see the results.
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.
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 "@
Step 3:
- Click OK to save the newly created format and see the results.
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 a 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.
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.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- 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 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.
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.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps for the rest of the cells to get the fill result.
Read More: How to Format Number to Millions 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.
Step 2:
- Firstly, press Ctrl+1 to open the Format Cells dialog box.
- Select Custom under Category.
- Then, type the format code # #/# to show the fraction remainder up to 1 digit in the Type Box.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes.
Notes:
- Instead of pound marks (#) use question mark placeholders (?) as illustrated in the above screenshot 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.
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.
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.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps for all cells.
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.
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 parentheses. The code is,
#.00; (#.00)
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes as presented in column headers to display negative numbers.
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)
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.
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);"-"
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes as presented in column headers to show 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 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.
To indent positive numbers and zeros from the right and text from the left follow the following steps.
Step 1:
- Select cells in the range you want to create custom formatting.
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_);_(@
Step 3:
- Click OK to save the newly created format and see the results.
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:
Read More: Excel Custom Number Format – Millions with One Decimal
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 includes 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.
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] @
Step 3:
- Finally, click OK to save the newly created format and see the results.
Note: The color code must be the section’s first item.
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.
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.
Step 3:
- Click OK to save the newly created format and see the results.
Step 4:
- Repeat the steps and type different format codes as presented in column headers to add repeated 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 the range you want to create custom formatting.
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* ;* @
Step 3:
- Finally, click OK to save the newly created format and see the results.
Read More: How to Apply Number Format in Millions with Comma in Excel
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 the range you want to create custom 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
Step 3:
- Finally, click OK to save the newly created format and see the results.
Download this practice workbook to exercise while you are reading this article.
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.
Great article and excellent guide, keep up the good work!
Thank you MOE, for your generous appreciation!
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.
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.
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
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.
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
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
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.
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.
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.
• 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
Thank You!
Hello, JeteMc!
Welcome, JeteMc. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy
How to show 100 in value 25=100/4 by using formula custom cell format , without change the original value
Dear Deepak,
Thank you for your comment.
To show 100 in value 25=100/4, you need to apply the following Custom Cell Format.
Best,
Afia Aziz Kona