WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Custom Number Format in Excel

Custom Number Format in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

Custom Format Excel Example 1

As of now, the excel format of the custom number is General.

Custom Format Excel Example 1-1

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.

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

The shortcut key to formatting is Ctrl + 1.

Shortcut Key to Custom number 1

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Date Custom Format example 1

I want to show this number as a date. Select the cell and press Ctrl + 1.

Date Custom Format example 1-1

Select Custom format in excel and type the required date format under Type: section.

Date Custom Format example 1-2

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

Date Custom Format example 1-3

Below are some of the important date format codes.

Date Format example 1-4

#2 – Time Custom Format

We can customize the format of the time as well. Below are the codes for Time format.

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.

Number Custom 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.

Thousand Number in K, M, B Format

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

Show Negative Numbers

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

Numbers Hide text Values

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

Numbers With Conditional 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
6 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Custom Number Format Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More