Custom Number Format in Excel

Table of Contents

Custom Number Formatting in Excel
How to Create Custom Number Format in Excel?

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 the 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 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.

You can download this Custom Number Format Excel template here – Custom Number Format Excel Template

How to Custom Format Numbers in Excel