Excel: Custom number formatting

When you have numerical values in your worksheet, and you want to format them in a specific way that is not present in the default format types, you can always create your own by using the Custom Formatting feature.

You probably noticed that such numbers as phone numbers or social security numbers are divided into small portions, usually separated by spaces or hyphens. That’s because the human brain is actually better in remembering a larger number of digits or characters when they are divided into small groups.

In Excel, you don’t have to create a new format for zip code, social security number or a phone number. But sometimes you need to work with other formats, for example, VAT identification number. You won't find it in Excel, you have to create your own. 

Example 1:

Look at the following example. You have two names with two VAT identification numbers.


To format those number, first select cells C3 and C4, open the Format Cells window (from the contextual menu), and in the Numbers tab select the Custom format. In the Type textbox enter “000-000-00-00” and click OK.


Thanks to the custom formatting we achieved the desired effect. Now, the numbers are easier to read and remember.

Download attachment