Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Bar Chart
  • How to Sort by Color in Excel
  • How to create Gauge Chart in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Excel Line Chart

References

  • How to get last row in numeric data in Excel
  • How to get last column number in range in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Offset in Excel
  • How to get address of named range in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation no punctuation
  • Data validation must not exist in list
  • Excel Data validation must not contain
  • Excel Data validation date in next 30 days

Add Leading Zeros, Text And Colours Custom Number Format

by

Excel Custom Number Format with Leading Zeros, Add Text, Colours Example

How to add a leading zero in excel

Beyond the built-in options: The Format Cells dialog offers so many options available “out-of-the-box.” The available format category and type options go far beyond the basics.

But, if what is available still doesn’t meet your requirements, you can always create a custom format.

Let’s consider a set of numbers.

We start by opening the Format Cells dialog box as we did before. This time we will select Custom from the Category list.

Then we select from the Type list.

We will start with “#,##0” and then build our own custom format from there.

If we want to build our own custom currency format, one of the first things we can do is add a dollar sign.

We can type that into the Type text box.

One of the things you can customize is the colour of your type font.

You do this by preceding your number format with the colour name enclosed in square brackets.

We add blue type font formatting to our current format.

The preview in the Sample box does not show colour changes.

We must click OK to see the change.

So far, we have only worked with number formats that consist of one section of code.

Using semicolons to separate, we can have up to four of these code sections.

This means that we can do something like format both positive and negative numbers.

For instance, we may want our negative numbers to show up red font. This will be a good contrast to the blue we have set for positive numbers.

We could also have negative numbers shown in parentheses.

We can even go a step further and set a third code section for zero values.

This will now change our list of numeric values to something more meaningful.

This is a great demonstration of the flexibility custom number formatting offers. But let’s go even a step further.

Custom formatting even allows you to add text to your formats.

You could be even more descriptive based on whether your values are positive or negative.

You can do this by adding text by enclosing the custom string in double quotes.

We have added the text “ Profit” to the end of our positive values. We have also added the text “ Loss” to the end of our negative values.

Pay attention to the spaces after the opening double quote and the first letter of each string.

Without these leading spaces, there would be no space between the numeric value and the text.

Now our updated custom format codes yield the following results.

Example: Decimal Places Custom Format in Excel

You can control the number of decimal places. Use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc.

1. Enter the value 839.1274 into cell A1.

2. Use the following number format code: 0.00

Example: Add Leading Zeros in Excel

For example, you might have codes that consist of 5 numbers. Instead of typing 00041, simply type 41 and let Excel add the leading zeros.

1. Enter the value 41 into cell A1.

2. Select cell A1, right click, and then click Format Cells.

3. Select Custom.

4. Type the following number format code: 00000

5. Click OK.

Note: Excel gives you a life preview of how the number will be formatted (under Sample).

Result:

Note: cell A1 still contains the number 41. We only changed the appearance of this number, not the number itself.

Example: Add Text Custom Format in Excel

You can also add text to your numbers. For example, add “ft”.

1. Enter the value 839.1274 into cell A1.

2. Use the following number format code: 0.0 “ft”

Note: remember, we only changed the appearance of this number, not the number itself. You can still use this number in your calculations.

Example: Large Numbers Custom Format in Excel

You can also control large numbers. Use one comma (,) to display thousands and use two commas (,,) to display millions.

1. Enter the following values in cells A1, B1, C1 and D1: 1000000, 2500000, 81000000 and 700000.

2. Use the following number format code: 0.0,, “M”

Note: we used 0.0 for one decimal place and “M” to add the letter M.

Example: Add Colour Custom Number Format in Excel

You can control positive numbers, negative numbers, zero values and text all at the same time! Each part is separated with a semicolon (;) in your number format code.

1. Enter the following values in cells A1, B1, C1 and A2: 5000000, 0, Hi and -5.89.

2. Use the following number format code: [Green]$#,##0_);[Red]$(#,##0);”zero”;[Blue]”Text:” @

Note: #,## is used to add comma’s to large numbers. To add a space, use the underscore “_” followed by a character. The length of the space will be the length of this character. In our example, we added a parentheses “)”. As a result, the positive number lines up correctly with the negative number enclosed in parentheses. Use two parts separated with a semicolon (;) to control positive and negative numbers only. Use three parts separated with a semicolon (;) to control positive numbers, negative numbers and zero values only.

Example: Custom Dates and Times Format in Excel

You can also control dates and times. Use one of the existing Date or Time formats as a starting point.

1. Enter the value 42855 into cell A1.

2. Select cell A1, right click, and then click Format Cells.

3. Select Date and select the Long Date.

Note: Excel gives you a life preview of how the number will be formatted (under Sample).

4. Select Custom.

5. Slightly change the number format code to: mm/dd/yyyy, dddd

6. Click OK.

Result:

Example: Repeat Characters Custom Format in Excel

Use the asterisk (*) followed with a character to fill a cell with that character.

1. Type Hi into cell A1.

2. Use the following number format code: @ *-

Note: the @ symbol is used to get the text input.

Post navigation

Previous Post:

TRUE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Pie Chart

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to return blank in place of #DIV/0! error in Excel
  • SWITCH function example in Excel
  • Invoice status with nested if in Excel
  • How to use Excel XOR Function
  • Nested IF function example in Excel

Date Time

  • Display Date is same month in Excel
  • Basic Overtime Calculation Formula in Excel
  • How to show last updated date stamp in Excel
  • Pad week numbers with zeros in Excel
  • How to calculate quarter from date in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel

General

  • Basic error trapping example in Excel
  • Using Existing Templates in Excel
  • How to password protect excel sheet?
  • Basic text sort formula in Excel
  • Creating and Opening an existing file in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning