 Hello, and welcome to this session. This is Professor Farhad. In this session, we would look at conditional formatting using Excel. This topic could be covered in an introduction to data analytics and accounting or simply put a data analytics course. 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, finance, and tax lectures. This is a list of all the courses that I cover. If you like my lectures, please like them, share them, subscribe, put them in playlists. If they benefit you, it means they might benefit other people. Connect with me on Instagram. On my website, farhadlectures.com, you will find additional resources. If you want to supplement your accounting education or pass your CPA exam, I have practice questions, notes, multiple choice, exercises that are considered quasi-CPA simulations, and resources for other accounting courses. So to illustrate the conditional formatting, I'm going to be using the example of a bank reconciliation. I'm going to be honest with you. When I was in practice, I never used this technique. As a matter of fact, I learned this conditional formatting from my wife. My wife, she works at Johnson & Johnson, and she worked under medical devices, and part of her job is to compare pieces from one medical equipment to the other. For example, in terms of competitor or in terms of within J&J, and some medical equipment, they have thousands of small pieces, and each piece has an identifying number. So for example, a part of her investigation or part of her internal control is to make sure all the pieces that are in this equipment are also in this equipment, if there's anything is missing. So this is what I got the idea of conditional formatting, but I'm going to be using this idea to apply it in an accounting setting, and I'm going to apply it to a bank reconciliation. Now if you don't know what a bank reconciliation is, by all means, go to my YouTube channel and learn about the bank reconciliation. And basically, what is a bank reconciliation in a nutshell? At the end of every month, the company will have to make sure all the information that's under cash general ledger, under cash general ledger is complete, and all the information on the bank statement is complete as well. So simply put, those two should equal each other. So what's on your books, what's on your books, and what's, this is your books, what's on your books, and what's on your bank statement, they should always equal to each other. Now how do we make sure they equal to each other? Well, to make sure they're equal to each other as we prepare a bank reconciliation, to make sure everything that's listed on one side is also listed on the other side. So we know nothing is missing from the books, and everything that's on the bank statement is shown on the books. Anything on the books is also reflected in the bank statement. So that's the purpose of it. So to prepare a bank reconciliation, you're going to have what's called reconciling item. What are the reconciling item? For example, you could have wrote a check, and you always record the check in your cash ledger, you wrote the check, but the check did not clear the bank. So it's in this list, the check in this list, but it's not in this list. If that's the case, if the check is missing, it's called an outstanding checks, or you might have made a deposit of $3,000 right here, but that deposit is not shown in the bank. What does that mean? It means that that's a deposit in transit. The deposit in transit means it's on my, in my books, I know I received the money, I know I deposited the money, but it's not in the bank, because maybe I deposited that money the last day of the month, and the bank did not reflect the deposit until the beginning of the following month. Or the bank might have gave me some interest, like $10.15. I'm not aware of that interest, because I don't check my bank statements every day. So once I receive the bank statement, I notice I have interest that's not shown on my cash general ledger. So this is the idea of a bank reconciliation, making sure I'm accounting for everything in those two, they should equal to each other. Now for the purpose of this example, I have a small list, I have maybe 30 items in the real world, and I'm just, I'm kicking myself now, like how, how didn't I know about this when I was in the real world? In the real world, a bank reconciliation could have 500, 600 items, every transaction, every check, every deposit is a transaction, and you have to reconcile those transactions. So conditional formatting, it's going to help me tremendously identify the items very quickly, what's missing, what's missing from each list. So how do I do so? So I have my cash ledger, this is one list. So what you do is you highlight the first list, and you highlight, you click on control and you highlight the bank statement numbers. And what I'm looking for is items that are unique. Okay, because every, when I write a check, it should go to the bank and the bank should clear it, it should be on my books, it should be in the bank. I want this conditional formatting to identify items for me, that's, it's in my cash ledger, but not in my bank, or it's in my bank, and it's not in my cash ledger. So how do I do so? I highlight the columns that I want to examine, I click on conditional formatting right here, and I'm going to go down and click on new role. Once I do so, I'm going to click on format only unique or duplicate value. I want to highlight unique values. So I want to highlight what unique values are in each, in each category, in each column, because those unique values, it means they're in one list, but not in the other, because everything in my cash should be on my bank, everything in my bank should be in my cash eventually. So anything that's missing, we call it outstanding, but I want to identify those outstanding items so I can prepare my bank reconciliation. And this list could be a list of two, of any two items that you need to reconcile, you could use the same technique. So I'm going to always like red because red, you know, you could see this clearly. So I'm going to go color, make it red, click on okay, click on okay, and here's what happened. For example, it highlighted check number 177. What it's telling me, check number 177, it's recorded in your cash ledger, but it's not in the bank in your bank statement. And check 177, it's not. Therefore, this check 177 is called outstanding check. Also check 182, it's on my, it's on my books in my cash general ledger, but it's not in my bank statement, it's an outstanding check. Deposit in transit, $3,000 deposit in transit. You made the deposit, it's not shown in the bank yet. In deposit number 226, it's not in the bank yet. Here it shows you that you have a non-sufficient fund fee of $25, the bank charge you this fee and it's not in your cash ledger. And you have a non-sufficient check number 172. Well, let me check this to 169 because I do have 172. Okay, 169 just to make the example more realistic because I do have 172 here. So check number 169, it's a non-sufficient check. It means whoever paid you this money, whoever gave you that check, the check wasn't, was not good. Okay, so this check and there's another non-sufficient fee for check number 170. There are not two checks and two non-sufficient fee 1425 and 135. You might be wondering why did I choose 125 and 135? I'm going to explain why in a moment, why I choose those figures because there's another point I'm going to try to make. Also check number 171, it's not on your list. It seems this check was outstanding from the prior month. Now it cleared. Now I know I'll be able to clear it and I noticed that interest earned of $10.15, it's not on my books, it's on my bank statement, that's a reconciling item and collection of a note of $2,500. It's also the bank received the money, but I'm not aware of it. Now I'm aware of it, I need to make an adjustment. So what this did, it clearly gave me what items I need to reconcile to adjust, make certain adjustment either on the cash or on the bank statement. Now once again, if you don't know what the bank reconciliation is, you're going to find this recording a little bit not clear, but the point is I like to design those Excel tutorials for accounting students, but any list you could do the same thing for any tool list. Now why did I choose $25 for non-sufficient fund 1 and non-sufficient fund fee as $25? So let's assume change this to $35. What happened is $35 is no longer a unique value because I have $235. It did not highlight the non-sufficient fee. That's why I put the number $25. When I put $25, it shows me that $25 and $35 are unique, but when I have both, when both numbers are $35, you have to be careful. Simply put, you have to do maybe another study, not another study, you have to run, if you know you have duplicate numbers, you have to run another conditional formatting. So what you do is you just highlight the numbers and you want them to give you the duplicates. So you could new rule and format only unique or duplicate and I want the duplicate and it's going to now highlight the duplicate for me. So be careful. If you have duplicate, you may have to go a step further. Now I highlight I have two duplicates. Now I can go back say, okay, these two duplicates, well, they're not on my cash general ledger. Then they are unique. The reason they did not highlight as $35 and $35 because I'm looking for unique and they're not unique, but if I do $25 and $35, they are unique. So it's basically I took this as a learning opportunity to show you. You have to be careful. You have to use your judgment a little bit more, okay? And I wish, I really wish I knew about this Excel function when I was in practice. I used to literally pull my hair out sometime looking for either a missing deposit and transit, a missing check and I hope you're listening to this. You could use this in your work, whatever you are doing, any two lists that you're reconciling and sometime you have hundreds if not thousands of items on each list. How do I know what's missing? How do I know what's duplicate? Very, very easy. Highlight it. Conditional formatting. Look for unique items or duplicate whatever you are looking for. And this is only one illustration of conditional formatting. There are many rules in conditional formatting. For example, you could look for format only cells that contain a certain number. Format only value are above or below average. And you could define, you could define all these numerical figures. So it's very, it's a very powerful tool. Make sure to use it. If you're an accounting student, subscribe. If you'd like to learn more about Excel, I'll be posting more tutorial. Subscribe. Share it with others. Like it. Study hard and stay safe during those coronavirus days. Good luck.