Use Excel Conditional Formatting to Enhance Data Analysis | John Smith | Skillshare

Use Excel Conditional Formatting to Enhance Data Analysis

John Smith, Business Owner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
35 Lessons (1h 19m)
    • 1. What Is Conditional Formatting?

      3:49
    • 2. More Examples of Using Conditional Formatting

      2:53
    • 3. Excel Knowledge Prerequisites Before Taking This Course

      1:01
    • 4. How to Remove Conditional Formatting

      1:59
    • 5. Greater Than

      1:50
    • 6. Less Than

      1:36
    • 7. Between

      1:10
    • 8. Equal To

      1:46
    • 9. Text That Contains

      1:32
    • 10. A Date Occurring

      2:49
    • 11. Duplicate Values/Unique Values

      2:53
    • 12. Top and Bottom X Amount of Items Conditional Formatting

      1:14
    • 13. Top and Bottom X% of Items Conditional Formatting

      1:24
    • 14. Above and Below Average Conditional Formatting

      1:27
    • 15. Gradient and Solid Data Bar Fills

      3:41
    • 16. Introduction and Explanation of Icon Sets

      1:26
    • 17. Directional Icon Sets

      1:03
    • 18. Shapes and Indicator Icon Sets

      1:35
    • 19. Ratings Icon Sets

      1:05
    • 20. Conditional Formatting Based on a Specific Cell (Highly Recommended)

      1:40
    • 21. Using Conditional Formatting In Filters

      1:59
    • 22. Order of Conditional and How to Change It

      3:17
    • 23. Copy and Paste Conditional Formatting in Different Sheets and Workbooks

      6:09
    • 24. Introduction to Creating Your Own Custom Conditional Formatting Rules

      4:24
    • 25. Before Creating Custom Formulas You Need to Know How to Lock Rows and Columns

      2:45
    • 26. Highlight Cells Containing Time Information Using Formulas

      4:05
    • 27. Creating Custom Icon Set Ranges

      2:01
    • 28. Highlighting the Whole Row Based on a Column

      2:52
    • 29. Dynamic Way of Selecting Whole Row Based on a Cell

      2:15
    • 30. Comparing Values In Two Columns Using Formulas

      1:30
    • 31. Comparing Two Different Lists For Matches or Mismatches

      2:44
    • 32. Changing Colours of Alternating Rows Using Formulas in Conditional Formatting

      3:05
    • 33. Finding Duplicates Using Formulas in Conditional Formatting

      1:06
    • 34. Finding Duplicates Using Criteria From Two Columns

      1:30
    • 35. Using Conditional Formatting to Find Discrepancies

      1:43

About This Class

What is conditional formatting and why is it useful for you?

Conditional formatting allows the user to format a group of cells based on one or multiple criteria. This means that you do not have to format the cells manually and you can let Excel do all the work for you! Also, if there is change in the data Excel will automatically change the formatting, if the cell will now meet a different criteria.

Most frequently I use conditional formatting to allow me visualize the data, meaning that I can start analyzing the data before even looking at the values themselves. This means that you can increase your productivity working with Excel to a high degree using conditional formatting.

You can preview the introduction lectures in order to see some of the applications of conditional formatting in order to get a better understanding how it can be used.

About this course:

In this course I show everything step-by-step, meaning that I will be showing each single button that I press. By doing this I want to make sure that you memorize all the steps and can apply conditional formatting in your own Excel spreadsheets.

This course is designed for a beginner/intermediate level. You need to have Excel fundamentals, meaning that you will have to know how to open an Excel file, save it and other similar fundamentals. However, you do not need to have an advanced knowledge of Excel. The most advanced part of this course is when I will be teaching you to use formulas to make a condition. However, I will explain each formula in depth, meaning that you do not have to worry that you will not understand it.

However, if you are an advanced Excel user, you might still want to go through the course in order to refresh your knowledge and possibly learn a couple of new tips.

By the end of this course you will be able to:

  • Become more productive with Excel and save time
  • Enhance the data analysis capabilities with conditional formatting
  • Learn how to use conditional formatting
  • Perform multiple conditional formatting rules to the same group of cells
  • Use conditional formatting to highlight group of cells
  • Use conditional formatting top and bottom rules
  • Use conditional formatting icons to highlight cells
  • Use conditional formatting based on text rather than simply numbers
  • Learn how to use formulas in conditional formatting
  • Create various custom rules using formulas
  • Edit existing conditional formatting rules
  • Impress your manager, colleagues and clients

Other benefits from taking this course:

  • You will get lifetime access to this course
  • This course is frequently reviewed and you will get access to the new lectures for free
  • My help is available to you, if you get stuck or have a question

Transcripts

1. What Is Conditional Formatting?: welcome everyone. And I'm super excited to talk about Excel. Conditional formatting. I will be going through the formatting in Excel 2010. But the layout off Excel is very similar in 2007 and 2013 as well and possibly also in the future. The earlier versions have a bit differently out, but the concept still remains the same. So what is conditional formatting? It is exactly as it sounds. It formats one all multiple cells, group of cells based on certain conditions. And I'm going to Demas demonstrate here Some examples. I have six rows off identical numbers. So, for instance, you can see the first. So is the same image one of those columns, and I'm gonna show you six different conditional formatting types. So I select one column conditional formatting, and we'll do data bars and you can see here based on the size it takes a look off at the highest number of lowest number and then makes a bar appropriate to the size. So this number is the largest, and you can see the whole bar is filled, whereas this one is the smallest. But that's why you can barely see it. Now we can do cholera scales, so it's simply assigned scholars, and we'll look at that a bit later. Ah, we could do different color skills. We can also do Aiken's. So, for example, like this or indicators, Or we can assign multiple rules to the same cells. So, for example, let's say top 10 should be green Phil Conditional formatting bottom 10 with read Phil, and you can see he sells highlighted. So these numbers are all random at the moment. And we can, uh, probably shouldn't do like this soared most the largest. And you can see now it makes more sense so you can see the as the numbers increased the length off the data bar increase as well. Here, the coloring is from red, orange, yellow, light green, dark green And here, similarly from dark red toe light red, white, light green and green. And here you can see some arrows. Red arrows, yellow arrows, green arrows the same way with you circle. Send in the signs and here you can see top Ah, bottom 10 and tucked in the So I hope this gave you a small inside what conditional formatting is and hopefully see you guys in the next lecture 2. More Examples of Using Conditional Formatting: I'm going to talk about more, uses off conditional formatting and see how useful it is. So, for example, in this sheet I have my lemonade stands or 1/2 a small business, and here you can see the revenue expenses. The Prophet and I have used conditional formatting to highlight Saturday and Sunday and make them dark because, let's see, I have shut down the business on weekends. So I have done data bars here. And you can see, for instance, Monday's small business all like few sales were rescues. The is big and also their expenses. Here I have done a double room, so I have done the color skills as well as the Aiken's. And here are some grass. But this course is not about the graphs. So this shows, or this allows me to spot their values information straight away so you can see straightaway without thinking. The red is minus the smallest number. The dark green is the highest number and most profitable day. So it just a visual representation of the data, and I'm going to show you what I called interactive conditional formatting. So here we have E that's it. Product code and then we have four different months now. Here are the rules that you can set and all of this will change. And this is done by conditional formatting. So let's say I want product for And there you go it highlights product for and then when the intercept, the cell is a different color and in the same way I can I meet a drop box and I can select their for instance, February on you can see it changes to this. So so again see straight away what you are looking for. And I have noticed that managers really, really like this kind off interactivity on the easiness do analyzed the data so to you guys in the next lecture. 3. Excel Knowledge Prerequisites Before Taking This Course: So now you understand some of the applications of conditional formatting, as I have shown you a couple of examples now, I wanted to briefly mention the prerequisites off discourse so they are not big. This is an intermediate Excel course. That means that I won't be talking about the basic stuff in excel. So, like how to say the fire, where the ribbon is, how to make filters and all that kind of stuff. You need to be familiar with Excel, but you don't need a very advanced knowledge of excel, and we will be talking about some formulas lay true at at the end of this course. But I will talk about the formless, how they work, so you don't need to worry about it, so let's get right into it. 4. How to Remove Conditional Formatting: before digging deeper into conditional formatting and starting explaining everything about it. I wanted to show you how you can ah, clear the conditional formatting in case you mess up and you won't know what to do. So there are three different ways you can do it. So one of them is you select the range, you want to delete the rules and then you go here under home tab, go to conditional formatting. Here you can see clear rules and then the first auction clear rules from selected cells. Press that and you can see everything becomes blank. Ah, now, the other way you saw here. But I'll get to this later because this will clear for the whole shit. So be careful if you have a re amount. Siri can maybe by accident press this one. So be careful about that. But you can also press manage rules and then it's as current selection select here this worksheet and you can see all off the rules that you have. Ah, put into this this sheet so sheet one and you can select and press delete. Cool, apply and you can see this column, uh, doesn't have anymore. And then the final one, which I have briefly mentioned, is this one which will clear all conditional formatting from the whole sheet. So she won. So see you in the next lecture where? Well, where We will start talking about the conditional formatting and how to create it. 5. Greater Than: in this lecture, I'm going to talk about how to highlight cells that have a value greater than you set it to be. So here we have an excel sheet where you can see student. There are 50 stewed 15 students and each of them to get test, and here are scores out of 100. So now in this section, I'm going to talk about different ways to highlight cells. Eso go to home tab, conditional formatting, and here you can see highlight cell roots. So the 1st 1 is great through them, and I forgot a crucial thing, which is to highlight the whole range you want Teoh, apply the conditional formatting, and so select that press conditional formatting great through then and then it will calculate a value on you can see it's below the average because the average is 69. But let's see, I want to highlight numbers greater than 90 because they will get, for instance, a star or the highest greed. You can see that 90 is not included because it's greater and not equal. So just greater than, and you can select here the formatting. So let's live light red filled with dark red text. And there you go. You can see without looking at the values that it has highlighted two cells so 99 100. 6. Less Than: Hey guys. So now you have learned how to do the greater than conditional formatting. So let's go clear on the rules. And now we will be talking about how to create conditional formatting for less than so. In this scenario, I want to highlight cells that are, let's see, less than 40 or 50 s o. I want to take those students two additional lessons and probably increased their greats in the next test. So we'll use the conditional formatting button again. Will be using this button throughout the whole course, highlight cell rules, and then the next one is less than so. Let's say 50 and you can see straightaway. It will choose all the values. And remember, I have to select the whole range. What this rule will be applied. And then again, you can select different formatting style. So as you can see you can. Or you can even try a custom of format. No, leave this as red Andi. Make it more visual. So, for example, if you have several conditional formats in the same sheet, let's see greater than and less than you might want to have them in different colors so you can identify the data much easier 7. Between: in this lecture, I'm going to talk about how you can create or highlight cells between a lower bound and our upper bound. So anything in between. So let's say you want to highlight anyone that is between 60 and 70% off their score, so select the range and, like always go under conditional formatting highlight cell rules. And then the next button is between. Now you're simply a type 7 60 70 and here again, you can select different colors, actually, usually if you have only greater than lower than an in between. I usually go for these three given colors so light red for lower than yellow. So for in between and green Phil for high numbers greater than so. There you go and you can see the do you scores between 60 and 70 being highlighted with a yellow color 8. Equal To: in this lecture, I'm going to talk about how you can make Excel search for a specific freeze or equals two a number. So, for example, here I want to highlight, uh, hundreds. So everyone who got the highest possible score. So I go under conditional formatting, highlight cell rules on and equal to you can see here off 500 you can see that it will highlight the exact number. This also works for words. So let's say I will type here. Hello, world. And let's imagine I have different text. But I assure you, with this one, how it works so conditional formatting highlight cell rules equal to and then I can type her low world, and you can see that it will, uh, highlight the cell. But you have to know that you have to type everything you want in the cell. So that means it has to be exactly the phrase. And for example, if you would miss type it or, for example, let's see here I would add a double space or oh, sorry, or a space. Afterwards, it will remove the conditional formatting because this cell does not match your phrase with which is with a space at the end of it. So I hope this is useful and see you guys in the next lecture. 9. Text That Contains: in this lecture, I'm going to talk about how you can highlight cells that contain a certain number or a phrase. So you select the range and then conditional formatting highlight cell rules text that contains. So that can be either a number or text and that see, I want to highlight anything, starting with a nine or containing a nine so you can see 99 90 49 69. And you can don't more u typed less information you highlight because obviously, if you type more, the freeze gets longer and fewer data sets will match your data. So let's do that. And, for example, AL type some freezes Hello, world and world. So if we select that high white cell roots again text that contains and for instance, I had in the last lecture type have a world, so it selects that if, for instance, I delete well oh, hello and I only leave world that shows both of them for example containing and no oh are no so world and world 10. A Date Occurring: in this lecture, I'm going to talk about a very cool thing in conditional formatting, which is the date format. So here we have a different example. So here we have, let's say a business and I'm recording this on a Friday 20th of February. Andi, here you can see the different days of the week the dates, revenue, cost, profit. And we will be taking a look at this column. The date caller here, you have to make sure that the type off the field is date. But usually if you type the date in this format, it will automatically make the field change the type to date format. So if I go here, it's General General. That is usually the case. Unless you type something like that. So select the range. Conditional formatting highlight cell rules at date occurring. And here you can see the different options. So you can see that yesterday has been selected today, tomorrow, etcetera, etcetera. So let's say today and speak green on the reason why I think this is very, very cool is because it will change whenever I reopen or yeah, When I reopened that say tomorrow and disassociate on, I can demonstrate this. So what did the US Excel looks at your set time on your computer? So let's say I'll just do this change date did in time and let's say we move back to past Boom. There you go. Now it says Prime on Thursday 19th because I change my calendar. So obviously you would not do this, But I'm here just demonstrating that if I let's say open it on Tuesday, it would highlight that ro o that self. But let's move and back to were supposed to be and you can see this is very good. I use this for my finances, but you can you There are many different uses for it. So if you have a long list off different dates, this highlights and then you can just, uh, find the rose you want to look at much easier 11. Duplicate Values/Unique Values: in this video, I'm going to talk about the way you can highlight duplicate values or unique values. And in case you don't know, unique means values that occur only once. So we will be looking at the Revenue column. So let's select on the range conditional formatting highlight cell rules and press duplicate values. So the first option is duplicate. And that means that basically it occurs more than once. Eso you can see 1005 100,500 it doesn't have to be in order. So, like you can see both of these rules have 1005 100 both of the zeros I can just show you. Let's see, I switch Yes, on 400. And I need Teoh change the references here she four. Actually, you can just do it like this. There you go. So if we do it again, conditional formatting highlight duplicate values and you can still see that 1005 100 is highlighted, even though they are not right next to each other. And the other thing you can do here is a unique, which means it occurs only once. So 1004 100 1250 and I usually use the duplicate and I used them in order to highlight grows where there are duplicates, obviously, and I do not expect there to be, so I use it to find my own potential errors and try to investigate why that is the case in this case. Obviously, revenue can be more than once can have the same value more than one day. But in some cases you expect only to have one value, and then you want to investigate why there at the value curse more than once and obviously you can delete the duplicates if you go to I think it's in data remove duplicates, but this will just say continue with the consolation. This would say why this duplicate that to duplicate values found and removed, five unique values remain, and then you don't really know well, where which rose were deleted. And then you can't investigate why the error occurred in the first. Please 12. Top and Bottom X Amount of Items Conditional Formatting: in this lecture, I'm going to talk about top and bottom rules off conditional formatting. So here we have the example off grades again, the students and their scores. And now let's say I want to highlight the top three on bottom three students out of all of these, so select the range. Like always here, under home tab styles. Conditional formatting. You can see top and bottom rules. Now we will start with top three items, and even though it's called top 10 items, you can select how many you want to select or highlight. So if you press that here, you can see it automatically the extent. So let's died three. And let's do that with Green Phil now, the same way we can do the bottom three. So here you can see bottom 10 items. But again, if we type three items, it will select three items or however many you want to do 13. Top and Bottom X% of Items Conditional Formatting: In this lecture, we will talk about a slight variation off top and bottom items. So in last lecture, we reviewed how to select the X amount off items. But here we will talk about percentages, but this is not the best example because there are only 15 students, but I will still show you. So select the range and conditional formatting top bottom rules and let's start with top. So here you can see top 10% again, like in last lecture. It doesn't have to be 10%. You can do 10% which in this case is 1.5 sorts, elects only one cell, but we can do 20 so three cells because it's, ah, 20% of 15 this three Bloodsteel green on. Let's do maybe, actually, let's do 30. So it's elects four cells in the same way you can do but tub bottom rules and then bottom 10% which again you can select however many you want. And that's night. 30 again 14. Above and Below Average Conditional Formatting: In this lecture, I will be talking about how you can highlight cells that are above or below average, using top bottom rules so select range like we always do under conditional formatting top bottom rules and then above average. And let's do green and you can see all of them are highlight. Now you might wonder. What is the every child? You can see it here. It's 69 on, and you might have that disabled in your spreadsheet. So if you right click, you can see average. Here, take that or make sure it's sticked, and then you can see it here by selecting the range. Another way you can calculate average is if you pipe equal average bracket and then select the range and you can see there it's 69 Now we will do in the same way with values that are below average conditional formatting top bottom rules below average, and there you can see that one value has not been selected, and the reason for that is because it is exactly the average value 15. Gradient and Solid Data Bar Fills: in this lecture, I'm going to talk about data bars, which is a new section in this course, and it is also under conditional formatting. So first of all, you have to select the ridge. I'll come back into in one second why it is important to select the whole range conditional formatting data bars. And then there are two types. We will be covering both of them in this video. So the 1st 1 is greedy and Phil on a side. You can see data bars. What does is that it fills the cell depending on the number. So if it's a small number like 400 the cell is filled only slightly, whereas the higher numbers are filled more and we'll do the same thing for the costs. But we will be using a salted Phil. Now, if you compare, you can see the difference. Grady in film means that it goes from dark color to very light, whereas the solid filled is basically as the names just solid. So they're the color is just one color, whereas this is changing from dark to light. Now, the reason why you have to select the whole range is because it uses the smallest number and actually uses the largest number to calculate. Ah, the with or how much other cells need to be filled because it will fill out the largest number to the maximum. So, for example, in this case, the maximum miss 900. This means that it is felt in the whole cell, and, according to that in tow, fill the other cells. Let's see, I make this to 800 and you can see that the others go up a bit. And this cell with 800 this l are now filled do throughout the whole length. So if you let's say, would do for each one individually, rather than selecting the whole range, it's Each cell would be filled ah, throughout its entire length. So I'll just show you that if you go like this, it is not very useful, and you can understand anything because all of them will be simply that color. So dealer bars are really useful, and they are visual representation off the size of the data you are working with. Also, please know that if you stretch, it will seem all increase the with off the cell. It simply means that the conditional formatting changes accordingly and you can't compare if you have marked this range and then this separately. You can't compare across them. If you want to compare them together, you would have to select the whole range. So let's firstly remove the rule on Let's do the data parts again. And now you can compare them together because now you can see that full length off the cell is 1005 100 then here the cost off 800 is about half off that. 16. Introduction and Explanation of Icon Sets: in this section we will cover. Aiken sets unconditional formatting. So, like always, we go into conditional formatting. I consents, and here you can see directional shapes, indicators and ratings and the important part in this lecture, which will be covered. This to get a basic understanding how this works. So basically you can see the amount off arrows or shapes or indicators. The amount of icons differ so there can be 34 or five on. This means that your data is split into the equivalent amount off sets. So, for example, three. That means that your dear is split into three sets. So from 0 to 33 from from 33 to 66 senden from 66 200 same similar way for four. It would be split into four sections. 25% and five is into five equal off 20% each. Andi. In the next few videos, I'm gonna show you the different Aiken's 17. Directional Icon Sets: so, like always, we need to highlight the range. You want to apply the conditional formatting and conditional formatting I concepts and then here, where you can see the directional on here, you can see the multiple choices you have. So firstly, you have the same ones with color and with out color. And I would recommend with color because it adds clear in this and you can straightaway see just by looking at the color rather than the shape, which, at least for me, is quicker. And then here you can have just three colors eso green, yellow and red. And then here you have more options, as you can see. But I usually go for this more simple one, which is this one. But it depends on the situation you are in and what you want to see. 18. Shapes and Indicator Icon Sets: in this lecture, we will continue with the icon sets and conditional formatting. So go here. Conditional formatting Aiken sets and we will be talking about ships. Here. You can see that you only half for example. This one compared to this is only the same shape, so you can't see the arrows. But this gives a color, her presentation and then slightly symbols. I personally wouldn't use this because I'm natural. Like yellow Triangle does not associate with medium values, and diamond does not associate with low values to me, that's why I would preferably use this one. But for example, this one if we look at indicators section, is quite good, because thes symbols are very internationally used and quiet, understandable you and throw this shape is the same. So here in the shapes, you kind of lose part off the information because you can't really tell the value by the shape only by color. That's why I would probably ago with indicators or directional and avoid shapes. But it's obviously your choice on do you can do whichever you prefer 19. Ratings Icon Sets: Lastly, we will be talking about conditional formatting Aiken sets. And then here you can see ratings. Here you have again mostly five there. This one is for different. Icahn's meaning split into 25% whereas this oneness into five sections and this gives you another representation. So, for example, here you can see the different bars, which is very useful toe. Understand the date engine neural terms. I would say that thes might be good for summarizing the data, but whereas if you want oh, have a deeper look at your data and analyze it, I would probably go with ratings because thes give you a broader view like visual view to see the data. 20. Conditional Formatting Based on a Specific Cell (Highly Recommended): in this lecture, I'm going to talk about how you can create conditional formatting without typing the values in itself. So we still have 15 students with their scores, and I have a pass. Create off 40. And I do not want to type 40 in here because it would mean that whenever I want to change it, I would have to go here, manage rules and then change it this way so that by changing here, it makes it much much are easy for me to manipulate the data. So I select the range conditional formatting. And let's say I want to highlight everyone that have failed so less than And then instead of typing a value, simply press here and select a so so pause. Great. So 40 I select e one and these dollar signs mean that this is a fixed. So select that and you can see here 35. Okay, so this person has failed. Now let's see, there is a change in policy and we increase the past creed. So let's say it goes up to 50 hit, enter and there you go. It highlights the cells immediately. So this is a much simpler way to change the rules 21. Using Conditional Formatting In Filters: in this lecture, I'm going to talk about how you can filter out stuff based on their color, which is created by conditional formatting. So I'm not sure if you know the filters. I'm not gonna go very in depth with it, but basically, you can select the range filter Andi filter, and you can select here all of the data on I will probably create a course about filters. But here I'm just gonna show you how you can fill through based on conditional formatting. So let's create some rules. Let's say conditional formatting. Less than 40 is red conditional formatting greater than 80 is green. Conditional formatting between 50 and 60 is yellow, and you can see the three different colors. Now you can press here and filter by color, and you can see by cell color or by fun color. And I have created the red, yellow and green the cell cores. So let's say the shows the person with read meaning less than 40 in the same way I can do yellow green. So this is quite useful. And by doing this, you can easier sort the data or what you want eso in this case It's not really necessary because you can see 15 records, so you can easily look through all of them by just looking at the sheet. But once you start manipulating large sets of data, this becomes very handy. 22. Order of Conditional and How to Change It: in this lecture, I'm going to talk about the order off conditional formatting. So that means if you have more than one conditional formatting rule, apply to the same Selves. So which ones you will see? So first of all we have talked about, and I have shown you that you can have multiple conditional formatting rules on same Selves . So I'm just gonna demonstrate so conditional formatting, Let's say, or one data bars and that same time are one color scales. The reason why one is red, that the screen is because we only have two cells, and here you can see both data bars and the color scales in the cells, So that's fine. However, there are cases were you will conflict and multiple conditional formatting rules cannot be shown. So let's clear that. And, for example, I want a rule. Let's just say great ridin three that will be light red. Now, if we say as well, it should be greater than five, and now it will be yellow. And let's do one more more than 10 a green. So here you can see that the yellow didn't change because it has to be more than 10 in order to change to green, meaning that 10 is not more than 10. It's equal to done so the last rule didn't apply. And as you saw, as we added new rules, the were overridden at the old rules, meaning that they are higher up in the ranking and they once that route is satisfied, it will highlight, as you have specified. And now let's say you want to change the order off your conditional formatting rules. So you go under conditional formatting rules, manage rules. And here's current selection. Let's select this worksheet. And here you can see the three conditional formatting rules that I have just created now simply select. Actually, the owner will be here, so this is applied in order, shown, as it say says here, this means that this will take priority and if it can't show multiple conditional formatting like day, the bars, because these are exactly the same changes, meaning the front and background color, so it cannot show all three off piece rules. So if you want to change, you can use thes two arrows. So, for example, I want to move the green down, apply. You can see that yellow has been applied. Or I can move the red, whichever where you prefer. Unless you can see, you can change the conditional formatting here. 23. Copy and Paste Conditional Formatting in Different Sheets and Workbooks: welcome guys to this tutorial. Here I will be talking about how you can copy and paste conditional formatting, two different sheets or workbooks if needed. So I have come Ah, in astray Shin, where I needed to do this. And therefore I decided to make editorial how to do this. So first of all, here you can see a sheet sheet number one where the numbers are 1234 and five. I've applied to different rules. So if we go to conditional formatting, manage rules Andi, this works so you can see right here that if the cell value is above three then it highlights it as red and the tax front is also read on Apply city from a one to a five in the same way. If the cell value is below three than it will highlight it as a Greenfield with green text . And again it applies to yeah cells from a one to a five. Now, if I want to copy and paste it in two different sheets or workbooks, then you need to select the cell which has your specific conditional formatting. So for example, I want to highlight this because it has the conditional formatting and press format painter . Now, if I go to my other shit here I have numbers 12 and 11. So if I select them and I have, as you can see here I have the format later on on There you go. It applies to these three cells. So right here you can see that it will not apply to from a one to invite. It only applies to a one to a three. If we go here, manage rules. There you go a one to a three and it applied both off the rules. So that is quite handy now if we here the rules the other way is actually here. You can also, if you select both of them, it will still apply both off the rules. So if I show you right here, it goes like this. Now if I oppress control, is that if I don't select the whole range, for example, the data said can be hundreds and thousands of lines and therefore it might be difficult to select the full range by just dragon. So if you press, I found the simplest way is Teoh simply go form and painter on one cell. Been select here. Andi, If you go back to the sheet, one manage rules. And if ideally, the your workbooks should have similar layout and similar structure, therefore, you could potentially apply this Teoh your other shit. So control, see? And here control that fly. Okay, These are obviously also true because this is below three. It's nothing. So that's why it highlighted them as Greek. But that's another way. If you have a large data set and you don't want to drag and using the former painter, so let's clear the rules and let's talk about the other way how you can copy and paste. So again, select a cell where the conditional formatting is supplied and I press control copy or simply right click and copy. Now go back here, select the range where you want the conditional formatting to be applied. So I have selected a one to a three right click and paste special. Here, you can select formats press that, and you can see that it applied the rules. So if we go to manage rules, you can see that in sheet you both off. The rules have been applied. If you want to do this on two different workbooks rather than two sheets, you can obviously apply the same methods, but my tip is that you have to open up both off the workbooks before doing anything. So I hope that this will be useful, especially if you are working with many sheets and many workbooks large data sets and you don't want to create the rules manually. In this case, potentially, it would have been faster to simply create the rule again. But when you have more complex rules, for example, based on a specific text and if you have many different rules, apply to the same range than it's much easier, and it saves you a lot of time to apply the rules in this way. So I hope you enjoy this quick tutorial and see you guys in the next lecture. 24. Introduction to Creating Your Own Custom Conditional Formatting Rules: in this lecture, I'm going to talk about how you can create custom conditional formatting functions or how you can modify existing ones. So, like always, you need to select the range and then the conditional formatting. And here you can see a new rule. And here I'll go through these briefly in just a minute because firstly, I want to show you another way you can access. This menu is if you press conditional formatting, manage rules, new rules. And, as you see the same menu pops up. So here you have different types off roots, so firstly format all cells based on their value. And then here you can change the different types. So, firstly, stew color scale. And here you can type the minimum and maximum values, either by values or by pressing here, and select the cell or hard coded values three color. Then you can select the midpoint by default. It select the 50th percentile, but you can change that data bars also. No, you can change the colors in all of the's. Andi I concerts, and as you can see, the menu is always changing. The next option you have is on Li Cel that contains. And then you can type again hard coded values numbers or sell references. And then here you can use value specific text dates and all of the's. And here you can use between, not between equal toe not equal to ex cetera, and just change the format. How you want the text where in the condition applies to look like the next one is format only top or bottom ranked drugs. This one is very simple. This is stop or bottom rules, which we looked at previously, and then here select top our bottom, and then their values are percentages. Format only values that are above or below average again very simple above the law, equal or above equal or below. And then, in case you have done maths or statistics, you'll know standard deviation. If you need some statistical analysis, you can select how many standard deviations shouldn't be high right, which is quite useful because you can't have thes. I don't think originally in the menu. So for statistical work, this is extremely useful format only that have unique or duplicate values that might be different reasons for it, but very simple menu duplicate or unique and here I'll probably be talking the most about this function in the future lectures because here you can use a formula to determine which sells the format, and this is extremely powerful, and we will go through some examples in the future. Lectures for now, all press cancel and close. And actually, when you will have some rules, I'll just condemn a treat. You can click edit rules to modify existing rules. So, for example, I will highlight cells greater than, let's say, 70. So men, Truls And here you have, where it applies at the truth. And then you can see this menu where you can edit the condition based on which it form. It's the cells, so see you guys in the next lecture. 25. Before Creating Custom Formulas You Need to Know How to Lock Rows and Columns: before starting to learn about conditional formatting and the different formulas you can type in order to do various stuff with conditional formatting. Our I want you to know the symbol dollar sign and what it means in excel. So by that I mean, flat CEO type equals this cell so you can see that it simply goes if I drop down my formula changes accordingly to d three, and I can simply dragged same way I can drag it to the sides as well. Which means see, too now, the dollar sign. If I put $2 signs, so here and here it means that this cell is locked. So both column G and room number two is locked. So if I drag this down, it will always say eight. It will always refer to the cell as you can see here, in the same way if I drag it sideways as well. And this will be very important when you try to play with the formless. So in the same way you can look only column D or only arrow or both of them, which is in this case. And the short cut short cut for this is F four, sir, If I press therefore now, you will see that now it looks only row number two, which means if I drag this down it will stay on this number. So let's try this out. However, if I drag it sideways, meaning it still stays the same row. But it has a different column, it changes its value. So in the same way, I'm going to show you what happens if we look the column D. So if I dragged us down, you can see the values changed because this is all column D. However, if I drag it sideways, it doesn't change because it has to be from column D. So I hope this gave you a better understanding, and we will be using these dollar signs when we are creating our own conditional formatting rules. 26. Highlight Cells Containing Time Information Using Formulas: in this example, we have five working days, the Star Times and end times. And here is a simple formula which calculates the amount of hours you have worked. Let's assume you do no get paid for overtime and therefore you want to reduce your overtime and highlight this in this worksheet. So select the range, and here, instead of using highlights, several ALS and great rhythm, we will be using our own custom function, so pressed new room and we will go a form. Now here you have to type the whole formula and we will say Equals said like the cell. And here you can see it's elects the dollar signs and we do not want to lock it to roll number two because it will be this value always so we want. It's still to be locked on D column and no, well, right e or great through then and then here we will type 0.333 and what I forgot to do is to make the format set a form it because now you can't see anything at the true format and let's say I want to fill off red apply and there you go. And now the reason why I typed 333 This is very important is because usually you would be inclined to type zero eight column 00 But that will cause you an error, and you won't be able to form it when dealing with ours. Think off the day as one digit, so eight hours is 1/3 off one day. So that's why it's 0333 now. It also has highlighted the eight hours. So in this case, I would actually need to type 34 because in math you will remember that 1/3 is it has infinite amount of digits. So by doing 0.34 you'll see it's slightly bigger than eight hours, and therefore this should not highlight so press. OK, apply under you can see, and you could potentially do this also with this conditional formatting and greater than I'll show you in a one minute. Let's take that copy paste. And then here how clear the conditional formatting and here all type highlight and for comparison sickles, he agreed through. Then let's see six, which will highlight all of them. But I will show you no one second, Howard calculates it. If we go conditional formatting manage rules, and here you can see 0.25 And as they said, the reason for that is that one day is equals to one. So six hours is exactly 1/4 so 0.25 27. Creating Custom Icon Set Ranges: in this lecture, I will be talking about how you can create custom I couldn't sets or how you can modify them as well. It works in the same way. So here this is the same example with working hours and we still want to highlight the ones that are more than eight hours. So select the range conditional formatting new rule. And then we will be doing here based on their value. I couldn't sets. And here you have this menu. Now we will reverse the icon order. So we have the red value here. So because we want Teoh the readiness for the higher eso greater than or equal to And then here we will type 0.34 Now for this one, you can see that this automatically changes because by default this wants to split up into three equal sections. But we don't want that in this case, let's say we only want red and green. So here we will change this to not be equal, but just to be greater, meaning that yellow will never beat the case If we type 0.34 as well. So it means it has to be less than 30.34 and greater than 0.34 meaning that will never be the case. And here we will change from percent number. I should have to change that first. And there you go. Dis changes, then according press. Okay. And there you can see the conditional formatting. 28. Highlighting the Whole Row Based on a Column: in this lecture, I'm going to tell you how you can highlight the whole row based on one cell. So, for example, here we have we could call this product, and then we have apples. So I want this whole role. Or each row with apples highlighted, one specific color. Carrots, different bananas different again. So let's select the whole range, excluding the title Conditional formatting and manage rules New rule and then use a formula . So here will have three different formulas because we have three different products. So here I will type equals. Select the cell, and then it will lock both the column and a row, which we don't want. We want to look only the column because all the products are listed here, so we want toe unlock the road, so delete that and we will type equals. And then here you need to type quotes because this is a text, and you I know what happened. I pressed the Arrow Camden and decided it's plus, but basically you need quotes around your text. So here I'll type apples. Otherwise, if you do not have quotes, it will not work. Format. Let's say Apple's we want them in this green. Okay, apply now. New rule in the same way equals selective cell removed there. Looking off, Rome equals quote can rights. And we will do this color and another rule equals. And it will be been, uh, this and let's do this color on there. You can see so you have three different colors. And now let's say I want to change this to a different type and you can see straightaway. The color will change. 29. Dynamic Way of Selecting Whole Row Based on a Cell: in this lecture, I'm going to talk about how you can make this interactive. So highlight all the cells or all the rows, depending on a product category or a product code or whatever. So here we have the description. So, for example, I want to highlight all apples and then maybe I dis change my mind and want to highlight all the carrots. So this is nothing to do with conditional formatting. Obviously, you can make it that you can type and match it against this field. But we don't want that. We won't make at drop down menu. So here I have typed all the three different types off products in order to make the drop down menu. Andi, In case you don't want this, you can make a hidden sheet or hide this row or make this as the white text if you don't want to stop here. But basically, if you go here and then I think it will be in data the revelation press that and they're not any value but a list source. This the three categories And okay, now you will see a arrow here, and you can change it. According to these cells. So we will be using this and now simply go to conditional formatting. Actually need to sit like this conditional formatting new room and then type equals this without locking the cell equals this so and here the cell needs to be locked completely. So both column and row because this will never move. And let's see our formatted According to this color, now you can see carrots have been highlighted. Now let's see, you want to highlight bananas and there you go. So this is quite useful and interactive. 30. Comparing Values In Two Columns Using Formulas: in this lecture, we will be comparing two values. So here I have different products than stock on hand and then reorder level. So this means that if my stock on hand falls below reorder level or is equal to it than I need to purchase or a replenish my stock. So I want to highlight those rows. So select the range conditional formatting new rule, and then we will Taipower formal. So equals this cell without locking their own number and has to be smaller than this cell without locking the row number again. Format highlight red. And that's simple, is that? But here I forgot to do. One thing is equals, so this was not highlighted. So let's go back and edit the rule, and here we can type equal or is greater. Si two is greater than beat or is equal tude. So there you go, and now you know which products you need to purchase additional quantities 31. Comparing Two Different Lists For Matches or Mismatches: in this lecture, I'm going to talk about how you can compare two different lists and find values that do not match to each other. So we will be using a formula called Count. If and now I'm going to demonstrate what it does here without the conditional formatting and what values it returns. So basically a type equals count. If and then bracket here, you need to select the range and you want to make this a locked range because I this range will never change. This is the full list, because if I dragged us down here, my whole range would drag it down as well to a three to a seven which I don't want. And then for criteria, I will simply pick this value close to bracket. And there you go. The reason why this zero is because there is a miss type. So potatoes and here potatoes and so they do not match. If there would be multiple that say, here would be another apples, you would see that it would count apples as to and then there are no piers. So this is how it works and then basically will be looking for values that have a zero. So let's delete this and we will highlight mismatches from list a conditional formatting new rule. And let's type this in equals count if bracket the range and here you can see automatically , it will look the range common. And this and here we do not want to lock their own number equals Teoh zero format read. And there you go, the single mismatch that there is. And as you can see, the two lists are in different orders. So it doesn't compare this value with this value. It's simply compares this list with anything in here, so I hope this will be useful for you. 32. Changing Colours of Alternating Rows Using Formulas in Conditional Formatting: in this lecture, I'm going to talk about how you can create colors full alternating rows, meaning that and even rose will be one color all uneven number. Droves will be a different color, and you can do this by creating a table. But let's assume you want to do this with conditional formatting, and you want to do this because it will enhance the readability off the data. So select the whole range. Actually, I'll show the formulas that we will be using. So here we will type equals even, and then you need to type a number, and in this case it will be row, which will take if you open and close the bracket. That will take current true. So you can see that the road issue and it takes the even number off to you, which is to if and you will see what I mean. Here three is uneven number, so the road is here returned as three, but it takes the even number. So it transit up to four, and then we will be using in the very similar way on. No. And there we go. So too, is not odd. It's even so it transit up to three, and we will be using piece to formless for conditional formatting. So let's select our table or the range new rule, and we will be creating two rules, one for odd and one for even so equals even. No, actually should have it like this and then equals row. So all of this statement will only be true when it is equal to roll number. So, for example, here it was Value number two. Then here it's forks, and it's not equal to roll number, which is three. So this will not be highlight. So let's select a color. And there you can see in the same way new rule equals odd equals true. And let's do this car. And there you can see the alternating rows have been assigned different colors, which will enhance the readability off your data. 33. Finding Duplicates Using Formulas in Conditional Formatting: in this lecture, I'm going to talk about how you can find duplicates without using this function. So how you can create your own rule for it. So here, select the range and we will be using the count if function, which we have used before so equals can't if select the range on this needs to be ah looked and then we want to match it against itself. Except we don't want to do this with or lock their own number. And all of this needs to be greater than one. Because if it's one, that means it appears only once in this whole range. And yet it's highlighted as a threat. And there you go. So you have two bananas and to pierce. 34. Finding Duplicates Using Criteria From Two Columns: In this case, we will find duplicates based on two different columns. So here, let's say I want to highlight all the duplicates that have the same product and the same quantity. So what you need is a helping column, so call it B and Q so product and quantity and what you will need to type this equals this field on this field. And as you can see, it simply combines the two. Drag this, and there you can see all of the fields combined now condition off, actually need to select the range conditional formatting new rule and just like in the previous lecture, will use counterfeit, select the whole range calmer. This without locking their own number has to be greater than one and call a red, and now you can see that bananas have not been highlighted. The reason is because there is quantity to a quantity one so dese two are not matching each other anymore, whereas beers are still same corner, so they are duplicates 35. Using Conditional Formatting to Find Discrepancies: there are cases when you need to find discrepancies in values. So here we have two people Andi there, let's see monthly spending. And then this total should equal to this total. I purposely have made a mistake. Eso instead of having some, like in this cell, I have just a value and I want to check if my calculation is correct or not. So I have here the some off these cells and then here the some of these cells and I can use conditional formatting toe highlight. If dese there is a discrepancy. So I will use conditional formatting new rule and then form it only cells that contain so sell value and then it will be not equal to. And here we want this and format red and a simplest that this is highlighted. So let's say I know see that I have made a mistake in my calculation. So I will figure out that it is in this cell and that's try some equals. And there you go this disappears Or there red color, meaning that this is now correct and dis compares that you sub totals