Excel Conditional Formating - Create your own rules - Complex Examples
Вставка
- Опубліковано 13 жов 2024
- Check my blog here: www.techtutoria...
This is a tutorial about excel Conditional Formatting. In this tutorial I am going to show how to use your own formula to define Conditional Formatting. After watching this tutorial you should be able to apply special formatting to a single cell or to a range of cells based on condition/ values of other cells. Also you should be able to copy Conditional Formatting from one cell to others.
Music courtesy of Kevin MacLeod (www.incompetech...)
Saved me writing a macro, thanks! FYI, I made three rules and applied them all to the same cells. This way, if one column contains a Red, Amber, or Green, I am able to format a different cell with that color.
Thank you - you helped me solve a problem you weren't even showing. I saw you take the $'s out from before the cell numbers in the formula, and this triggered an idea to ADD those to my own formula for my own problem. It gave me the solution I needed.
you have explained it very nicely, now I can use it in my excel and thank you for clearing my doubts
That tip about format painter helped me so much thank you
Excellent !! Thank u 4 sharing.
I have a question: I have a range of cells which contain sales values and each cell has a unique value. What formula must i use to color-format MAX and MIN values at one go.
Thank you very much.
I don't know what to refer at this moment but see below if it helps.
Ex, having these values in cells A1 = I, A2 = AM, A3 = THE, A4 = BEST. A formula on cell A5 using ampersand/&/AND can be used to add them together. This will look like
=A1&" "&A2&" "&A3&" "A4
and this will show as "I AM THE BEST" in cell A5.
Oh I *_must_* get to take a proper look at this when I get time!
Good video you are clear my dought
Thank you. very clear and informative
I work at a college where I provide an Excel report to collect data from campus departments. Sometimes my colleauges leave cells blank that requires data. I want to format all of my reports to where the cells are light red and they turn light green when text/number is added to the cell. I believe this will help elimate receiving incomplete reports. Do you have a video that can walk me through that?
Thank you for the lecture. Please i have sets of certificates from different personnel that will be expiring in two years time. I'd like to have a prompt on their expiration on excel. I know its conditional formatting but don't know how to go about it. Please help?
I am hoping you can help me with creating a conditional formatting formula that will automatically advance to either the next column or cell to obtain the information that will in turn highlight a cell.
Thanks for this useful tutorial video. I have date that I want to apply the formatting conditions on it but I couldn’t. I want to change the whole row based on a dropdown list “letters”. Any tips?
Thanks a lot . after watching your video i get more knowledge of conditional formating
Sir, your presentation is very excellent to understand easily.
One request....suppose there are 5 subjects in a class. Condition of pass in the exam. is....Pass Mark in each subject is 40 and aggregate pass mark is 50. So how can I formate and how will be highlighted this particular row who passed?
Hello,
I would like for you to tell me how to format a new rule? I'm using to four cells, they're name nominal, lower, upper value and actual value. When I put in actual value in the actual value cell, I would like the actual cell to turn red if the value is outside of the lower and upper value? Can you help me out, please?
Thanks
THANK U ITS USEFUL FOR ME...
I want to be able to toggle highlighting of rows as values in a column change. In other words, highlight a group where column A has duplicates, then turn off highlighting for the next duplicate group, then back on for the next. Is that possible?
Thank you so much for your knowledge.
Hi. I have 4 cells with percentages in them. I want for format the first cell only, if the value of that cell is greater/lesser than the value in the other cells. Blue if it is greater, and yellow if it is lesser. This should only be done if the first cell is greater/lesser than all the other 3 cells, not if it is greater/lesser than one or two other cells only. And then I will need to copy this conditional formatting to other cells in the same column.Please help.
I have a question and please bear with me here. Imagine we have a sheet with people, and I am ordering something for those people. And I am marking these people as "done" in column E and type the date, of when this is Done in column F (always todays date), can I make it so, that when I mark the person as "done", F fills itself autamatically with the current date in the calendar??
hello i love this vid really clear but im stock a format painter. im doing everything as he does but after i click the 4th row the (big + along with the brush) disappears :(
This was very helpful - thank you!
please advise for my problem , between two different prices ,the difference is 1, 2 or3 range should say same , so If you have any video
I need to color few cells green or red depending upon criteria. then I Need to link those coloured cells from one excel sheet to another excel sheet. Conventional linking techniques using "is equal to'" sign and clicking on that cells links the data but does not link colour associated with it. Help me in linking data automatically with colour retaining.
Can we use a phrase rather then an average or number to this rule? If so, how?
what is the conditional format formula for when LTP(last traded price) changes nearest values to be highlighted in resistance values r3, r2, r1, and support values s1,s2 &s3
Hi Sir, can you make video formatting the succeeding cells by date start 7 date finish
Thanks.. It is really helpful!
very nice
EXCELLENT. JUST HELPED ME A GREAT DEAL.
Can you suggest a rule that would indicate whether a cell has greater than or less than a specific number of digits? Mainly, if the number of digits required is 5, including a dash and the correct amount of numbers is not entered.
Awesome Teaching
Hello I want to know how to adjust a column in excel to highlight any text value other than numeric value in any color
My friend was trying to teach me this last night I got a headache watching this video I now get it, Thank you.
I have to do this on a huge spread sheet, 8 conditions for each one the thing is I am a total noob to excel is there a way I can write the conditions on to one row and then copy that row to the whole spreadsheet?
any help will be appreciated
Thanks for checking my video. You can select all cells where you want to apply conditional formatting and then work on it to apply a rule for it. Sometime it depends upon how complex is your rule is. Anyway try to select all cells together and see if you can build one based on your requirement. Let me know if you need further clarification.
I googled my self and found this comment forgot I even posted it. Thanks for the help I don't even remember what project that was for.
I really relly thankful to u because of your clear and steady voice, ca you please share asome of the important rules to start the Excel.
Can you Explain how to do if i Want my cells to chance collor
If it is -0,1 to 0,1 = green
If it is -0,2 to -0,1 or 0,1 to 0,2 = yellow
If it is -0,9 to -0,2 or 0,2 to 0,9 =red
WHAT do i do when the next cells is
500,35
1000,12
1499,8
2000.05
An so on for the next cells op to 52000
Thanks sir.. you really help me a lot!!😊
can you please tell me how you manage to keep the grid lines visible even when the cells are filled?
when values of one column in excel exactly equals to values of another column
how to conditional formatt?
thanks
hoping prompt reply
How would you highlight cells that were greater than another cell. For example if the question was highlight the cells of student a that are larger than student b?
ua-cam.com/video/wgRG5pdF3W4/v-deo.html
Open conditional formatting->any rule->custom rule- Cell A go.
For more queries you can visit our website www.nurturetechacademy.in
hi my problem is I want to apply a colour to the number in a long row of cells if the number in a cell is greater than originally applied to that cell so if I have 3 cells with 10 20 30 in a row of 3 cells ,if any increase above 10 20 or 30 the new number to appear in another cell (next to it)in red
You can select your cell and go "Conditional Formatting" on top and then choose "highlight cell rules" and then "greater than". Specify the number as 10, 20 or 30 based on your need. Then change value of the cell to a great number and see how that works. if you want to change the highlight color, edit the rule.
quotation mark ("" / '') used to indicate a text in a formula. When you write some text directly on a cell, you don't need it. Like a formula is
=IF(A1="I","error","good") the result will be "error" in A5
Now parentheses in excel are used for formulas and follows simple mathematical rules.
Sir, ஒரு cell-ல் ஒரு மதிப்பு கொடுத்தால் மறு cell- ல் நான் நினைக்கின்ற மதிப்பு வர எப்படி formula உன்டாக்குவது .அதாவது ஒரு cell ல் 10 போட்டால் மறு cell ல் 17.00 என்றும் 11-19 வரை போட்டால் 34.00 என்றும் 20-31 என்றும் போட்டால் 68.00 வர வேண்டும்.
thank you plenty for your effort.
Excellent work. Its really helpful for me.
very nice example
Thank you... i got it right!
guidance needed?? take example of two cell a2 and b2 a2 contains digit 23 and b2 contains25. what i want to do is add both numbers only if a2 is equal to 23 and b2 is equal to 26. if any of these cells have values less than the condition then only that value be shown in new cell d2 which has the condition value. if both cells do not meet the required value than d2 should show zero
Wow, this video is great.
It's a good one. Thank you.
your technique is very critical, please be simple otherwise fresher students unable to catch up your lessons
If one alphabet is repeated continue 9 time 10th one it's should show warning or indication .
Example in A1 cell to A9 cell "P" is repeated if I tried to write is A10 cell P meas it's should show a warning or indication .
Alt+D+L->any value-> custom-> =Countif($A:$A,A)
thank you. very helpful
Excellent sir
my formatting rule does not look like yours its small on a mac! i cant type
Thank you! helped me alot :)
your vid is very useful, thank you so much
Do you know how to conditionally format a cell based on the formula in the cell? For example, let's say that I use a formula to compute the value of a given cell. Well, I am trying to do a conditional format based on a string of text that is included in the formula that was used to create the value. (e.g., =if(c3="red apple, "eat apple). I want to conditionally format a cell if the string "red apple" is within the formula. Note, I am not trying to conditionally format the result of the cell; I'm trying to conditionally format based on what's within the formula of the cell.
Read more
REPLY
Lee Garrett
Really Helpful!!!
Thank you.
Thanks but I am really sorry video is not at all clear. when you type formulas in conditional formatting they are so tiny too see. Pls zoom and then upload
useful video
GOOD VIDEO!
The rules used in the illustration are far from complex contrary to the subject heading unless the audience are first time excel users
nice vishnu
Nice
good one!!
Thanx a lot ....I got it
can I have your whatsapp
Thank you
tks!!!
The video is not at all clear though the content is very much useful :(
Watch This One ua-cam.com/video/iPbwr-Zu6ak/v-deo.html
video is good but take a new version of execel
Good
But you slowly skeepking