Custom Number Format Excel (Table of Contents)
How to Custom Format Numbers in Excel
Formatting is nothing but making the data look better or make the data visually more appealing. Excel has many inbuilt number formatting, on top of this we can customize the excel number formatting by changing the format of the numbers.
Excel works on numbers and based on the format we give it shows up the result. For example, look at the below example.
In cell C1, I have a number 43473.
As of now the excel format of the custom number is General.
If you click on the drop-down list there are several built-in number formats are available here, like Number, Currency, Accounting, Date, Short Date, Time, Percentage and many more.
These are all already predefined formatting’s but we can customize all these and make the alternative number formatting and this is called Custom Number Formatting.
How to Create a Custom Number Format in Excel? (Using Shortcut Key)
Normal formatting is available under the Home tab. In order to do the custom formatting, we need to right click on the specific cell and select Format Cells.
The shortcut key to formatting is Ctrl + 1.
Ok, now we will discuss the different types of formattings.
#1 – Date Custom Format
This is the most common formatting we are work with day in day out. Now, look at the below image I have a number in cell C1.
I want to show this number as a date. Select the cell and press Ctrl + 1.
Select Custom format in excel and type the required date format under Type: section.
- DD means date should be first and two digits
- MM means month should be second and two digits
- YYYY means year should be last and four digits
Now hit enter it will show the date in the mentioned format.
Below are some of the important date format codes.
#2 – Time Custom Format
We can customize the format of time as well. Below are the codes for Time format.
If you observe the above table I have not mentioned the code to show the only minute. Since “m” & “mm” coincident with the month we cannot show only minute section.
In the case of time exceeding more than 24 hours, we need to mention the hours in brackets i.e. [hh]
#3 – Number Custom Format
When we are working with numbers it is very important how we show the numbers to the readers. There are several ways we can show up the numbers, below codes will help you in designing your number format.
#4 – Show Thousand Number in K, M, B Format
Showing numbers in lakhs will require a lot of cell space and will not fit in the report sometimes, but we can customize the number format in excel.
#5 – Show Negative Numbers in Brackets & Positive Numbers with + Sign
Showing negative numbers in brackets and positive numbers with the + sign will make the number looks beautiful.
#6 – Show Numbers Hide text Values
Showing only Numerical values and hiding all the text values technique is required sometimes so below code will help you on this.
#7 – Show Numbers With Conditional Colors
I have seen many cases where I wanted to show some values in Blue, some in Green, By changing the number format we can alter the font colors.
Things to Remember about Custom Number Format in Excel
- These are the most commonly used number formats but there are several available on the internet.
- Displaying huge numbers with millions and billions will make the report more appealing. Nobody wants to count the zeros and read rather they like millions and billions in front of the numbers.
- We can show the different numbers in different colors.
Recommended Articles
This has been a guide to Custom Number Formatting in Excel. Here we discuss the Top 7 Custom Number Formats in Excel along with its excel shortcut keys, practical examples and downloadable templates. You may learn more about excel from the following articles –
- Excel Formatting Numbers to Millions & Thousand
- How to Edit Drop-Down List in Excel?
- Pivot Table Sort in Excel
- Pivot Table in Conditional Formatting
- Using Shortcut Key in Excel
- Using AutoFormat in Excel
- Use Conditional Formatting Based on Another Cell Value
- Tips on Conditional Formatting in Excel
- File Formats in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
