The custom number format in excel is a very key feature that can help organize your data for presentation. Although excel has a lot of built-in number formats such as date, percentage, currency, time accounting, these may be limiting depending on how you want to organize your data. Thankfully, excel allows for including your custom number format. We would show you how to access the custom number feature in just a second.
Accessing the Custom Number Format
It is pretty easy to navigate to the custom number format in excel. With a combination of keys or through the home tab, you can open the custom number format window and format your numbers as you so desire. Let’s take a look at steps to opening the custom number window through the home tab.
Step 1: Navigate to the home tab > Number Group > Click on the button to the right of the number text
Step 2: Select Custom in the format cells window
Step 3: Type in your custom number code in the type box and click ok when you are done
Custom Number formats
Now that we have gotten the introduction to this topic out of the way, let’s delve into the various custom number formats you can use in your worksheet. Let me run you through the list then we begin
- Leading Zeros
- Large Numbers
- Negative numbers in Brackets
- Dates & Time formats
- Add text after number
- Custom Number with Conditional Formatting
Leading Zeros
The custom number format in this section is used for displaying leading zeros, this could be useful for creating codes that need 2, 3, 4 5, or any amount of numbers you desire without doing away with single digit numbers from 0 – 9. For example, if you maintain a 4-digit code system, you can include 3 leading zeros (0) to the number 1 to create your first code (0001). If you type in the leading zeros into a cell with general number format, excel removes those leading zeros on pressing enter. With the custom number format however, you can force excel to display those leading zeros. Let’s see how:
Step 1: Enter a value in any cell of your choice. We have chosen the number 1 for our example
Step 2: Pop out the format cells window using the steps discussed in the previous section and navigate to custom in the format cell window
Step 3: Type in the number format code: 0000
Step 4: Click OK
Result: You have a number 0001 which can be used as your first code
Large Numbers
I used this trick a lot during my time developing business cases as a Finance business Partner but when I moved to the Financial Planning & Analysis team it was frowned upon because we could not afford to make the mistake of forgetting what it should or should not be, so the rule of thumb was to clearly divide and not use custom number tricks. What am I talking about? Talking about depicting large numbers such as millions and billions as a few hundred with the caveat (in millions or billions written somewhere) No, I didn’t divide by 10^6 or 10^9, the numbers were the original numbers, however they were being displayed like as though they had been divided by 10^6 or 10^9.
Let’s assume that that we have 2 numbers (100 million and 7 billion) that we need to apply these custom codes on. Let’s look at the steps below
Step 1: Enter 100,000,000 in cell A1 and 7,000,000,000 in cell B2
Step 2: Select cell A1, open the custom number format window and type in the code: 0,,
Note: One comma (,) after the zero removes three zeros. So for millions you would need 2 commas, and for billions you would need 3
Step 3: Select cell B1, open the custom number format window and type in the code: 0,,,
Negative Numbers in Brackets
In accounting, we are used to presenting negative numbers in brackets. You can replicate this in excel and can even add a color code to differentiate between negative and positive numbers. Let’s take a look at a custom format to enclose negative numbers in brackets and add a red colour to differentiate.
Step 1: Select the cell or range containing the negative number(s) and press Ctlr+1 to bring up the format cells window
Step 2: Navigate to custom in the format cell window
Step 3: Type in the number format code: #,##0_);[Red](#,##0)
Result: The negative number is in brackets with a font color of red
Dates & Times Format
There are many ways you can display dates & time with your custom code in the format cell window. we would review some of these codes below, but first you must be aware that to format dates, you would need a combination of a couple of d, m and y.
Of course, d would represent the day, m the month and y the year. In this section, we are assuming a date of 31st December 2022, and some of the custom dates we can display using a combination of the 3 letters are as follows:
- dd mmm yyyy to display 31 Dec 2022
- mmm yy to display Dec 22
- ddddd dd to display Saturday 31
- mmmm yyyyy to display December 2022
Add Text After number
Adding text after a number is the easiest custom number format to do. All you need are quotations before and after your text in the custom tab in the format cell window. Let’s assume that we have calculated the payback period of an investment on cell B3, but we want that figure to be in Years, but we still want to be able to use it actively in formulas. What do we do? Simple, just follow the steps below:
Step 1: Select the cell in question and press Ctlr+1 to bring up the format cells window
Step 2: Go to custom in the format cell window and add opening quotations, type in your text and then add closing quotations. In our example we want to add the text years, so we input ” Years” after the General text in the custom format window.
Result: We have the text years added after our number and screenshot shows that the cell can still be referred to in a formula that derives numbers.
Custom Number with Conditional Formatting
Custom number format avails you the ability to control how positive, negative, zero values would be displayed in your excel worksheet with a single number code. In this section, we would give examples of number codes to use in achieving this.
In our example for this section, we would specify format for regular numbers, positive numbers in millions, negative numbers in millions and text, along with color codes for each of these scenarios. Let’s begin, shall we:
Step 1: Enter random values in 4 different cells as seen below:
Step 2: Select one of the cells, for example cell B2 and open the format cells window by pressing Ctlr+1
Step 3: Type in the number format code:
Code for positive numbers in millions: [Blue][>=1000000]#,##0.0,,”m”;
Code for negative numbers in millions: [Red][<=1000000](#,##0.0,,”m”);
Code for regular numbers not falling into the categories above: [Green]0;
Code for Text: [Blue]”Text:”@
Step 4: Stay on cell B2 and select format painter to copy formats to the other cells
Result: The remaining data is formatted according to the conditional formats specified in your code.
Note: For positive and negative numbers (without converting to millions but with the color codes), you can use the number format code below:
