Gain in-depth knowledge for passing your exam with Exam-Labs MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course. The most trusted and reliable name for studying and passing with VCE files which include Microsoft Excel Expert MO-201 practice test questions and answers, study guide and exam practice test questions. Unlike any other MO-201: Microsoft Excel Expert (Excel and Excel 2019) video training course for your certification exam.

Advanced Formulas & Macros

3. Explicit vs. Structured References

Okay, so we just talked about the difference between fixed and relative references. Now we're going to see the difference between explicit and structured references. Explicit references are the traditional "one style cell" references that Excel uses by default. So when discussing reference types, we were using explicit references, and this is because we were working with data ranges. However, when you convert a data range into an Excel table, then structured references are applied, and these references include the entire table and column names rather than individual cells. Now, I know this sounds a bit ambiguous, but let's take a look at an example. Let's consider this table with sales data by employee and department. Now, as far as Excel is concerned, this is a data range. We've added formatting to make it look like a table, but really it's a series of text, strings, and numerical values. So if we were to calculate the total sales for the company, we would need to explicitly tell Excel where these sales are, in this case, from C 2 to C 7. Now, let's say we converted this data range into something Excel recognises as a table. So to do that, we can select any cell in the range and press Control T. Excel is going to automatically outline the data, ask if it has headers, which in this case it does, and then we can go ahead and create the table. So now your data will look something like this. Excel usually likes to add formatting to its tables. To be honest, I'm not a huge fan, but you can easily change the formatting to whatever you'd like. We can change the table name to something significant like "depth sales." So for department sales, and now if we were to calculate the total sales for the company and went to select range C 2 to C 7, you'd see that Excel recognises this as the sales column in our department sales table. And the formula would look something like this: And this is the difference betweenan explicit and a structured reference. And in both scenarios, we'd get the same exact result, which is total sales of $3,970. So in the end, the main difference is really just the way these references look, where structured references offer a greater degree of readability and easy understanding for end users. But the process of writing them is almost identical, as most of the time in Excel you're just using your mouse for the selections. And Excel will then translate this into explicit or structured references, depending on which one applies. I still wanted to include this lecture, though, becausesome of the tasks in the Mo 201 examuse structured references, while others use explicit references. So we'll be using default explicit references for the course project. But some of the tasks in the practise tests will include structured references so you can practice. But before that, let's jump to Excel and practise using these. Right now, here in Excel, we'll still be working with the Excel Expert Reference Types workbook. So if you're following along, make sure you have it open to the explicit versus structured tab. And here you'll see we have what are basically two identical tables. We have the products that we actually saw in the last exercise, the category, the third quarter sales, and the margin for each. So this is a date range. And here we have the same exact data, but formatted as a data table. And what we can do first is, if we select any cell within that table, you'll see that this contextual menu opens up, which is table design. And if we select that, we see that the table name is, by default, table one. But we can give it something a bit more meaningful. So perhaps Q3 sales. And there we go. Now, what we want to calculate is the total sales for the third quarter and the total profit. Now, you'll notice that we don't have a profit column yet, but we can go ahead and add that. So we'll write profit. You'll see, Excel automatically adds the formatting, and this is simply the sale multiplied by the margin. So we'll press equals, and we can use the arrow to go over to the sales. So two. As you can see, this is an explicit reference multiplied by the margin, which is D. To press Enter, we get $3,800 for the smartspeakers' tracking, and we get the rest. Now let's see what happens when we do the same thing but inside our table. So let's write profit down here. As you can see, Excel intelligently needed to add this column to our table, so it added the table formatting as well. And now let's do the same thing and calculate the profit. So press equals and watch what happens as we wend over to the rest of the columns. It knows that this is a margin, and it knows that this is a sale. Now you'll see something that we haven't seen before, which is the at symbol before the sale. And what this means is that they're just taking the sales for this particular row. If we were to press shift and select the entire sales range or column, you'll see that now we're selecting the entire sales for our table, but in this case, we just want the sales for this particular product. We want to multiply that by the margin for that product. Press Enter, and as you can see, the table automatically fills that formula for the rest of the rows, which is exactly what we want as we're getting the same results. Now let's calculate our sales. So we're just going to write the sum of our sales column. So, c2 through c nine.For our explicit reference, we get 45 grand, and we'll just do the same for total profit. Select that e two to E nine. Perfect. Now let's do it for our structured references. So total sales are going to be the sum. And instead of selecting our range, what we can do is hover our mouse over the Sales column until we see this arrow pointing down. And if we simply click, it will automatically select the data in our Sales column, which, as you can see, is a Sales column in our Q Three sales table. Close parentheses, and we get the same result. Now we repeat the process for our Profit column's profit total. There we go. So it's some of our Profitcolumn in our quarter three Sales. and we get the same result as well. Now, one of the advantages of working with tables versus data ranges is that they automatically include data that's added or new rows of data that are added to the table. So similar to what happened when we wrote Profit here in Columbia, let's see what happens when we add another product to both of these tables. So let's say after tennis racket we write leather jacket; it's going to be clothing. Let's say the sales were, I don't know, eight grand with a margin of 15%. And as you can see, Excel is very smart even when working with data ranges. So it automatically knew to format this as currency. This is a percentage. And it even added our formula to the profit calculation, which is absolutely correct. And you may have also noticed that when we added the sales, it knew to include cell CTEN in our sum for total sales. And we can press F-2 to prove that. So there we go. But Excel wasn't smart enough, however, to know to include this profit or sale in our total profit formula. So, as you can see, this is only from sales E two to E nine. Now, we can adjust this ourselves, but if we hadn't noticed this, then it may lead to misleading data. So there we go. Now let's see what happens when we do the same thing using our table. So, leather jacket clothing is going to be 81 percent. So the same thing happened, and I knew how to apply the correct formatting. It automatically calculated the profit. But because we're not using explicit references here, simply referencing the sales in our Q-3 table and the profit in our Q-3 table, it knew to include all of the new data. And that's it. So again, another quick, easy example that very clearly illustrates the differences between working with structured versus unstructured data and the small advantages that one may have over the other.

4. Logical Operations

moving on to logical operations. Logical operations are made up of logical functions, which are Excel decisionmaking tools. And logical functions are based on the if statement. So if the logical expression is true, then do this; otherwise, do that. And as a quick example, let's say if you're hungry, then order a pizza; otherwise, well, don't order a pizza. So a logical expression compares numbers, text, strings, cells, or functions using operators. And here are the most common operators used in logical expressions. We have equal to, greater than, less than, greater than or equal to, less than or equal to, and not equal to. And here to the right, we can see examples of logical expressions using these operators and their results. So it's ten equal to seven? No. So the result is false. Is ten greater than seven? Well, yes, so the result is true. And you can even compare letters based on their order in the alphabet. Is a less than or greater than r, or does it come before r? Yes, so the result is true. Is it greater than or equal to r? No, so false. And you can also do this with dates using their underlying date value, which will be covered in the date and time functions portion of this objective domain. So is May 2012 less than or equal to May 2008? No, it's not. So the result is false. Is it not equal to May 2008? Of course, this is true. So, as you can see, it's pretty simple, intuitive logic. Now an important note when using these in formulas: Excel will interpret a logical value of true as a one and the value of false as a zero. So just keep that in the back of your mind. Now these are the logical functions we'll review in the following lectures. We have the essential if function, and we have the if function, which performs more than one logical test. We also have the not and or functions, which often work as operators inside of an if function. And finally the switch function, which is similar to an if statement but compares exact matches against the result of a single expression. We'll also review conditional functions, which perform basic arithmetic functions like count, sum, average, and maximum, but require a given criteria or set of criteria. Now there's no need to get hooked or hung up on these yet, as we're going to review them all in separate lectures. But for now, let's roll up our sleeves and get ready to start using logical operations in the course project.

5. The IF Function

We got our bases covered with reference types and went over what logical operations are. Now it's time to get real with the if function. The if function checks whether a condition is met and returns one value if true and another value if false. Now, syntax-wise, the if function has three arguments separated by a comma, the logical test, which, as its name states, is a logical test or expression that can return true or false. So, for example, a one equals pizza, or b two is greater than ten. Now, the value of true is where you tell Excel what to return if the logical test is true. So this can be a value, a reference, or even another formula. And finally, we have the value of false. And this is where you tell Excel what to return if the logical test is false. Now, let's look at an example. Let's consider this table with movies, their genres, and their rating according to Rotten Tomatoes. For those of you not familiar with Rotten Tomatoes, it is sort of an online movie critic. And they evaluate movies as either fresh if they're good or rotten if they're not so good. So these are actually some of my favourite movies, and I'm interested in seeing which are fresh and which are rotten. Well, we know that movies with an AA rating of 60 or higher are fresh. So we can use an if function here in column D to determine where they land in the tomato meter. So our formula would look like this: If C2 is greater than or equal to 60 and this is my logical test, then my value of true will be fresh. And notice that fresh is wrapped in quotation marks, as this is required for all text drinks in a formula, then comma over to my value of false. So if the rating is not greater than or equal to 60, we'll then return it "rotten." Now, if we pressed enter and copied this down, we'd get this. So, as you can see, all movies with a rating of 60 or higher scored fresh on the tomato meter, while the rest were deemed rotten. And that's all there is to it. Now let's jump over to Excel and apply this to our Airbnb data here in Excel. We'll finally be working in the Excel Expert Course project workbook. So if you're following along, make sure you have this open to the Places worksheet. And remember that you can download this file from the Download Course Resources lecture in section one of the course. Now, if you recall, the Places tab contains information about all the listings for Airbnb in New York City, which we're going to be using to analyse the marketplace. Now, the first thing I'm interested in is looking at the Accommodate field, which states the number of guests allowed to stay in each place. And if we look at the current values, we can see that there are places that only accommodate one guest, all the way up to places that can accommodate up to 22 guests. And while this is interesting in itself, the range of numbers is too broad for me to be able to analyse properly. So what I want to do is divide these into size categories to make them easier to digest. And this is where the If function comes in. So first, let's add a new column here between the accommodations and the price columns. So I can right-click here on my price column and select Insert. Let's name this size. Okay, so how do we want to group these? If we think about it, the smallest, most basic size of a hotel room or an AirBnB room accommodates up to two people. So we can use that as our first category. Now let's start writing our IF function. So it is equal if we'll open parentheses and we'll select ourselves as two, which are the number of guests that this place accommodates, and if that is less than or equal to two. So, if this location can only accommodate two or fewer guests, then comma, then the size of this location will be small, which remember, we have to write between quotation marks because this is a text string, then comma over to the value of false. So if this is not true, and the place accommodates more than two guests, then let's say the size is simply large. Again, if they function with the parentheses, the space between quotation marks can be closed. Press enter, and we get our first small assault of small.And this is perfect because this place only accommodates two guests. Now let's apply this to the rest of our places, and we can do that by double clicking this green square at the bottom right corner of the cell. and let's see. Did this work so far? It looks like it did. We have small for one person, small for two, alarge place since it accommodates five, we have another largeone here for four, et cetera, et cetera, et cetera. So to actually check on this more thoroughly, we can filter our whole table by size. So let's say we are only going to select our small places. And if we open up our "accommodates" field, we'll see that we're only looking at places that can accommodate one or two guests, which is exactly what we want. And if we filter these by the large places, then we'llsee that we have the places that accommodate three or morepeople, which is exactly what we wanted as well. So there it is. a quick example of how to use the if function and how it's a great tool for creating categories using our data. So I'm just going to clear the filter size, and we're good to go.

6. Nested IF Functions vs. The IFS Function

moving on to nested if functions. Nested if functions allow you to include multiple logical tests within a single formula, and they work by including or nesting an if function within the value of true or false arguments of another if function. I know this may sound confusing at first, but let's take a look at an example to clear things up. We have the same group of Rotten Tomato movies from the previous lecture, and we're actually going to determine the ranking in the tomato mirror again. But before we do that, there was actually a category that we left out last time. So other than movies being qualified as fresh or rotten, the movie can also be qualified as "certified fresh." which means that not only is it good, but it's excellent. And the movie is certified fresh when its rating is higher than 75. So considering this additional piece of logic, we can no longer determine where each movie lands in the tomato metre with a single if function. We know that if the rating is higher than 75, then it's certified fresh. But if it isn't, then it can either be fresh or rotten. So we need to add another if function to our value of false to determine which one of those it is, turning it into a nested if function. So let's take a look at the function itself. So if c 2 is greater than 75, then our value of true is certified fresh. Now, if it isn't greater than 75, then our value of false is fresh, and we need to open another if function where if c is greater than 60, then our value of true will be fresh. And finally, if it's not greater than 75 or 60, then our value of false will be rotten. So applied, the result would look like this: We have three certified fresh movies with ratings of 82, 94, and 92; fresh movies with ratings of 73 each; and three rotten movies with ratings of 36, 41, and 19, which fits our logic perfectly. Now, quick pro tip: when writing nested functions, you can copy and paste repetitive pieces and tweak their individual elements to save time rather than writing them all from scratch. Before moving on to Excel, let's also go over the Ifs function, which is very similar to working with nested if functions. So the if function checks whether one or more conditions are met and returns a value for the first true condition. Syntax-wise, it has two required arguments and then a series of optional logical tests that can be added. So the logical test, as you know, is an expression that can return true or false, and the value of true is what is returned. If that test is true, you can continue to add logical tests and their corresponding values, if true, as needed. So, looking at the same example, the if-function would look like this: if c2 is greater than 75, which is the first logical test, then it returns certified fresh. The second logical test evaluates if C2 is greater than 60 and returns fresh if that's the case. Now, you may have noticed that there is no value if it falls here, which usually serves the purpose of a sort of catch-all in case any values don't meet the criteria we specified. Therefore, we need to include a final logical test here, saying that if C2 is less than or equal to 60, it will then return rotten. And as you can see, we'd end up with the same exact result from the nested if example. Now let's head over to Excel and put this to practice. So we're back in the Excel ExpertCourse project file in the Places tab. And if you recall, we used an if function to group the places by size, where small places can accommodate up to two guests and large places accommodate more than two guests. And while I'm okay with the small-size category, I'm not entirely happy with the large size. I wouldn't really consider a place that accommodates three people as large. So I say we should add a medium-sized category in between. So if two people is a standard small size for hotels and AirBnB, then I think the next size up would be one that accommodates a standard family. so anything from three to five people. And let's use what we just learned about nested if functions to get this set up. So in the same size column, let's edit the existing formula by pressing F two.And as it stands, we're saying if the place accommodates two or fewer guests, then the size is small. And this part we like, so it's staying in our formula. But if it can accommodate more than two guests, then it can either be medium or large. So we need to delete what we have in our value if false here, because it's only saying that it's going to be large, and we need to add another if function. But before we do that, though, let's use our pro tip and copy our existing if function without the equal sign. We will copy that. And now we can delete what we have here, and we can paste that if function allows. So what do we have now? Well, again, we're saying if the place accommodates two or fewer guests, then the size is small. If it accommodates more than two, then we need to modify our logical tests here and ask if the place accommodates five or fewer guests. So if E 2 is less than or equal to 5, then the size will be medium. If it's not small or medium, or rather, if it accommodates more than five guests, then it's going to be large. So this is fine. We can press Enter and see what we've got. So this is still small. Ironically, since it's called a large furnace room, And if we apply this to the rest of the places So double-click here on the corner. We'll see that we do have a few medium places here. So this has five guests, this has four, and we do have a large place here that accommodates eight guests. And let's verify this the same way that we did last time. So if we filter the size by small, we'll see that we're only looking at places that can accommodate one or two guests. If we filter by medium, we'll see we're looking at places that can accommodate three, four, or five guests. So our new nested function is working. And if we filter by large, then we're looking at places that can accommodate more than five people, which is awesome. So before we finish, let's actually remove the filters here and then rewrite this by using the Ifs function. So I'm going to select cell #2 with my first formula here, and I'm going to press Delete to clear the cell. Now let's open up our if function. So, if if equals if, and our first logical test is that two is less than or equal to two, then our true value is that place is small. Press Comma. Our second logical test is going to be if e2 is less than or equal to five for our medium place, which is our value of true. And finally, we need to add a third logical test for the rest of the places, which is going to be when e two is greater than e five. and that is when the value of truth is going to be large. So we'll close this formula out, press Enter, and write this down. And as you can tell from the fact that it looks like nothing happened, well, we got the same exact results. So that's it for using nested if functions or the if function to include multiple logical tests in a single formula.

7. The NOT & “<>” Operators

Next up, we'll be reviewing the equal or not equal to operators. The not function returns the opposite result of a logical expression. So it basically changes false to true or true to false, and it's referred to as an operator, since it's commonly used within the logical test argument of an if function. Syntax-wise, it only has one logical argument, which, as you know by now, is the logical test that can return true or false. So let's go over one final example using our Rotten Tomatoes movie data, which, if anyone's interested, is available to download for free in the Data Playground on the Maven Analytics website. Now we've determined the tomato metre ranking for each film, and now we need to know the final verdict. That being said, is it recommended viewing or not? Well, as long as it's not rotten, I'd say that it is. So let's write that into a formula. We'll start with an if function and then use the not function as a logical test. I know it's a bit counterintuitive to read, but if "not D2 equals Rotten" translates to "if cell D2 is not equal to Rotten," then that movie is recommended. And if that's false, So if CD 2 is actually equal to Rotten, then that movie is not recommended. And you can actually write that same formula but using the not equal to operator as well, which actually makes for easier reading. So again, if cell D2 is not equal to rotten, then that movie is recommended. Otherwise, it's not recommended. And both of these formulas would give us the same result. Now let's jump to our course project file in Excel and put this to good use. Here in Excel, we'll still be working in the Places tab. And so far, in order to gain a better understanding of the Airbnb marketplace in New York City, we added the size column based on the Accommodate field to differentiate between small, medium, and large places. And now we're going to focus on the availability field. And as a refresher, each value in this field represents the number of days that the host for each place has listed it as available to be booked during the year. So they can go from zero days if it's not available at all to 365 days if it's available any day of the year. And since we're trying to gather an understanding of the current market, we may not want to consider places that are inactive or have zero availability. And for that, let's actually add another column here, which we can call status. Now, as long as the availability isn't zero, then the status for that place should be active. And if the availability is zero, then that place is inactive. Now, does any of this sound familiar? Well, this is a perfect situation to use the not function. So we'll start with our equal sign,and then if function, open that up. And here's a logical test we're going to use or not perform. So for a place to be active, what do we need its availability to not be? Well, we needed to not be equal to zero, right? So we can close our not function. And if our availability is not equal to zero, then our value is true. It's going to be at that place when it's active. And if our availability is zero, well, then our status is going to be active and we'll close our if function. Let's put this down by pressing enter. So there we go. All of these places are active since they do have availability. But for this one that has zero, we're getting inactive, which is exactly what we wanted. So again, a quick double check Let's look at our active places. So from one to 365, Perfect. And we've got all our zeros. Beautiful. Finally, let's clear our status filter and replace our not-function with a not-equal operator. So let's press F2 to edit and delete this not-function from our logical test. And to replace it, we're basically just going to say the same thing basically.So if our availability, or L 2, is equal to zero, then that place will be active. And if not, or if it is equal to zero, then that place will be inactive. Press Enter, apply this down andwe get exactly the same results. So there we go, a nice little bit of the not-operator that is going to allow us to slice and dice our AirBnB data in new and helpful ways.

Pay a fraction of the cost to study with Exam-Labs MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course. Passing the certification exams have never been easier. With the complete self-paced exam prep solution including MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course, practice test questions and answers, exam practice test questions and study guide, you have nothing to worry about for your next certification exam.

Hide