The number 1000000000 is difficult to read at a glance, isn’t it? How many zeroes was that? Similarly, the number 12.349845423084 sure contains a lot of decimal digits, but are all those numbers really necessary if you’re looking at a product’s cost-per-unit? Take a quick look at these numbers and tell me how you would compare them.

12.54332
955.3345
1.058383
2342.234

At a glance, they might all seem like they’re of the same order of magnitude, but the decimal point appears in different columns for each number. All the numbers are actually different orders of magnitude.

All these examples illustrate some serious readability concerns, which is a big human performance error trap. To fix these readability issues, use VBA NumberFormat, which is a property of Range objects (the same Range object that controls individual cells in your spreadsheet). In Excel, the main formats are easily accessible via the Number portion of the ribbon:

Dropdown Menu Showing Common Excel Cell Formats
The most common Excel cell formatting options, displayed in a dropdown

You might have even seen the cryptic number formats under Custom, like these:

Custom Formats Dialogue Box
Custom Formats Dialogue Box

In this tutorial, we will work with the VBA NumberFormat property, which is a way to automate number formatting in Excel without having to slowly navigate through the screens above. At the end of this tutorial, you’ll understand how these codes work and you’ll be able to write our own in your VBA macros.


Assigning and Accessing the VBA NumberFormat Property

Most of this tutorial will focus on how to write the codes themselves. But since this is a VBA blog, we’ll note how to programmatically assign the formats first.

The VBA NumberFormat property is a property of a range object, so you can assign the format to your range like this:

Sub assigning_numberformats()
Dim cell_to_format As Range
Dim vector_to_format As Range
Dim matrix_to_format As Range

Set cell_to_format = Range("A1")
Set vector_to_format = Range("B1:B100")
Set matrix_to_format = Range("C1:D50")

range_to_format.NumberFormat = "YOUR FORMAT CODE HERE" 'replace range and format code

End Sub

Of course, you need to replace range_to_format with the range you actually need to format. In this macro, we’ve predefined three ranges (cell_to_format, vector_to_format , matrix_to_format), but you can use whatever you want.

That’s all there is to assigning the range format. It’s just as easy as clicking the buttons in Excel, except with VBA you can mass-produce your desired results

If you want to access a cell or range’s existing NumberFormat property, you can read it as a string in your macro like this:

the_format = Range("YOUR RANGE HERE").NumberFormat

That’s all there is to it. It really is quite simple to assign and access a NumberFormat using VBA. Now let’s look at the hard part: writing our own format codes.


The Format Code

VBA NumberFormat codes are similar to Regular Expressions in that you define a pattern for the computer to follow. That makes the format dynamic and capable of accepting many inputs.

An expression like 0.0#" per part" might seem a bit odd at first, but it really denotes a pattern. Let’s break it down into its parts:

The Zero (0): there must be at least one digit displayed to the left and one to the right of the decimal

The Pound/Hashtag/Number symbol (#): if there is a significant digit here, it will display, if not, there will be no digit here. Conversely, if there are more than 3 digits to the right of the decimal place, the number will be rounded to two decimal places.

The Quotes (“”): The text between the quotes will always be appended to the numbers entered.

By applying this format code with this line:

Range("A1:A4").NumberFormat = "0.0#"" per part"""

We will end up with these outputs: 2018-09-19-first-one.png

Two Lists of numbers, with the formatted ones in Column A and the raw ones in Column B
The raw numbers (General format) are on the right and the formatted ones are on the left


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

Notice what happens here. If we only enter 5, we get back 5.0. But if we enter three digits after the decimal place, our formatted number is rounded to 5.58. It’s important to note that the underlying value stored in cell A3 is still 0.57777. It’s just displayed as 0.58. That’s what readability is all about. We’re not actually changing the numbers; we’re just presenting them in such a way that they’re easier for the user to digest.

See how the words “per part” were appended to every entry? I want you to note the double quotes in our macro If you want text within the format via VBA, make sure to keep track of your quotes, like "Outer Text ""Inner Text"" Outer Text." If you type just one set of quotes around the inner text, VBA will think the string is finished and get confused. This common mistake will throw frustrating errors at you until you satisfy its strict quotation mark requirements. I’ve discussed the complexities of concatenating quotation marks in VBA before. I encourage you to take a look if you want additional guidance.

Here’s a quick test for you: How would our output change if we changed our NumberFormat macro to

Range("A1:A4").NumberFormat = "#.0#"" per part"""

The only difference is the # before the decimal.

Answer: Everything would remain the same, except A3. Remember that # means “display if there, but do not display if not there”. Thus, A3’s number part would change to .58 from 0.58. All the other entries have digits before the decimal point, which are considered significant and will always be displayed.

Forcing Zeros

You might want to force all your numbers to include 3 decimals, regardless of whether the original input had 3 decimals. To do this, we can use “.000”. More than 4 decimal places will round, less than three will result in at least one trailing zero.

Thus 5 will become 5.000 and 5.9337 will become 5.934.

On the other hand, we could write “000.”, which would force a number like 52 to display as 052.

Aligning the Decimal Place

The 0 and # allow us to modify the number of insignificant digits by either forcing them to be displayed, rounding them, or ignoring them altogether. But what about that set of numbers we had earlier with different orders of magnitude? How can we make them easier to compare at a glance? We use the question mark. This number format example

Range("A1:A4").NumberFormat = ".????"

will produce the following result:

List of numbers, unformatted are unaligned while the formatted are aligned
The decimal points are now aligned

The decimal points are now aligned, with four decimal places. If there are more than 4 places, they’re rounded (just like # and 0).

Of course, we could achieve the same thing with 0s (".0000"), but our last entry would become “2342.2340”.

Caveat with Alignment

Technically, the question mark (?) is simply a placeholder that adds a space if the digit is missing. It pads the display with spaces when no number is present. Thus, if we center our text above and use the same code, we will get

Formatted with ? but stil unaligned
Unaligned Decimals

To remedy this, you will need to use at least as many ?s as your biggest number has on the left side. Here, we would need "????.???".


The Decimal Point and the Comma

The decimal point obviously denotes where the decimal part of the number begins. If it is omitted, all numbers displayed will be whole numbers. The format 000 will always be at least three digits, but none of them will ever be less than one (neer have a decimal part).

Set of unformatted and formatted numbers, using 000 as the formatting
Formatted and Unformatted using 000 as the NumberFormat code

If we have a code like "00.", then the display will always include a decimal point but never any digits after it, like 13..

The Comma

The comma is somewhat more interesting. When I say comma, I really mean whatever your thousands separator is. My visitors from outside the States may be used to seeing a period as the thousands separator.

Anyway, in most formats, it will likely just be the thousands separator: "#,#00.0#" and 0,000.0# will show us

Four sets of numbers, unformatted and then formatted with either #,#00.0# or 0,000.0#
Two different formats with commas as the thousands separator

This is how the comma affects output. We can see the difference with the forced digits (using 0) and optional zeroes and decimals (with #).

This is where things get interesting. The comma also acts as a scaler by a factor of 1000, or three orders of magnitude. To use it in this way, simply append it to the end of your format. To scale by one thousand, use a single comma. To scale by one million, use two commas. You get th idea.

Let’s say we are entering market capitalizations of the biggest companies in the world. Reading a number like 957847923 is a little difficult, but we can easily round to the millions place using this format code "$#,###.00#,, \M". What does this do?

First, it requires at least two decimal places and has an optional third, and it scales the number by one million. It also uses the \ character to add a single letter, M (for millions), and it adds the dollar sign before the number.

The number 957847923 would be displayed as $957.848 M, which is far easier to read. We immediately know it is a dollar amount on the order of millions.

The Literals

Literals show up exactly as typed. Using a backslash, we can add a character literal, such as M (for millions) or C (for Celsius). Most symbols (other than the reserved ones like 0, #, ?, and the like) also act as literals. So the degree symbol, the euro symbol, or the trademark symbol can all be typed into the NumberFormat string and show up on the spreadsheet.

One use of literals that frequently occurs in accounting is showing negative numbers in parentheses. People do this because a minus sign is easy to miss when scanning many numbers. You can simply use "-(0.00) in the “negative section” of the format, which we will look at next. This code utilizes the parentheses literal.


VBA NumberFormat Sections and Coloring

The VBA NumberFormat property has four sections corresponding to positive, negative, zero, and text entries. You separate the four sections using the semicolon.

So "0.0#; [Red]-0.00#; [Blue]0.; [Magenta]@" will format the cell depending on what’s entered. Positive numbers will be black, with at least one and up to two decimals, negative number will be red with the at least two and up to three decimal digits, zero will be blue and simply a zero with a decimal point, and all text will be magenta.

A negative, positive, zero, and text entry with formats
Four types of entries, all formatted differently

The @ Symbol

What’s with the @ at the end of the previous format code? Well, the @ notifies VBA that whatever text is entered there should take the place of the @ symbol. If you leave it out, any text type into the cell won’t appear at all. That means whenever you type out the sections, if you want to include formatting for text, you must include the @ symbol.

On a related not, if your purpose is to format for text (say for coloring or adding information), you can override the number format with a text format, like so:

Sub text_formatter()
Range("A1:A4").NumberFormat = "[Magenta] ""[TEXT:]"" @"
End Sub

Cells with formatted text
Each entry has [TEXT] appended to it and its color changed

The takeaway here is that our entered text (in Column B) is displayed in place of @, and our forced text [TEXT:] precedes it.


Conditional Formatting

You can even use conditional formatting directly in your VBA NumberFormat format codes by using square brackets, just like for the colors. You would separate the conditions with the semicolon.

Sub conditional_number_formats()
Range("A1:A4").NumberFormat = "[>=10][Green]$0.00;[<10][Red]$0.00"
End Sub

This NumberFormat example will force anything with a dollar amount greater than or equal to 10 to green and anything less will be red (negative or between 0 and 10).

VBA NumberFormat conditional formatting


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

Fractions

Decimals are definitely the most common way to displays information, but sometimes the industry standard is to use fractions (in some length measurements, for example). To force fractions, you just need to use the forward slash, like this:

Range("A1:A4").NumberFormat = "[>=10][Green]0 ""dollars and"" 00/100 ""cents"";[<10][Red]0 ""dollars and"" 00/100 ""cents"""

Raw numbers and fraction formatted
Common way to write check amounts, with a whole dollar amount and a fractional part for cents

Remember to separate the whole number part from the fractional part with at least a space to get mixed fractions. Otherwise you will get a pure fraction. Here’s what I mean. The NumberFormat "0/4" on 2.25 will display 9/4 instead of 2 1/4

The base number can be anything. So if we just wanted to show eighths (as in 5 and 3/8ths inches for a carpentry measurement), we could use this code:

Sub fraction_non_base_ten()
Range("A1:A4").NumberFormat = "00 "" and "" 0/8 ""inches"""
End Sub

which would give us

Shows two sets of numbers, one formatted with fractions with a denominator of 8
Fractions with a denominator of 8 and some textual information included

Adding more 0s will increase the accuracy of the fraction. For 0.25, of course, we will get 2/8 exactly. But something like 1.86, using 00/80 in our code, will yield 69/80 instead of 7/8.


VBA NumberFormat Symbols

Before we go, I want to leave you with a table you might find helpful when creating your own VBA NumberFormat strings. This table explains each of the common symbols used for formatting numbers and gives a brief example.

Symbol Purpose
Example
0 Placeholder, forces zeroes if no digits entered, rounds if too many digits entered 00.00
5.339 would display as 05.34
# Placeholder, only appears if digits available; otherwise it rounds like 0 ##.##
5.339 would display as 5.34
? Placeholder, inputs space if no digits entered; otherwise rounds like 0 and # ??.??
5.339 would display as _5.34
(_ represents a space)
. Decimal point, acts like expected, except just the decimal point will lead to leading or trailing points .
55 would display as 55.
0.34 would display as .34
[]

Conditional Formatting, can be colors or a test case. Conditions are separated by semicolons
[Red] ; [>10]
"" Allows the addition of strings for every cell ##.# "pounds"
10.32 would display as 10.3 pounds
, Thousands separator OR scaler; if added after a format code, it becomes a scaler 0.##,
10953 would display as 10.95

156 would display as 0.16
\ To add literals, like a single character 0.##, \T
10953 would display as 10.95 T
@
To denote text, forcing any entries to become text formatted @
1 would display as "1" (as text)
/ To create fractions; adding more 0s will increase the decimal-to-fraction conversion; can change bases, and remember to separate the whole number part from the fractional part to obtain mixed fractions
0/0
0.5 would display as 1/2

# 0/2
11.5 would display as 11 1/2

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

Conclusion

This has been a really informative tutorial for working with number formats and the VBA NumberFormat property. It certainly isn’t comprehensive, though. Format codes are quite extensive. For your garden-variety macro, these format codes should be sufficient for you. We’ve shown you how to force numbers to look a certain way, including requiring decimal points and leading zeroes. We’ve also demonstrated how to format text, display fractions, and include conditional formatting in your VBA NumberFormat strings..

The next time you open up the “Custom” cell formatting dialog box, scroll through some of the listed items. They might just make a bit more sense to you now!

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!

Share this article on Google+, Twitter and Facebook, then leave a comment below and let’s have a discussion.

This article was written by Cory Sarver on behalf of The VBA Tutorials Blog.

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.