 Hello and welcome to this session. This is Professor Farhad in which we would look at conditional formatting using Excel and the minor data visualization purposes. This topic could be covered in introduction to data analytics and accounting or simply data analytics course, auditing, as well as managerial accounting, many uses for the conditional formatting. As always, I would like to remind you to connect with me on LinkedIn. If you haven't done so, YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, tax and finance lectures, as well as Excel tutorial. If you like my lectures, please like them, share them, put them in playlists. If they benefit you, it means they might benefit other people, share the wealth, subscribe to the channel and connect with me on Instagram. On my website, farhadlectures.com, you will find additional resources for your accounting courses. Or if you are studying for your CPA exam, a lot of practice, a lot of questions, exercises, simulations, so on and so forth to get those 10 to 15 extra points to pass your CPA exam, as well as your CMA exam enrolled agent exam. Please check out my website. So to illustrate the conditional formatting function, we're going to be looking at this data to start with. And I'm sure you know that in Excel, you could use chart to visualize your data. Well, guess what? Conditional format can also help you visualize your data based on color coding. It helps you going to see trend and patterns in your data. It sounds very challenging, but it's pretty easy and very powerful. So let's assume we're looking at this data. It's the salespeople as the best car dealer in town. So here we have the name of the individual and per month. And what I can do real quick, for example, if I want to know who has the highest sales or the lowest sales here, what I can do, I can highlight all the names. I'm sorry, highlight all the data that I want to examine, click on conditional formatting. And what I'm going to do, I'm going to be using color scales. And I like to the yellow, red and yellow, red, yellow and green because people are familiar with that. Let me click on that. And what's going to happen, it's going to highlight the large numbers, the large figures. For example, in March, I see that Ashley had 183,282 in sales. Also, in May, Anthony had 181,942. Notice it chose me the large numbers. It also highlight the low numbers would light green. For example, here I see that Anthony in January didn't do very well. He only had 12,700. Maybe he did not. He started late in January or Anthony was still in training. But I noticed by May, his sales reached 181,942. So this is a good way at looking at the data. Or let me clear this conditional format. I'm going to show you later how to clear this. So I can either clear selected rules or I can clear the entire sheet. I'm going to clear those selected rules. Also, how can I use conditional data? I can use it, for example, if I'm looking for all the sales that are above 160. So those are really impressive. So what I can do is I can click on highlight sales rules. Greater than, and here I have a greater than if I want to look for less than, for example, if they're too low, too high, in between a certain figure, a specific sales number, a cell that contains a certain tax. If I'm looking at dates and duplicate value, I would look at later. But let's assume I want to see which salespeople reach at least per month above 100. And let's make it 150,000, 150,000, make it 160, 160,000. I believe that number is impressive. And I want to reward them or I want to know who they are. For example, Ashley in two consecutive month reaches above the only person that in two consecutive month reach above 60. And I notice right away, I would notice that here and there, Joseph also had two months. I notice here that Adam did not have any month above 160, as well as Bill and Tom, so maybe they need additional training. So notice I can real quick kind of get a good idea about who's doing well and who's not doing so well. And what's neat about, what's neat about conditional formatting, they have some preset, preset rules and basically you could see those numbers in data bars and show you what it looks like. It's not really good here. I'll show it to you in a different scenario. Let me highlight everything just to show you how it goes. And the higher is the number, the higher is the bar. Obviously it's a bar you should be familiar with bars. You could look at color scaling. I showed you how color scaling works. Or you could use icon sets. For example, here, it tells me right away for the month of February, who's doing really good, like the green, that person that's doing really good, Avi and Ashley, and who's doing in between the yellow and who's who was doing bad in February, Anthony L. Same thing in January. I would know right away immediately what's going on really quick. So that's the beauty of it. Now let me clear this. I'll keep this conditional format. It doesn't matter. The reason I'm telling you this, because when you want to clear the rules, you could clear the rules from the selected cells or you can clear the rules from the entire sheet or you can manage your rules. So you could have many rules at the same time and you could manage them. For example, here I have the cell above 160 and the icon sets. I'm going to delete both because notice you could have more than one. I'm going to delete both for, well, delete this one and delete this one. Now I basically I cleared my rules. Another way to use the conditional formatting, let's assume, and I use this a lot in my classes when I have grades. Sometimes I have 36 students and I want to kind of highlight who's doing the best and who's doing the worst and who's in between. So I could click on my, you know, select my data, go to conditional formatting, and I'm looking at the top bottom rules and I want to see the top 10% in the course and I only have Justin. Now I can, you know, I only have Justin. I also want to see the top, the lowest 10%, the top and the bottom 10%. Let's see, I'm going to change, you can change the color here, you can change the yellow, green, green fill with dark green text, light, red fill, red text, or you could just customize to whatever you want to. Okay, here you could use any colors you want. I always use red. So I'm going to use red for this purpose. Click on this and Bill is not doing very well. The bottom 10% in my class. Also, what I like about this is also it's going to give me any glance. Who's the color scale, the icon sets. Notice here, it's going to tell me who's at risk. For example, let me just kind of select this, select this one. I like this one. So who's at risk? I see that Bill and Thomas are at risk. John S. as well as at risk. George K., Ashley K., and Avi and Balader in between, and the green one are doing well, they're above average. Or I want to highlight the people who are above average, so I can highlight this, go to conditional formatting, and I want the people that are above average, above average. So click on above average, and I'm going to, I don't know, I'm going to do, I'm going to do custom format, and what I'm going to do, I'm going to put them in bold. Okay. And okay. So now I know who's doing above average. Okay. These individuals are obviously the green ones, just, yeah, well, as well as George K., now doing above average based on the average class. So this is how I can, the many uses of conditional formatting. Also, if I have two lists of items, for example, I might have my cash ledger, for example, I wrote those checks in my company. And now I receive my bank statement, and I want to know which check did not clear. So basically I'm looking at two lists. So you could highlight both lists, highlight this list, click on control and highlight the other list. Go to conditional formatting, highlight cells, and I'm looking for click on duplicate values. And I want to look at, you could either look at duplicate values or unique values. I'm going to click on unique values, click on okay. And I see that check number 58 and 62 are unique. In other words, they're in one list and not the other. So notice here, check 58 did not clear the bank yet because it's not what my checks that cleared the bank and check 62 did not clear the bank. I know this check 58 is a large check 9,720 compared to the, my average check is 2,517. I'm looking at it at below here. So those are some of the many uses of conditional formatting. Now how would you use your conditional formatting? It's up to you how you're going to use it at your work. As long as you know its capability, you'll be able to use it whether you are deducting fraud, using it for internal control, financial statement analysis, or simply to manage your business. And simply put, notice managing rules, you could, you know, delete, edit rules. And also what you can do too, you could click on new rules and set basically your own rules. They're similar to the preset functions but some of them you could use formula to determine which cells to format. So you could use any formula you want to to look up a specific form, to look up a specific cell value. As always, I would like to remind you to visit my website and subscribe to the, subscribe to my YouTube. And if you are a CPA students or accounting candidate, I strongly suggest to invest in my website. Good luck. Study hard. Stay safe, especially those coronavirus days. Good luck.