Custom Number Formatting: Apply Logical Conditions, Color Index and Symbols

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to apply Logical Condition, Color Index and Symbols for custom formatting. Let’s get started

In the caption below, we have sales data generated by each of the Account Managers. We can see that some of the Account Managers generated Sales running to millions while some are in thousands.

What we want to essentially do is to apply Custom Number Formatting combined with logical conditions and the inserted up and down arrows symbols in cell E3 to display the values of:

  1. All the Account Managers whose Sales is above or equal to 1,000,000 using m for millions; red and upward arrow font formatting with 2 decimals
  2. All the Account Managers whose Sales is below 1,000,000 using k for thousands; cyan and downward arrow font formatting with 0 decimals

We are going to use the data in column C as our output

The first thing we are to do is copy the upward and downward arrows

Select all the data in column C and execute CTRL + 1

Click on Custom and clear the General

Execute the syntax: [Red][>=1000000]₦#,##0.00,,”M”↑;[Cyan]][<1000000]₦#,##0,”K”↓;0

[Red] – the red in square brackets is the color we want to apply

[>=1000000] – represent the first logical condition

₦#,##0.00,,”M”↑ – Naira currency symbol, 2 decimals, million and the upward arrow symbol

[Cyan] – cyan color

[<1000000] – second logical condition

₦#,##0,”K”↓;0 – Naira currency symbol, 0 decimals, million and the downward arrow symbol

Click OK

From the caption above we can see that all the Sales values that is greater than and equal to 1,000,000 are formatted with the specified custom formatting; and vice versa

In the order way round, we have option to deploy any of the 56 Color Index as we can see in the caption below

Let’s change the Cyan to Color29 in the Custom Formatting Syntax and see how it will display

Click OK. From the caption below, we can see that the color index has taken effect on the numbers.







Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *