Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Basic INDEX MATCH approximate in Excel
  • How to get relative column numbers in a range in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Perform case-sensitive Lookup in Excel

Data Analysis Examples

  • How to calculate current stock or inventory in Excel
  • Chart Axes in Excel
  • Excel Line Chart
  • How To Compare Two Lists in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text
  • Excel Data validation only dates between

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

by

Beyond the built-in options: custom formatting

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 color of your type font.

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

We add blue type font formatting to our current format.

The preview in the Sample box does not show color 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.

Worked Example:   Change Cell Styles in Excel

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.

Decimal Places

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

Leading Zeros

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.

Worked Example:   Wrap Text Automatically vs Manual Line Break in Excel

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.

Add Text

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.

Large Numbers

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.

Colors

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.

Worked Example:   Clean and reformat telephone numbers using SUBSTITUTE function in Excel

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.

Dates and Times

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:

Repeat Characters

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:

How to setup and print Excel worksheets

Next Post:

How to share Excel data with Word documents and other files

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
  • Remove text by position in a cell in Excel
  • How to count total words in a range in Excel
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • How to count specific characters in a cell in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • Extract time from a date and time in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Display Days in month in Excel
  • Convert text timestamp into time in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate principal for given period in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles