Advanced Conditional Formatting - Google Sheets - Use Formulas, Cell References
Вставка
- Опубліковано 4 лип 2019
- Learn how to apply advanced conditional formatting in Google Sheets using formulas. We'll create logical test formulas to apply conditional formatting, sample cases with cell references, highlighting the entire row, references to other worksheets, highlighting duplicates & extra duplicates, highlighting rows that appear on a list in a different worksheet. and more.
INDIRECT
• Indirect - Google Shee...
IF, IFS
• Google Sheets IF & IFS...
IF, AND, OR
• Excel - IF Function, M...
#googlesheets #conditionalformatting #advanced
I watched this a year ago as I was getting into writing formulas and using Conditional Formatting and loved it, but only half understood the logic. Now, a year wiser with a lot of experience messing with spreadsheets and personal projects, I came back to this again to get the formula and it's like a lightbulb went off. Everything just clicks now. Best feeling. Truly, your channel is just the best thing, just thank you so much for all your hard work! :D
I can't thank you enough for this - I have been looking for an answer for days for this and it worked perfectly. Cheers!
There's nothing better than this videos. No one can explain better. Direct and to the bone. Excellent!!!
Amazing lessons! Thank you!
Just one hint I give you, if you let me do so: in the conditional formatting rule box, you can use the "Apply to range" to set the range you want the rules to apply.
So, no need to delete the entire rule and start setting it all over.
And, when you start setting a rule OR you are about to modify one, you just need to have one cell selected, at least. The conditional formatting rule box will show the entire range to which that rule is applied (even if you selected one cell or a part of the data range) .
I think it helps a lot when modifying existing rules.
I was doing it different to get my results. But yours is a lot shorter and easier. Great video
Man this is EXACTLY what i was looking for thanks SO MUCH!!
Thank you! I'm used to the conditional formatting in Excel. Now I'm learning how to use Google sheets. So of course when I got to the point that I needed use Conditional Formatting, I was just putting in formulas like "=F13" (My reference cell) and getting illogical results. Now I understand how it works for Google Sheets. The formulas need to be Boolean! Thank you!
You are part of the USA treasure my friend. Thanks for all you do
Funny that the best video about Conditional Formatting on YT I found so far is not made in Excel.
Best tutorial yet! Thank you!
OMG!!!!!! A million thank yous. I have over 400 names I need to have highlighted on my spreadsheet that from the beginning of the year, and bam just like that, they were all highlighted.
Love your content!!! Always comes in clutch. Thank you
GENIUS!!! Thank you for amazing and practical tutorials. You are super PRO!!!
Thank you very much ! I was getting headaches from trying to use formulas for conditionnal formatting, I still have headaches but now I know why.
:)
Great step by step explanation
Awesome video! Is there a way to highlight cells when your table is focussed on cells within rows instead of entire rows based on their value in a particular column? To clarify, I have a monthly timeline going from left to right and there's a benchmark number in de rows that keeps on getting back (every month) that I'd like to give a transparent look as long as the month-values aren't filled in yet. (Hope that makes sense 😬)
Very good lesson, thank you for making the video.
Siempre aprendo mucho de ti, muchas gracias por compartir.
I would love to see a lesson on creating a conditional format using Script. For example, Adding a a border to every row where the date changes. Thank you for your awesome videos
Very interesting trick to highlight only second and onward occurrences of duplicates - at 20:30
You have been a great mentor, I want to highlight a word in a column or row particularly, it shouldn't highlight the entire row or column!
Thx for INDIRECT solution
amazing! thanks for this a great lesson, many times I tried but I always failed but now I know, it so very big help to me...
sus vídeos siempre son muy buenos yo diría Excelentes....
I like this tutorial, thank you
Simply awesome!!!
Thank you so much! It helped me a lot!
I was exactly looking for this
in-depth indeed awesome
You are a brilliant teacher
Hi. How do i add a conditional formatting to find duplicates of numbers scattered over 3 columns? And each column has a column in between.
Is it more efficient to make a complex formula in a conditional formatting test when you want to highlight certain rows with the same color that meet multiple criteria or to have separate conditional formatting formula tests.
Great video! Please more tutorial for web applications:)
you are the guy, simply.
Your videos are wonderful, and have helped me very much. I have built a Google Sheets check register and budget, but I have a question concerning "Conditional Formatting". I have added several conditional formats to my check register, but there is 1 that I cannot figure how to construct. First, I have built my register a little different using "1 column" that contains Withdrawals, Deposits, Transfers In, Transfers Out, & Credit Card Refunds. I have set up conditional formatting to set font color, and cell fill for each of these items. Here is what I would like to do: When I enter a number that is a "Withdrawal" or "Transfer Out", I would like the number to "automatically turn negative". I already have the negative numbers turning red, but would like the additional feature if possible. If you need it, I could email you a partial section of the file with fictitious figures. Thank you again!!
Would you be able to help? I want sheets to recognize Jon in column A, take column J dollar amount, and multiply . 75 into Column O. Would you be able to put a formula for that? Thanks
Awesome explanation to conditional formatting.
Glad you liked it
thank you!
i'm new to Google Sheets and Excel and I'm surprised highlighting the second occurrence of repetition is only a matter of locking the components
Really cool video!!!
THANK YOU SO MUCH
You fix many of my work with simple steps. thank you for your great work
Great to hear!
how do i do highlight an entire line based upon the value of a number to the right of the decimal point? for example 208.310 = (blue bc of .310) or 208.331 = (green bc of .331). i'm trying to conditional format based on the set value of the right side of the decimal only as it would denote in my example the manufacturing plant (12 in total). Any help would be much appreciated as i'm pulling out my hair
Thank you for your video. However, I have noticed that every time you are giving static data range(A2:H12) in "Apply to Range" of conditional formatting. Is there any way to give dynamic data range in "Apply to Range". Thanks in advance
I am trying to figure out if I can do a conditional formating that will automatically change based on the value of another cell which also changes automatically. This is the cell that I want to link my conditional formating to =COUNTIF(A4:A1002,"") I have few other columns with =countif(C4:C1002,true). In column A I am adding data manually, that will change the value in the cell with the first formula for example to "100" I want to make the background of the cells with the second formula change colors from red to green when the number is closer or equal to the value in the cell with the first formula.
In every column with the second formula, there is a specific number of tasks which need to be done, that number is always changing and it is shown in the cell with the first formula
Thanks a lot
Is there a way to modify the "Apply to range" box such that it applies the conditional formatting rule every nth row? For example, if I wanted to look for the highest number every 3 rows, how would I do that? Could you use a MOD formula?
can i replace a text based on condition of another cell? because all tutorial i found only change color based on condition from another cell.
Once you have the highlighted rows, how would you copy and paste them on a new tab without any gaps, so that they are still able to be updated and change from the original table?
hi thanks for the video..it helped me alot! i hope you can answer my question though.. I created several conditional formatting for a range of cell. But I want the conditional formatting to stop if the first one is already true...how do i do that?
Thank you this is very helpful. Can you also explain IF another google sheet is "available" then 1st google sheet = color, how would that formula look like. I tried =indirect("Input!$A1:$A10")="available" THEN WHITE, but I have 300 cell range, and I also want if Hold THEN green, if Doctor THEN RED... It seems I have to enter each cell (3 times) for 300 cells.. is there anyway to do this faster?
Great video . What if I want to do an if the value is
Thanks, as always, for your videos!! Can conditional formatting be used to change the tab color, as it can in Excel?
I assume by Tab color you mean worksheet color. If that's the case in Excel you can only do this using VBA. You should be able to do this with Apps Script in Google Sheets, however, cases when it will work vary depending on the situation. For example, you can make it work if the value in the cell changes based on manual entry, but if it updates based on a formula update, then I'm afraid it might be not possible.
Thank you
Great sir
is it possible to display text on a cell based on the text color of another cell? For example, in A1 contains the word "Text" in green text color and I want B1 to display "Green"
I did things a little differently, which was to wrap the match in NOT(ISNA( ))
Which basically gives a TRUE for every n/a error, then flips the trues to false and vice versa.
So then to do the opposite as he did at the end, you'd just delete the NOT
What if I wanted to highlight the cells in the Sales column when they are more than 2 times greater than the number in their corresponding Cost_Of_Goods cell? So for example, highlight H2 if it is 2 times greater than G2, highlight H3 if it is 2 times greater than G3, and so on... Is it possible to do conditional formatting in this way?
=$H2>=($G2*2)
What if I want to use two criterias to enable highlighting a entire row?
In my food planner, I want to highlight a food item's row based on two conditions, 1. if I allocate grams to that item, and 2. color code according to whether it's primarily a protein, fat, carb, or fiber source.
Does this conditional formating show up in datastudio?
Is there a way use a Colour scale, but rather than Highlighting the Background, it highlights the text in the same scale? By this I mean the background colour stays the same, but the text colour changes based on the data in the scale.
Hi! This video is extremely useful thanks! I still have an issue: I formated a cell (conditional) but letter I had to move the collumn used by the formula. I found that the condition didn´t anchor to the colum. for eexample: conditional formula: =$s40=3 , the I moved column S by draging and droping it before columns M. The conditional formula cannot relate to the new column M. It keeps looking for the Column S and that triggers thee error. Is there any way I can anchor it?
maybe =INDIRECT("$s"&ROW($s40))=3
Thank's Sir. Great. One question,
would it be possible to use conditional formatting just by using formula without having to click on the Format Menu ?
Right click > Conditional Formatting.
I use it always. Much easier.
@@JohnBGood1 Thanks Sir :)
IS there a way to do Color Scale for text and not backfill?
Is there a way to be able to use more than 10 conditional rules? Excel gives like 200
Yo what's good homie. I'm trying to make a formula that when a certain cell has the words "LOSE" in it, a different cell will automatically go up 1 digit. Do you know if this is possible?
Do you have a data validation custom formula is video? I want to know how to make a formula when a user types a number more than the total number, it will automatically decline the number and it will show a validation help text.
QUESTION for anyone: I use a query tab to return filtered results from a large data set on another tab. The query results need to be printed out with full borders (users pencil-in extra notes in each row). As expected, the total number of rows returned by the query will vary. Can I set up a conditional format which only puts borders around query results; but leaves the rest of the tab blank so the printer does not spit out extra pages with nothing but borders around empty rows?
Pls teach me how to make a copy of google doc template then save it as PDF to a folder in gogle drive. Then attached it as an email attachment..thank you
When using the formula: =TEXT(RRI((DATEDIF($A$3, $A$14, "Y")), B14, B3),"#.##%")&" CAGR", how do I conditionally format the number, Black if the result is positive and Red if its negative? TIA
Please let me know the date formula if I want the cell to alert like color it orange 5 days before until the due date. the orange color will remain until due date please.
Please use nested IF formula n compare previous date and due date by math operator. Or I think you can also use Network days n nest it in IF formula.
thank you. please show me how to make condition formatting in app script 😓
Thanks for this video, really helpful.
I am trying to put a colour coded gantt chart together, i am using the condition format code =AND(I$6>=$F9,I$6
=AND(I$6>=$F9,I$6
Learn Google Spreadsheets thanks- if there are 4 or five different categories and you want a different colour displayed for each- can that be done. Eg: decision on feb 12 shades that week green, communication between feb 1-10 shades that time blue...etc etc
Just apply one formula for each color/category for the same range. Repeat for each category/color.
@@ExcelGoogleSheets you rock... much appreciated
I am trying to apply conditional formating based on different sheet. And the formula working perfectly. But still the conditional formatting not changing automatically. Where is the problem. Please help
🙏🏻
I sure would like to talk to you for a few minuets. Bet you could get a couple of nice videos out of it.
If you are trying to apply these logics to conditionally format each cell's value in a single row make sure to lock the row number with the $ sign.
Hi, is possible to use conditional formatting with a calendar? I mean, I have a date range and all the days inside this range will colored according to the range. I tried the date before, date after and custom formatting but it did not work. Thanks!
The short answer is yes. You'll need to create logical test using dates to give you true/false.
@@ExcelGoogleSheets Thanks.
Is it possible to have the cell refer a name of a sheet? For example,
Instead of =sheet1!a1:a2
I want something like ="c1"!a1:a1
And ofcourse this formula will not work, but is there a way to get around this?
ua-cam.com/video/Vuh7lh2mhQk/v-deo.html
Is it possible to to match the "format of another cell" via a custom formula?
No.
how can we highlight as column sir?
How would highlight every other 5 rows? So row 1-5 is grey and 6-10 white. Then back to grey
You would need MOD function with some math magic.
Sheet1 cell condition formatting base on sheet2 value how can i do in Google sheet please help
=ifs($C22=$D$4, $I22>$D$10, $F22=$D$7,) I tried using this formula for conditional formatting. It only formats based off the first two criteria not the third.
This formula gives me n/a
=ifs($C22=$D$4, $I22>$D$10, $F22=$D$7)
Is there a way to format based off more than two criteria
use OR( or AND( instead
@@ExcelGoogleSheets 🙏🏽🙏🏽🙏🏽Thank you
I’m trying to make one I’ve watched like 4 videos and can’t find a real answer. So I’m looking to make a row where if, for example E2 is greater than 200 than I want it to use E2, if it’s less than use a flat 100.
use IF function ua-cam.com/video/hG5vKMb0Lpo/v-deo.html
Learn Google Spreadsheets thanks dad.
how condition formetting to product cells
Hi, this formula is calculating true and false but when I place it in conditional formatting rules it's not displaying the required format. Any thought on this. Apart for it this video gave me broad understanding. Thank you for this learning.
=and(isnumber(match($Y6,indirect("EHdata!$AU:$AU"),0)),$Y6
=and(isnumber(match($Y6,indirect("EHdata!$AU:$AU"),0)),$Y6
Well, what range did you highlight to apply the formatting to?
Apply to range B6:AD1147
@@Shoaibzahoor It looks like you're doing everything right. I'm not sure what the problem is. Maybe share an example sheet.
Thanks for this video, really helpful :)
Is there a way to be able to use borders like conditional formatting in google sheet ? ( without using the scipt ^^)
It's basically what's in the box. Unfortunately, they don't allow much.
@@ExcelGoogleSheets@ thank you for your reply
sir how to remove duplicate but keep last data
Hi, is there a way to highlight column instead of row?
It's possible, but I don't know what rule you're trying to apply.
@@ExcelGoogleSheetsThanks for replying! For example, if I have a list of dates arranged in a row, whenever the cell date = today, then I would like the whole column related to the date to be highlighted; if it's possible
You would use a formula with row reference locked. Like
=A$1=TODAY()
@@ExcelGoogleSheets ahh, great!! Thank you so much!
How To Automatically Highlight Rows And Columns Of Active Cell In google sheet ?
find the answer here
THIS IS SO DIFFICULT OMG.. PLS HELP ME