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:
- 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
- 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.
