Transcripts
1. 01 Excel Intro: Hi, everyone. Today we're going to cover an Excel Floors have been asked by a lot of people to create an Excel force. And I thought about it for a while again and take a couple of years old since I traveled the world and I was thinking, You know what? There actually is an Excel course that I want to create. It's not gonna be your Basic Excel course where you learn how to move the mouse around and stuff like that. But it's gonna be a little bit of an intermediate course, will recover the most commonly used functions. But you can see here on the power points live below, we're gonna cover the look up. If statements some if averages count. If trim Captain eight left right mid. We're gonna cover some data validation. Some removing duplicates will also cover some conditional formatting and all of those things to say. How do you know if this horse is right for you? The way you're gonna know this course is right for you or not, Is that usual? Excel a lot of work or Google sheets, the Excel version of that, and you're really very manual on everything if somebody asked you to look up something in the list, you kind of like scroll through the list. You're like, OK, well, no, I found it. That's what you look up before. If you don't understand if statements, this is a good foundation. Learn the basic formulas, using them individually, and we're actually gonna focus a little bit on nesting formulas, which is actually really cool. So all that being said, this course is really, really great if you want to learn the basics of Excel formulas, Okay, So not basics of excel, of how to actually use Excel. But the basic formulas to use the course a little over an hour long. We spend about 5 to 7 minutes on each one of the different formulas on Ben. This comes within Excel Sprint spreadsheet, and will she link, which you can watch a use as well, which you should use because the only way to learn is by doing this is not a course where you can just watch and understand what to do. So you're gonna have the watch it and do it together, and then you'll see the power of these formulas, and then you'll learn when to use them as well. So with that being said, I hope you enjoy the course
2. 03 What we will not cover: Let's talk about what we will not cover items not being covered. We will not cover basic Excel formulas, mainly cause it's boring, like there are videos out there that you can watch on that. And there's Google, and I highly suggest getting the basic formulas down like some average etcetera. It's boring to me, so I'm not gonna cover that. Also, there are other things I'm not gonna cover, like simple formatting moving and scrolling around in Excel, adding a new sheet, etcetera. If I see that is a big need, then I'll add that into the scores. Right now, I feel like a lot of those things you can just learn on your own. However, I can add that if that is requested.
3. 04 Excel File Explanation: so with all my courses is always a handout. However, in this one there's not gonna be hand out because we're learning excel, and there's no reason to write things down. And mainly I want you to do this with me and learning. So download the Excel workbook for common formulas. Here I will give you a dropbox link, and that's in the course. There's also that you can use this with Google sheets so you can import it into Google sheets. I'll actually show you how to do that very quickly. So let's go into Google Sheets. I did it earlier so you could see it. So we open up Google sheets and then go to Google Sheets. What we could do is click this left button here, make sure that you're on sheets and then what you're gonna do is click here, which is opening file. Okay, so right here is open a file and you can you upload, select a file from your computer and then selecting example workbook for common formulas, and then you can click it and then it will upload it. What you'll see here is the same exact workbook that we see in excel. Okay, so if you do not have excel, you can upload this into Google sheets, which is free and then follow along. So that being said, let me go back to my actual Excel spreadsheet, which looks very similar. OK, there we go. Here's my exact Excel spreadsheet. Here we have a table of contents, and each one of the tabs is hyperlinks, so you can actually click on the tab and just go straight to it will be. Look up. If you want to go to that and then return to table of contents is here, um, to set this up, how it looks this here's one the table contents. But if I go to the D, look up here, you'll find the title off what we're working on. So they said the look of example, the purpose will be online. To find an item in a list. Returning attributes on Denver return to table contents so you can quickly do that. Each one of these formulas will be a separate video. So if you already know one, so let's say I know if you look up really well, then you don't need to take that one. You could skip over and go to left right mid. You know, I've never heard of that. So maybe I need to take that so all of these will be separate into a separate video. Each video will be 2 to 3 minutes long. Kind of just walking through how to use the Formula One thing over here till right is learning formulas is easy. Knowing when to use the formula is the hard part. That is when you move into becoming the Advanced Excel user. Okay? So knowing when to use that and then putting it into with, like, invested formula, so I'm not gonna cover that right away. But again, knowing when to use a formula is the hard part. When you ask the right question and say, Look, I'm asking ex question This is best solved by using the look up or if statements or some combination of both on DSO that's that's the next level Excel. So I'm gonna stop here and then move on to the next video
4. 05 Vlookup: So now open this up in excel. So you should have already Diamond Dropbox link and download the itself up. You don't have excel opening up in Google sheets. You need to follow along If you don't follow along and do this with me, it's not It's not gonna hit home and you're not gonna learn it. So the only way to do this is the Have the Excel spreadsheet open and work the problem with me. So the first thing we learn is the look up. So you click here to take you to tap to. So if you look up example purposes, find an item in the list and then return it. Attribute. Okay, so you have a list. So what I've given you here is some example Information. It's not always gonna be this easy. Okay, so I deal with files that have thousands and thousands of employees. This is literally I can count in here down here. 14 items. This is 14 items with one attributes. This is a very simple list because I need to teach you it. But understand, if you've got a huge excel file with 10,000 lines, this still works. Okay? So first thing is, I want to see if award exist in this list. Type of warning, This yellow box. So I see this original list here. I'm gonna type in something like lamb. Okay, so now I want to see if that lamp exists in this list. So what I'm gonna do is type in D whole sign. OK, that's how we start all formulas be. Look up, open parentheses. It's telling me, what value do you want to look up? I wanna look up Link. What table do you want to look it up in? Okay, so this table, I'm gonna select this entire table. Now. I want to say what call? Um, do you want to bring in? I want to bring in column one. So this is column one here or Roby? Column two is Ciroc. I mean, column C. So I wanna bring column one and range. Look up. I'm going to put false. I always use false because I want to find an exact match. Okay, So I would recommend always typing in false and then hitting. Enter. What you confined is that lamp does exist. So let's say I change this lamp and This is what we call a dynamic formula. Let's say it change this to cat. This cat exist, not available, does not work. But let's say I type in Fridge is at available. Yes, that formula works. So that is a simple of you. Look off formula. I'm gonna delete this again, and I want to re type it with you, so it hits home. So let's do this formula again. I want to find the word fridge in this list. Okay? I want to see if it exists. Equal sign you. Look up. Look up. What value this values what I want to look up in. What table? Select my table here. Okay, what column do I want to pull? I wanna pull column one and range. Look up. Always false, because I want to find an exact match hit. Enter and you confined fridge again. So here's another variation of that. OK, so this is a dynamic formula. I wanna do one variation. You look up saying exactly equal to be Look up, but I'm gonna type it in here if you're typing in a word. The reason why I'm doing this because I want to teach you that when you type in a word, you have to use the quotation works. Type in fridge. In what table? We select that same exact table B six to see 19. What column? Call him one. We talked about that and false because I want an exact match. See? False exact match. Want that close? My pregnancies hit. Enter fridge does exist. OK, but what happens if I take away these quotes? I get a problem. Okay? Because it doesn't understand that this is a word that these air a text Cherie. So this these quotes determined texture. So it's very important. Understand that Here's the other thing that you I want to show you. Hear this? You may No, you may not know, but when I created dynamic formula So let's do this one more time. Now is the third time we've done this. What? I want to look up Fridge. At what table? I want to look it up in this table. We'll call him. I won't call in one in false because I wanted exactly should enter. So now I found fridge. But what you can see here, I'm getting f two to get to this short cuts and look into the formula. I drag this down. What's happening is everything is moving. Okay, so this reference is not It is not a locked reference cell. So how do I lock that reference cell? So one, this table doesn't keep going down To see how this table is not picking it all up. That's a common mistake. Common mistake. Number one is that I do have the look up, and then I drag it down and I don't have the same the same tape. So what you need to do is lock the cells, and how to do that is you can come up here and that you hit F four or put dollar signs in front of it. Okay? And then I'm gonna hit. Enter. So now those air locked and I'm gonna lock sell e five. Which is that search term? I'm looking up. Get locked there. Now, if I copy and paste this down here, for example, all that remains the same. Do you see this? The look up value is still e five and the table is still exactly the same. You'll find that to be very, very important when you're looking up more than one value. Okay, this is a very simple example. So locking in the cells is very, very important if you need to do that. So I'm gonna hit the lead here and now. I found Fridge, but now I want to find the cost of that. I Okay, I want to see how much of fridge costs like, Okay, so, yes, this is 14. It's very easy to do, but let's say this list was very long and I wasn't looking up prices, but something along with how much an employee was paid several months ago. So now I want to see that cost of side of it was very simple list. Do be. Look up. What value do I wanna look up? I want to look up Fridge. I'm no lock that Sell what table? I'm going to select this table. I'm gonna lock that by hitting f four. It locks it all. Now what? Call him. Do I wanna pull? Well, here's my original list. Last night I pulled column wine, but I want to pull attribute A This is what I call it cost. OK, so this is the cost of it. This is an attributes of the original list. I want to see how much a fridge cause. So what column I pulled last time was one. Think about it. What column do I want to pull now? You're right. What you want to do is pull column, too. So I'm gonna put call him to here, and I want an exact match. So I put falls and I closed my parentheses, toe clothes off my formula, and I had Hunter $1000. So that bridge costs $1000. I can formatted up here at the dollar sign. So now it's $1000. So with this dynamic formula, I can type in something else. This is the really cool part. Laptop 14 99. How much is a desk? $224. How much is a bed? $332. How much is a cup? $4. And why does it change? Okay, so you may, son, Why lies the price changing so quickly? Well, it's looking up based on what I'm typing in here. That's the best part. It's dynamic. It's looking to this and saying, What do I want to type? What do I don't look up versus if I wouldn't hear typed in cup. Okay, Now I know how much a cup calls will. Great. Now I want to see how much a desk cost. Now I'm gonna go back into formula and type in desk, see? And so that takes longer versus it. Referencing the cell. I delete this and reference this cell again. But now I could just change this. How much does the washer cost? Boom done. And that is the basics of of you. Look up one. So to recap, is it in the list? I can determine if it's in the list and now wanna pull another another attribute. But a lot of times you'll have a huge table with a ton of different attributes. And that's why the column number matters will be calling Number one column to calling three calling for calling five etcetera. OK, so pulling that attributes really, really great because it allows you to look up the item. Okay, so I'm looking up washer and then determine what see attribute. Oh, it's a cost. How much is it? 505. Return that value for me. So that's the That's how we're going to return the price of the item based on a D. Look up. So that's the end of the V. Look up.
5. 06 If Statements: so, whatever. Tabun on, just like return table contents. Now let's learn about if statements. So let's click if statement. So this in if statement example line to what's the purpose to do something size, return of value if criteria is true or false. So here's what I want to do. If an item is more than X number, return too expensive. Otherwise return. Affordable. Okay, so type in the amount here. Okay, so let's type in $50. So the type of 50 well formatted by cooking this $50 either. Wait, So works. This is just a formatting thing. OK, so let's do our 1st 1 together. If this item is greater than my mount here, what I want to say if it's greater than 50 I want to say too expensive. If it's false, meaningless, it's less than 50. What I want to say. I want to say it's affordable. I'm getting that from up here. Okay, I close off my tab are my formula and hit. Enter. So is this right? Is sorry. Let me go ahead and move. That back is $50. Is $500 already greater than 50? Yes, that's correct. It is too expensive. So this dryers too expensive based on our criteria of it being greater than 50. So let's type out another one. If this washer is greater than $50 value of true is too expensive. Otherwise, it's a fort. So is that is 505 greater than $50 off course. Answer. Yes and yes, that is too expensive. Okay, so now, instead of typing it again, I want to make the formula a little bit more dynamic. What I'm gonna say is I'm comparing everything to this. $50. Okay. And so this $50 is F four. I wanna lock this cell. If you watched my be look up example, it's the same exact item. Now, when I drive this down, it's gonna always compare to the $50 here. $50 here, $50 here. Okay. Until one. Let's just check to see him make this formal. If this formula worked. Okay. These are all too expensive. That's greater than 50 0 this one's less than $50 it says it's affordable. This is greater than 50. So it's too expensive. This one's $4. So it's affordable. Okay, so now it's all working. So we know this works. And now let's put this. Let's just try it again and choose a different number. Let's say 100 $50. Okay, well, now we just saw that the microphone is now affordable. Let's change this to $225. Well, now the desk is still affordable, so the desk is affordable. That lamp is affordable, the cup is affordable and the microphone is affordable. The rest is too expensive. So let's stop here and do this one more time. So I'm gonna delete all this stuff, and you should delete it as well. So if the item is mawr than the number, type here, then return too expensive. Otherwise return before. So if this number is greater than $225 value True, it is too expensive. Value, false. It is affordable. Does this return? Yes, it's too expensive. So another couple of things here on any time I'm typing in a text. Oring. I need to use quote if I do not Look what happens to delete the quotes here and it doesn't recognize this is text. It doesn't know what to do. So any time I'm typing text in. I need to type in the quotes now. It works. Okay, so if we do not lock the cells, look what happens. So now that we know how to do this, look what happens. It's returning a value, but it is not right. This is not what we wanted to do. Okay, so this is another air prone issue. When you don't lock the cells when you're trying to make a dynamic formula, you have this problem. Okay, so now, instead of pulling 225 it's pulling nothing. Okay, so it's returning the wrong answer. So I need to delete this. I'm gonna show you again how you can lock this cell block F four F four to do that and then drag it down. If I double click this damn bar, then it shows the correct formula. It's always looking to that $225. Okay, So if I don't want to make this dynamic, let me delete this. I could do this if this number is greater than 225. Value of true. Too expensive. Value falls affordable, closed my parentheses hit. Enter. So yes, that's too expensive. And affordable, affordable, formal lamp. The cup is affordable. Microphone is for bull in the death. So the formula is working. But it is not dynamic in the fact that I have to. If I want to change this to $50 at the type it in here and then oops, I forgot I forgot to drag it down. If I don't drag it down now, this hasn't worked, right? And then possibly get fired from my job for doing something bad, then had to copy down now works. Okay. So that's why when you create a dynamic formula, you don't have to keep changing all of these and that's that's something to remember. So again, in if statement, let's recap. This is an example of an if statement if do something in return. A value of criteria is true or false, true or false. Right here. You can see here If Okay, so we start the formula. Let's do this one more time. Just hit it all. Sorry when you think equals on it. So first is my logical test. My logical test is this number is greater than this number comma value if true. Okay. True or false too expensive. It's greater than. And then what? What do I do if it's false? Open quote, affordable close quote and closed parentheses. Enter. Too expensive and remember, I drag it down. Oops. What did I do wrong? Why is this all look weird? Because I didn't lock the cell. So instead of getting a former type of dollar signs typed in dollar signs of lock at four. But I don't want this to change right, because I'm comparing every number right? The washer, the dryer fridge. I don't want to lock that cells so that when I do want to move and there we go, that is the if statement, a little bit about more about dynamic formulas, and you can see how it works. So you can apply this to a different list many different areas. So think about when is a good time to use the if formula. That's what this course is about. Toe, learn the formula and then learn. When is it a good time to use the formula
6. 07 Sumif: now where they hit some. Let's click this link. It's going Take a system that paid. So this is some if example purpose to some values if a criteria is met. Okay, so always have an original list for you. So you already have some data here? This a very small this again, obviously, Because I want you to learn this. So type in the name of the person you want to add up all donations for Let's say this is the people the name Bob, John Lilley, Rachel, etcetera. And that, actually, days amount of donations they gave us. So $4. $1 etcetera. So let's type in the name here. I want to see how much law gave us type in my formula here. What I wanna do is some, if this range. Okay, so what? You can see Here's range. This range. Sorry. That range meets this criteria. And what range do I want to some? I'm sorry. What range do I want to some? I went to some this range. Okay. I'm gonna close it. So some if this is what I'm looking at, Sea seven to see. 27 doesn't meet my criteria of G seven, which is Bob and I want to some that range hit. Enter. So Bob gave us and reform at this $17 is that correct? So if I were old school and I didn't want to use a formula and very air prone, I go in there manually. Check that. So it see Bob gave us $4 here. $4 here. So I'm not highlight than each and yellow. Four bucks year, four bucks here. Another $9 here. And I think that's it. Imagine if this was, like 1000 Longs Line. I mean, 1,000,000 lines long, right? How hard this would be. So let's say Adam up. I'm just gonna show so everyone can see it here. Obviously, you could do this map on your own $70. Okay. Yes, it worked. Okay. 17 bucks. But do you see how manual that was? And what happens if my eyes weren't good and I accidentally miss it? I'm gonna go back to know Phil here. Okay. So beauty of a dynamic formula as I could change the name John Giannini. $11. Lowly. Coming. Lilli, Give me Lily gave me $24 on Ben. Rachel, How much Rachel give me. She also getting $24. How much? Katie? Katie gave me $14. So do you see this dynamic formulas working? So let's do this again. I want you to learn the formula. So some if this range, so I highlight my range meets. What criteria meets Katie. What range do I want to some? I want some. The donations. Close my parentheses. Hit. Enter Katy gave me $14. Let's manually check this again, which is a horrible way to do it. But highlight yellow Katie's gave me $6 here. $6 here. That's 12 bucks. It's $14 6 plus six plus two is $14. So, yes, this works. Okay. Obviously, the worse is Excel. Automated Excel formulas are always better than typing in, and you're looking at it manually. So let's say that this was not a dynamic formula, which I can change. This changes. The Lauren longing me $15. Let's say that this was not a dynamic formula, So let's type it up again. If it was not dynamic some If this range is one criteria, the criteria is now type in. Lawrence. What range do. I went to some I want some of this and closed parentheses. 15 bucks. Now, let's do it. If it were dynamic some, if this range the criteria is Lauren. And then what range do I went to? Some this close parentheses, both $15. So I use the word dynamic. What is dynamic means? It means that I can change the variable without having to re read. Type the formula. Okay. If I changeable variable here from Lauren, I need to go in here and type in. John it enter in this one, all ideas have been John here. Do you see how much faster that is? So our goal is to make us many formulas possible dynamic. What you'll see here is I did not lock the cells, but I can't lock this else. Okay, so let's say in this one what sells? What? I wanna lock. I wanna lock this Sell this cell because I'm only typing in this yellow highlighted box. I'm actually locking all the cells here and hit Enter, and that's $11. Okay, let me show you one other thing. Here. Let's say I wanted to find out how much bond John and lily game in donations. OK, so it's a little bit long, a little bit longer demo, But I think this is really good to learn, so I won't do some. If this is a more real world example, My range. Here's my range. Meets what criteria? This criteria right now. And what range? Don't want to Some. I want some of this range. Let's hold on a second. Does this look right? If I drag this down, what problems will have your right? That's exactly right. Need a lock, T cells? You want lock C seven to see 27? Do I wanna lock G 11? No, because I want to see how much John gave me and Lily. So I want this to move as I drive this down. Do I wanna lock d seven d 27 yet? Because that's the attribute. That's how much they gave me. I'm gonna hit. Enter here. Now I'm gonna drag this down and you can see that this formula is now dynamic. This is an awesome. This is a sweet formula. Okay, look, Bob tells me how much John, $11 Lily, $24. Let me format this. So it looks a little bit better. And there you go, Right. This is how people use it in the real world. Because they're not going in here and typing every time. Well, how much did Bob give me? Oh, Bob, give me $17. I believe that's what Does it confuse us? How much, John giving John, give me $11. How much did little give me? Give me $24. They'll type out a list here and then just drag it down. And that's it. And that's the beauty of a dynamic formula. Now you can see how a dynamic formulas were building on each other here. I can type out bomb John Lilly. I could talk about Rachel here, drag this down $24. I can type in. Um, well, seven at Katie Put Katie and $14 done. OK, so that being said now, we understand that some it formula
7. 08 Average If: we're going to learn average. If now something clicked here, here's my average. If so, with my average gift formula, what am I going to do? My average if formula is going to tell me to average value if a criterias men. So we're to stop here. Think about this. This is very similar to some, right? That's exactly right. Average if in some if are very similar other than the fact that now you're averaging instead of something here. So let's not in our name here because we want to make this dynamic sold type in John. And let's type in the formula together here. Equal sign average if What? My range, si seven to see. 27 7 Exactly. Meets what criteria meets this John Criteria, which is sell G seven comma. What range I want to average I want to average C seven D seven d 27 hit. Enter. So John on average gave me $3.67 for every donation. What about Bob? $5.67. Kim Kim gave me $6.66 without Rachel. Rachel gave me $8. Again, this is average. So this is a dynamic formula. Let's that make this non dynamic. So hammer that home average. Oops, sorry average if open for disease. What range, Bob to Kim and how I'm doing that is I'm hitting control Shift down and it's taken me to the last possible cell close parentheses. Criteria is Rachel Average. What range this all the way down D 70 27 Center, $8. Okay, let's do the dynamic version of that like we did with the sum if average. If this range c seven to see 27 meets, what criteria Rachel Average Range down close. But he said, enter both, say, $8. So obviously we're doing incorrectly. This one. I have to go into the formula to change it. This one is based on slot on G seven. So now let's make a dynamic list here. Let's say I want to look up again. Real world example. Here Rachel fought and John. I want to see how much the average donations of this is the name, and this is the average donation of that person. So type that in make a little bit prettier. This puts out underlining the box. Now let's make it dynamic formula for this average, if this range. Right. Okay. So let's just stop you. Should this be locked or not? Yes, you're right. That should be locked in the F Florida Lock it now. What criteria? Right here. I'm looking up. Rachel Average. What range? Ranges Here. It for the locket. Now I closed on. This form is already good to go. Hit. Enter and let's just verify $8. $8. Here. I'm gonna change this to a dollar sign. So it's a short cut for that. Bring it down by dollars. 67 cents and $3.67. So, Rachel, Bob and John, these air their average donations, not a check it. Remember, this one's not dynamic. Something to lead it. Let's check in here, Bob. Fine. Dollars. 67 cents and then join $3.67. So this works now even know how to use average if in a dynamic formula in a static formula , which we have the type in the name and change it every time. And then in the real world. Example. Rachel Buck John typing in. Let's add one other person with that kim here coffee and pay some doing control. C control. Be right here. $6. 67 cents. So it is working. Let's just do one final example before the end of this video. Kim, $6. 67 cents. There you go.
8. 09 Count If: the criteria is meant going correct that So what I'm trying to find Here's how many times did X person donate so type in the name of person You want to count for all the nations for ? Answer the question How many times that x person donate Sometimes it Bob Donate or Lily or a Kim or Katie. Okay. As always, we want to start with a dynamic example cause I want to hammer that home to you so we'll start with block. Let's live in our formula here account If open parentheses weren't range, Here is my range C seven to see 27 my criteria Is this yellow box close? Bob gave three times. Okay, so now this is dynamic. So what's like Katie? Katie gave three times. So Katie here. Make sure one yellow, uh, to yellow and three yellow. Ok, so she did give three times if I manually checked it, which is the horrible way to do it. Go back here and on. Highlight this by clicking here and then no feel. So Rachel gave me three times as well. Kim Kim, give me three times. So that being said, it looks like everyone gave three time. So let's actually just make this and say, Had three more Kim's eight and $9. Okay, so let's add three more kids at the bottom here. And why didn't this change? Okay. What I wanted to teach you here is one you can change the less, but to look, the formula only says C seven to see 27. I need to change this to 30 or 29. Okay, Some people will do. See 29. What I would say. Look, if you're having a list to see 3000 okay, or 30,000 that were. You don't ever have to worry about it. So how many kids are there now? They're five. Okay, Watch Mad Kim. Now again for $1 6 Kim again for $4. Seven there. Seven times a kid has donated to us. Okay, so that is the beauty of making your list a little bit longer. Your data table, Your range. Okay, so now let's make this not dynamic. Count it this range. So the exact range in the criteria I'm not typing Kim. So seven times I'm gonna go in here and change in tow. Bob Three. Now you can really see how terrible it is when you don't make a dynamic formula. Sometimes it's OK, but long times it's really, really tough to not do that just makes it really inefficient. So now let's say the name of the person, A number of donations. Okay, let's make this look pretty putting an underlying under the box or putting a border on the box. And let's type in people's names. Say, Kim, Let's say Bob, let's say Lily, let's say Rachel, it's a number of donations. Count it this range. Okay, What's that? At 31 I'll put 31,000. And what criteria? Kim close parentheses. Is that right? Take a second. No, it's not right. And you are correct because you need to lock these cells. You lock C seven to C 31,000 and hit. Enter. So skims Give seven times Bob Lilly and Rachel gave three. Let's just make sure this is working. But Rachel down again for another two bucks. Oh, she's donated four times. Put Rachel down that many times, all for $2. I don't even know. I'm just guessing I'm just putting it down there to show you that this works now. Rachel has given 48 times and this is already changing, cause it's going down a 31,000 right? So I don't have to worry about extending this data table this range. So a couple of things that we learned here one what count? If what is counted, it counts a number of instances. We also learned in this in this scenario, how we can extend for our range from C seven and C 31,000. How making that. It makes it even more dynamic on. And then we did a dynamic list here. What you could just type in and then let's look type in my own name. Zero. Um and then John three. What Excel is doing is it's filling in the formulas for me. So now we know account. If. Is this a real world example name and number of donations that that person has given us? Or you could do a number of transactions. It doesn't have to be, you know, donations. If your mind's not, you can't understand that. Yes, you could do number transactions. You could do so many things here, and that's what counts. If the beauty of count if ISS
9. 10 TRIM : So now we're gonna do trim. Okay, This is gonna be a pretty short example. The restroom or being like, 78 minutes on this one will be a little simpler. So let's click Trim here. And when it take us to the trim example purposes the format data so you can run formulas on it there. So many times, I've received files from people that look like this. And I'm not kidding too. Okay. Where people have random space is here, there at the beginning. OK, What the problem is, is that I can't run any formulas off of this. So you've already learned if you've done these in order some if, etcetera. So let's they do a quick some if formula here, some, if this range. Okay. I need to What criteria? I'm gonna just do a static formula. Works is Bob and I want to sum this range. I mean, closing. Well, now zero. OK, why does it say zero? I'm gonna manually check in. I don't get up. Bob's here. Okay, I'm highlight him. Yellow Here. There's one up. There's two. It's only gonna my manager if I don't know what I'm doing. I'm saying, look there's something wrong with Excel. I don't understand. I need a new laptop or something, right? And that's not that's not true. What it's happening here is that it does not recognize that Bob is equal to Bob because this is really five spaces possible. And this is not a human, right. You have to tell the logical things to do. And it cannot tell that this is Bob. What you need to do is transformed this original list into a list that has no spaces. And how do you do that is used trip. Okay, so I'm in. Started calling here to keep this formula and this summit formalism to keep it, and I'm gonna show you what happens. Sorry. This is all black on the naked. No, Phil. Okay, so I'm talking our formula for trim here. It should be simple, simple, simple shrimp. So you type in the work trim type in the text used. I mean, you select your tax and hit enter, and now it says, Bob, I'm a break this down by double clicking. It's gonna bring it down all the way to Kim. Okay? And now I'm going to do the same exact thing. So I'm gonna set this up where I'm gonna bring over Bob, and I'm gonna show you here. Bob, John Lilley It's all the same, but they're just No, space is right. This is my trim. The let's let's like this out. Trimmed the list. So I have an originalist Now have a trim list, and I still have the attributes here. So now I want to know how much bought has given me. Okay, so let's type this out again. Some if this range equals work criteria, Bob, somewhat Range this range here. Enter. Done. Okay, so now I have 17 dollars that bald has given me. Bob is giving me $17 that's what a trim list does. Okay, the trim or I could have just done it here, but a trim list says trim and take out all leading spaces in that way, I can use my data, and I can actually run analysis on it. So I'm gonna delete this from here, and I'm gonna just move it up here and try one more Some. If this range is equal to John Hoops, remember, if I don't have clothes on, Texan doesn't understand what's going on and then went ranged. I want some on some this close currencies hit enter. So $11. John gave me $11. So let's, you know, as we've always been taught, these dynamic pointless here on make this yellow actually make this clear This so it doesn't confuse us. I have John here and set up making a static formula. I'm gonna delete this and make it a dynamic formula Enter year. Now I have John. So let's say Bob, 17 bucks, Lily, $24. But let's say I move this right and my range is now the original list. Got a problem here, Rachel Zero. It doesn't even tell you it's an air, right? That's the problem. Is limitations and air just zero. So I find showed this to my boss, and I was like, Look, Rachel gave me $0 she actually gave us significantly more than that. Then you know there's possibly I get fired or getting in trouble, or I get in trouble. So that's why we need to make sure that we understand what leading spaces could do. And then how to get rid of those leading spaces by using the function trip. So now you can see the power of trim because I guaranteed if you work anywhere that you're gonna get some sort of list like this and it's not gonna be this audience, right? It's gonna be something like this once base delete, and then once base, you're not gonna be able to tell. And you're gonna run this on Bob, and you're not gonna get anything back because this is really space ball front space John Wright leading is based on, and that's the problem. So using trim is always very, very helpful to remove leading spaces. If you get something weird like this, your laptops not broken, excels not broken. It's just excel is a computer, its function that's running off. Ah, logical function. And what it's finding is that there is a space ball versus just block. So that is our example for trim very, very helpful tool in understanding how Teoh Organizer
10. 11 Concatenate: So this is a fun example. It's a contaminate example. Okay, I'm gonna return to the table content since and just in case and then click Couldn't cat me or dollar signs eso purpose are the and sign so purpose. Create a unique identify or so you can run formulas on it. Okay, so this is the question over here on the right hand side and column age. I want to know how much Bob from New York gave us. However, there's a ball from San Diego I want to exclude. Let's go down here and let's look Bob from New York gave us four bucks ball from San Diego . Gave us 10 ball from New York. Gave us another four. Later on, there's another San Diego $9 from Bob. Let's see if there anymore in New York. Bob's New York bob for $44. It's all I want. I want to look at this list. I would say, Look, how much did Bob from New York give us? Well, you're gonna say, Well, something to you. Something problem is some If can only look at one column. So let's try to do that was a Okay. Someone I would just use some if Well, I'm gonna say something. What? Range this. What's my criteria? The static formula, Bob. And what range do I want to put in here? $80 0 the problem is that it's including the San Diego's, right? All the Bob's on the bottom from San Diego's now, including this. My boss just asked me for how much Bob from New York gave us. Well, what do I do? Let's say this list is 10,000 lines long. Are you gonna go with manually counted? No. Okay. What you're gonna do is using Katnik concoct Nate. Example. Okay, You're gonna use Cat named as a function to create a unique identify. Right now. I only can identify Bob This attribute A and the natural beauty of city. So what I'm gonna do is I'm gonna create a unique identify her and using a unique identifying sochi and understanding how to do data analysis. Okay? You gonna create a unique identifying without unique identifying just too hard to do really anything. So the united, unique identifies going beacon, Captain eight. Okay, it's gonna type it out for me, so I'm just gonna select it. Cat to me Open for disease. My first sex is Bob in. My second text is New York. So now I have a look here. Bob New York and Bob San Diego. Right, Sweet. So what I need to do is change this some. If I'm delete the summit now, Craig, you want some? If this range is what criteria the criteria is bob New York, and I'll show you in a second. I'm gonna rewrite that formula. And how much did they give me? A donations. Closed parentheses. This is my range to enter. Bob from New York gave me $52. Okay, How much did bomb from saying the Eagle? Just in case. I want to make sure, because a total of 80 last time. So I just copied and pasted that formula. And instead of New York, I'm gonna delete this into San Diego. $28 is 80. Yes, it is. Look down here. It's $80. So we did it correctly. Okay, that's awesome. So now we can see how the contaminated example can add two attributes together, Bob, in your create a unique identify, and then we can run our formulas on that. Okay? So that is the beauty of contentment. We have an easier way to do it. You want the right outcome. Cat named what you want to look smart just do equals two. Bob and New York, That's it. And then drag it down. Same exact thing. A lot easier. Easier on the eyes without them. Long word named Katnik. So is the same exact thing. So I want to know how much barred from New York gave us. However, there's also bought from San Diego. I want to exclude. How do I do that one? Create unique identifier and then use my stomach formula. Okay, something we already learned. If you haven't, you need to watch that video. It's such an important, I think, the right to understand. And then you type in Bob New York. Okay. And then here's one more time. I'm gonna do this and make it a little bit more dynamic. Some if this range, does he call the what criteria? Instead of typing out Justin type, I'm gonna click here B seven B seven, Bob New York And what range? I want someone to some of this range. That's it. $52. Got the same exact answer this way. Don't have type it out. Remember relative and lock references. So do I need a locket or not looking at four locks? It get a couple more times in it unlocked. So it's very important. Understand that if you give your boss a wrong information and that's not gonna be good. So again, that's Arkan Katnik example. It creates a unique identifier for us to run our data analysis on.
11. 12 LEFT, MID, RIGHT: Okay, so I'm on the left, Right? Mid example. Just in case I'm gonna go return to table of contents and do left right now. Okay. So left, right. Mid example. Purpose of the pullout. Part of a text to gain information. So how many people aren't here? Accountants, or you see something like this. You see all these accounting codes and you see an amount, and really, you have a chart of accounts, which is up here. Okay, So the type of expensive it's an office. Supply it. 001 meal zero to location. Houston age Austin A employees. So this is like a city. Employees, executives, e and staff level is s okay. You have all this stuff and you see me formats little better put a border around it and then center it and emboldened. So even accounting cold here in an amount here. What I want to know is okay. How much did we spend in Houston? That's what I want to know. All I have is all this craziness. I mean, I see a bunch of h is here, and I want to summit, but I don't know really how Teoh I could manually highlight each one that be terrible. Never manually do stuff. If you're manually doing something, you're doing it wrong, Okay? Just let you know. So, how much do we spend in Houston? I don't know. I have no idea. What you're going to use is the left. Written right in mid example. Okay. I want to pull out my account code, which is my type of expense. So pyramid type of account code. Okay, So account code or type of expense account code here, I want to pull the 1st 3 digits cause I know the count. It's always gonna be three digits long. Okay, this out. Almost every accounting system works. I know this is gonna be three digits, so I'm gonna type in left. Okay. What left does returns this best by number of characters from the start of a text string. So from the left hand side. What text I want. I want this. How many characters I want. I want three. Why three? Because I know it's either as usual one or 00 to enter. I'm gonna bring it all the way down. Okay, so now my account code pulled out, so at least I know if it's an office supply or meal. Okay, Is your Tuesday meal so I know this. $24 are spent on you. Still doesn't help me answer this question. How much do we spend in Houston? Manimal. Okay, so now unemployed out location. I want to pull out this eight. Should I wanna pull up the A C. These h is Anais Aitchison days. I wanna pull those out. How do I do it? I'm gonna use my bid function, so make type in mid accounting code. I'm gonna select my entire strength. What's number do I want to start out? Okay, so I want to start at position 123 on starting position for okay. I think so. And how many characters? Only polite. But I just want to plot one that worked. Okay, I'm gonna bring this all the way down. And what I'm what you see here is it's pulling out from the middle of the text. So from mid I'm started with this text. I'm pulling starting a number four text string for 1234 So that is my character position. And how many characters on a pole I just want one because I know locations either in a chair in a enter. And then now I'm gonna say what level of employees to finish this off when they use the term, Right. I'm gonna give this how many characters I want. I just want one. Okay, so it's pulling from the right hand side, starting on the right. It's just pulling one character enter. And now I know my employees. So now broken it all out, and I can pull in the amount again from over here. And I could answer any questions How much I spent in Houston. How much did we spend on meals? How much did executives spent? Okay, let's answer all these three questions. We manually did it. I'd be pissed. I'd be like, Oh, my gosh. Going Take me So long the man of my boss. But since I know formulas, I'm like this in technique. Two seconds. Okay, not really two seconds. But let's answer these questions. How much do we spend in Houston? What formula? How much did we spend? We should be thinking, OK, it's Some in Houston tells us it's based on criteria. So summit. What range this range? What criteria in Houston. So I'm gonna choose Houston over here. That h And what range? All want some When something this hoops. Some all the way down here. Close my formal off hit. Enter. We spent $297 in east. Well, how much did we spend on meals? Some it again. Right? Some some stuff up on meals is based on my account code. The highlight. That what criteria do I want? Meals. A 002 And how much did what range I want to do? I want to do this range. Enter. We spent $235 on meals. Well, how much did Executive Stead okay, Executive spent some, if again will range. I want employees type. So it's either E or ness. It's like my rage. How much is my criteria? Are What is my card here? Executive. And have What is my some range? Right here. I'm gonna close. Princess Exactly has spent $179. And what? You know, if you're on a company, they're gonna say Ok, well, how much did we totally spend? Because they want to find out percentages. They wanna look at whole numbers. So This is my total. I'm gonna hold it. Put a border down here to make it look nice. For $27 my total spend was born $27. So this is my gross dollars. And this is percentage of total spent. Just make a little bit more real world here. So 297 over 427. Oops. 297 divided by 4 27 I'm gonna drag this down already. Know it. Okay, so what I'm gonna do is I need a lock this total because that total Dr Boo, I wanna make this percentage by clicking here. So you're learning lots of tips and shortcuts here, Actually. Pretty sweet. So 70% of my spend was in Houston. 55% was on meals and executives only spent 42% remain. The remaining over it was on staff. Okay, So that's how we use this in the real world. Make this bold control be put a border on the bottom, make it look nice on. And this is what I give to my boss. He wants to know. How much do we spend in Houston? How much do we spend on meals. And how much did the executives spend? You're going to start with this weird accounting code. That could be 50 string of 50 characters Long could be five characters long, but still, this was like 1000 lines. You know, like, how could I do that manually? You cannot. You have to use excel. You're gonna have to use left right mid. You're gonna use some if you're combining formulas here. That's the beauty of this. You started with very simple formulas and now we're getting more complicated forms. Where were combining formulas and then awesome, like, that's the beauty of excel. Get gather. So much information analyzed. It is so much faster. If you know these formless and when to use them, right, That's the key. When do I use this formula? I get some crazy accounting code, but it's fitting a logic. I know. Logic is the count Khowst. Three digit the locations you don't nature and a and the employees here s I got This is easy, right? And then I just pull all this information over, make it look pretty. And then now I could do whatever type of analysis I want so much better this way. So that's my left, Right? Mid example. Um, it's pretty simple. I know I didn't do it multiple times, but I'll just do it one more time just for my peace of mind that you know what you're doing here. Okay, So account called. I'm typing left. This is my text. How many characters are when I went three. My location. Okay. I want to need This is my text. What do I want to start? I want to start the fourth character in how many characters don't want. I just want line a turn. A an employee. I'm starting from the right hand side. This is my text. How many characters I want I want And then Oops. Sorry that didn't work. Start from the right hand side. How many characters I want? I just want one. And that's any. Okay, let's play around here. I could do mid same text Start number two and I could do to characters. And that's gonna bring me 01 OK, or I can changes. Teoh the start position of three. One h. Starting position to four h e. Okay. So I can I can mess around with this is is again just learning the basics of a So you know how toe know how to apply it. That being said, that's the end of left right in mid.
12. 13 Nested Formulas: So now we're gonna combine two types of formulas. Okay, So you've seen this already? Type of expense. Location employees. That was in our left. Right? Mid. You see the accounting code here? The amount here. My boss asked me where did we spend money for each purses. So each one of these purchases say this is like 1000 lines plus long. He wants to know, or she wants to know. Is this in Houston or Austin? Houston or Austin? Accelerate center Right on day one. Clearly see it. They wanted to say Houston here. They wanted to say, Houston here, they wanted to say Austin here. Okay, how do I do this? Okay, so what I'm gonna use are two different formulas. First, something used mid and then on these, if okay, used to different formulas. What we're focusing on here is nested formulas in our purpose, advanced functionality and increase efficiency. Okay, so I'm gonna do it separate, and then I'm gonna ness those formulas together. So we know mid and I know how to pull out my location mid here. Start for I want one character. So here gives me that. This gives me the H OK or a So now it's either a trade, not with my boss ones. I can't get this my boss in, like years. Take this. I don't know what else to do. So this is my mid port mid part. And now I want to do it. If this is equal to age, I want to write out. Remember, it's a text oring. I need to write out Houston. If it's false, then it's gonna be awesome because I only have two cities. So Houston, make sure this works. Austin, Austin, Austin, Houston. Okay, right till works. So let's check this one. This was purchased in Austin. That makes sense. So where do we spend money here? Is used in Austin. He's in Austin so I could write out to formulas. That's totally fine. But a more efficient way to do that. It's in right a nested formula. So this is We move this up. See, this is Formula One. This is formula to misses the nested formula. Okay, The message formula. All I'm doing is putting Formula One and two in tow. One cell. Okay, first what I have to think about. I need to start from the outside So if what? This text starting with character four with one character is equal to H than what one type in Houston. Otherwise it's Austin. I'm gonna close that parentheses. So what I want to do is pull out the mid and I want to see if the h if it's a age. What I want to do is look, to make sure it is an age. It's gonna be Houston, if not in the Austin I'm gonna hit. Enter here and I'm bring this down. That's all a nested formula is. I'm combining both of these forms Formula One and formula to Okay, So Formula One and pulls out the letter. And then Formula Two converts that letter into the word Houston. Okay, And that's what my mid my message formula is doing and saying, OK, first, if mid this is able to age, give me Houston or give me Austin and that's all I'm doing. I'm just combining Formula One and formula to, and that is the simple nested formula. You'll get way better at this as time goes on, but I just wanted to show you what a mess that formula is. All you're doing is combining multiple formulas into one cell. So any time you see stuff like this, don't get overwhelmed, say, look, you don't take a step back and say, What? What is this person doing? They're just combining two different formulas. That's it.
13. 14 Data Validation: So now on the data validation tab. Okay, data validation doesn't really mean anything to you if you don't know what data violation is. But what is it? The purpose of this is the limit. What people can input. Okay. You want to limit what they can input into your Excel spreadsheet. So the account code, for example. So we've all seen this in our left ridden mid and right examples and in our message formula example. So I only want them to type in either 001 or 002 Okay, in account code, location. Either a churn, a me account location. Either a churn, a andan, employees either. Earn s It's got a hit. It's got to be one of those two things. I don't want them employees to type in teeth. Okay, that doesn't mean anything to be so Now I'm pissed because you talked in whatever you wanted retyped in p here or you accidentally try to type. It s but then you get a okay. I want to prevent that from happening. How do I prevent that from happening? Here is the best part. It's called data validation. What you're gonna do is Open up, Excel. You gonna go to data? You gonna go to data validation? Invalidation criteria. Allow what? You want to base it off of a list, Okay. And what's your source information? Either 001 or 002 That's all I did. I just clicked over here and then hit. Okay. Okay. Now I can only type in 001 or 00 to type in a 002 So I've typed in both 001002 and Oregon selected from a list. And that's the beauty of it. This was a little bit different because I had to put a apostrophe Teoh have the leading zeros in there. Don't worry about that for right now, Okay? So just focus on the fact that I can only select years ago wonders years over to buy copy and paste this down than all of these cells. I can only select either 001 or 002 So now with location, let's do the same thing. Data validation. Allow only a list, and I only want allow a turn A Okay, so I'm gonna hit OK, and I'm gonna copy and paste this down. And what you can see here is, every time I click on this cell, you see a little drop down. That's how you know there's a data validation by typing a h h h h a h or if I type in t cannot, you're not allowed this value doesn't Max undated Validation restrictions to find for the cell. Okay, so I can't do that like an employee. I'm using your spreadsheet like O t. Why? Then I got to go to you and ask that question. You're going to say, Look, so you can Onley type in HR A. Because that's our offices are only in Houston and Austin. You can type in. You can type in end for New York. That doesn't We don't have an office in New York. And now the types of employees there's only executives in their staff. How do I do this? Click on data data validation. Allow only a list. And then what's my source of information? Either Urine s it. Okay. And then now I can only type. You're in s and I paste it down. OK, But a here a here and my employees can either only b E. R s And what happens if by trying type um janitor Saroj? No, you can type in J son. Why can't I type in J? Well, the problem is, we only have executive and staff level, Jr congenital considered staff. So put it s for the janitor. And then you put in the amount here. Just make up numbers. I'll teach you a random formula now, actually, just type in whatever number you want. And then now you can limit what people are in putting Andi even on the amount I can actually do it. David. Validation on the amount. Watch this data. Data validation Allow ah, whole number minimum between zero and 100 or 199 would say OK, now if I times have been 200 not allowed by type in 1 50 it is allowed 21 allowed. 21 negative. 14 negative. 55. I didn't copy and paste down my data. Validation Copying bases, data validation now type of negative. 50 doesn't work. 200 doesn't work. Okay, so now I know how to do all tipper types of data validation just for here. I'm gonna put in pull down data validation so you can see it. You can put in any value hold number A decimal from a list, which is all the ones we did A date, a time, attacks lead or a custom. Okay, so you can even change the input message. So let's do list. You can change the input message with the cell is selected What do you want to say? Title select type of employee Onley staff and put masses Only staff or executives allowed. Okay. And an air alert. You can actually say please Onley executives or staff allowed. As always. I'm not gonna go through every step with you. Play around with this, OK? And now let's say a type of tea, please. Onley executives or staff allowed, Please stop and look so I can look at that Done OK and now select type of employees only Staffer executives allowed E or S C. So it's coming up all the cool things you can play around with data validation. They're very cool. The main thing is that you're limiting what they can type in either zero or one or two years or two here. This list h r A. In this list estern e and this between a certain number. That's it. That's a simple part. Data validation. So incredibly useful when you're trying to prevent 1000 employees of putting on whatever they want into your Excel spreadsheet.
14. 15 Remove Duplicates: do book. It's OK. There's a very easy way to do this. I use this function a lot. It's not a formula, it's just a function. So how many different cities did we received donations from? So let's say that we have all of these different cities, but I see a bunch of different lists. And let's say this list that 1000 lines long What I want to do is find out how many unique cities we've received donations from. So here are 22 cities that we received donations from. I'm gonna copy and paste that over. All I did was do Control C Control V. Now I will not go to data. So let's say I was informed that data and then remove duplicates. That's it removed. Do quits column H hit. Okay, 17 duplicates values found and removed. Five. Duplicate values remain. I mean, five unique values remain. So the unique values or New York used in Los Angeles, San Diego in Dallas. These are my unique values. Let's do that again. Copy over my list. Selected control. See pace control. Be, um, go to sales. Or do you go to data? Remove duplicates. Select my column column H, is that correct? Yes, that's right. Okay. 17 duplicate values found removed. Five unique values remain. These air my unique values, That's it. Unique. I mean, duplicate values is a very good thing to know, because there's a lot of times your boss or someone's gonna ask you. Hey, for New York, Houston, Los Angeles, San Diego in Dallas. How much money did we spend our did we receive from there? Okay, so let me sell you the next step. Instead of typing out each one of these, you can just do remove duplicates and then do a summit. Okay, So some if this range equals this criteria and I locked it, what range do I want? Some. This range. And I locked it again, and then I'm gonna go back to home. Make this a dollar sign. Boo. Okay, $169 total 52 from New York dollar from Houston. Let me confirm this is the correct amount of some All of this. $169 Right? Some this. Hold it. Border. There you go. And that's what you would do in the real world. Somebody says, Hey, how much in total donations that we received from each unique city. You remove duplicates here, and then you do some if here, so combining multiple things that we're learning throughout this course. And that's why this course is so important and learning when to use it. Okay, so don't go through it like Well, son, I don't know when to use this stuff. Think about it. You've got to think about when to use it, cause that's gonna give you the power of excel.
15. 16 Conditional Formatting and Data Filter: So this will be a very fairly quick lesson on conditional for batting. It's a really strong tool. It's really easy to learn. So let's say the real world examples your boss comes over there like, Okay, you know what? Here's an excel file. I want you to highlight any transaction or donation over $50. Okay, so over here, $50. So do you manually go in there and select this one? You're like, Oh, this is $80 on the highlight. This one Red. I'm gonna highlight this one, Red, because that's only $50. No, you would never do that. So how do you do that? First, you're gonna select the data that you want to world. Wisoff highlighted all my data. It d and what you're gonna do is let's say that you're informed is gonna go home. You click up here on a conditional formatting. You highlight cells greater than this is 45 50 and already selected it. So I want to change it to 50 and then you can change it with yellow Phil or red. Fill whatever you want to do and you can change this number. Tow anything greater than one have been greater than 4 10 20 Okay, you can change this, but let's say 50 because that's what the spreadsheet said it. OK, and now we have three things over $50. Another way we can do this. Yes, it's conditional formatting, but is go data in the credit filter. Go here and you could do number filters greater than 50. We should get the same exact list. And that Pretty cool. So we actually learned conditional formatting and data filters all on this one. Quick lesson with the information that was already provided. So again, conditional formatting is a really great tool. So I can go here, go home, conditional formatting. And then I can actually clear the rule here. And then it's gone. Or I can create a new rule greater than Or I could do one less. Stan, how about that? Less than 20 bucks. Let's say okay. And highlights of all red to do the data filter again, go to data filter. Click this down. Air number filters less than $20. Should all be read. Beauty of data filters in conditional formatting. Very quick lesson on both on. And you know, again, what I'm trying to help you do is avoid manual work, make yourself faster at your job and more accurate
16. 17 - Excel Outro: like always. I like to end my forces like I began them with us, looking at each other face to face and saying, Look, I hope you enjoy this course. I hope you got the foundations Now We took time to learn the look up way spent time to learn if some, if averages count if we learn trim whenever our information isn't given to us, our data isn't given to us in a data friendly format cabinet when we want to create unique values left right mid when we want to pull certain shrink's out of accounting streams or something similar like that way. Also learned other self like invested formulas, which is the key to becoming an Advanced Excel user. David Validation Toe limit What people could put into Your Excel file removing duplicates and conditional formatting. I will be adding other things, too, this course, but right now I want you to focus on the foundation. Okay, so if you look up the if statement thinking logically, some if averages and counted that understanding that criteria and understanding dynamic formulas versus static formulas when you need to use quotes for text string stuff like that , just pay attention to my videos. I know sometimes I speak quickly, but because I'm so passionate. So if you don't, if I speak too quickly, just rewind it and watch it again. That's the a beauty of these videos that you can just watch them over and over. I do strongly believe it. Let me say this very clearly that the formulas that I presented to here today are the basis of what you need to know to build your foundation. Okay? And some people may come back and be like, Hey, somewhere about index matching all that other stuff. Don't worry about that right now. Worry about these formulas. Get toe, learn these know these. And then when you become strong and very well versed with these London's, then you can learn new forms. Okay, so focus on these formulas. These formulas are the foundation and are the core becoming strong Excel user s. So with that being said, I hope you enjoyed the course today and please let me know if you have questions
BrainyMoney And Son Han, CFA,CPA, Personal Finance Made Easy!
