🎉 Thanks so much for 30,000 Subscribers! 🎉 It really means a lot to reach this milestone and if you have any questions about the video, then let me know in the comments and I’ll try my best to respond as soon as I can, here's to 50,000!
Hey @TropicalTech!! Topical Tech, Such an outstanding video! So simple and easy to understand. I have a question... I have a table with four columns. And I have another sheet that I want two columns to be automatically formatted when I enter one of the values into a third colum, but it matches a value in the 4-column table. Let me explain, Let's say I have a Volume number, a Reference number, a Frequency it occurs, and a Title for that reference. Each volume has a specific color to it also, so many references will be in Volume 1. Every Volume 1 item will be colored a single color. Volume 2 will have another color, etc. I want to be able to enter the smallest unique number (the reference number) on one column, and have the title automatically paste on another (using Vlookup, I have accomplished this). But I also want to be able to automatically format an additional cell to show the volume number, with that single manual entry of the reference number. If I have 20 items with unique reference numbers in Volume 1, I want to be able to enter that unique reference number in a cell, have it automatically detect that that reference is one of the unique references in Volume 1, and enter the number 1 on a third cell (indicating that it is from volume 1), and conditional format that same cell with the color corresponding to Volume 1. I have already accomplished the automatic entry of the title when I enter the reference. I also can manually enter the volume number and I have formatted it to change colors to match the Volume. But I want that part done automatically. Please help!! I would really appreciate it.
Hi @TropicalTech, is there a way to format a range of cells in a row based on the value of 1 cell? And without specifying a particular cell in the formula? Similar to something like “if cell = “Hello”, cells (A,rownumber:J,rownumber).fill(color.green)” ?
I think you'd need to use the section called "Use a formula to determine which cells to format" which is an option after choosing new rules. First select the cells that will turn green and then try and create that rule
What a great video and just what i was looking for! May i ask how do we take the highlighted cells from muiltiple charts or sheets(if possible) and compose them in to a chart showing the relevant lines where the value hass been meet please
Hi thank you for your nice presentation ☺️. Can you tell me, after conditional formatting I want to insert a sell which is shows me the minimum value ??
hey do you know how to change the green color in none active selection cell.. if you click on any cell, the border is always green ???so if you click on the sheet the border always green.. i want to change it to red?? do you know how??
HI. How can I create a rule for the cell color to change if the value (percentage) drop below 10% (red), Between 105 and 15% (yellow) and over 15% (green) ? Thank you.
Thanks for the video. I have a small issue. I am using the 3 value color range. My issue is the text is hard to read based on what color it is. For example the red with black text is hard to see. I want to change the text color based on the cell color dynamically. I say dynamically because the list is active and one day to the next the bottom person may change etc. Is there a way to always have the red with white no matter what cell it is?
@@TropicalTech Right but it won't do it dynamically and I can't do it in the 3 level value. Here is an example of my data. I have 3 employees. On Monday Fred is last in productivity and is coded red. Jane is the middle and is yellow and Rick is best and is green. Tuesday Fred is best and is green and Rick is middle and Jane is last and is red. What I need to happen is no matter who is Red I want the Font to be white.
If you go to conditional formatting, then new rule it should allow you to format the cell with all of format cells abilities, based on the rule you choose.
Nice sir but you are explained numeric change colour but i want /suppose type the text the (low ratting) after changing color red. Next cell or in sheet anothe cell same type the text automatically fill color red
Hi, many thanks for the video. It was very helpful! Could you please give me a clue how to do one thing? I would like to format a column using a function which analyse the cell above it. If the value in the above cell differs than in the cell which interests us, it automatically turns the color of the cell. E.g I have in the cell C3 the value 5, I will type in the cell C4 value 10. I want the cell with 10 to automatically be red (because >5). However, if I want to type not 10, but 2 (
hi. i get the formula to highlight cells which are another cell. but i have negative values tyoo and it doesnt seem to work accurately. can you help please
I am working on a project having different tasks with their respective dates. If task timeline is over its "yes", if not over its "No". The task maybe done or not done at specific date. Combination becomes: 1. No and done 2. No and not done 3. Yes and done 4. Yes and done Can i put conditions which automatically highlight cells color. Thanks in advance.
I want to change the cell color based on value, for example anything less or equal to 50 would turn the cell red and everything greater would turn the cell green, let’s say I want to delete the 50 and then I input 80 in the same cell so it updates the cell color to green. Is it possible to do it?
Your videos are great!! If i wanted the final cell to be red green orange or yellow based on how many cells in the row are red? Can you change the color of a cell based on the different colors in a row with each representing a value of 25%
Thank you! I'm not 100% sure if that's possible but there would be a workaround if you used numbers (or the numbers generating the colors) that would equal a total or average/weighted average in the chosen box. You could then format that box for the color.
I followed the steps in the beginning but it won’t highlight the cells. When I click on the cell the color shows, but when I don’t click on the cell the color goes away. Any tips?
Is there a way to color code based on number typed in but the actual number not show? For instance, typing "1" in a cell turns a cell pink but the "1" is invisible.
man how do i do this but with two cells. i have in one cell and i want to change the color of another cell when the first cell reach certain amount...i tried everything :(
hello , for example A column is 1000 and B column is when it will be 40% of A column ( in this case 400 ) B column will be Red color .... how to do it any idea ? if anyone helps will be appriacated
I need to highlight based on two variables - A range of values in column A _and_ a word in column B. =IF($B:$B="PUB", =IF(A$>14,Red,=IF(A$>7,Yellow,None)),None) fails.
🎉 Thanks so much for 30,000 Subscribers! 🎉 It really means a lot to reach this milestone and if you have any questions about the video, then let me know in the comments and I’ll try my best to respond as soon as I can, here's to 50,000!
Hey @TropicalTech!! Topical Tech, Such an outstanding video! So simple and easy to understand.
I have a question... I have a table with four columns. And I have another sheet that I want two columns to be automatically formatted when I enter one of the values into a third colum, but it matches a value in the 4-column table. Let me explain,
Let's say I have a Volume number, a Reference number, a Frequency it occurs, and a Title for that reference. Each volume has a specific color to it also, so many references will be in Volume 1. Every Volume 1 item will be colored a single color. Volume 2 will have another color, etc.
I want to be able to enter the smallest unique number (the reference number) on one column, and have the title automatically paste on another (using Vlookup, I have accomplished this). But I also want to be able to automatically format an additional cell to show the volume number, with that single manual entry of the reference number.
If I have 20 items with unique reference numbers in Volume 1, I want to be able to enter that unique reference number in a cell, have it automatically detect that that reference is one of the unique references in Volume 1, and enter the number 1 on a third cell (indicating that it is from volume 1), and conditional format that same cell with the color corresponding to Volume 1. I have already accomplished the automatic entry of the title when I enter the reference. I also can manually enter the volume number and I have formatted it to change colors to match the Volume. But I want that part done automatically.
Please help!! I would really appreciate it.
Thank you so much, I was struggling how to remove color, your tutorial helped me do it in seconds.
Exactly what I am looking for.Thanks.
You saved my life😂😂😂😂😂 dealing with huge data sets can be time killing
Just what I needed. Great tutorial BTW!
Thanks so much - this was really helpful!!!! Thank you for taking the time to do this video - it had to be a lot of work!
You're welcome, glad this helped!
Great tutorial thank you very much, just what i was looking for
Hi @TropicalTech, is there a way to format a range of cells in a row based on the value of 1 cell? And without specifying a particular cell in the formula?
Similar to something like “if cell = “Hello”, cells (A,rownumber:J,rownumber).fill(color.green)” ?
I think you'd need to use the section called "Use a formula to determine which cells to format" which is an option after choosing new rules. First select the cells that will turn green and then try and create that rule
Can this be done in reverse? I have cells in a table with 4 colours can I assign a number to each accordingly?
I don’t think so unfortunately, they need a numerical value to be customized
@@TropicalTech Thanks for the response
What a great video and just what i was looking for!
May i ask how do we take the highlighted cells from muiltiple charts or sheets(if possible) and compose them in to a chart showing the relevant lines where the value hass been meet please
Hi thank you for your nice presentation ☺️. Can you tell me, after conditional formatting I want to insert a sell which is shows me the minimum value ??
Thank you ❤ 3 years on still needed
hey do you know how to change the green color in none active selection cell.. if you click on any cell, the border is always green ???so if you click on the sheet the border always green.. i want to change it to red?? do you know how??
Very good video. Is there a way that the cell color can change by using a comparison between 2 cells. Example: green if >= 80% of cell B1?
I think you should be able to create that as a custom rule, and just input the formula as the test.
Outstanding video, figured out how to do exactly what I wanted with no hassle, thanks for posting this video!
HI. How can I create a rule for the cell color to change if the value (percentage) drop below 10% (red), Between 105 and 15% (yellow) and over 15% (green) ? Thank you.
You can use the numbers that correspond to the percentage for each rule, so .10 for 10% and 0.15 for 15%
thanks. how can i get cells to show currency automatically ? if eg. my dropdown has USD or Euro how do i then get the cells to show the right symbol
Is it possible to change the colour based on which currency is in the box instead of its value.
Thanks for the video. I have a small issue. I am using the 3 value color range. My issue is the text is hard to read based on what color it is. For example the red with black text is hard to see. I want to change the text color based on the cell color dynamically. I say dynamically because the list is active and one day to the next the bottom person may change etc. Is there a way to always have the red with white no matter what cell it is?
You should be able to change the font colour for each of the three, just like with the cell colours.
@@TropicalTech Right but it won't do it dynamically and I can't do it in the 3 level value. Here is an example of my data. I have 3 employees. On Monday Fred is last in productivity and is coded red. Jane is the middle and is yellow and Rick is best and is green. Tuesday Fred is best and is green and Rick is middle and Jane is last and is red. What I need to happen is no matter who is Red I want the Font to be white.
Where you make the rule for the red cell, you can also change the font colour for the red cell too.
@@TropicalTech really? I don't see that?
If you go to conditional formatting, then new rule it should allow you to format the cell with all of format cells abilities, based on the rule you choose.
Please make a video on comparison while making QCS it will help the lower value cell will be green automatically in row
Nice sir but you are explained numeric change colour but i want /suppose type the text the (low ratting) after changing color red.
Next cell or in sheet anothe cell same type the text automatically fill color red
You can do things with tex, just make sure you include speech marks either side of the text
thus this format can still be use for alphabets??
It helped me, Thanks
Nice man Thank you for this
Hi, many thanks for the video. It was very helpful! Could you please give me a clue how to do one thing? I would like to format a column using a function which analyse the cell above it. If the value in the above cell differs than in the cell which interests us, it automatically turns the color of the cell. E.g I have in the cell C3 the value 5, I will type in the cell C4 value 10. I want the cell with 10 to automatically be red (because >5). However, if I want to type not 10, but 2 (
Worked for me, Thanks alot.
Thanks a lot Buddy 👍
Thank you, excellent!
You are welcome!
thanks a lot brother !!!!!!!!
hi. i get the formula to highlight cells which are another cell. but i have negative values tyoo and it doesnt seem to work accurately. can you help please
I have a spread sheet I want to show Negative Numbers in a red box white font, and postive number Green Box white font can this be done
Yes, use the greater than 0 or less than 0 rule when conditional formatting
I am working on a project having different tasks with their respective dates. If task timeline is over its "yes", if not over its "No". The task maybe done or not done at specific date. Combination becomes:
1. No and done
2. No and not done
3. Yes and done
4. Yes and done
Can i put conditions which automatically highlight cells color.
Thanks in advance.
You should be able to create an IF statement for each of the four variables, and then conditional formatting for the resultant cells.
I want to change the cell color based on value, for example anything less or equal to 50 would turn the cell red and everything greater would turn the cell green, let’s say I want to delete the 50 and then I input 80 in the same cell so it updates the cell color to green. Is it possible to do it?
Yeah that will work
Thank you!
Your videos are great!! If i wanted the final cell to be red green orange or yellow based on how many cells in the row are red? Can you change the color of a cell based on the different colors in a row with each representing a value of 25%
Thank you! I'm not 100% sure if that's possible but there would be a workaround if you used numbers (or the numbers generating the colors) that would equal a total or average/weighted average in the chosen box. You could then format that box for the color.
Is there a way to set up all the color changes based on the cell value at the same time if there's more than one condition?
Thanks!!!
No problem!
Best idea👌👌👌👌👌👌👌👌👌
Thanks so much 😊😘
Thank you✨👍🏼
You’re welcome!
Thank you so much😺
You're welcome 😊
I followed the steps in the beginning but it won’t highlight the cells. When I click on the cell the color shows, but when I don’t click on the cell the color goes away. Any tips?
Very useful, thanks :)
On the same cell, it should turn BLUE when I type DONE and YWOOLW when I type "IN PROGRESS" and RED when I type "DELAYED" - is that possible ?
Yeah it should be with the exact text rule. You might need to set a few rules though.
PERFECT!
Thank you :)
How do I apply this but using “specific text” rather than a value?
when i set a formula to a blank table to get red color if the cell values are 0, this blank table turn into red . how can I fix it
How do I do a group of cells totaling a certain value
Use =sum(*Selected Area*)
THANKS BRO
You're welcome!
THank you so much
No problem!
now i can color in my F's
thanks
It would really help if you actually zoomed in the screen so we can see what you are selecting. Not all of us have eagle eye vision
Is there a way to color code based on number typed in but the actual number not show? For instance, typing "1" in a cell turns a cell pink but the "1" is invisible.
You can format the text to be the same colour as the fill, that should do it.
@@TropicalTech Thank you; don't know why I didn't think of that!!
can you help me, what if i want to make the cell red if the value is less than 0 but green if the value is more than 0. how to do it?
Use the formula option. =A10 (format green)
thank you
Superb
HOW TO CHANGE CURSOR COLUR OF ANY OPENED EXCEL WORKBOOK
Cursor colour will be down to your device’s settings
Hi I have one scenario similar to this can you please help me
I'll try my best, let me know what you need to do!
how can I change the colour of the cell I am typing the value based on this value?
Thanks
How to apply 3 different color for 3 data highest lowest and medium
man how do i do this but with two cells. i have in one cell and i want to change the color of another cell when the first cell reach certain amount...i tried everything :(
hello , for example A column is 1000 and B column is when it will be 40% of A column ( in this case 400 ) B column will be Red color .... how to do it any idea ? if anyone helps will be appriacated
I need to highlight based on two variables - A range of values in column A _and_ a word in column B. =IF($B:$B="PUB", =IF(A$>14,Red,=IF(A$>7,Yellow,None)),None) fails.
FUCKING HELPS A LOT. THANKS BUDDY.
How to change colour of a different cell on the text of another cell
Thank you very much.. Exactly what i am looking.
it is not working can you tell me whats wrong
Too small, even at full screen.
the view are small
too fast
Fantastic! Thank you
You're welcome, glad this helped!
Thank you sir
Thanks, a lot.
No worries!