In Excel, you can define custom number formats to display numbers in a specific way. This can be useful for formatting currency, percentages, dates, and times.

In this tutorial, you will learn how to define a custom number format in Excel. You will also learn about the most commonly used codes for custom number formats.

Number format creation

To define a custom number format prepare the data.

Select the cells that you want to format.

Click the Format Cells button.

In the Number tab, select the Custom category. In the Type field, enter the custom format code.

Most commonly used codes

Some of the most commonly used codes include:

• #: Represents a digit placeholder.
• 0: Represents a digit placeholder, but if a number has fewer digits than there are digit placeholders, the extra placeholders will be filled with zeros.
• . (period): Specifies the position of the decimal point in the number format.
• , (comma): Specifies the position of the thousands separator in the number format.
• %: Specifies that the number should be displayed as a percentage.
• $: Specifies that the number should be displayed as a currency value, with the appropriate currency symbol.

When defining a custom number format, you can combine these codes to create a format that meets your specific needs.

Custom formant examples

Here are some examples of custom number formats:

• “#,##0.00”: This format displays a number with a comma as the thousands separator and two decimal places.
• “0.0%”: This format displays a percentage with one decimal place.
• “# ???/???”: This format displays a date as a day, month, and year, with a space between each.

Note: You can even format colors. Just write [color]

How to Format Date and Time?

How to Easily Format Date and Time in Excel Format of date and time can be tough, but it will easily be performed together with me, follow me as I format date and time.

Date formatting

Click on a cell (1), type in =DATE(2014(year);10(month);05(day)) (2), and press enter.

Time formatting

Click on another empty cell (1), and type in =TIME(hour;minutes;seconds) (2), and finally press enter.

Right click on the time, and choose format cells.

Click on date (1), and choose desired format (2), before clicking ok.

Repeat previous step, but this time choose time (1), the type you want (2), and then press ok (3).

Advanced Custom Number Formats

In addition to the basic custom number formats that we have discussed so far, Excel also supports a number of advanced custom number formats. These formats can be used to display numbers in a variety of ways, including:

• Scientific notation
• Fractions
• Currency symbols from other countries
• Time formats with AM/PM
• Date formats with day names

Here are some examples of advanced custom number formats:

• “0.0E+00”: This format displays a number in scientific notation, with two decimal places.
• “0 / 100”: This format displays a number as a fraction, with a space between the numerator and denominator.
• “€###,##0.00”: This format displays a number in Euros, with a comma as the thousands separator and two decimal places.
• “mm/dd/yyyy hh:mm AM/PM”: This format displays a date and time with AM/PM, in the format “month/day/year hour:minute AM/PM”.

Related posts:

How to Consolidate Data in Excel How to Use DAX (Data Analysis Expressions) Functions How To Use Slicers In Excel