Description

FormatNumber is a property of the Range object of Excel. The Range object can be considered as a cell or a group of cells — be that a single cell, a column in an Excel table, or the UsedRange of an Excel sheet.

The FormatNumber property can be used to format time, date, currency/accounting format, and many others. There is a constant need when working in Excel to set the cell’s number format in a way that relays information in the best way to the reader.

For example, long numbers could really benefit from having some commas in them. Similarly, numbers with long decimals are very distracting, rounding them up (or down) to the nearest 2 or 3 decimals usually makes your data much more presentable.

Furthermore, FormatNumber can be used to set a custom format that opens up a whole array of possibilities. It’s important to remember that all numerical values are stored in Excel as serial numbers. So even date and time are stored as serial numbers.

Syntax

Range.NumberFormat

The following tables show the symbols for the most common formatting.

Format	Format Code	Example	Result
Number (decimals)	#.#	122.333	122.3
Currency	$#	122	$122
Percentage	%#	122	%122
Fraction	# ?/?	122.3	122 1/3
Separators	#-#-#	122.333	1-2-2
Commas/decimals if applicable	#,##0.00	122333.444	122,333.444
Thousands (with commas if applicable)	#,##0, “K”	1223334	1,223 k
Millions	#,##0.00,, “M”	1223334	1.22M

Special Characters in Format Code

Code	Format Code	Example	Result
The Zero (0): Ensures that a number will be placed, if not, then it places zero. If you want the space to be empty, then use ? instead of 0.	0000.00	122.1	0122.10
The Pound/Hashtag (#): If there is a number to be displayed then it will, if not, then nothing.	#000.0##	122.1	122.1
The Quotes (“”): Anything between the quotes will be appended to the number. When used in VBA, an additional set of double quotations should surround them.	###, “K” In VBA: “###, “”K”””	122333 122333	122K 122K
The Commas: Aside from being used as a Thousand Separator, commas are used to blank out 3 digits to their right (Number scaling). Placing two commas in this example removes 6 digits to their right.	#,###,, “M” #,###, “M” #,### “M”	123456789 123456789 123456789	123 M 123,456 M 123,456,789 M
$ / + – ( ) { } < > : ^ ‘ space: These symbols are considered literal characters. They are displayed exactly as they are typed in the formatting code (in the same position). You don’t need wrap them in double quotations as they are considered literal by default.	+# (#)	123 123	+123 (123)
; (semicolon): semi-colons are used to separate conditions areas as [display of positive values] ; [display of negative values] ; [display of zero values] ; [display of text values]	[Green]0.0;[Red]-0.0;[Blue]0.0;[Magenta]@	1 -1 0 text	1.0 -1.0 0.0 text
= < > <= >= <>: comparison symbols are used to specify conditions based on which the formatting code would be executed.	[Green][>=70]0.0;[Blue] [>=20]0.0;[Red]0.0	80 50 10	80.0 50.0 10.0
d m y (day month year): date symbols are used for date formatting	d dd ddd m mm mmm mmmm mmmmm yy yyyy	9/19/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020 9/9/2020	19 09 Wed 9 09 Sep September S 20 2020
h m s (hours minutes seconds): time symbols are used for time formatting.	h hh	10:15:20 AM 8:15:20 AM	10 08

Double quotations in VBA

Using the format coding above can be done either through the Excel interface directly or through VBA. A very important difference in the way that the codes are written is that double quotations need to be enclosed in another set of double quotations, as we will see in the examples below.

Using # vs ###

You can get away with a single “#” symbol instead of listing an (unseparated) number of then (ex. ###) because Excel will convert a single one to the appropriate number of hashtags.

For example, you can write #,#,, "M" instead of #,###,, "M". This doesn’t apply to the zeroes though because the zeroes clearly indicate that a digit must be placed in this position, and instructing Excel to place a zero if there’s no actual value there. This means that using the formatting code “00” will format “5” as “05”. Furthermore, “?00” will format “5” as “ 05”.

Example 1: Formatting Cells Using the Excel Interface Instead of VBA

Right click the cell that you want to format, then click “Format Cells..”

In the left menu, go to “Custom”.

In the Type field, insert the formatting code, press OK and you’re done!

If we are to implement the same formatting code using VBA, we would write it as

“#,###,, “"M"””

The outer double quotations must be written because the code is a string (characters). The M needs to be surrounded by double quotations naturally. But in order to indicate to VBA that we want those double quotations literally (meaning we’re not just using them in a functional way, but that we actually want them as part of our string), we surround them with another pair of double quotations. The code we would use in VBA is as follows:

Sub format()
Range(“A1”).NumberFormat = "#,##0.00,, ""M"""
End Sub

We start with the range that we want to set the NumberFormat property of. We then add NumberFormat and set the property equal to the formatting code that we want. Note the bolded double quotations, which indicate that the double quotations around M are literal.

Example 2: Basic VBA Formatting

This is a simple example to demonstrate utilizing the NumberFormat property in VBA. In this example, we want to set the number of decimal points to 2 for Range(“A1”) and add commas for Range(“A2”) to make them more readable. Currently, our cells A1 and A2 look like this

Let’s see how we can make them more readable. We’ll start with writing down the cell range we want to work with.

Next, we specify the value of the NumberFormat property.

Sub Example_2()
Range("A1").NumberFormat = "#.##"
Range("A2").NumberFormat = "#,#"
End Sub

The results are shown below:

We probably want to add commas to the cell A1 for easier readability. For A2, we want to remove the six digits on the right and have “M” instead, and we don’t want any decimals in there.

Sub Example_2()
Range("A1").NumberFormat = "#,##0.##"
Range("A2").NumberFormat = "#,##0,, ""M"""
End Sub

Example 3: Conditional Formatting

Sometimes you might want to turn the font red if the number is negative. Maybe you want to display a particular string (set of characters) when the value of the cell is zero.

You might want to set something up to handle a situation where the data stored in the cell is text value, which could ruin your formulas if they are set up with the expectation that the cell would contain numerical data. The syntax for handling these types of exceptions would look like this:

[display of positive values] ; [display of negative values] ; [display of zero values] ; [display of text values]

Within each of these square brackets, you can specify the formatting code that gets the job done. Let’s take a look at an example. Here, we have a list of positive and negative decimal numbers:

The list will look much neater if we apply the following formatting:

• Have the negative numbers easily distinguished by having them in red
• Set the decimal points in a uniform way
• Set the whole list to have 2 decimal places
• Add commas
• Replace zeroes with “-“

 Here’s the code that would get this done for us:

Sub Example_3()
Range("A1:A9").NumberFormat = "#,0.00;[red]-#,0.00;””-“”"
End Sub

Let’s break down the formatting code. We have 2 semicolons. This means we have code for the positive value, and then code for the negative value, then the code for zero values.

Positive values formatting

• “.00” ensures that we have two decimal places.
• “0.00” ensures that we have at least one digit to the left of the decimal, if there isn’t then a zero will be placed there.
• “#,0” ensures that commas are added if applicable. Note that we’re using hashtag symbol instead of zero because if we only have a single digit to the left of the decimal, then we wouldn’t want to add a zero to its left.

The output is as follows:

Summary

This was an introduction to the Number Format property in Excel that can be implemented through the Excel interface or VBA. The formatting code was explained with examples to get you started with creating and understanding customized formats.

We also saw that there is conditional formatting within the Number Format property that can be used to set specified formats for specified conditions. We also saw how we can combine text with numbers when formatting.