Custom Number Formatting with Complex Display Rules in Excel

Excel’s custom number formatting feature lets you control how numbers, dates, and text are displayed without changing the underlying data. This functionality is handy when dealing with complex datasets or when displaying numbers in a certain way for readability, reports, or specific calculations. This tutorial will explore how to create custom number formats with advanced and complex display rules.

Basic Structure of Custom Number Formatting

Number formatting in Excel is controlled through specific syntax. In Excel, the custom number format consists of four sections, separated by semicolons (;).

[Positive Numbers] ; [Negative Numbers] ; [Zero] ; [Text]

Each section controls the formatting of a different type of number:

• First section – For positive numbers
• Second section – For negative numbers
• Third section – For zeros
• Fourth section – For text strings

You can use one or more of these sections, depending on your needs.

1. Custom Formatting for Positive, Negative, and Zero Values

Let’s assume you want to differentiate how positive, negative, and zero values are displayed. Display positive numbers in green with commas, negative numbers in red with brackets, and zero values as N/A.

Color Formatting: You can apply color formatting to differentiate ranges of values. Use predefined colors like [Red], [Blue], [Green], etc., within your number format.

• Select the cells you want to format.
• Press Ctrl + 1 or right-click and choose Format Cells.
• In the Format Cells dialog,
  • Go to the Number tab >> select Custom.
• Enter the following format:

Custom Format:

[Green]##,##0.00;[Red](##,##0.00);”N/A”;@

Explanation:

• [Green] and [Red]: Apply specific colors to positive and negative numbers.
• #,##0.00: It adds comma separators and displays the number with two decimal places.
• ( ): Shows negative numbers in parentheses.
• “N/A”: Displays the string N/A for zero values.
• @: Displays any text data as-is.

2. Using Symbols and Text in Number Formatting

You can insert text or units into numbers. It helps to deal with currency, percentages, or units like kg or hrs.

• Select the cells you want to format.
• Right-click and select Format Cells.
• From Format Cells dialog box;
  • Under Number, select Custom.
• Insert the custom format then click OK.

Custom Format:

$#,##0.00″ USD”;[Red]($#,##0.00″ USD”);”Zero”;@

Explanation:

• Positive numbers will be displayed as currency in USD ($).
• Negative numbers will be displayed in red and enclosed in parentheses.
• Zero will be displayed as the word “Zero”.
• The text remains unchanged.

3. Conditional Display Formats

You can customize number formats with conditional rules by using brackets []. This is especially useful when you want to display large or small numbers in different ways.

• Select the cells you want to format.
• Right-click and select Format Cells.
• From Format Cells dialog box;
  • Under Number, select Custom.
• Insert the custom format then click OK.

Custom Format:

[>1000000]#,##0.0,, “M”;[>1000]#,##0.0, “K”;0

Explanation:

• If the numbers are over 1,000,000 are displayed in millions with one decimal (M).
• If the numbers between 1,000 and 999,999 are displayed in thousands with one decimal (K).
• If the numbers less than 1,000 are displayed as they are.

4. Apply Special Indicators

You can apply symbols for special indicators by using custom number formatting. You can add symbols like arrows, percentage signs, or any custom characters that help visualize data better.

• Select the cells you want to format.
• Right-click and select Format Cells.
• From Format Cells dialog box;
  • Under Number, select Custom.
• Insert the custom format then click OK.

Custom Format:

[Green][>0]▲ 0;[Red][<0]▼ 0;0

Explanation:

• Positive numbers are displayed in green with an up arrow (▲).
• Negative numbers are displayed in red with a down arrow (▼).
• Zero is displayed as it is.

5. Combining All Rules to Show Complex Custom Formatting

You can combine all the rules into a single custom number format to handle complex cases.

• Select the cells you want to format.
• Right-click and select Format Cells.
• From Format Cells dialog box;
  • Under Number, select Custom.
• Insert the custom format then click OK.

Custom Format:

[Green]0.00%” Growth”;[Red]-0.00%” Loss”;”Break Even”

Explanation:

• [Green]0.00%” Growth”: Displays positive percentages green with two decimal places and adds “Growth”.
• [Red]-0.00%” Loss”: Formats negative percentages red with two decimal places and adds “Loss”.
• “Break Even”: Replaces zero with the string “Break-Even”.

Conclusion

Custom number formatting is a useful and powerful tool that can significantly enhance the readability and presentation of your data in Excel. Complex display rules enable you to tailor the appearance of numbers to suit specific needs, improve clarity, and make data easier to interpret. By understanding the basic structure and experimenting with different format codes, you can create dynamic and complex display rules tailored to your specific needs.