Format cells with Conditions

In the post, we are going to see how to format the cell value with conditions. Excel formats the cell values based on the result of the condition that we specify in the format string.

Before I walk through the format cells with conditions concept, let us see what comparison Operator is, as the comparison operator plays a vital role in custom formats.

Table A: Comparison Operators

Operator	That means	How to use in logical tests? (assume A1=10; B1=15)	Result: Logical test can return only a true or false answer
=	Equals	=(A1=B1)	False
<	Less than	=(A1<B1)	True
<=	Less than or equal to	=(A1<=B1)	True
>	Greater than	=(A1>B1)	False
>=	Greater than or  equal to	=(A1>=B1)	False
<>	Not equal to	=(A1<>B1)	True

Note: we use the comparison operator in IF formula; based on the logical test result, we show the value as either “TRUE” or “FALSE”. 

How to use comparison operator in Custom Cell Format?

The condition consists of a comparison operator and a value. Both should be specified in Square Brackets.

Example A:

[Red][>=100];[Blue][>100]

In this example, the format displays numbers that are less than or equal to 100 in Red and numbers that are greater than 100 in Blue.

Note: You can use any color that is specified in excel color palette (refer post: custom format –play with colors)

Formatting Number with Conditions:

Assume that we have some core operational income/expenses data and the same should be displayed in millions/thousands (with two digits rounding off) in the dashboard or in the top sheet of the MIS.

Example entries and how it should be displayed in the dashboard are given in Table B. Just have a look and try whether it can be possible using custom format to display the values in the cells as specified in Table B (column 2).

Table B: Example data to create custom formats with conditions

If the value entered in a cell is	It should be displayed as
1500000	1.50 M
1000000	1.00 M
1500	1.50 K
1000	1.00 K
500	0.50 K
0	-
-500	(0.50) K
-1000	(1.00) K
-1500	(1.50) K
-1000000	(1.00) M
-1500000	(1.50) M

I.e. if the value entered in a cell is greater than or equal to 10 lakhs, we need to convert the value in million with two digits rounding off and should add M as suffix; and if it is less than 10 lakhs and greater than 0 we need to convert the value in thousands and add K as suffix.

The conditions and the formats are given below:

Table C: Conditions and format Table

Conditions	Format
>=1000000	0.00,, "M"
>0	0.00, "K"
0	"-"

We can form the custom format string as given below:

[>=1000000]0.00,, “M”;[>0]0.00, “K”;“-”

As specified in Example A,

1. First section of the format string (i.e. [>=1000000]0,00,, “M”) displays the numbers in millions with M as suffix to indicate Million, if entry in a cell is greater than or equal to 10 Lakhs
2. Second section of the string displays the numbers in thousands with K as suffix to indicate thousand, if it is greater than Zero and less than 10 lakhs
3. Third Section of the string displays zeros as -

Shall we apply this format and test the result?

Enter values in column A & B as given in Picture A and apply format style in Column B.

1. How to apply the custom format in a cell?
2. Press Ctrl + Shift + F
3. Select Number tab
4. Click Custom Category
5. Enter the format string in Type box
6. Click OK

For Pictorial presentation, refer the steps specified in post – Custom Cell Formats (Numbers)

Picture A

Alignment:

Did you notice that the decimals are not properly aligned? See the picture given below (Picture B), you can notice the alignment.

Picture B

How to fix this?

Before fixing this issue, let us understand the reason why the numbers are not properly aligned. It is because of character space. We have used K and M as suffix to the display the values in thousands and millions. The character space of K is not equal to the space of M and vice versa. Hence, we are facing this alignment issue.

We need to add

1. _K in the first sections in between 0.00,, and “M”;
2. _M in the second section in between 0.00, and “K”; and
3. _M_K in the third section after “-”

Note: for better understanding that how to add character space in custom formats, refer post - Post Custom Number format – How to display a negative sign on the right side of the number?)

Once you add all these characters in the format string, it will look like as given below:

[>=1000000]0.00,,_K "M";[>0]0.00,_M "K";"-"_M_K

Apply this format in column B and see the final result. The decimals will be properly aligned.

Final Result is given in Picture C

Picture C

Where can we use these values? (Either in millions/thousands)

• To present the values in
• Dashboards
• MIS Top Sheet
• Executive Summery sheet (in financial presentations)
• Charts data

For better presentation of financial reports, we never show the value or amounts both in Millions and in thousands. To explain the custom format with conditions, I used this example. But in reality, we should use either amounts in thousands or in millions.