Conditional Formatting: Formula Based (Colored Rows Based in User Input)

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, we see one of the features of conditional formatting, where we are able to color the cell or the entire rows based on a certain user input. And let me give an example. In this case, the division names I'm putting there in the yellow cell, if I write h, f d, notice what is going to happen as I press Enter or click outside all the rules within the data, which is a part of the HFT division is getting colored. Now, this could be very useful for your EMI support when you have the control over the look and feel of the report. Now, we see how to make that happen. I will show you a way which is slightly easy, but will not cover the entire group of cells for the same row, only one cell instead.

On the other hand, if you go one step ahead, using formula based condition formatting the entire who will also be covered. So quickly, let me put my cursor in the exercise sheet one Each cell which contains any of the cylinder is my, I press Shift control, right, it quickly chooses the last cell, Shift Ctrl down. And since I want to go to the top of the data to see the header, I would want to press Ctrl backspace key. Now this is not compulsory, this is only for your convenience. Once I've chosen the data in which you want to apply the color, I will go to conditional formatting, go to new rule, within which let me target the last option which says use a formula to determine which cells to format. And the best part of this option is you can write your custom formula, you can write your own customized formula in this box, the only condition is the output of that formula should be in true or false.

So if it happens to be true, only then that particular format which you have decided will be applied. So now let's see how to build this formula which will enable me to color the entire rows based on the predefined user input. Write a formula equal to. And then I choose that one cell, one cell from the data itself. I want to compare this cell, in fact, all the cells below it with the user input. Now imagine if I were to compare this, I would want this to move, go down, go down, go down, and so on so forth for the individual comparison with the user input.

And when does that happen? Certainly not when I'm applying dollar c dollar eight, because that indicates it's fixing and locking the entire range. So I'm going to press f4 f4 once again, such that I get an indicator of dollar c eight, I would want that a to go to nine through nine to 10 through and at the same time, column C should hold to its current position and not go to column D. So is dollar c eight equal to equal to the cell which contains the user input? Now you would have guessed correctly, yes, I am not going to remove the dollar from This l dollar B dollar three Why? Because come what may all the user input that is there for comparison must be done from this particular cell. So be very careful, make sure it is dollar c eight, and why eight because my selection has started from a through.

In fact, I've seen people, they press Ctrl A, which also selects the seventh row, but by writing the formula, they put eight. Now, that is inconsistent. So make sure from wherever whichever row you are choosing, it should be consistent with the formula. In this case, since my selection is from the A through it says dollar c eight. Now, let me quickly apply the fill color or the format. Go to fill option, apply your favorite color, okay.

And as I press OK, notice immediately our ad has been colored. In fact, just to test whether my approach has been correct Let me press h f d and duck. Hmm, perfect. So this is how you can apply a basic formula in the condition formatting, be careful about the dollars and that will all lead you to a very wonderful EMI support. We'll see more of these tricks in the next few videos.

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.