Excel quick and easy - Conditional formatting: let Excel do the work for you | Karin Rodgers | Skillshare

Excel quick and easy - Conditional formatting: let Excel do the work for you

Karin Rodgers

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
8 Lessons (28m)
    • 1. Introduction

      0:41
    • 2. Going over the basics

      5:32
    • 3. Using formulas in conditional formatting

      7:38
    • 4. Alternate coloured rows

      2:36
    • 5. Colouring your weekends

      8:00
    • 6. Removing conditional formatting quickly

      0:59
    • 7. Project

      2:20
    • 8. Thank you!

      0:42

About This Class

A quick overview of the conditional formatting feature in Excel, delving into the formulas to use to achieve interesting and advanced results.

Conditional formatting means that your format will change as soon as a condition is met. This means that Excel will actually do the work for you!! 

In this class you will learn to :

  • highlight rows according to a cell content
  • show alternating rows in different colours
  • set up a calendar showing weekends in colour

Users should have a basic knowledge of Excel before taking this class, as it refers to formulas.

Transcripts

1. Introduction: Hi, I'm Karen. Welcome to my channel. This class is about excel and more specifically conditional formatting. In this class, you'll be learning how conditional formatting can help you to save a lot of time with formatting and have excel do part of the work for you. You will learn how to use formulas and conditional formatting. You will also learn how to set two different lines to alternate colors. And you will also learn how to configure a calendar so that your weekends show up in different colors. So go ahead and jump into the first lesson, and I'm sure you will learn tons of stuff that you will be able to use immediately and all of your Excel files. Thank you very much. 2. Going over the basics: So before we get into how to do more complex things on conditional formatting, the first thing I want to do is show you again how it works at a very basic level. So I'm working on this fall called Project Workbook. He will find it under your project, section off the class for download and you'll be able to follow along or try it on your own . The different things that I could do in conditional formatting are set out up here on conditional formatting on the home tab. I can highlight cells. That's the easiest thing to do, greater than and less than between. So that's for numerical values equal to if you're looking for either. A text value are also numerical value text that contains well, that's pretty obvious, a date occurring if you're looking at the date range and you want to look for dates that riker between certain periods, or, if you're looking for a duplicates, the top bottom, which will give you the top 10 of a list or the top 10%. Same thing with the bottom Andi also values that are above or below average, so they're fairly easy to use where it's going to get more complicated is when we want to start using formulas, which is what we're going to be doing essentially in this class. However, first I want to show you how it work with a very basic range. So I'm just going to select here this Colin of sales and I'm going to conditional formatting and I'm going to go in highlight any sales that are greater than 2000 francs. So just go to highlight cell rules greater than and then here it's automatically going to suggest a value. And I'm just gonna go ahead and type in the value I want. So in this case, I've decided to go to 2000 and then it asks me what I want to fill it with. So in this case, it's suggesting to use light red filled with dark red text, the presets or red, yellow or green. And then you can also go to custom format, which is what I'm going to do just to show you what you can do here and here you can change the font. Wait so you can make it bold or italic. You can change the color of the front So let's just say I want to make it bright orange. I just wanted to stick out. And then, as for the fill color for my cell, I'm also going to change it to a nor INGE, but kind of a paler orange and I click on OK, I click on OK again, and any cell that is above 2000 francs in my file are automatically going to appear in that bright orange color. So that's the easiest way now. I can also go ahead on DA Show, the highest 10 values, so we'll go back in, go back into conditional formatting. Now, one thing you've got to be very careful with is that if you go ahead and say that you want to the top 10 items in this case, it's going to fill them with red. I'll just put them in green because that seems more logical because that's a top values. I'll click on OK, you'll notice that some of my cells are still in bold. Why is that? Well, it's because one format has been applied for one of my rules and the other formats been applied for the other one of my rules because it's actually accumulating both of those rules. So I do have to be careful. And if I want to add a different rule from the 1st 1 I added, I've got to go into conditional formatting. I've got to go into manage rules, and I just want to get rid of this rule here that I've entered earlier on by selecting it and click on Delete Rule. Click on OK, and as you see, the bold has disappeared and my 10 top values are now in green, so it's fairly easy, fairly straightforward. There's nothing very complicated in there. Ah, you can go ahead and play around with it and try the different cell rules. Like for instance, here I could select all of my lines, and I could decide that I want to go and highlight Elton John, for example. So I will goto how that sell rules. I will go to other text that contains or equal to it doesn't really matter one way or another equal to I would just put Elton John and here again, as you see in the preview, it's already showing them up as red. I'll put him angry and click on okay, now that would be very confusing, because I'd have Elton John and Green and 1/2 Tom Tatton values and green. So you know, that would not mean the same thing. So I would have to be careful with the colors I chose. But it's just, you know, just to show you as a very quick example how it would work. The advantage of conditional formatting, of course, is that if I now enter out on dawn on another line, it will automatically turn green here. If I change the value, it will automatically also turn the cell green. So it's automatic, and it means that you can put pointers in your cells that will automatically light up when certain conditions are completed, which is very convenient now. What would happen if we wanted to turn the whole of Elton John's line green? That's when we've got to start playing with formulas, and that's what we're going to be doing in the next lesson. So stick around and I'll show you how it works. 3. Using formulas in conditional formatting: Okay, so let's move on to using formulas in our conditional formatting. I'm still in my project workbook. I am on the top cold, working with dates number two. And as you'll see, it's a little database in which I've got a client number. The clients last name, first name, whether they're male or female, the city in states end zip code in which they live, they're birthdate. And the revenue that I've made on these clients now, what would happen if I wanted all of the clients that were born? Let's say in 1950 What I will do is I will go ahead and select the whole of my birthdate. Colin, I'll go to conditional formatting. I will go to the Highlight cells rules, and in this case, I could try with the date occurring. But unfortunately, as you can see, none of them gives me the option of selecting a specific year. So what I could do is I could go back into conditional formatting highlight cell rules, and I could say that I want any dates that is between, and I could put it in in this way I could put First of January 1950 let's say the 31st off December 1950. And now let's have them in red. That will be perfect. Click on OK and all of my people born in 1950 will be highlighted in red. That's one way of getting around it. Another way of getting around it would be, and I will just get rid of my rule. So go ahead and select the whole of my date calling again. Go back into conditional formatting. In this case, I'm good to go to new rule. I'm going to click on use formula to determine which sells to format, and in this case I'm going to use a formula, which is Cold Year, which is going to retrieve the year off a specific date. I will come and click on the first date because that is the 1st 1 in my selection. I will remove the dollar signs from the one because I wanted to be testing on each separate line, and I'm going to tell it that I want all the dates where the year is equal to 1950 and I'm going to click on formats and I'm going to go tell it what color I want to show my 1950 people up as I'll just click on a random blue for the fill Click on OK, click on OK, click on OK, and there you go. All of my 1950 people are in blue. That's another way of doing it. Now, this way is going to be interesting to us because that is the way we're going to do. The conditional formatting still has the whole of a room shows up. In this case, only the birthdate shows up in blue on. What I would like is I would like the whole line to show up, which would make it stand out mawr and would make it easier to see in my file. So what I'm going to do is I'm going to go ahead and clear the formatting, so I'll just go ahead and delete the rule. I'm going to select the whole of my table. I'm gonna go back into conditional formatting. I'm going to go back to new rule, use a formula, and again I'm going to go ahead and say, equal year off and I'm gonna come and click on the very first cell in Colin H in this case , I'm not going to remove the dollar in front of the age because I wanted to test Colin H all the time, But I do want it to test different lines. So I'm going to remove the dollar in front of the one again equals 1950 and I'll just go ahead and formatted in blue again. Format blue. Okay, okay. And here we go. The whole of my line is now formatted in blue, which is a lot more easy to see than just having one cell. Now, I can do the exact same thing. Let's say I want to make all the clients that are from Texas stand out. So I'm gonna go ahead and select all of my lines back into conditional formatting new rule . Now, I want this to be a different rule to the 1950 ruled. I'm not removing my 1950 rule. I'm just adding another one, so I'll use another formula to determine which sells to format. In this case, I want to go test if Colin f. So, in this case, if dollar F because I always wanted to be calling AF start in the first cell, and I want to test if that is equal. Two. And I'm gonna test for text. So I've got to put it in quotation marks t X and close my quotation marks again. Go back into the four months Al puts the Texas people in yellow. I'll click on OK, okay, again. And here we go. I've now got people who were born in 1950 in blue and people who live in Texas and yellow. Now what would happen if I have somebody born in Texas in 1950? Well, what is going to do is it's going to go in to my conditional formatting, and it's going to start with the first rule. So in this case, because I entered the rule Texas last, it's going to appear on top of the list because every time I had a new condition is going to bump the other conditions down. So because this person lives in Texas, it's going to show up as yellow and not blue, because basically, once it has tested for a condition, it's not going to go ahead and test for the next condition. So in this case, it's just going to say this person lives in Texas. I'm making them yellow so you can go ahead and experiment with all of these different formulas. You contest for text content to contest for date content. In this case, I just tested to see which people were bored in 1950 but I could have made it anybody board in the fifties, which would have been from January 1st, 1952 December 31st 1959 and I would have done that. I can go at it. My rule for 1950. I could have said that I wanted to conditions. So in this case, I've got to start with an and because I want to conditions and they have both got to be filled. I want the first condition to be that the year has to be greater or equal to 1950. The second condition is that the year again Dollar H one has to also at the same time be smaller or equal to 1959. That's both of my conditions. Close my brackets, click on OK, click on OK again and all of the people born in the fifties are now in blue, so there's loads of different combinations to, um set up your conditional formatting Onda. I strongly advise you to play around with it and see how it works. And if you have any questions, just go ahead and open a discussion below, and I will answer your questions as soon as I see them. 4. Alternate coloured rows: in this lesson. We're going to be formatting alternate rows and different colors. We're going to be going on all numbers and even numbers for the room numbers. How do we do this? The first thing we need to do is to select. If I click of little Square up here, I will select the whole of my file, which means all of the Collins and all of the lines, the over one million lines. Then I go into conditional formatting manage rules, and I'm going to create a new rule based on formula. Now we have a very convenient formula, which is called Roe, which is going to retrieve the road number of a cell. And we're wanting to test if that parameter is going to be odd or even. And for that, we've got a formula which is is old or is even so. I'm going to start with an equal sign because it's a formula. I'm first going to test the odd so is odd. And then I want to test the rope and I want to start in the cell, which is my first cell selected. So in this case, dollar A to block the Colin So it's always going to test on Colin A. And then I am going to enter a number one because I'm on the first line, closed parentheses for the Roe formula, closed the parentheses for is odd. And then I go into my format and I'm just going to pick a color any color. So I'm gonna go with a light green. I'm going to click on, OK, I'm gonna click on OK, again and okay again. And all of my old lines are now colored in green very easily. Now, to get the alternate lines colored in a different color, I go back in. I'm good to do the exact same thing. So manage rules, new rule formula. And I'm going to do the exact same thing. Only this time I'm gonna test if it's even. So is even the road dollar a one because I'm testing on the first common closed my parentheses, go to my format and in this case, I'm going to select. Port goes with green. Um let's go for a pale, great click on OK, click on. OK, click on OK, and there we go. I've got one line in green. The next line in gray, etcetera, And it will go all the way down to the bottom of my file very easily. And thats hold, it takes. 5. Colouring your weekends: So for this lesson, we're going to be creating a calendar tool for planning. So I create a new sheet, Onda. I'm going to show you the easiest way to create a series of dates without having to think about how many loans or a colander selecting. So we're going to first set up a series of dates and then we'll move it to the place where we want it to. Ah, reside in the end. So the first thing I'm going to do is I'm going to enter the date of the first of January 2019 because I want to be doing my planning for next year. So I select my whole Colin. Here I go to my fill tool, and I'm going to go to fill Siri's and infill Siri's. It already has detected that I'm feeling dates. I want to go instead, values of one, which means it's going to go daily and my stock value has to be the 31st of December 2019 and I'll click on OK and it should automatically stop at of the 31st of December 2019 or 365 rows, which is very convenient I could also have done that by entering my dates and then dragging down. But then you never know how far you've got to go, and sometimes you go too far and then you've got to come back, and it's quite messy. So with this Phil Siri's tool, it's extremely easy to tell it how many days you want to fill. And if it's days of its weekdays, if it's month or if it's year, weekday is very convenient because it will give you oniy the days from Monday to Friday, which can be quite convenient if you're doing planning for weekdays. Now that all the days have been set out, I want to take them, and I want to move them to a horizontal plane here now that they're all selected. What I'm going to do is I'm going to copy them, and I'm going to go to D five and I'm going to do right mouse click. I'm going to go to Paste Special, and here I'm going to ask it down at the bottom in pay special to transpose. So it's going to take everything that is set out vertically and set it out horizontally, and I'm gonna click on. OK, now I am going to go ahead and select all of my Collins that have been filled in because for the moment, they're not wide enough. And as you can see, there are quite a few. So I'm gonna go ahead and select all of my Collins. Here we go. I've gone a bit too far, but it doesn't matter. Then I'll position my cursor between two of the Collins. Any Colin and I'll just double click to make them the right way. Now control home to get back to the home. Colin and I can go ahead and deletes my first Colin here that I don't need any more. Now you'll say, Why didn't I just do a cut and copy? I didn't do it because the paste special transpose only works if you use copy beforehand. And no, if you use cut. So that's why I did it that way. Now, in my first Colin, I want to enter the name of a person, and in the second Colin, I want to enter the task that they will be doing. What I'm going to do now is I'm going to take all of my dates, and I am going to set them up with the text here to be rotated up. And now I'm going to select all of my Commons again. I could have done that earlier. That would have saved me the hassle of having to do this twice. But that way you can see that sometimes even a seasoned Excel user can mess up and do things in the role larger double click. And that makes my Collins a lot thinner, which is gonna be easier to navigate now. What I want to do is I want to enter the name of a person. Let's say Bugs Bunny, the task will be. We grow carrots and this way I can very easily decide when he's gonna be growing carrots by just selecting here and entering a color for growing carrots. That's one way of doing it. Now we're in a class about conditional formatting. So what I do want to do here is I want to show you how to set up the file so that all of the weekends show up in a different color. So what I need to do is I need to select my Collins all the way here. And then I want to go into conditional formatting, manage rules, new rule and formula. And now the formula I want to use needs to know that my weekdays or weekends. So for that there is a formula which is cold weekday, so I'll enter equals weekday. I want to take the weekday off the cell that contains the date. In this case, I'm going to start in college and see, because that's where I'm at. But I want it to systematically always look at the same line number because I'm formatting my Collins. And so the whole Colin has to be the same color, which means that I always need to look at Line five in this case because that's where my dates are. And the second parameter for this formula, in my case is going to be a number two, which indicates to excel that for me Monday is Day number one and Sundays. Day number seven. I know that you do things differently in the States, but being Europe, this is the way I'm going to do it. And I just want that to be a greater than five, which means if it's equal to six or to seven. In that case, it means it's a Saturday or a Sunday. I'll go ahead and select a format and I'll just put my weekends in shade of blue. I'll click on OK, I'll click on OK again and I'll click on OK again and voila! All of my weekends have not been turned blue. So I showed you how to do the European formula, which is equal weekday reference and then parameter to to indicate that your first day of the week is Monday. And in that case, you're going to test if that formula is greater than five, which will cater for Saturday's, which is Day six and Sundays, which is day seven. Now. The way you would do this if you're in the States where your week starts on a Sunday, is in the following way. You've got two criteria that you have to meet well, either or so you're going to start the formula with an or open your brackets and then test the week day of your reference. And then, instead of using the parameter to you, use the parameter one, which indicates that your week starts on a Sunday and so you want to test if that is equal toe one, which will cater for Sundays or if the weekday off the reference still parameter one is equal to seven, in which case you'll be catering for Saturday's, which is day seven. So by entering that formula, you will actually obtain the same result as US Europeans get with the week day reference to greater than five, so that covers it and that will color your weekends in different colors. 6. Removing conditional formatting quickly: Finally, let's see how to quickly scan through a file and remove any conditional formatting that might be lurking in there that you wouldn't want. You can be on any one of your tabs. You'll go into conditional formatting, manage rules and initially will show you what the rules are for your current selection. In this case, I have absolutely no conditional formatting here. You can click here on the drop down list and you can expand that to the whole worksheet, or you can actually specifically go to a specific sheet. Now, in this case, I know that I've got some conditional formatting on working with dates to. So if I click on that, it will show me my rules. And then I can just go ahead and select the rule and delete it as appropriate. That will allow you to quickly scanned through a file and see if there's anything lurking in there. Before you either set up new rules or, um, before you save it 7. Project: Okay, The last thing we need to see is the project. So, as a project, I would like you to set up a calendar that looks a bit like the one I'm showing you right now on the screen. I will want you to set out dates along the top. I want you to ensure that the Collins that have weekends in them show up in a different color. I chose Gray, but feel free to feel to fill it with another color. And then I want to prepare a training program for a certain number of people. I used fake names of cartoon characters. If you want to do this for real, people feel free. We have five different classes, which are excel, PowerPoint, word painting and photography. So I've gone with different colors for the different classes. And what I would like you to do is if you enter a one in the cell, I would basically wanted to disappear and show up as green. Now, how I did this was by conditional formatting telling it that if the cell is equal to one, show it up is green and also show the font up as green, which makes the fund disappear against the background. So as long as I enter ones in my cells whips, as you can see, if I enter in 11 it's not going to work. But if I enter one in the cell is going to show that while the coyote is going to be following an Excel class from the second of January to the 15th of January 2019 same thing if I enter a number five, Mickey Mouse is going to be taking a photography class over a long weekend on Minnie Mouse is going to be taking up painting every Friday. As you can see, the front always disappears onto my background color. So this is what I would like you to try and achieve with conditional formatting rules. You will have this particular sheet in the downloads for the project so that if you are stuck, you can look at the formulas that I entered. But do try and do it on your own, because the only way of learning is to try on her own, to fail and to try again until we actually succeed. So good luck with your project guys, and I look forward to seeing what you set up in the project section below 8. Thank you!: thank you very much for following this class. I very much appreciate it. And I hope that in turn you will give me a little help by clicking on the follow button. And also by leaving me like and hopefully a little review so that I know what you have light. Don't hesitate to ask any questions you may have in the discussion below. I will be able to answer them. And if you want, you can also go and join me on my Facebook page. You will find the address of that on my profile. And we can interact a bit more closely on Facebook because I can actually respond to you directly. So thank you again for following my class and see you soon in another class.