Custom number formats - Formatting text

Customizing number formats can be very useful in helping you control the appearance of numbers, dates, and text in cells.

Overview

This series of tutorials assumes that you have a basic understanding of what number formats are, and how to access the custom format category. (If you’d like a broad overview of the applications of custom number formats, refer to the introductory tutorial about custom formats).

In this tutorial, we will firstly look at the general structure of number formats, and then we will see how we can use number formatting for including text and adding spaces.

General structure of a number format

Four-section layout

In order to specify how cell input should be presented, we use a four-section structure, which allow us to specify the presentation of positive numbers, negative numbers, zero, and text (in that order). As a starting example, suppose use this four-section format:

• [Green]0.00;[Red](0.00);[Magenta] 0.00;[Blue]"Text -> "@

The first section [Green]0.00 indicates that positive numbers will be formatted in green, with two decimal points.

The second section [Red](0.00) indicates that negative numbers will be formatted in red, with two decimal points, and the number will be enclosed with brackets according to convention.

The third section [Magenta]0.00 indicates that a zero will be formatted in magenta, with two decimal points.

The fourth section [Blue]"Text -> "@ indicates that any text values will be formatted in blue, and that will be comprised of “Text -> “ joined with the actual text in the cell (represented by @).

If we specify only two sections (instead of four), then the first section is applied to positive numbers and zero numbers, whilst the second is applied to negative numbers. If you specify only one section, it is applied to any number (regardless of sign).

Displaying text with numbers

Sometimes, a number by itself might not be meaningful, but instead we might need to add text to make it more descriptive. For example:

• Adding currency symbols (dollars, euros, yen)
• Displaying percentage symbols (%)
• Indicating units (kg, cm, m)
• Using labels such as profit/loss, gain/loss, surplus/shortage, increase/decrease to differentiate between positive and negative numbers (e.g. $600 Loss, rather than -$600)
• Adding commas as thousands-placeholders for larger numbers (5,000,000) or perhaps abbreviating larger numbers with K or M symbols (24.3 M, 5.1 K) for summary reports

Some of the advantages of using number format:

• Don’t need to type any text directly into cell
• Don’t need to use formulae such as CONCATENATE()

Currency symbols

The number format for dollars is: $#,##0.00

The comma is the thousands-separator, the 0 and # are digit placeholders, and the dot (.) represents a decimal point. We will be looking at these in detail later on.
To work with both dollars and cents, we apply the format:

• [<1].00¢;$#,##0.00_¢

(The [<1] is used to test whether or not the value is less than 1, which is when we use only the cents ¢ notation.)

Percentage symbols

To display the percentages, you need to include the percentage symbols. For example, if we want to display percentages with 2 decimals points, use the number format:

• 0.00%

Text labels and numbers

Firstly, custom number formatting enables us to automatically display text after numbers. This can be useful if we want to show units next to numbers.

Secondly, custom number formatting may be used to display text to the left of numbers. Cash flow models might require “Year” labels, scenario models might require “Case labels.

Thirdly, we can use custom number formatting to interweave the display of text and labels.

Labels based on positive/negative sign

Thousands separator

Including a section for text entry

Adding spaces

We can use the underscore character (_) to insert spaces.

“Conditional formatting”

We could use the following to format positive as green, negative as red, and everything else default:

• [Green]; ; [Red];

COMMENTS