How to Custom Format Numbers in Excel

Excel custom number formatting is nothing but making the data look better or visually 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 is based on the format we give. It shows the result. For example, look at the below example.

In cell C1, we have the number 43473.

As of now, the Excel format of the custom number is “General.”

If we 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. We need to right-click on the specific cell and select “Format Cells” to do the custom formatting.

The shortcut key to formatting is Ctrl + 1.

Ok, now we will discuss the different types of formattings.

#1 – Date Custom Format

It is the most common formatting we work with day in and day out. Now, look at the below image. We have a number in cell C1.

We want to show this number as a date. So, we must select the cell and press Ctrl + 1.

Next, we must select the “Custom” format in Excel and type the required date format under the “Type:” section.

  • DD means the date should be first and two digits.MM means the month should be second and two digits.YYYY means the year should be last and four digits.

Now, press the “Enter” key. 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 the time format.

If you observe the above table, we have not mentioned the code to show the minute. Unfortunately, we cannot show only the minute section since “m” & “mm” coincide with the month.

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 codes below will help you design your number format.

#4 – Show Thousand numbers in K, M, and B Format

Showing numbers in lakhs can sometimes require a lot of cell space and not fit in the report, 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

Sometimes, showing only numerical values and hiding all the text values technique is required. So the below code can help us with this.

#7 – Show Numbers With Conditional Colors

Things to Remember

  • These are the most commonly used number formats, but several are available.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.

This article is a guide to Custom Number Formatting in Excel. We discussed the top 7 custom number formats and their Excel shortcut keys, practical examples, and a downloadable template. You may also learn more about Excel from the following articles: –

  • Formatting Numbers to Millions in ExcelExtract Number from String ExcelExcel Page NumbersPivot Table Field Name Is Not Valid