Advanced Conditional Formatting in Google Sheets

Подписаться 10 тыс.

Просмотров 18 тыс.

50% 1

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

Наука

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 33

@my_pleasure_in_your_leisure 6 месяцев назад

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.

@benlcollins 6 месяцев назад

Thank you! Enjoy the new content. PS the website and newsletter have been active all this while ;)

@vids9647 6 месяцев назад

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.

@benlcollins 6 месяцев назад

You're welcome! Thank you 👍

@LauraORourke 29 дней назад

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!

@TahaMirzPC 4 месяца назад

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

@VirginieWGBM 2 месяца назад

thank you so much!! I was already searching for hours on something and thanks to this video, I could accomplish it 😘

@ThepExcel 4 месяца назад

really good explanations, thx

@dannylayton317 6 месяцев назад

Great video Ben!

@benlcollins 6 месяцев назад

Thanks, Danny!

@johndavidthacker 6 месяцев назад

Thanks. I learned a lot in this video.

@benlcollins 6 месяцев назад

You're welcome. Thanks!

@edwardkiddle5389 5 месяцев назад

Absolute legend

@chadwatkins6914 6 месяцев назад

Thanks!

@benlcollins 6 месяцев назад

Thank you 🙏

@xder6k 2 месяца назад

Grate video! what about aggregative formula such as max? if I want to bold a cell that is the max out of the Colum?

@jeffmattheis Месяц назад

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?

@WenSaiPanther Месяц назад

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.

@robertnwardle 6 месяцев назад

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.

@benlcollins 6 месяцев назад

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.

@robertnwardle 6 месяцев назад

Thank you Ben. Will try your suggestions

@adamagave 5 месяцев назад

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!

@benlcollins 5 месяцев назад

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!

@adamagave 5 месяцев назад

@@benlcollins awesome thank you for the speedy reply, I'll take a look! Much appreciated

@Nerosus 5 месяцев назад

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?

@benlcollins 5 месяцев назад

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.

@Nerosus 5 месяцев назад

@@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

@RussellTaylorSupernova522 4 месяца назад

Great video, thank you. The correct title to help me find relevant content

@grahamlees4394 6 месяцев назад

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...

@benlcollins 6 месяцев назад

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.

@grahamlees4394 6 месяцев назад

@@benlcollins Thanks Ben.