Excel Conditional Formatting with Formula | Highlight Rows based on a cell value

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

% 13 408

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-courses
Learn the art of Conditional Formatting in Excel using formulas! This tutorial shows you how to dynamically format cells and rows based on other cell values, perfect for enhancing your Excel skills.
⬇️ DOWNLOAD the workbook here 👉 pages.xelplus.com/conditional-format-rows
✨ What You'll Learn:
▪️ Basic concept of Conditional Formatting with formulas
▪️ Highlighting cells based on the value of another cell
▪️ Use conditional formatting to highlight entire rows
▪️ Troubleshooting tips for Conditional Formatting
Conditional Formatting in Excel is a great way to improve the readability of reports. You can focus the reader's eye on the important points and create an interactive element to your Excel data.
For example, you can use conditional formatting to highlight values that are greater, equal, or less than the value in another cell.
I'll also show you how you can properly use Excel conditional formatting with a formula. It can be tricky to get it right. If used incorrectly your conditional formatting will not work. To make sure you can successfully use formulas in Excel conditional formatting, I will share an Excel trick you can use to get it right every time.
00:00 Excel Conditional Formatting with Formula - the logic
03:12 Excel Trick for Conditional Formatting with Formula
04:42 Conditional Formatting based on another cell
05:35 Conditional Formatting Highlight Entire Rows
07:45 How to Copy a Range With Conditional Formatting in Excel
Playlist dedicated to advanced Excel tips & tricks: ru-vid.com/group/PLmHVyfmcRKyxvxehq3fiGjKDsEyy6d4Tz
_______________________________________________________
➡️ Join this channel to get access to perks: ru-vid.com/show-UCJtUOos_MwJa_Ewii-R3cJAjoin
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: lgharani
LinkedIn: www.linkedin.com/company/xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

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

3 дек 2020

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@LeilaGharani 8 месяцев назад

Grab the file I used in the video from here 👉 pages.xelplus.com/conditional-format-rows

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

The link is not working

@MrNoBSgiven 2 года назад

Leila, you are a true Goddess of Excel. Same as for others, you demystified the conditional formatting for me today. Thank you.

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

I’ll never get the 2 hours I spent last week trying to get this to work BUT you have solved the headache that I got from that and I’ll NEVER get this particular headache again. As I’ve stated before YOU ARE THE BEST!!!!!

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

❤I watch your videos every time I get stuck in a formula and cant thank you enough. You always put the exact solution in to what I am missing. This one you had me remove the “$” from the lookup reference and POOF! Fixed. Thank you so much!!! Love your videos so much.

@dkt22190 3 года назад

Your videos have genuinely helped me demystify Excel! You have been that tutor that I was always looking for! Genuinely useful and amazing videos! Please keep doing this. Even your shorts have been extremely useful.

@mpart_woodlathe-stuff 9 месяцев назад

Thank you very much Leila. I must've spent 2 days (off and on) trying to get conditional formatting to work. That is, until I saw yours with all the little nuances that were causing me so much grief. Again, thank you

@LeilaGharani 9 месяцев назад

Glad it was helpful.

@sachinrv1 3 года назад

The keyboard short cut key for conditional formatting is (well it's not that short cut as such) ALT+H, L then N for conditional formatting new rule, with keyboard select last option "use formula to determine" and then ALT+O for formula bar activity. Using this will make you look like a Pro (hahaha) Thanks for sharing Leila :)

@andywrentmore856 2 года назад

I've written excel macros for over 20 years and never knew the tips I learned from this video. Personally, a game changer.

@harishn3331 3 года назад

She is unbelievable!!! The is the best example to show how the explanation should be made on a particular topic. Best tutor for excel Thank you Leila

@davidchip6075 Год назад

I was looking for one solutionf googling the resolution, but after one and a half hour, I found your video and helped me in 5 minutes.... thank you!

@egasbritesdasilva6773 3 года назад

thank you for your video makes my work easier and more fun to read, greetings from us in Dili, Timor-Leste, keep up the good work

@robmoore2780 2 года назад

I have been working in Excel now since it first came out and replaced SuperCalc (anyone remember that?). I never could get my head around conditional formatting until I watched this video. thank you so much, it was so simple to follow.

@ogrammer Год назад

Thank you so much. I was having trouble getting my formatting to apply to the other rows. This was the 4th video I watched and the only one that covered the removal of the $.

@mezianeremila454 3 года назад

Again it took me months not to say years of practicing to know all these tricks and you compiled it a short 10 mn video, great job as ever thank you very much Leila...

@LeilaGharani 3 года назад

Great to hear!

@mattschoular8844 3 года назад

I have always found Conditional FormTing via formulas to be challenging. This is a great explanation. Nice shout out to Mike Girvin and ExcelIsFun. You both deserve more credit than I can offer with a "Like" and a comment. Keep up the great work, it is appreciated.

@LeilaGharani 3 года назад

Like and a comment is all the credit I need. Thank you for watching!

@new_moon_12 11 месяцев назад

Your video's a life save. Thank you so much. This is such a little bug, but a real big headache. Was literally trying to figure this out for over half n hour.

@meshell7987 3 года назад

Thank you, Leila, for explaining everything so simple and in easy to understand way! Also, your voice is so pleasant, you are not in a hurry, and I can understand everything without the need to give a pause in order to assimilate. You are my favorite excel trainer :) Thank you and keep the same way!

@surindersingh_A25 11 месяцев назад

Hello ma'am I ve been watching some of your videos for a long. At first it was hard to me to understand, gradually I am understandable to that. Your ideas are advanced at the same time works for me . Now I use the way you say. Thank you so much for creating stuff like that.

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

Thank you, thank you, thank you!!! I had watched three other tutorials before this one, and yours was the first one that I could follow and it worked.

@romeshnixByte Год назад

i got the benifit of watching this video as i was needing to colour some cells automatically. Thank you so much

@andrevanopstal2143 2 года назад

I have been using this method for a long time. The way you explain this is to the point and very easy to understand. Congratulations.

@CaseyToland 3 года назад

I always struggled with these and this video made it so much easier to grasp. The trick to copy the formula and drag it across cells to see how it performed was perfect. Thank you.

@godfredotoo1555 2 года назад

I am not only fascinated about how you have explained this lesson, but I am also impressed about your video editting skills. You are awesome.

@prin4520 3 года назад

Just wanted to thank you, your tutorials have been my favorite because you explain things so usefully! ❤️

@charlescaison4376 Год назад

Your videos have saved me many times. This one included. I am grateful that you are out there making your content.

@LeilaGharani Год назад

I appreciate that, Charles!

@piggyware Год назад

Thank you, that it starts from the first cell in the selection was an important lesson for me.

@ryco2000uk 2 года назад

Leila you are so good, you are amazing. nobody explains excel better than you, thank you so much ive learnt bundles

@ijirving 2 года назад

This just saved me such a headache at work. EXACTLY the problem I was having. Thank you!

@miguelplg19 3 месяца назад

Thank you for creating and posting these tutorials. Your knowledge has helped me several times over, and your teaching method is clear and very precise. Thanks again!!!

@djbcybertench 3 года назад

Your tutorials are fantastic ! The right pace and right balance of enthusiasm. I can't see any of your tutorials on Excel cell styles and / or workbook styles (?) I've used these but as always, via your tutorials, I'm probably "scratching the surface" of what could be done. Comments or tutorials welcome...

@LeilaGharani 3 года назад

Wow, thanks David!

@yashsharaf8819 3 года назад

Thank you Leila, I tried using this the day before but was doing the "fixed cell reference" mistake. then I did the entire thing without formula. Never too late though.... It will be very handy now... Thank you for your contribution...

@LeilaGharani 3 года назад

My pleasure. Hope it will come in handy.

@cassioalexan 3 года назад

I wish I knew that when I was learning the basics of Excel, trying to find the correct way to apply this Conditional Format! Thanks, Leila!!

@yogibearstie 3 года назад

With all of the knowledge you have generously shared in so many videos I was able to create drop down lists and date fields in a Word document and then copy them into an Outlook email and save it as a Template. I now have a very reusable form when creating assignments. I couldn’t find these objects within Outlook, but copy/paste works. I bet you would do it 100x smarter though.

@jimtsai7545 Год назад

Very clearly explained. Followed the instructions and got it done in one try. 🙏🙏🙏

@adjarahma706 3 года назад

Best tutorial. I was stoke with my assignment and looking for a clear explanation for the conditional formating using formula and you are the one who explained it very clearly. May ALLAH bless you.

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

This is great, thank you Leila! I can binge watch your channel for hours, I learn so much!

@yogibearstie 3 года назад

Thank you. Your video reminded me how to fix some cells with broken conditional formatting formulas. All better.

@rajeevguj 3 года назад

The row highlight trick was show stopper. Thanks for the tip.

@LeilaGharani 3 года назад

My pleasure.

@TylerStanczak 3 года назад

This is super helpful. Appreciate this tutorial on conditional formatting :)

@LeilaGharani 3 года назад

Glad you enjoyed it :)

@santhoshsubbiah8120 2 года назад

Thankyou so much leila your courses never cease to amaze me, so calm and insightful at the same time

@w2class 3 года назад

Great clarification of how to apply conditional formatting. Thanks, Leila.

@LeilaGharani 3 года назад

Glad you enjoyed it, Anthony!

@hemantbhalsing6823 2 года назад

Really helped me. I require this kind of formatting in my workplace. This was super helpful. Thanks a ton !!!

@abdulquadir9342 Год назад

Hi Leila, With reference to my query below ... Yes, I found the way do it using $. Thanks, MAQ

@michaelomosebi6374 3 года назад

You probably just saved me my job with this video. Thanks

@LeilaGharani 3 года назад

Glad it was helpful, Michael.

@tapanjayswal1005 3 года назад

I always struggling with condition formatting, great help, thanks

@mom4818 3 года назад

Leila you are very skilled trainer. I find it hard why would anyone give you thumbs down. Keep up the good work.

@LeilaGharani 3 года назад

Thank you!

@TheSlarge 2 года назад

A phenomenal piece of work. Thank you.

@Ghoopiman Год назад

Thank you very much i need to takeout the 10% hold back i just found it thanks again 😊

@artsmith1347 3 года назад

This was very helpful. I have found the mysteries of conditional formatting to be confusing for years. Thank you.

@LeilaGharani 3 года назад

Glad it was helpful!

@tahajasden1948 2 года назад

nobody explained the logics behind it but you did thank you.

@rossanderson5218 3 года назад

I had an excel test for job towards beginning of year that had the second conditional formatting you did in, (highlight whole row).. I remember I got completely lost,I even googled trying to workout how and to do it and couldn't find anything to help. I still got the job but I was dumfounded by that question and you've just made it seem so simple! I feel special now haha but thankyou, it is so obvious. bugga! keep up the good work!

@LeilaGharani 3 года назад

Hope I didn't stir up some unpleasant memories 😊

@rossanderson5218 3 года назад

@@LeilaGharani haha no not all! all good and now I know for next time :)

@Jules-ip1jh 2 года назад

Thanks so much for this, I have been puzzling over this for days, should have come here first! So helpful!

@LeilaGharani Год назад

You're very welcome!

@daecarman67 3 года назад

You are my go to for anything Excel. Amazing tutorial. Thank you! This helped my situation.

@djfmate 3 года назад

Fantastic video. I have recently encountered the same problem you have described here and your explanation of why it occurs is perfect! Thanks Leila. Greetings from David in Australia. 😉👍

@LeilaGharani 3 года назад

Glad the video is helpful :)

@ubaidyaseencubaidyaseenc8207 Год назад

Truly I was looking for this one today as to get an idea to apply the formatting to the entire row Thanks 🙏🏻

@LeilaGharani Год назад

Glad it was helpful!

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

Just used it in one of my sheets. Worked nicely!

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

Great to hear!

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

This is a great tutorial, thank you! I learned a LOT.

@walllee6470 11 месяцев назад

Leila Thank you so much for making my life easier by teaching me Excel

@LeilaGharani 11 месяцев назад

You're very welcome!

@surfer3662 3 года назад

Oh dear Leila my teacher ... This is exactly what I am currently looking for !! Thanks a bunch !!

@surajbhardwaj428 2 года назад

Very useful trick... Simple but very useful.... Thank you Leila...🤗👌

@michaelsmith935 2 года назад

This has always been difficult for me to do. Nice clean solution. Thanks. Great video!

@AccountingandQuickbooks 3 года назад

Conditional formatting is my favourite tool in excel thanks for sharing 👍👍👍👍

@LeilaGharani 3 года назад

Very very welcome!

@vijayarjunwadkar 3 года назад

I missed your videos for last 2 weeks due to some work occupancy, but going to watch them soon! And this one of course is best as usual! Your appropriate examples with just enough explanation makes every task so simple to perform! Keep it up! 😊👍

@LeilaGharani 3 года назад

Welcome back! Thank you Vijay 😊

@vijaygusain119 3 года назад

I knew it already though but the visualising it on the sheet is something new for me...Thanks again and you look lovely as always😇

@LeilaGharani 3 года назад

Wonderful!

@RogerStocker 3 года назад

Fully agreed! I can just copy/paste!

@silverlicious2086 3 года назад

Very useful tip. Thanks Leila. Cheers from Loveland, Ohio. (city near Cincinnati)

@kolavithonduraski5031 3 года назад

very helpful for highlighting and visualizing the importance of some numbers in bigger sets of numbers 👍 Thanks !

@LeilaGharani 3 года назад

My pleasure!

@jewelhannathomas Год назад

Leila, your videos are very easy to understand. Thankyou!

@rohitshukla4652 3 года назад

Leila your teaching style and specially your voice both are just amazing

@LeilaGharani 3 года назад

Thank you! 😃

@Eibo63 Год назад

Thank you so much. This was very easy to follow and solved my problem.

@farhadhossaindu 3 года назад

Thanks for explaining a difficult concept in a step by step approach. Great explanations.

@LeilaGharani 3 года назад

Glad you liked it

@robertthompson7334 3 года назад

Thanks, Leila for another great video. One quick correction. It's minor, but screw things up if that's the problem. Rule 2 (at 1:54) is often correct, but not always. The correct rule is that the format is always from the point of view of the cell which has the focus. In the example given (and most times this is true) it happens to be the first cell of the selection (cell B6 as noted just below the word "Clipboard" in the home ribbon). But depending on how you select your range, it could be the last cell or some other cell.

@sethjuarez333 3 года назад

Love what you do, always concise and helpful. I would love you to add another level to this. Copying the conditional formatting when the second set of data is on another worksheet. Currently, I have to copy numbers and formatting to the second sheet, then adjust manually the cell reference on the second sheet. Is there a trick you could show about managing conditional formats across multiple sheets?

@jackthegod9988 2 года назад

very cool you gave respect when its due to mike girvin . nice video

@doctorsangeeth Год назад

@Leila Gharani, you make the most awesome tutorials of any genre. Short effective and beautifully presented. Thanks a lot.

@AhmedAli-uy8el 2 года назад

Very clean and simple your learning method. I have learn very well.

@komninoskamamis1098 Год назад

Just Excellent! Thank you Leila and may God bless you

@LeilaGharani Год назад

Our pleasure!

@mehdiJoe 11 дней назад

Saved my life Leila jaan! merci

@LeilaGharani 10 дней назад

My pleasure :)

@bestscenes1469 3 года назад

This Tutorial is super Helpful. Thanks a lot Leila

@diggydubb 3 года назад

This was a great help!!! I just could not figure this out. Thanks!!!!

@Pygmygerbil88 3 года назад

i love Leyla. thanks for sharing knowledge.

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

Keep up the great work! So helpful, and easy to follow. Thank you so much for your hard work!

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

Thank you! Will do!

@NaveenTamtaIamExcelVBA 3 года назад

No words defining you... you are just incredible.. you are amazaing

@darylseaton6493 3 года назад

Hi, you are an extremely proficient excel user. My compliments to you. However, your video involves the use of mixed cell referencing, which is somewhat complicated. I think taking a moment to further explain how to correctly incorporate mixed cell referencing takes this tutorial to another level. It's always okay to reinforce good habits, practices and knowledge. Many thanks for your past and future efforts.

@LeilaGharani 3 года назад

Thanks. Yes. It is a confusing topic. I have a separate video on the referencing. I will add it to the description of the video for now.

@darylseaton6493 3 года назад

@@LeilaGharani Many thanks.

@abdulquadir9342 Год назад

Hi Leila, It has been an absolute pleasure to learn many skills in excel from your lovely tutorials. I have a query and would appreciate very much if you can elaborate and throw some light in solving it. I have a grid with sales people (in rows) and sales totals for each month (in columns). The query is when a particular sales person is selected from a dropdown list to see the performance for a particular month, how can we use conditional formatting to highlight the particular cell that contains the sales value based on the row of the sales person name and the column of the month. Only the cell must be highlighted, not the entire row and column. Appreciate your help on this. Thanks MAQ

@thomasponniah8848 11 месяцев назад

Thank you. Explained in way that can be easily understood.

@LeilaGharani 11 месяцев назад

Glad to hear that!

@MukulDey-lv1yn Год назад

Yeah, it hepled me but as i didnt have dollar sign in my input, so i had to figure it out for myself the hidden trick. however it was helpfel. thanks darling.

@devendranarayanverma4726 3 года назад

I was really confused before but you have cleared em

@user4742-t9d Год назад

Thank you Leila for the information🙂

@barryspry6558 3 года назад

Thank you, Leila. I always enjoy your videos. I was surprised by one of your older videos showing Bryon Smedley. I worked at the same company with Bryon years ago. He was a company software trainer. I always enjoyed his classes, as well.

@LeilaGharani 3 года назад

Oh wow! Bryon is the best.

@mohammadashiq9996 3 года назад

Thank you Leila, I love the simplicity.

@MehmetAliMD 3 года назад

I can't thank you enough, Leila. I could not download the excel file giving error 'Page Not Found' just wanna keep you posted :)

@LeilaGharani 3 года назад

That's strange. I just tried it and it works fine. Here is the link again: www.xelplus.com/excel-conditional-formatting-rows

@SushilShrestha-ur6uw Год назад

Thank you very much for your informative tutorial.😊

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

Thanks, this worked for me - and really lovely tips

@andrewmoss6449 3 года назад

Leila, I am sure you will be making a video shortly about the new LAMBDA function that was announced yesterday. Very exciting! 🤗

@LeilaGharani 3 года назад

I am! Coming in the next weeks :)

@sparcnz 3 года назад

Another great video and the explanation. Would be grateful to get an explanation video on how to grab data from a sheet that has a date range, a person (from about 20 people) and report attendace(sick, al, and other types of leave). Could supply an example. Thanks

@chh8860 3 года назад

If it's Thursday morning (i.e. 5:15AM) … It 'must' be w/Leila … and a mug of black coffee The major takeaway for me was the Pro Tip of 'helper cells' … very clever … and I expect will be very helpful as I spend too much time jumping back and forth, i.e. editing the conditional formula … back to the spreadsheet … no result … back to the conditional formula … edit again … back to the spreadsheet … no result (again) … on complex formulas I feel like I am stuck in an endless (frustrating) loop as I bounce back and forth. Another simple explanation and useful tip … thank you … thank you … thank you!

@LeilaGharani 3 года назад

Thank you. I've been there myself. It's quite frustrating!

@mientertainment1086 Год назад

Hi i saw many videos on the same topic , Actually, you did the best

@LeilaGharani Год назад

Thank you so much 🙂

@osamasaid8476 3 года назад

YOU ARE GREAT TEACHER

@fernando5166 Год назад

Very grateful for the videos

@mcd22630 3 года назад

Another in a long line of excellent videos that make Excel an even better tool analyzing data. I'm embarrassed that I am unable to include my usual irreverent remark as part of this comment.

@LeilaGharani 3 года назад

Thanks for commenting - even if it's relevant 😁