Apply Conditional Formatting to an Entire Row - Excel Tutorial
HTML-код
- Опубликовано: 2 окт 2024
- Apply Conditional Formatting to an entire row of a table in Excel so that no matter how large the table, you can see that the record meets the criteria.
Master Excel today with this comprehensive course - bit.ly/Ultimat...
Typically Conditional Formatting is applied to a single cell. When handling large tables of data, highlighting an entire row can be advantageous. By doing this the Conditional Formatting is always visible.
Download the file used in the video - www.computerga...
To format an entire row you will need to write a simple formula for the Conditional Formatting rule. This is so that we can specify the column we are testing. This video tutorial shows you step by step.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
It took 4 minutes to explain what I had been trying to do for an hour. Thank you!
You're welcome Andrew.
I have watched 5 different videos and none were helpful for what I needed. This one was simple, easy, and exactly what i was needing to do. Thank you very much.
My pleasure. Thank you.
This was the best and easiest by far, thank you
My pleasure, Susan.
Thank you!
You're welcome!
Straight to the point. Nice work!
Thank you, Thomas.
You are the GOAT 🤩🤩
Thanks Mikael 😊
What if I want the row to change to a specific color when I add a DATE (any random date)... how can i make it recognize the format "mm/dd/yyyy"
I’d like to know this too please!
EXCELLENT
Many thanks!
Can this be done on the online excel version? I don't have the same options when clicking on conditional formatting
thank u
You're welcome!
it worked!
Excellent. Nice work!
3:59 What if I want to highlight the rows that contain the word Antonio?
Hi thanks for this as its really useful.
I have however, ran into a problem on my spreadsheet where the rule highlights the row above the one I've typed the value into.
Have you any idea what might cause this?
Im having the same problem
@@jessaperlada1673 reselect range on "Applies to" and try removing headers cells. For example instead starting at $A$1, try using $A$2 as start cell. It worked for me :)
Aye bai je kaa bol rha hai🤨🧐
A couple of hours and 3 different, confusing tutorials later - I come here and BAM - 4 minutes & done! So easy - Thank you!!
You're very welcome.
Computerergaga - When I enter my word (Your example "Platinum") the formatting changes in another cell. So in relation to your example when I type Platinum in column G it changes the membership price column H to not have its currency formating. When I check the cell formatting in the cells in column H they are still set to currency but the $ sign disappears and it looses its decimal places ect. Any advice?
It sounds like the cell reference in the Conditional Formatting rule is incorrect. Maybe missing a $ there. So in my example I test $G2. The first cell in column G of my selected range.
@@Computergaga same with my case i have a line list with name and i want to put a name in a cell (your example palt) to show me green color. when i press enter the formatting is only applying on upper cell.
do you have any solution for this
Advice (and formula 🙂) would be appreciated!
I would like to apply conditional formatting to an entire row when a cell within a certain column is "not blank". The column is just a "free text" field so a specific word or number is not appropriate in this case.
Summary of Objective:
-- Highlight entire rows in a spreadsheet when there is ANY TEXT in a certain cell of a specific column.
Purpose:
-- To clearly see (with highlighting) the rows that are no longer "actively in progress".
Thank you, lifesaver BUT why Microsoft does make it so complicated; should be basic.
My pleasure.
I have 11 sheets within a workbook and every week I add a new row of data to each sheet. I copy the last row and paste above since it has formulas then add the new weeks data. But the conditional formatting does not update to include the new row in comparison to all data in the column (color shading is based on column data). So, I then have to manually update the conditional format rule to include the new row. Is this possible to automate?
Just preselect all the empty cells and it will update in future when input is given, i might be wrong
You're the best! This is what I was trying to do for a long time, finally I know how. Thank you
Thank you. Great to hear.
Is there a way to save this rule so I can easily apply it across multiple sheets? It's going to take a while to type =$AE3="X" every time...!
Thanks for this helpful video btw!
I am trying to format a formula where a cell is highlighted if it matches the value based on another cell. For some DUMB REASON, when I enter the formula I am using into a blank column, it returns FALSE, which it's NOT FALSE! The formula that I am using for the condt'l format is: "=$E3=$A$2" and when I press Enter, IT DOESN'T FORMAT IT CUZ THE LOGICAL RESULT IS FALSE WHEN IT SHOULDN'T BE!
What am I doing wrong??
[Ctrl] + [*] will select the entire range of populated cells.
Thanks for the video. Unfortunately, when i follow exactly what is instructed here, not only many rows containing my criteria are left without getting the formatting but also I get many other unwanted rows highlighted. I have excel 2016. 'Wondering is this error due to the version I have or what else
how to add formatting by Column B for example if column contain "victoria" highlight row..any victoria? whether its victoria summers or victoria simpson, if there is victoria then highlight row. any idea?
Sure, you can use the formula below.
=ISNUMBER(FIND("victoria",$B2))
This formula is case sensitive so will view Victoria and victoria differently. You can use the below instead for non-case sensitive.
=ISNUMBER(SEARCH("victoria",$B2))
Computergaga thank you very much sir..
You're welcome Shamsudhiin.
Why this formula in conditional formatting is working on my drive office 365? Please can u help me?
Hello from NC! I have followed these instructions exactly and I'm still having trouble getting my rows to highlight. I am using a Macro, does that matter? I can do typical single column formatting no problem. Also, I've got more than 4800 rows, Columns A-Z.
Macro is fine. I wouldn't use Conditional Formatting in the macro. Just test whatever the criteria is and format the row in A-Z as a result.
I am having this same issue. Highlighting single column using "text that contains" works, but using this rule to apply formatting to the entire row yields no results. Any solution? I am working off a document that i did not originate, but it is a simple sheet with emails and RSVPS
ruclips.net/video/MAjWpPwcTZ0/видео.html
Hello 🙏
Please like share & subscribe my Channel.
I have a data of 150000 rows. In column b value repeats. I want only those rows in which b column value repeats more than 5 times
In the same scenario, if we want to auto populate full row into another existing worksheet with the condition that G2 cell has value 'platinum'. Is this possible
Thank you for your explanation, however it doesn't color all the rows in my excel sheet. What am I doing wrong?
Hard to say without seeing what you have. It will format the rows that you highlighted and if they meet the criteria.
How if i want to make green color to range value? Such as, if 0 to -10000, it will become green
Is it also possible to do trick but with searching for a specific text in a text?
For example, if there are 3 rows with "platinum chip'', ''platinum hull'' and ''steel hull''. I want to apply conditional formatting to the rows containing the text "hull".
Is this possible?
For sure. If you had 3 columns and the column containing "Platinum Chip" etc was in column B, then select the range and create a new CF rule using a formula as shown in the video, and use the following formula:
=ISNUMBER(SEARCH("Hull",$B2))
SEARCH finds an occurrence of "Hull" within text and ISNUMBER checks whether it was found or not.
Hi, just want I was looking for. Thank you. However, do you have a video how to hyperlink image from excel to WhatsApp message. Thank you
Great! I'm glad it helped.
I've not done anything with WhatsApp before.
How can do conditional formatting using two criteria for ex. highlight the row whose country is USA and membership type is platinum???
In the formula box you could enter - =AND($D2="USA",$H2="Platinum")
I have a video on doing this - ruclips.net/video/cESkN7XBE9M/видео.html
By using AND formula you can do it
Thank you sir for nice and clear video, I have a question pleas. I make a table that contains in culomn A doctors names and the headers in row1 is the month date. Inside the table all the cells has data validation with departments in hospital to make a monthly rota shift for the doctors. The list of the data valedation contains (main department, long shift, short shift, emergency, operation room, DAY OFF).
I want to make a conditional FORMATING THAT IF I CHOOSE "long shift" from the data validation automatically gives this doctor "DAY OFF" for the next day and color the cell of "DAY OFF" in red.
How can I do that.( For example if I choose in C5 "long shift" automatically mak D5 "DAY OFF" with red background in B5.) thank you.
You're a genius - This is what I as trying to do! Awesome
Excellent, thank you Ernesto.
Same here!
ctrl shift end
Thanks so much. Very straightforward and helpful video. I appreciate the way you explain not just how to do it but why and what the relevant code means.
You're welcome, Imran.
What if you later change the value of a cell (say, album 2 was actually only Gold). When you change Platinum to Gold, will the row automatically "unhighlight?"
Absolutely!
I want to know how to apply different colours in that in column
Ex: for gold one colour, for platinum one colour, silver one colour like colour. Please tell the formula for that....
You need three separate rules. One for each colour. Follow the tutorial three times.
This is very helpful, but I am stuck and cannot find a way to do this.
Q:
a spreadsheet with a list of vaccines on top row with different dates on the below corresponding rows. I am trying to highlight the row to include their name if a date have past DATE(today). This will allow me to see who have missed their appointment quicker because their name is also highlighted.
Thank you
Awsome ! How to apply an excel formula and have entire row highlighted ? For example Your column G with lowest membership fee ? =$G2 = MIN(G2:G100) ?
Thank you so much for the video but it should be better if you add excel data set for exercise purposes.
Thank you Ishimwe.
Does this work only once? I wanted to colour the rows different for each ie Platinum = Green , Gold =yellow and bronze =orange. When I added more rules the colour coding did not work correctly. Thank you kindly for you assistance with this., sorry you have answered this already
Thank you so much! I was struggling with this for so long, and you showed me exactly what I needed to do ($E2 instead of E2). You rock!
You are welcome. Thank you 😊
Vsnvsn z
How can I use it with shared file without losing track changes data ?
Excellent tutorial -- delivered perfectly! To the point, and demonstrated with simplicity. Thanks for this... and yes, I've subscribed!
Thanks Jesal
ruclips.net/video/MAjWpPwcTZ0/видео.html
Hello 🙏
Please like share & subscribe my Channel.
Can more than one condition / rule be applied to a table? Eg. Have silver blue and platinum green. Thanks 😊
Yes absolutely. You can create as many rules as you like. Repeat the process but specify the rule & colour you require for the second one.
My cup of water just turned into 5 O'Clock tea.
🙂
I need to highlight specific cells in the row based on the text in one of the columns. Is this possible?
Using your spreadsheet as an example. If you select platinum membership for someone it only highlights the person's name, their country and the cell with platinum in that row.
This doesn't seem to work in Office 360 version. When I enter the formula =$G2='Platinum' I get an error that it's an invalid formula. If I change to =$G2>'Platinum' it accepts the formula, but of course doesn't work
I was finally able to get it to work. I had to put the constant in double quotes
Yes indeed. Well done Jeffrey.
Excellent Tutorial my problem is solved.
how do I do this with a ranging number. for instance the rows I want to highlight can have a value from 1-100. I need any rows that have a value above zero in the cell to highlight horizontally so I can then move them to another sheet to upload as a CSV file.
Hurray! Awesome video! I can finally format an ENTIRE ROW thanks to your very helpful video. Thank You!
Thanks Mike. That is great to hear.
Hi , this is good thanks, however it does not change back , if i change/delete , one of the "platinum" it stays, also can you show the "applies to" look like. thanks YB
Thank you very much from San Diego, California
My pleasure Roy.
Well I did the same but I am receiving error with output as it colors two rows. One with the key word and a row above it.
Sounds like you have two rule for it to colour two rows. If it is highlighting the row above, this normally means the cell used in the formula is not the same as the first row highlighted.
For example, if I selected cells A1:G10 then the frmula references row 1 - =$B1="Gold"
But, if I select A2:G10 then the formula references row 2 - =$B2="Gold"
I've one question Alan a silly question I suppose, I want to highlight last n rows say 5 or 8 in a column. I'm going to enter number of rows to be highlighted in cell day D5, my data is in say B2:B22. Can you please help me in this
Hi Sachin, sorry for the delay. I posted a video which I hope answers your question - ruclips.net/video/oUp_7sPoXBQ/видео.html
Thanks, I kept highlight just one column thanks
Thank you, very simple. I want to colour a row when the date in column A is today. Thank you
You are welcome!
why for me the row above the word I typed gets collored and not the one I type the word?
This is an issue with the references used versus selected. Don't select the header row. Start from row 2 (or whatever is the first row of the data).
It is highlighting the row above because, for example, you selected A1:D10, but used a formula that started from row 2, for example, $D2="London". The selection and formula are not aligned.
thank you very much to clarify the concept , you went very fast to the concept avoiding to make many bla bla bla bla, your are a fast consulting in the market. thanks
finally, just the excel video i have been scouring youtube for, thank u for the easy explanation mr gaga
That's it, so easy, watching for the last 45 minutes but mostly too much talking. Certainly going to see other videos too.
Somehow, instead of the row where my chosen word is in, excell colors another row somewhere down. Does anyone has a solution for this?
This behaviour is normally related to the range you selected to apply formatting to, and the cell you reference in the formula. The cell you reference in the formula, for example, $B2, must be the first row of what you select. So, row 2 in that example.
What about highlighting anything before todays date
How would I accomplish this if I wanted to apply the rule to something more generic than specific words? For example, I want to highlight the entire row where a date is present in a particular column.
Hello, I’m trying to highlight a row in green if the row is all filled in and highlight it red if it’s not how can I do that.
Hi Jah, imaging that the data is from column A:C and starts from row 2, the following formula can be used in a CF rule to highlight the row in red if the row is NOT completely filled in
=COUNTBLANK($A2:$C2)>0
And a second rule with a slight variation in formula for green if row completely filled in
=COUNTBLANK($A2:$C2)=0
Good God, THANK YOU!! Simple and easy. I looked up 5 different videos and all of them were poorly explained. Much appreciated
Glad to be of help.
is it possible to highlight an entire row but a cell reference has a sentence and I need a specific word only within that sentence
Yes absolutely. I have a video on using the IF function in that way ruclips.net/video/0_IGpT0bYZY/видео.html
Use the ISNUMBER(..... part of the formula in the Conditional Formatting rule insead.
This formula does not work for my table. it highlights other rows that did not meet the criteria i set
This sounds like it is an issue with the cell used in the formula. The cell used must be the first cell in the selected range.
So, if your testing column D, and you select a range from A4:F100, you would use $D4 in the formula, as 4 is the first row of your selected range.
Using a different cell would cause different rows to be highlighted.
Like your charming accent, thank you for the helpful video!
Thank you! 😃
Good video...
I'm from India
Thank you, my friend 👍
@@Computergaga 👍🏻😊
what is the formula for getting a shade of color (with differing color intensities-proportional to numeric values) for a specific category in an excel sheet .please help asap
This is colour scales available from the Conditional Formatting menu. But I don't believe this tool can be used with conditions such as a specific category. Unless you filter for category and colour scale the results. Could use a Pivot for that.
@@Computergaga thanks sir😄
Doesn't work for me.
I have loaded a .txt file. I inserted rows and added text "Total" in the rows. Now I just want these rows to be changed to a different color.
Used the formula =$A7="Total"
A7 is the cell with the text "Total"
This randomly selects the rows and highlights them. Not sure what's happening. Can you help?
Thanks
Your reference should start with the firs cell. So if the range you selected starts from A1 then the formula should be =$A1="Total". If you selected from A2 then the formula should be =$A2="Total".
Not sure why it's different but I have to fill in [=$G1="Platinum"].
Since it starts from the first row you have to fill in the row whereas you start, which is "1". If i fill in $G2 each row is coloured grey one row above.
Any explanation how this is different to me?
I have the same Excel version it seems.
Yes, the formula for the Conditional Formatting rule must match the selected area. So if you select a range from row 2 then you can use =$G2="Platinum", but if you start from row 1 you can use =$G1="Platinum"
I would like to copy the rows in green automatically to another sheet and not manually. How do I do that
Thanks.. It's helpful, but if choose use number in place platinum..it doesn't works
Nice! how to you align all the highlighted rows after each other? Better yet ; how would you align/seperate all the rows of platinum after each other
Thank you. I'm not 100% sure what you are asking, but if it helps, you can sort values in a column by cell colour. By sorting any of these columns by the column they will be aligned to each other.
@@Computergaga thanx
I have a bit more complexed story, taking ur apology, I have "Platinum 1" "Platinum 2" 3,4, so on. now when I want to highlight rows >that has cells>that contain>"*Platinum*" it doesn't work. I used the built in highlight cells that "begin with" "Platinum" it works but it only highlights the cells but not the row. the asterix using ur anology doesn't work. so how do I highlight the row that contains a cell that contains a text?
You can use the following formula -=ISNUMBER(FIND("Platinum",$H2,1)) which assumes the first cell (containing platinum) of the selected range is in H2.
Put this in the box when you go into "New Rule" and then "Use a formula to determine which cells to format"
Thank you for the tutorial, mine is not working.... i am using Google doc. And i need all YES on One column turn the entire row in the. Same color. It’s not working. Do you know why?
Do you have it working Lily?
Yes’ i was selecting the title too! That’s why it wasn’t working
Awesome! I saw a notification for a comment saying you had fixed it but then couldn't see the comment itself. Nice one! 👍
Omg thank you so much for this video, I have been finding it difficult to learn this and this video just explained this easy , THANK YOUUUUUUUUUU
Great to hear. You're very welcome.
hi how do you do a row of formatting, so say i have a list of dates or times and at the bottom of the row i type a time, I want to see the the times that are the same in that row.
This has just helped me loads. Thanks for the clear and simple instructions!
You're very welcome. Glad it helped 😀
What if you wanted to highlight those rows that column H is less than 75?
For those interested, it would be: =$h2
Is there a way to fill the whole row using "Top 5" from say column F?
Heelo sir
If we want to highlight whole row for top 1 value
Plz suggest me whats the formula!
please see this video showed above 1 more time you will find you answer... I tried but got it what he explained
Thank you for simplifying this process. My hair will not be pulled out no longer. :)
Good to hear. Thank you Anastasios.
What if, you are using multiple dates functions such =today()+(x amount of days) in a cell, how could you right that within the "Format Values where this formula is true"
I'm not sure on exactly what you are asking Laionel. If you want multiple rules, then you can keep adding rules and have multiple in a cell.
If you want to know if a date is between today and a date x number of days in the future. You could use =AND($E2>=TODAY(),$E2
When i do this it only works for "g2" and not the full table?
Ensure that all rows and columns are selected so the rule is applied to all. And be sure to use $G2. The use of the dollar sign is critical.
Doesn't work with Excel in Office 365 :(
It works in 365.
So I want the row to change formatting once there is text in a cell. Does not matter what text. In your example, if column G has value / text execute row formatting. What is the proper formula?
Thank you, clear and to the point!!!
Thank you, Praveen 👍
Good Explanation. I did it Thanks. it is very easy.
Great 👍 Thank you.
Đơn giản và rất hay, cảm ơn bạn!
How to compare two different columns in a excel with another two different columns in another excel at the same time?
It depends on how you want to compare them. In a typical scenario the simple solution would be to join the content of two column into one and compare them. But it really does depend on specific situations.
@@Computergaga Thanks for your reply. That idea works well.
great job. I have some kind of query. I want to change color for the due date with the entire row, can you help please? thanks in advance
Sure. Depends on the criteria you need. But as an example, if your due date is in column F. Header in F1 and first due date in row F2. And you want to overdue expired dates. You can select all data from A2:bottom right, use the formula =$F2
I'm having trouble highlighting a row based on an alpha character in a cell, specifically a persons name in a cell to make the row highlight. It needs to be set for any random name to be put in a cell and then highlighted up to the name. All the tutorials ive seen only account for number values. How do I do it so it will recognize any random letter?
Just as in the video. It shows highlighting a row if the word Platinum is in a cell. You can do the same for any character, or name. You can also compare against another cell value (where you could type a name at random) for example; =$D$2=$H$2. Imagining you enter the random name in H2 and columnn D contains the list of names.
Please help me how to do it if the cells that determined contain error?
You will need to deal with the error Wulandari. But I couldn't tell you how from here.
Double inverted commas? Never quotation marks called that before. Must be a UK thing. Nice video.
😀 Thank you Jack.
I am not sure, but I was trying to apply the same on Table form "Ctrl+T" and it gave me a different result.
Being formatted as a table shouldn't cause any problem. I use tables all the time. It maybe the way you select the range to format. Is it formatting the rows above the ones it should format?
Be sure to select the table columns and not the cells in the table header.
@@Computergaga CUZ. Of the $ sign it should be before the cell like "$B4" it works perfectly now thank you. My idea was to cross the whole line if it met sit condition it is a great help in "bank reconciliation".
Awesome! Nice one Dorbaga.