What Is Custom Number Format In Excel?
Custom number format in Excel is an option to customize a number’s format to display it in a required form without changing the number’s underlying value.
Users can custom format numbers to display numeric values in the required formats when working with data types such as text, currency, date, and time.
For example, the table below contains two numbers in cells A2:A3.
The task is to show the cells A2 and A3 values in cells C2 and C3. And then, apply a custom number format in Excel cells C2 and C3 based on the format codes given in cells B2 and B3.
Then, we can manually enter the required values in the corresponding target cells. And then, use the Custom category in the Number tab in the Excel Format Cells window to apply a custom number format in Excel cells C2 and C3 based on the specified formats.
In the above example of custom number format in Excel, we manually enter the cells A2 and A3 values in cells C2 and C3.
Next, cells B2 and B3 show two custom number formats. While the first one is an existing custom number format, the second is a modified version of an existing custom number format.
And we can apply the required format in each cell one at a time by selecting the required cell and pressing Ctrl + 1 to open the Number tab in the Format Cells window.
And then, select the Custom category in the Number tab.
Finally, choose the required number format code in the list, or select and modify the chosen number format code in the field in the Type section and click OK to complete the process.
For cell C2 data, we can select the required custom number format code in the list in the Type section.
On the other hand, we can choose the number format code in the same list, close to the custom format code specified in cell B3. The chosen format code will appear in the field in the Type section, where we can modify it to fit our requirement for cell C3.
Table of contents
Key Takeaways
- The option to custom number format in Excel enables one to display a numeric value in a customized format without changing its value.
- Users can custom format numbers in Excel when the requirement is to display numeric data in specific formats.
- We can use the Custom category in the Number tab in the Format Cells window to set, change, edit, save and delete a custom number format.
- We can set custom number format for positive and negative numbers, dates, time, and currency values, and for showing values with units and hiding texts.
How To Custom Format Numbers In Excel?
An Excel number format typically contains four sections of code, which are semicolon-separated.
POSITIVE; NEGATIVE; ZERO; TEXT
While the first two sections represent the positive and negative numbers’ formats, the third and fourth represent zeros’ and text value formats.
Furthermore, below are a few critical number formatting rules to follow while creating a custom number format:
- If the format consists of only one section, it applies to positive and negative numbers and zeros.
- If the format consists of two sections, the first applies to positive numbers and zeros, and the second applies to negative numbers.
- The text values get formatted when the custom format includes all four sections.
And the following table shows the digit and text placeholders we will use in custom number formats.
Also, the following are the common codes required to create different custom number formats.
We can custom format numbers in Excel using the following steps:
- Choose the required cell or range, where we must apply a custom format to the given numbers.
- Choose the Home tab – click the Number Format drop-down – choose the More Number Formats option to access the Format Cells window.
[Alternatively, click the Number Format dialog box launcher in the Home tab.
Otherwise, right-click the required cell or range and choose Format Cells from the contextual menu.
The Number tab in the Format Cells window will open.]
- Click the Custom category to select it.
- Enter the required custom number format code in the field in the Type section.
Otherwise, please select the required custom number format code from the existing options in the Type section to use it as is. The chosen format code will display in the field above the list in the Type section.
But, if we must modify an existing custom format code in the list, select it. And then, edit the chosen code displayed in the field above the list in the Type section.
- Click OK in the Format Cells window to view the data in the target cell or range in the required format.
Using Shortcut Key
The steps to create an Excel custom number format are as follows:
- Select a target cell or range.
- Press Ctrl + 1 to access the Format Cells window.
- The Number tab in the Format Cells window opens, where we must click inside the Category section.
- Press C twice to choose the Custom category.
- Enter the required custom number format code in the field in the Type section. Otherwise, select a custom number format code in the list in the Type section in the Custom category to use it as is or modify it to suit the requirement.
- Press Enter to view the selected cell or range data in the required custom number format.
Examples
In this section, each example of custom number format in Excel explains the practical ways to create and use the different custom number formats in a worksheet.
#1 – Date Custom Format
The table below shows the same date in cells A2 and A3 but in General data format.
The aim is to show the dates in cells C2 and C3 based on the custom Excel date format codes specified in cells B2:B3.
Here is how to use the custom number format options from the Format Cells window to achieve the desired outcome.
1: Choose cell C2, enter the following formula, and press Enter.
=A2
2: Select cell C2 and choose Home à Number Format à More Number Formats.
The above step will open the Format Cells window.
3: Select the Custom category in the Number tab in the Format Cells window.
And then, select the required custom date format code based on the code specified in cell B2.
And click OK.
4: Select cell C3, enter the following formula, and press Enter.
=A3
5: Press Ctrl + 1 to access the Format Cells window, click inside the Category section and press C twice to choose the Custom category.
And then, choose the custom date format code in the Type section, close to the required format specified in cell B3.
Next, edit the chosen date format code, displayed in the field above the list, to match the required custom date format code.
Finally, click OK to achieve the following output.
The custom date formats use the characters d, m, and y to denote date, month, and year. And the number of times we use the characters and their order in a custom date format decides how the date value gets displayed.
#2 – Time Custom Format
The table below shows the different phases in a project with their durations.
And the aim is to display each phase duration in hours in cells C2:C6.
So, we can use the applicable custom time format code in the target cell range to obtain the required output.
1: Choose cell C2, enter the following formula, and press Enter.
=B2
And using the Excel fill handle, update the expression in the remaining target cells.
2: Select the cell range C2:C6 and press Ctrl + 1 to open the Format Cells window. And then, choose the Custom category in the Number tab.
Next, enter the custom time format code, in the field above the list of existing formats, in the Type field.
Please note that the character “h” within Square brackets will show the total duration in hours when the given period exceeds 24 hours. On the other hand, using only the character “h” will give the corresponding hour.
And click OK to obtain the following output.
The output shows the phase durations in hours. For example, 2.5 days equals 24 hours + 24 hours + 12 hours, which is 60 hours.
Typically, we denote hours, minutes, and seconds using the character “h”, “mm”, and “ss”. And the combination we use them in displays the time in the corresponding format.
#3 – Number Custom Format
Here is an illustration of a custom number format in Excel formula.
The table below contains three numeric values.
And the ask is to show the numeric data in the custom number format codes specified in column B cell in column C.
While we can use the Custom category in the Format Cells window to directly apply the required custom number format, we can use the Excel TEXT function to obtain the desired outcome.
1: Click cell C2, enter the following formula, and press Enter.
=A2
And use the fill handle to enter the expression in the remaining target cells.
2: Select cell C2 and press Ctrl + 1 to access the Format Cells window.
Next, click Number – Custom and enter the required custom number format code in the field in the Type section.
And click OK.
Next, repeat step 2 to update the custom number formats in cells C3 and C4.
Next, here is how to use custom number format in Excel formula.
3: Select cell D2, type in the TEXT(), and press Enter.
=TEXT(A2,”#.##”)
4: Select cell D3, enter the TEXT(), and press Enter.
=TEXT(A3,”????.????”)
5: Select cell D4, enter the TEXT(), and press Enter.
=TEXT(A4,”#°C”)
The TEXT() accepts two argument values as input. While the first one is the number we wish to custom format, the other is the custom number format code we must use to view the data in the required form.
#4 – Show Thousand Numbers In K, M, And B Format
The table below contains the sales figures of three branch offices, in thousands denomination.
Here is how to view the thousand numbers in K, M, and B formats in column D.
1: Click cell D2, type in the following formula, and press Enter.
=B2
Next, use the fill handle to update the expression in the remaining target cells.
2: Choose cell D2 and press Ctrl + 1 to access the Format Cells window.
And then, click the Custom category in the Number tab, enter the custom number format code given in cell C2 in the field in the Type section, and click OK.
Cell D2 will display the value in K format.
Next, we must custom number format in Excel for Millions in cell D3. And for that, we must repeat step 2 by selecting cell D3 and opening the Format Cells window.
And then, enter the custom number format in Excel for Millions code in the field in the Type section and click OK.
Next, repeat step 2 in cell D4, with the customized number format code to enter in the field in the Format Cells being that of Billions.
Thus, the final custom-formatted values in the target cells will be:
#5 – Show Negative Numbers In Brackets & Positive Numbers With + Sign
The table below shows three teams’ score points in a quiz.
Using custom number format codes, we can uniquely show the positive and negative score points in column D.
1: Choose cell D2, type in the following formula, and press Enter.
=B2
Use the fill handle to enter the expression in the remaining target cells.
2: Select cell D2 and press Ctrl + 1 to access the Format Cells window.
And then, select the Custom category in the Number tab, and enter the custom number format code specified in cell B2 in the field in the Type section.
And then click OK.
Next, repeat step 2 to view the given values in cells D3:D4 in the custom number formats specified in cells C3:C4 for each target cell one after the other.
#6 – Show Numbers Hide Text Values
The table below contains a list of students and their Mathematics test scores, with some text entries.
Here is how to show the numbers and hide the text values in column D.
1: Click cell D2, type in the following formula, and press Enter.
=B2
And use the fill handle to update the expression in the remaining target cells.
2: Choose the cell range D2:D11 and press Ctrl + 1 to access the Format Cells window.
Click the Custom category in the Number tab and enter the custom number format code specified in cell C2 in the field in the Type section. And click OK.
Thus, Column D cells will display the numbers and hide the text values.
#7 – Show Numbers With Conditional Colors
We can show numbers with conditional colors using the custom number format codes. However, a custom number format code can include only two conditions, separated by a semicolon.
For example, the table below shows five experiment results.
The aim is to code the results using different colors based on the ranges of values and display the output in column D.
And the conditions are to view the results below 100 in Red and Green otherwise.
Then, the steps are as follows:
1: Choose cell D2, type the following formula, and press Enter.
=B2
Use the fill handle to update the expression in the remaining target cells.
2: Select the cell range D2:D6 and press Ctrl + 1 to open the Format Cells window.
And then, choose the Custom category in the Number tab and the custom number format code, given in cell C2, in the field in the Type section.
Click OK to view the experiment results in the required colors.
Important Things To Note
- The custom number format in Excel option does not change the underlying value of the number.
- Ensure you provide the correct format codes in the required sections to achieve the desired custom number format.
- Use the correct number of digit placeholders and formatting codes, maintaining the appropriate order, to obtain the required custom number format.
- A custom number format can contain only two conditions, with a semicolon separating them.
Frequently Asked Questions (FAQs)
Excel custom number format with units is possible.
For example, the table below lists the top US states by land area in square miles.
Here is how to custom format the above numbers with the sq. mi. unit.
1: Choose the cell range B2:B9 and press Ctrl + 1 to access the Format Cells window.
And then, select the Custom category, which will show the current number format code of the chosen data in the list and the field in the Type section.
2: Enter a space character and “sq. mi.” after the existing format code in the field in the Type section.
3: Click OK in the Format Cells window to achieve the custom formatted numbers with units.
You can save custom number format in Excel.
You can access the Format Cells window by following the path Home – Number Format – More Number Formats. And then, use the Format Cells window – Number tab – Custom category to save the required custom number formats, one at a time.
You can change the custom number format in Excel using the following steps:
1. Choose a target cell or range.
2. Use Ctrl + 1 to access the Format Cells window.
3. The Number tab in the Format Cells window will open, where we must choose the Custom category.
4. Replace the existing custom number format code with the new one in the field in the Type section. Otherwise, choose a custom number format code from the list in the Type section to edit it to suit the requirement.
5. Press OK to view the chosen cell or range data in the changed custom number format.
Download Template
This article must be helpful to understand the Custom Number Format In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Custom Number Format In Excel. Here we learn to create different custom number formats in Excel with examples & points to remember. You can learn more from the following articles –
Leave a Reply