Custom Number Format in Excel

How to Custom Format Numbers in Excel

Excel custom number 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 available here, like Number, Currency, Accounting, Date, Short Date, Time, Percentage, and many more.

These are all already predefined formatting, 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 the date should be first and two digits.
• MM means the 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 the 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 the 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; the 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 look beautiful.

#6 – Show Numbers Hide text Values

Showing only Numerical values and hiding all the text values technique is required sometimes, so the below code will help you with 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

• 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 along with its excel shortcut keys, practical examples, and a downloadable template. You may learn more about excel from the following articles –

• Formatting Numbers to Millions in Excel
• Extract Number from String Excel
• Excel Page Numbers
• Pivot Table Field Name Is Not Valid