Cracking the Mystery of Custom Number Formats in Excel

//

Brian Krisanski

CRACKING THE MYSTERY OF CUSTOM NUMBER FORMATS IN EXCEL

Excel has many built-in formatting options that you can apply to your information. These range from Number, Currency, Accounting, Date, Time, Percentage, etc to name just a few.
 
But what do you do if you cannot find the exact format you want to apply to your information?
 
Why you create your own custom format that’s what.

 
To create your own Custom Format, click the small arrow in the Number section of the Ribbon Menu, then on the Number Tab click Custom in the Category: table.
 
Alternatively, you can also click on the cell you want to format, then right-click on it and select Format Cells… from the dialogue that pops up.

image - Custom Format

To apply a custom format to a cell, it is very important to understand how cell formats are seen by Excel. Excel sees a cells format as having four individual sections.

These are:

  • positive numbers;
  • negative numbers;
  • zeros; and
  • text.

Each of these Sections are separated by a semi-colon (;). These code sections define the format for <POSITIVE numbers> ; <NEGATIVE numbers> ; <ZERO’s> ; <and TEXT> in that order.

If you create a custom number format you do not have to specify all four sections. For example, if you create custom formats for the first two sections, the first section would be used for both positive numbers and zero values and the second section would be used for negative numbers.

If you only used one section, all number types would use that one format.

Text is only affected by custom formats when we use all four sections.

It is important to note that by applying custom formatting to the information in your cells, you are only impacting what is being displayed in Excel and you are not changing the underlying value being stored in the cell.

There are many different Formatting Codes that can be used within sections of a Custom Format. The tables below show these. These tables are direct from Microsoft®

image - Custom Number Formats
image - Custom Number Formats
image - Custom Number Formats
image - Custom Number Formats
image - Custom Number Formats

Whenever you create a custom number format, this format is stored in the workbook where you created it.
 
If you copy a value with a custom number format to another workbook, the custom format is also copied and will also be available in the new workbook.

Download

You can download a PDF transcript of this tip, simply fill in your details in the box below and you’ll get instant access to our 100% FREE downloads.

We also have available for you to download a copy of the spreadsheet we used in this article. Feel free to also download a copy of it so you can explore this tip even further.

image: PDF Download PDF Transcript – Cracking the Mystery of Custom Formats in Excel.

image: download Excel File – Cracking the Mystery of Custom Formats in Excel.

Continue the Discussion

Do you customise the formats of your data in Excel? If so, how do you do it? Do you use the formatting codes described above or do you have others you use? Continue the discussion and add your thoughts in the comments section at the bottom of this article.

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter, and/or Linkedin.