In the previous post (Format Cells with Conditions) we
formed a format string that displays value in thousands or millions with two
digits rounding off along with K or M as suffix to indicate thousand/million.
The format string that we formed is given below:
[>=1000000]0.00,,_K "M";[>0]0.00,_M
"K";"-"_M_K
This string has 3 sections;
- First section displays the value in Millions if the value entered in a cell is greater than or equal to 10 Lakhs.
- Second Section displays the value in thousands, if the value entered in a cell is in between 0 and 10 lakhs.
- Third Section displays Zero as –.
Negative Entries:
Do you have a doubt that how the negative entry will be
displayed? Apply the above format in a cell and try with any negative entry. Are
you getting two hyphens as result? (i.e. --)
As per our earlier discussion in the post – Post Name, the
first section of the format string is for positive values, second section is
for negative Values, third section is for zeros and the fourth section is for
text (refer Picture A – General custom format string)
 |
| Picture A - General Custom Format String |
But when you use ‘conditions’ in custom format string, excel
will not interpret the sections as given in Picture A. In other words, if a
format string has conditions, excel will not apply first section to the
positive values, second section to negative values, third section to zeros and
fourth section to text.
Excel simply ignores general formatting rules and applies
the formats based on the conditions given in the format string. Hence, when you
enter a negative entry in a cell with the above format style, it displays two
hyphens, i.e. one hyphen is the mines symbol that you have entered and the
other is an indicator of zeros specified in third section of the format string.
In other words, the negative entry will not satisfy the first two sections
condition so, excel will chose the third section and apply the format style.
Solution:
We cannot handle this with a SINGLE custom format string and
unfortunately, in a cell, we cannot apply MORE THAN ONE custom format.
Why we cannot handle this with a single custom format
string? All four conditions are given below (Table A)
Table A:
Conditions and Formats
Conditions
|
Formats
|
Result
|
>=1000000
|
0.00,,
"M"
|
1.00 M
|
>0
|
0.00, “K”
|
1.00 K
|
<0
|
(0.00,) “K”
|
(1.00) K
|
<=-1000000
|
(0.00,) “M”
|
(1.00) M
|
All these four conditions cannot be placed in a single
custom cell format string. Maximum, we can place only two conditions in a
string. Hence, we have to break this as TWO Custom Cell format string. They are
given below.
0.00, “K”;(0.00,) “K”; “-”
0.00,, “M”;(0.00,,) “M”; “-”
The former is for displaying the values in thousands and later is for
displaying the values in millions.
Conditional Formatting
We cannot apply this
two format style in a single cell; hence, we need to do some work around. Using
conditional format feature of excel, we can solve this issue.
Conditional formatting
works based on rules. In this situation, we need to define 4 rules and need to
set the corresponding formatting style; they are given below (Table B)
Table B: Rules and
formats
Conditions
|
Formats
|
Cell value <=-1000000
|
0.00,,
“M”;(0.00,,) “M”; “-”
|
Cell Value >=-1000000
|
0.00,,
“M”;(0.00,,) “M”; “-”
|
Cell Value between -999999 and 0
|
0.00,
“K”;(0.00,) “K”; “-”
|
Cell Value between 0 and 999999
|
0.00,
“K”;(0.00,) “K”; “-”
|
Steps to apply the
conditional formatting
Step 1:
On the Home
tab, in the Style group, click the arrow next to the Conditional
Formatting, and click New Rules. (Shortcut: Alt + H + L + N for Excel2007/2010)
Step 2:
In New Formatting
Rules dialog box, select Format only cells that contain rule type (i.e.
second rule type in Select a Rule type panel) and set the rule description
(refer Picture B)
 |
| Picture B - New Formatting Rule |
Note:
Format only
cells that contain:
Use this rule type to create rules that format cells based on mathematical
comparisons (greater than, less than, greater than or equal to, less than or
equal to, equal to, not equal to, between, not between).
Step 3:
Click Format
button in New Formatting Rule dialog box once you define the rule description.
Step 4:
Select the Format
String that you defined under the Custom Format Category and click OK
(Refer Picture C)
 |
| Picture C - Apply Custom Format Style |
Step 5:
Click OK in New Formatting Rule Dialog Box
The same way create all the rules and apply them to the cells.
To conclude, we can now enter negative entries also in the
cells. Based on the rules description, excel will display the values in the
cells.
