Conditional Formatting: Formula Based w. COUNTIFS() - Highlighting Inputs if From Restricted List

Advanced Excel Crash Course Section 13: Conditional Formatting
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hi there. In this video of conditional formatting, formula based conditional formatting, let's take one case study, a case study, which I recently came across when a client asked me this question. He asked me that look, they have a restricted list of ID codes. And if one of the staff members enter any ID code, which happens to belong to the restricted ID code, it should get colored automatically. So that they know that they are entering an ID which is already restricted or in the blacklist. Now, it looks something like this.

In this case, 19 52,013 both of them are in the restricted Id also 3000. So if I write 3000 colors, see thousand 10 no color, because of course that is not there in the restricted ID list. Now to be able to achieve this, you will require two things one is count if formula and conditional formatting. Let me quickly walk you through the count. If formula In case you have not been using in recent times, what count is does is when you write count, if it asks you primarily two things, one is the criteria range that is, in which range, which I'm going to fix at this moment, you are looking for a certain criteria. In this case, it is talking about this 1950.

So, primarily 1950 is being searched in this list and if it is present, it will tell you how many times does it appear. So, this logic I'm going to put in the formula based conditional formatting and find out if there is any value whose count is more than zero, which indicates it's going to be there in the restricted list. So now I choose the entire area where I'm supposed to have that condition filled in. I go to Home tab, I click on conditional formatting. Next, I go to new rule. And towards the last option, which allows me to put a formula in the conditional formatting, I will go to that section.

So use the formula to determine which cells to format. And that's where I write the formula whose answer is going to be either true or false. Now notice my selection of the cells had begun from two seconds. Hence, I'll be careful when writing the formula I say equal to count ifs. And the worst part is you don't get any hints, when you open up the parenthesis or brackets, you have to remember what was the next parameter. So counters, it asks you criteria range.

So I say, let me choose the industry in which restricted list exist. Already It is been locked, which is good for us that we put a comma and I will choose only one cell that is my criteria, not the cell above, not the cell below, not the entire range, that one cell from where my selection had begun. And also, before you go ahead, make sure that this is locked in such a manner that you are Allow conditional formatting to move down but not sideways, which means dollar D, I keep on pressing f4 until I get dollar D to close the bracket. But notice this is only going to give you an answer in digits 01234 and so on so forth. I don't want that I want an answer which should exhibit true or false. So I asked is discounted answer whatever that is greater than zero which means is it there in restricted idealist?

Because only then it will give you an answer more than zero. So if it is so, in which case I will get an answer to and because of which this condition formatting gets activated. I go to Format and decide on my color. So that there you go, a light blue color will do Okay, okay. Nothing gets applied, I write 1950. And proof of the pudding is in the eating there you go enter and for instance, I apply a number which is not there.

In the list, it doesn't get colored, but that does affect the client had wanted. And you can use the same technique to give your condition based on counties formula

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.