An outstanding tutorial! Far too often I find that contributors here on You Tube leave out small parts of a particular process, leaving users frustrated and aggravated. Everything here was clear, concise and to the point. I was able to learn this process without even having to watch the video a second time. Well done!
This is a brilliant tutorial. Short, simple and straight to the point. Instructions were excellent pitched exactly at the right level for a non proficient excel user. Thank you Chris
Good job Chris! You took something and broke it down in a systematic, concise, and discernable manner. It is one thing to teach someone, but it is all together a higher level when you are able to achieve this with brevity! Well done.
Thanks for the clear explanation. Watched a dozen of these and yours was the first I came across that took the time to point out the Dollar sign needs to be removed from before the row number.
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it please?
Awesome, thanks. I wanted to do this and was searching online; the steps in the tutorials were not clear until I stumbled on this one... Thanks a lot; Keep up the good work
I'm struggling to find the answer to this so hopefully you are still active. I want to conditional format based on a cell but that cell is a calculated value (example =e3+1). Is this possible? I can conditional format if there is no formula but as soon as I go to format based on a calculated cell it refuses.
thanks. i saw a few videos but no one mentioned how to copy formatting with reference to a respective cell. it was always referring to a common single cell.
Nice Video Sir. I have a question Sir, how to use conditional formatting on Month.. when i want only the month of January, April and October? can u teach me the formula? thanks
Thanks for the great videos, but I have a question. if I have have a cell with a number and I want to make sure that this number highlights if it ever goes above more than 10% of another cell, is it possible to use conditional formatting for this.
Hello Chris, I am great fan of yours and learned lot of excel formulas by seeing your vedios. Now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
Thank you. This is great. I have a similar problem like this one with a twist! See if you can come up with a solution, please! I want to make the fill color of a cell in a row based on the comparison of the value of the current cell and the cell to its left. If the current cell value is larger than the value of the cell to the left, then I want to fill it with green, if it is equal, I want it to be filled grey, and if less, I want it to be filler yellow. Is that possible?
Hey, tnx for sharing a great video. I've got questions though. How are you gonna modify your formula if you wanna highlight a cell from an array of lookup cells based on the value of another cell, e.g. you have a list of employee IDs in an array and you are using say, Index function, to lookup for an employee's ID, and whatever lookup value you have it will highlight the ID in the lookup array? tnx!
can i apply two of the same formula on one cell? like if it says value "xyz" then it turns the other cell green otherwise red? please help me with this
how do you format based on another cells value which is a range for example (=$B$13=Source!$H$127:$H$150) if i am only worried about one value then this works (=$B$13=Source!$H$127) but what if any value on b13 is between H127:H150
In my case I want to show a red arrow next to the value indicating that the amount has increased or a green arrow indicating that the value has decreased? Any way to create that rule?
I am stuck with a case where I need to compare two cells and change formatting based on the value in the other cell, I don't have a fixed number like greater than 60 or less than 60. For each cell the value will compare with the value in the other cell and of it is greater or less than that value the formatting should change. Can anyone help or give reference to a video tutorial?
Hi!! Is it possible to change the color of another cell based on the color of original cell? its like copying the data of another cell but this time just the color itself. please help me. TIA
what if you want to do it actually based on another cells value and not based off a flat value like 60? like $E2 > $B2 --- should it it be comparing to E2 > B3 in the next row and so on?
I have a formula (monthly budget) in one cell and I want a format that says if the actual is less than the budget highlight the cell. Not sure what I'm doing wrong but when I do the formating if the value is equal it highlights as if it is as less than rather than not highlighting the cell What am i doing wrong ??
Hello Chris. Could you please help me? I work in the collections dept of a business. We have excel sheets, which cathegorize customers according to the time it has passed since they bought our equipment (0-30; 31-60; 61-90; 91-120 and 121+) I'd like to know how to format each column at the time in order to have the names of the customers highlighted according to the amount they owe us? Thanks for your help Chris!
Hi Luis, you can use the same technique shown in this video, only in your case you will have to put in 5 rules - one for each category. Your first rule should be =cell#>120 and select the formatting of your choice, the second rule will then be =cell#>90 and again you select the formatting you want for customer in the category 91-120; and so on until the fifth rule =cell#>0. Note that Excel applies the rules in the order you enter them, so be sure to enter a rule for the highest bracket first and walk your way back to 0. Hope this helps.
How do I change a value in one column, based on another column value? For instance, if my column labeled "Debit or Credit" value is "Credit", I want to change the value on that row in the "Transaction" column to a negative value. Right now all "Amounts are listed as positive whether they are a debit or a credit. Many thanks
how can I increase value in a particular cell by 10%. Eg. Suppose i enter the value - 10 in a cell, the value that i enter shud automatically increase by 10% in the same field. Is tht possible?
I have one column that i have a formula on which is .7* value on the same column i have a conditional format for anything .7>value turns red but that same column i need to stay with no color .7>-value from a different column does this make sense i could use some help
Why is A17 turning red when its value in E17 is lower than 60? I don't get it. Same with A4. Why are they red when their values are lower than 60. Isn't the formula based on >60?? Pls explain.
How A4 is highlighted in Red colour if it’s having Value “14” in E4 which is less than 60…? Also E17 is less than 60, but still A17 is highlighted in Red colour?
DO YOU HAVE A WAY TO PROTECT THE FORMATTING AND CONDITIONAL FORMATTING IN A CELL THAT YOU MEAN TO LEAVE EDITABLE IN A PROTECTED SHEET? SOME OF THE PEOPLE USING MY SHEET MAKE MISTAKES ENTERING THEIR DATA. WHEN THEY DELETE THEIR ERROR THEY ALSO DELETE THE FORMATTING
thank you, but i still have problem. yours formula is wether a cell is greater/lesser than a constanta (in this vid is 60), but what if i want the formula to be for instance (=$E2
Is there any way you can make a video showing how to have a drop down menu and once you select 1 value in that menu it will auto fill 2 other cells with information. But in saying that the other 2 cells with be filled with different information.
have a question, I want to create a rule if value in cell A1 is >40 than move extra value to cell B1, e.g if I have value 42 than 40 stays in A1 and 2 goes to B1.. help please
Chris, I sent you a private message. Really need you help with my worksheet. Cant figure out how to change the text in a cell based on the date in two other cells. In other words, if a cell reaches a certain date then the text in a different cell will change along with the color of the row. Please help
How do you format if a cell "contains" text In the example below I would like Column C to formatted yellow if the word "partly" is in the Column E E4 Work partly completed - Because of fault E7 Work partly completed - Because no materials
I am In the oilfield and have drop down list of casing sizes. I need 5.5" casing with another cell of 20lb weight to display the factor of 0.022 In a third cell when it's selected. I tried to follow this video and use equal instead greater sign. I can't make this work for the life of me. Can anyone help me here?
Sir, A B C 20 40 30 (10) 50 [60 20 30 20 50 10 10 60 10 80 30 (90) 50] 20 20 70 From column A min value 10 to column b max value 90, how can I add column c all cells from min value to max value at column A to column B.
I want highlight duplicate rows with out adding helper column plz help me. To make things clear let me elobrate my self In cloumn A row 2 i have some thing written there and in column D row 2 i have some other thing written there and i want to highlight row if this pair entry is repeated below somewhere down the sheet. If there is any way with out concatenation and adding helper coulmn then plzzz make video.
Thank you so much for releasing such a great work and that is exactly what i'm dying to look for to solve my homework. And beside learning excel tips, i get to practice listening skill as well.
hi sir this is shashikala i have data in excel with number of names & batch number this work need to allocate to multiple users on every transaction with the use of VBA Please help me
i want to know is there way that i can prepare a excel sheet , such that when finally when i click on the button placed end of the cell(say z) depending upon the values of (A-y) there is a output generated .for more reffernce see below A B C D E F Z cat dog three four pot hot Output(button for click) after clicking on button the output should give me cat Dog three
Hi Chris,Have a question. So in one cell (A1) I have a number which can vary and in another cell I want to use a formula that can also vary depending on the number in A1 and the result of that 2nd number multiplied by a dollar amount reflected in another cell. Example B2 is a percentage of A1 and if A1 is 100 to 200 I want B2 to be 15% of A1, then in C2 that % number from B2 x $100. So if A1 is 100 then B2 would be 15 and C2 would be $1500. First part I'm stuck at is the range 100 to 200 in A1.Here's where it gets even trickier. If A1 is 201 to 300, then the same formula with a lower percentage. So let's say A1 is 250, then I want 12% of A1 in B2 and that number x $100 in C2. If A1 is 301 to 400 then the % of A1 is 10% x $100. So the % drops with a higher number in A1. Can you provide any help, say a formula?
Hey Chris, could you do a video (if this is possible) on how to change on cell's background/color based on the value of a different cell? For example, a column of values are conditionally formatted to show as a 3 color blend of red,yellow,green based on the value, however, you want that color to carry over to a different cell without the value in that different cell being changed. This is possible?
So what if you have an Icon set that you want to highlight in red or green lights, and the cells you want to highlight are dependent on the value of another cell, but each cell changes. Basically, I want to compare one set of cells to another, and if they show positive vs. then highlight them with a green light, and if negative vs. the other cells show them as a red light: account X account y TOTAL HEALTH & BEAUTY (6.4%) (6.1%) TOTAL BEAUTY (6.1%) (7.9%) TOTAL HEALTH (5.5%) (1.0%)
Thank you! You're the first person to explain this in a clear, straight-to-the-point way!
An outstanding tutorial! Far too often I find that contributors here on You Tube leave out small parts of a particular process, leaving users frustrated and aggravated.
Everything here was clear, concise and to the point. I was able to learn this process without even having to watch the video a second time. Well done!
This is a brilliant tutorial. Short, simple and straight to the point. Instructions were excellent pitched exactly at the right level for a non proficient excel user. Thank you Chris
Thank you so much! After countless videos, I came across yours and finally figured out how to create this formula!
Good job Chris! You took something and broke it down in a systematic, concise, and discernable manner. It is one thing to teach someone, but it is all together a higher level when you are able to achieve this with brevity! Well done.
Thanks for the clear explanation. Watched a dozen of these and yours was the first I came across that took the time to point out the Dollar sign needs to be removed from before the row number.
Excellent tutorial. I had to adapt it to Excel for Mac, but because of your excellent communication style I was able to find the differences quickly.
Relevant and useful 9 years later! Quick and easy to follow…thank you
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it please?
Thank you for teaching example in a precise and clear way instead of adding unnecessary words
Awesome, thanks. I wanted to do this and was searching online; the steps in the tutorials were not clear until I stumbled on this one... Thanks a lot; Keep up the good work
Precisely what I was looking for. Thanks for making it concise!
You can't imagine how much your video was helpful ,i saved a lot of time , thank you so much.
Thank you. Something so simple that I couldn’t find the answer to until I found your video.
I'm struggling to find the answer to this so hopefully you are still active. I want to conditional format based on a cell but that cell is a calculated value (example =e3+1). Is this possible? I can conditional format if there is no formula but as soon as I go to format based on a calculated cell it refuses.
Under 5minute video and straight to the point keep it up👍
thanks. i saw a few videos but no one mentioned how to copy formatting with reference to a respective cell. it was always referring to a common single cell.
Nice Video Sir. I have a question Sir, how to use conditional formatting on Month.. when i want only the month of January, April and October? can u teach me the formula? thanks
Hi,, great video.. in Row 4, the days outstanding is 14, yet the rule still applies to this row. How is that ?
Thanks. I subscribed just because you were very straightforward and you explained it well.
Thanks for the help, I've been wasting hours formatting hundreds of cells (didn't know about the '$' formatting).
Thanks for the great videos, but I have a question. if I have have a cell with a number and I want to make sure that this number highlights if it ever goes above more than 10% of another cell, is it possible to use conditional formatting for this.
Hello Chris,
I am great fan of yours and learned lot of excel formulas by seeing your vedios.
Now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
Thank you. This is great. I have a similar problem like this one with a twist! See if you can come up with a solution, please! I want to make the fill color of a cell in a row based on the comparison of the value of the current cell and the cell to its left. If the current cell value is larger than the value of the cell to the left, then I want to fill it with green, if it is equal, I want it to be filled grey, and if less, I want it to be filler yellow. Is that possible?
Thank you! After 8 other videos I finally got my answer, appreciate the quick tutorial
Hey, tnx for sharing a great video. I've got questions though. How are you gonna modify your formula if you wanna highlight a cell from an array of lookup cells based on the value of another cell, e.g. you have a list of employee IDs in an array and you are using say, Index function, to lookup for an employee's ID, and whatever lookup value you have it will highlight the ID in the lookup array? tnx!
can i apply two of the same formula on one cell? like if it says value "xyz" then it turns the other cell green otherwise red? please help me with this
Thanks, I would have thought this would be more self explanatory but you did a great job!
how do you format based on another cells value which is a range for example (=$B$13=Source!$H$127:$H$150) if i am only worried about one value then this works (=$B$13=Source!$H$127) but what if any value on b13 is between H127:H150
Hi Chris. Thanks for your info. Can you tell me how I can ignore a zero (0)? I have a format that highlights blanks but 0 is not blank.
Tks.
Can you use the format painter to copy the formatting down a column, if you use a formula to determine the format?
In my case I want to show a red arrow next to the value indicating that the amount has increased or a green arrow indicating that the value has decreased? Any way to create that rule?
I am stuck with a case where I need to compare two cells and change formatting based on the value in the other cell, I don't have a fixed number like greater than 60 or less than 60. For each cell the value will compare with the value in the other cell and of it is greater or less than that value the formatting should change. Can anyone help or give reference to a video tutorial?
Hi!! Is it possible to change the color of another cell based on the color of original cell?
its like copying the data of another cell but this time just the color itself. please help me.
TIA
what if you want to do it actually based on another cells value and not based off a flat value like 60? like $E2 > $B2 --- should it it be comparing to E2 > B3 in the next row and so on?
I have a formula (monthly budget) in one cell and I want a format that says if the actual is less than the budget highlight the cell. Not sure what I'm doing wrong but when I do the formating if the value is equal it highlights as if it is as less than rather than not highlighting the cell What am i doing wrong ??
Hello Chris. Could you please help me? I work in the collections dept of a business. We have excel sheets, which cathegorize customers according to the time it has passed since they bought our equipment (0-30; 31-60; 61-90; 91-120 and 121+) I'd like to know how to format each column at the time in order to have the names of the customers highlighted according to the amount they owe us? Thanks for your help Chris!
Hi Luis, you can use the same technique shown in this video, only in your case you will have to put in 5 rules - one for each category. Your first rule should be =cell#>120 and select the formatting of your choice, the second rule will then be =cell#>90 and again you select the formatting you want for customer in the category 91-120; and so on until the fifth rule =cell#>0. Note that Excel applies the rules in the order you enter them, so be sure to enter a rule for the highest bracket first and walk your way back to 0. Hope this helps.
Thanks for this. Took a while to find a solution, but you have now solved it for me. Cheers!
how to copy that to other cells?.like if I am copying this to down, then that one cell is working for all other cell..hope u understand my problem
I wanted to calculate a tons of number with text on it?
I wanted to either delete the text or just ignore and continue to calculate the number?
Can you help? I have the DATE in one cell and I want the DAY to auto-populate in another cell base on the date.
so simple yet so helpful, keep up the good work! saved me so much headache and frustration, so glad i found this!
If I needs to filer the data basis upon some data validation, how do i perform that, please revert??
Great video, it helped me a lot. I am trying to format another cell range when looking for a specific word and I can't find how to do it
How do I change a value in one column, based on another column value? For instance, if my column labeled "Debit or Credit" value is "Credit", I want to change the value on that row in the "Transaction" column to a negative value. Right now all "Amounts are listed as positive whether they are a debit or a credit.
Many thanks
Why does line number 4 was impacted by the formula? It supposes just the >60 will highlight?
What if you want to see is another cell is lower than the cell you are conditioning?
like =if(c4 < f4) then do something
how can I increase value in a particular cell by 10%. Eg. Suppose i enter the value - 10 in a cell, the value that i enter shud automatically increase by 10% in the same field. Is tht possible?
I have one column that i have a formula on which is .7* value
on the same column i have a conditional format for anything .7>value turns red
but that same column i need to stay with no color .7>-value from a different column does this make sense i could use some help
Thank you for the video, it helped me to finish a template report that needed to insert some conditional formating using formulas.!!
Why is A17 turning red when its value in E17 is lower than 60? I don't get it. Same with A4. Why are they red when their values are lower than 60. Isn't the formula based on >60?? Pls explain.
Wow that was a great video. I was getting so frustrated trying to figure that out and this video just made it so easy. thank you!!
4 hours to find this GREAT video!
What if I want it to be red if greater than 60, blue if equal to 70 and black if greater than 80?
How do i use conditional formatting to link the accumulated numbers of individuals into totals
Is it possible to do a two color scale based on values in another column?
How A4 is highlighted in Red colour if it’s having Value “14” in E4 which is less than 60…?
Also E17 is less than 60, but still A17 is highlighted in Red colour?
Will it carry to new rows added if I only select the current rows in the table?
sir i want to enter ony10 numbers in 12 cells in a row. 11th number could not be entered. please help me
Is it possible to do this with dates instead of numbers?
DO YOU HAVE A WAY TO PROTECT THE FORMATTING AND CONDITIONAL FORMATTING IN A CELL THAT YOU MEAN TO LEAVE EDITABLE IN A PROTECTED SHEET? SOME OF THE PEOPLE USING MY SHEET MAKE MISTAKES ENTERING THEIR DATA.
WHEN THEY DELETE THEIR ERROR THEY ALSO DELETE THE FORMATTING
thank you, but i still have problem. yours formula is wether a cell is greater/lesser than a constanta (in this vid is 60), but what if i want the formula to be for instance (=$E2
nvm, my cell actually contain spaces that i need to trim first haha thats why the system doesnt detect it as a date format. it works well thank you
Cell A4 is red but E4=14 and not >60 ... Looks like there is a problem with the formula and it is not working as expected.
Is there any way you can make a video showing how to have a drop down menu and once you select 1 value in that menu it will auto fill 2 other cells with information. But in saying that the other 2 cells with be filled with different information.
How about indenting text in one cell based on the number in another cell (in the same row?)
have a question, I want to create a rule if value in cell A1 is >40 than move extra value to cell B1, e.g if I have value 42 than 40 stays in A1 and 2 goes to B1.. help please
if you want to assign a variable to an account number with multiple entry in the worksheet, how do you do that?
Thank you, GEEZUS I was struggling.
Exactly what I was looking for! Thanks!
Chris, I sent you a private message. Really need you help with my worksheet. Cant figure out how to change the text in a cell based on the date in two other cells. In other words, if a cell reaches a certain date then the text in a different cell will change along with the color of the row. Please help
How do you format if a cell "contains" text
In the example below I would like Column C to formatted yellow if the word "partly" is in the Column E
E4 Work partly completed - Because of fault
E7 Work partly completed - Because no materials
Thank you so much!!!! you just saved me from an exam!
I am In the oilfield and have drop down list of casing sizes. I need 5.5" casing with another cell of 20lb weight to display the factor of 0.022 In a third cell when it's selected. I tried to follow this video and use equal instead greater sign. I can't make this work for the life of me. Can anyone help me here?
Sir,
A B C
20 40 30
(10) 50 [60
20 30 20
50 10 10
60 10 80
30 (90) 50]
20 20 70
From column A min value 10 to column b max value 90, how can I add column c all cells from min value to max value at column A to column B.
I want highlight duplicate rows with out adding helper column plz help me.
To make things clear let me elobrate my self
In cloumn A row 2 i have some thing written there and in column D row 2 i have some other thing written there and i want to highlight row if this pair entry is repeated below somewhere down the sheet. If there is any way with out concatenation and adding helper coulmn then plzzz make video.
I'm trying this, but excel won't allow me to remove the $ Anchors?
Does anyone know how to make one cell the lesser of the values of two different cells, using conditional formatting or otherwise?
Thank you so much for releasing such a great work and that is exactly what i'm dying to look for to solve my homework. And beside learning excel tips, i get to practice listening skill as well.
hi can anyone help, i need to highlight the top cell if 4 or cells in the same column contain the same text. how do i do this?
hi sir this is shashikala
i have data in excel with number of names & batch number
this work need to allocate to multiple users on every transaction with the use of VBA
Please help me
That was so helpful..thanks man, I have been looking for that solution for so long
i want to know is there way that i can prepare a excel sheet ,
such that when finally when i click on the button placed end of the cell(say z)
depending upon the values of (A-y) there is a output generated .for more reffernce see below
A B C D E F Z
cat dog three four pot hot Output(button for click)
after clicking on button the output should give me
cat
Dog
three
Thanks! The fundamentals here allowed me to expand and go another direction! Appreciate your knowledge!
ATB,
David
Why did you format it as $E2? Shouldnt it be formatted as just E2?
Hi Chris,Have a question. So in one cell (A1) I have a number which can vary and in another cell I want to use a formula that can also vary depending on the number in A1 and the result of that 2nd number multiplied by a dollar amount reflected in another cell. Example B2 is a percentage of A1 and if A1 is 100 to 200 I want B2 to be 15% of A1, then in C2 that % number from B2 x $100. So if A1 is 100 then B2 would be 15 and C2 would be $1500. First part I'm stuck at is the range 100 to 200 in A1.Here's where it gets even trickier. If A1 is 201 to 300, then the same formula with a lower percentage. So let's say A1 is 250, then I want 12% of A1 in B2 and that number x $100 in C2. If A1 is 301 to 400 then the % of A1 is 10% x $100. So the % drops with a higher number in A1. Can you provide any help, say a formula?
Sweet! .. Now, how do you make this happen for a date turining yellow after ten days has passed?
Hi, i need two reference in one cell is it possible e.g. % and Number format in one excel cell is thr any formula or conditional formatting pls help
Just the format rule I was looking for.Thanks
Some " reds" in colummn A , In "E columm"n in less than < 60 , not > 60 , like you defined "?
Hey Chris, could you do a video (if this is possible) on how to change on cell's background/color based on the value of a different cell? For example, a column of values are conditionally formatted to show as a 3 color blend of red,yellow,green based on the value, however, you want that color to carry over to a different cell without the value in that different cell being changed. This is possible?
Thank you! Really helpful for formatting a spreadsheet I'm using for students' results during the lockdown!
Awesome... clear, concise, just what I was looking for.
How add condition۔ i want only enter number and Pop up automatic Name and color ؟؟ is it possible ؟؟
Thank you for sharing. Short and sweet
Great! I spent a few hours yesterday trying to figure out how to do this!
It was the last step I could figure out on my own.
Thanks!
how do I format the date in a a cell in column c to highlight if its less than the date in column D
So what if you have an Icon set that you want to highlight in red or green lights, and the cells you want to highlight are dependent on the value of another cell, but each cell changes. Basically, I want to compare one set of cells to another, and if they show positive vs. then highlight them with a green light, and if negative vs. the other cells show them as a red light:
account X account y
TOTAL HEALTH & BEAUTY (6.4%) (6.1%)
TOTAL BEAUTY (6.1%) (7.9%)
TOTAL HEALTH (5.5%) (1.0%)
Great video and explanation Sumit
how to do that if a cell contains date , the other cell gets red?