Fantastic way of teaching sir .. these days im working on something similar, I got frustrated with this color thing, you explained so easily and within 10 mins of your video all my issues are solved, thank you
Just found your videos and they're perfect. I never considered the in-sheet formulas to test the conditional formatting so that's a new trick I've learned along with some advanced tips I haven't considered.
This is SO cool! Thank you for this tutorial! You made it so clear and answered a question I've been having for so long! It felt so good to automate this in my spreadsheet!
Having a nightmare conditional formatting a Table column. Custom formulas that work fine elsewhere, show as invalid. Are there issues with Tables? Even Indirect() doesnt work.
Hi Ben, do you have a post or link that explains all of the regexmatch modifiers such as .+ and $? For example, =REGEXMATCH($E2,".+Ross$") around 30:19. That was really useful and could not find anything except what you shared in this video. Would like to learn more!
Lots of examples in this post: www.benlcollins.com/spreadsheets/google-sheets-regex-formulas/ And here's the documentation: github.com/google/re2/wiki/Syntax I also have a REGEX course that is comprehensive: courses.benlcollins.com/p/google-sheets-regex-formula-cookbook/ Also, try ChatGPT or Bard. Both good at creating REGEX expressions for you. Hope this helps!
Great tutorial! Thanks for posting this. Quick question. Is it possible to apply border as a formatting style, instead of, say cell color. E.g. I have a GOOGLEFINANCE function that pulls "all" historical fields for a given stock over a period of a month. The resulting 22x6 (no weekend rows) table for August would be easier to read if the Friday row ( =WEEKDAY($A2:$A)=6) ) had buttom border applied. I'm not able to work this out.
Hey, I have 3 columns of numbers I am trying to conditionally format for the ones who have the highest number to be highlighted a certain color. How do I do this across 2 columns?
Great video. But how do I add the same formatting rule to multiple ranges? I have tried adding additional ranges, but it is only the first range that gets formatted. The rest remains unformatted. Or is the only way to make a new rule for every single other range?
Thanks! To your question... it depends and it can be tricky, depending on the complexity of the conditional formatting rule. You can use the paintbrush tool to transfer a block of conditional formatting (see my basic CF video for example), or you can add ranges to the existing rules. But sometimes you just have to create a new rule because the custom formula needs to change.
@@benlcollins It is not complex, just a TRUE/FALSE condition. The ranges are in the list, but it is only the first range that is formattet: docs.google.com/spreadsheets/d/1N3z6TmO9GATNaeoSqk_E8QiRLKAelURTBcFHC6L45HA/edit?usp=sharing
Hi. How can I get a cell, in a group of cells, to mimic the format from another group of cells? This is also to consider that the 2nd group can be changed at any time.
Hi Ben, a very interesting video. I use an annual spreadsheet and would like to highlight the columns where public holidays occur. I would like to know is there a way to highlight all the public holiday dates through conditional formatting or would I need to fill the columns in manually.
Yes, this sounds doable. If you have the public holiday dates in a separate table, then you can use a combination of the "date rule" and the "lookup rule" that I showed in this video to do it.
This makes complete sense, but I cannot get it to work. Not unfamiliar with Excel or Google sheets, followed the steps precisely, even tried using the SEARCH instead, it just wouldn't play nice.
The biggest issue I have had is knowing where to start constructing custom formulas in conditional formats. Most of what I have managed to achieve has been done purely by trial and error...
Hi, I recommend building the formulas in the Sheet first, get it working, and then transfer it to the custom formula rulebox. It's very hard to build long formulas directly in the conditional formatting sidebar.
You, sir, just saved my life. I was adding conditional formatting per row. I'm crying and no one understands why. Thank you.
Fantastic way of teaching sir .. these days im working on something similar, I got frustrated with this color thing, you explained so easily and within 10 mins of your video all my issues are solved, thank you
Just found your videos and they're perfect. I never considered the in-sheet formulas to test the conditional formatting so that's a new trick I've learned along with some advanced tips I haven't considered.
Great stuff! Thanks for the indirect formatting tip that's awesome.
Thank you very much, Ben. Your videos teach how to use spreadsheets in the everyday work with a nice touch of caring details, they are really useful.
You're welcome! Thank you 👍
Really appreciate your instructions
You're welcome. Thank you 🙏
Thanks. I learned a lot in this video.
You're welcome. Thanks!
This is SO cool! Thank you for this tutorial! You made it so clear and answered a question I've been having for so long! It felt so good to automate this in my spreadsheet!
Great advanced condition formatting
really good explanations, thx
Great video Ben!
Thanks, Danny!
Very helpful! Turns out the indirect function was the missing piece of the puzzle for my sheet here.
thank you so much!! I was already searching for hours on something and thanks to this video, I could accomplish it 😘
Having a nightmare conditional formatting a Table column. Custom formulas that work fine elsewhere, show as invalid. Are there issues with Tables? Even Indirect() doesnt work.
Great video, thank you. The correct title to help me find relevant content
Hi Ben, do you have a post or link that explains all of the regexmatch modifiers such as .+ and $? For example, =REGEXMATCH($E2,".+Ross$") around 30:19. That was really useful and could not find anything except what you shared in this video. Would like to learn more!
Lots of examples in this post: www.benlcollins.com/spreadsheets/google-sheets-regex-formulas/
And here's the documentation: github.com/google/re2/wiki/Syntax
I also have a REGEX course that is comprehensive: courses.benlcollins.com/p/google-sheets-regex-formula-cookbook/
Also, try ChatGPT or Bard. Both good at creating REGEX expressions for you.
Hope this helps!
@@benlcollins awesome thank you for the speedy reply, I'll take a look! Much appreciated
Great tutorial! Thanks for posting this. Quick question. Is it possible to apply border as a formatting style, instead of, say cell color. E.g. I have a GOOGLEFINANCE function that pulls "all" historical fields for a given stock over a period of a month. The resulting 22x6 (no weekend rows) table for August would be easier to read if the Friday row ( =WEEKDAY($A2:$A)=6) ) had buttom border applied. I'm not able to work this out.
Hey, I have 3 columns of numbers I am trying to conditionally format for the ones who have the highest number to be highlighted a certain color. How do I do this across 2 columns?
Grate video! what about aggregative formula such as max? if I want to bold a cell that is the max out of the Colum?
Great video. But how do I add the same formatting rule to multiple ranges? I have tried adding additional ranges, but it is only the first range that gets formatted. The rest remains unformatted. Or is the only way to make a new rule for every single other range?
Thanks! To your question... it depends and it can be tricky, depending on the complexity of the conditional formatting rule. You can use the paintbrush tool to transfer a block of conditional formatting (see my basic CF video for example), or you can add ranges to the existing rules. But sometimes you just have to create a new rule because the custom formula needs to change.
@@benlcollins It is not complex, just a TRUE/FALSE condition. The ranges are in the list, but it is only the first range that is formattet:
docs.google.com/spreadsheets/d/1N3z6TmO9GATNaeoSqk_E8QiRLKAelURTBcFHC6L45HA/edit?usp=sharing
Is there a way to highlight a cell with a certain number for example... anytime you enter 47 or 79 or 98 the cell will turn a certain fill color?
Hi. How can I get a cell, in a group of cells, to mimic the format from another group of cells? This is also to consider that the 2nd group can be changed at any time.
Hi Ben, a very interesting video. I use an annual spreadsheet and would like to highlight the columns where public holidays occur. I would like to know is there a way to highlight all the public holiday dates through conditional formatting or would I need to fill the columns in manually.
Yes, this sounds doable. If you have the public holiday dates in a separate table, then you can use a combination of the "date rule" and the "lookup rule" that I showed in this video to do it.
Thank you Ben. Will try your suggestions
Absolute legend
This makes complete sense, but I cannot get it to work. Not unfamiliar with Excel or Google sheets, followed the steps precisely, even tried using the SEARCH instead, it just wouldn't play nice.
The biggest issue I have had is knowing where to start constructing custom formulas in conditional formats. Most of what I have managed to achieve has been done purely by trial and error...
Hi, I recommend building the formulas in the Sheet first, get it working, and then transfer it to the custom formula rulebox. It's very hard to build long formulas directly in the conditional formatting sidebar.
@@benlcollins Thanks Ben.
Thanks!
Thank you 🙏
god bless!