Advanced Conditional Formatting in Google Sheets
Вставка
- Опубліковано 15 лип 2024
- In this video, we do a deep dive into the advanced features of conditional formatting, focussing on the "Custom formula is" rule.
Learn how to use conditional formats across entire rows when a condition is met. Then we'll look at conditional formats down columns, identify duplicate entries, multi-condition rules, date rules, search rules, look ups to other sheets, and more.
📚 Additional resources:
- Conditional Formatting blog post: www.benlcollins.com/spreadshe...
- CHAR function blog post: www.benlcollins.com/spreadshe...
- Custom Number Format blog post: www.benlcollins.com/spreadshe...
- Custom Number Format rule:
[color50]$#,##0 ▲;[color3]-$#,##0 ▼;[blue]$0 ▬
- REGEX functions blog post: www.benlcollins.com/spreadshe...
- REGEX Cookbook course: courses.benlcollins.com/p/goo...
📖 Chapters:
0:00 - Introduction
1:53 - How the "Custom formula is" rule works
8:37 - Format entire row
10:22 - Conditional formatting with checkboxes
11:53 - Conditional rule precedence
12:35 - Identify duplicates with conditional formatting
18:11 - Format entire columns
19:28 - Multiple conditions in conditional formatting
21:33 - Custom date rules
23:52 - Number comparison conditional formatting rules
25:10 - Custom Number Formats method
27:04 - Text matching
31:24 - Conditional formatting with lookup formulas and data in different sheets
35:29 - Secret bonus rule for you...shhh!
🎥 Related video:
Basic Conditional Formatting in Google Sheets: • Basic Conditional Form...
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollins.com/google-sh...
#googlesheets #googlesheetstutorial #spreadsheet - Наука та технологія
Ben, found your website and channel recently via googling, and like your stuff. More advanced than most, lovely tricks, nice manner. I see you've been inactive for 3 years, good to see you're uploading again. I'll be checking more of your materials. Keep it up, great job.
Thank you! Enjoy the new content. PS the website and newsletter have been active all this while ;)
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 👍
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!
really good explanations, thx
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
thank you so much!! I was already searching for hours on something and thanks to this video, I could accomplish it 😘
Great video Ben!
Thanks, Danny!
Thanks. I learned a lot in this video.
You're welcome. Thanks!
Absolute legend
Thanks!
Thank you 🙏
Grate video! what about aggregative formula such as max? if I want to bold a cell that is the max out of the Colum?
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?
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.
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
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
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
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.