Pass Microsoft Excel Expert MO-201 Exam in First Attempt Easily

About the MO-201 Exam

1. Exam Structure

All right, time to start talking about the exam itself, beginning with its structure. Number one, the exam is presented using Compass, which is a web-based interface from a sort of port. When you show up to take the test, your proctor will open up Compass on the computer and launch the exam, and you won't be able to leave Compass until you've completed it. Now, there's no need to worry about this right now, as we're going to talk about the interface in depth in another lecture. Number two, this is a project-based exam, so there's going to be no multiple-choice questions. It's made up of around six projects, each of which is a real-world business case, and has a total of around 25 tasks between those projects that revolve around those business scenarios. number three, you'll have 15 minutes to complete the exam. So pacing is very important. If you're having trouble with a specific task, you can mark it for review and revisit it as many times as you'd like within this time limit. And finally, the exam is scored from zero to 10, and a grade of 700 or more is required to become an official Microsoft certified expert. Your exam score report is going to be available immediately after submitting the exam. Now, Microsoft doesn't provide task-level feedback, but they do give you score bars for each of the exam subjective domains, which we're going to review in the next lecture.

2. Objective Domains & Skills Measured

Okay, time to talk about the actual content of the exam. So what will Microsoft be evaluating to certify me as an Excel expert? Well, the skills measured in the Mo 201Excel Expert Exam are broken up into four objective domains: creating advanced formulas and macros, managing and formatting data, creating advanced charts and tables, and managing workbook options and settings. Now, each of these objective domains contains a set of skills that can be measured and demonstrate competence in that area. So for advanced formulas and macros, you'll need to be able to perform logical operations and formulas, use advanced date and time functions, create and modify simple macros, look up data using functions, perform data analysis, and troubleshoot formulas. For managing and formatting data, you need to be able to fill cells based on existing data, format and validate data, and apply advanced conditional formatting and filtering. For advanced charts and tables, you'll need to be able to create and modify advanced charts, create and modify Pivot tables, and create and modify Pivot charts. And finally, for workbook options and settings, you'll need to be able to manage workbooks, prepare workbooks for collaboration, and use and configure language options. So, before we continue, if you download the official skillsmeasured PDF from the MO 201 website, you'll notice that the order is a bit different in the course. And then we broke some of these skills down to illustrate the topics more clearly. And this was done with the purpose of having a natural flow between the objective domains, the skills measured, and the course project. But other than that, these are all the topics covered in the exam and in the course. So I hope you're ready.

3. Exam Interface & Scheduling

Alright, the exam interface is what you'll be interacting with while taking the exam. So once you're encompassed in the exam and it's started, your screen will look like this: As you can see, the Compass interface is divided into two main sections, the Project File and the Exam Panel. Now, the project file is the Excel interface, where you're going to be performing the tasks listed in the Exam Panel below. In other words, this is Excel as you know it. Just note that you won't actually need to open or save any Excel files yourself. These are going to be opened automatically within Compass when you start a new project and will be saved once you submit it. Now the Exam Panel is where you can view the project tasks, check the time remaining, restart or submit projects, and mark tasks as complete for review or for feedback. Now just note that restarting a project won't restart the time; it's just going to clear the tasks so you can get a fresh start. Now, what you see marked as number two here in the picture is simply the divider line between the project file and the exam panel, which you can move up and down to resize them. Now, for example, summarise The Exam Summary view will appear once you've submitted all of your projects, but it can also be accessed with the Go to Summary button in the Exam Panel. Now, as you can see, it contains the tasks for all the projects and indicates whether they've been marked as complete for review or for feedback. So from here, you can revisit any task by clicking on it and making any necessary changes until you finish the exam. Now, once the exam is finished and submitted, you'll have the opportunity to leave feedback about specific exam items that you've marked. So once you've wrapped up this course and feel ready for the AC exam, well, you'll need to schedule it. Now, all Microsoft Office Specialist exams are conducted at Certipport-authorized testing centres and typically can't be taken remotely. Now in special cases, Microsoft may offer remote testing arrangements, so you can check out the latest updates at the link below. To find your nearest test center, you can head to www.surfour.com/locator. And here you'll need to enter your location, select the programme version and exam, and then just click Search to find the nearest centers. Finally, the cost of the exam varies depending on where it will be proctored, but it usually costs around $100.

4. Helpful Resources

As a final note regarding the exam, I'd like to introduce you to a few helpful resources. Now the first, which you probably already stumbled upon by now, is a Microsoft certification website that contains official exam details, a link so you can schedule the exam, a PDF file summarising the skills measured, which I've included as a resource in this lecture, and links to other helpful resources like exam policies and frequently asked questions. So, if you haven't already done so, this is a great place to start familiarising yourself with Microsoft's Mo 201 exam. Now, next is a certified port MoS exam tutorial, and this is great because it surrounds them with helpful information about their exam environment and offers a slightly more detailed overview of the exam interface tasks and panel controls. You can access them from the link below, but I've also included the PDF as a resource you can download in this lecture. And last, but certainly not least, is geometry. And Gmetrics is a certified and approved test simulator to help you prepare for the MO201Excel Expert exam, among many others. I actually use their practise test myself specifically for this certification to get an idea of what they are like, and I can assure you that they do a fantastic job of recreating the real test experience. So this is actually a screenshot of what their simulator looks like, which as you can tell, is very similar to the real test interface that we showed in the last lecture. So they have practise tests both with and without time. So you can perfect your skills first, and then you can put them to the test against the clock. And the best thing for me was this help button that you can see next to the tasks, which actually walks you through each of the tasks in case you have any trouble. Now, Geometrics isn't free, but it's definitely worth checking out. And that's it. We're ready to go over the course project now and get started on the first subjective domain.

Hide

The Course Project

1. Course Project Intro

The time has come to introduce the Course Project. So here's the situation. You've just moved to New York City, and you're considering purchasing a property to list for rental in the Airbnb marketplace. Now, you've already walked through Central Park, you've been to the top of the Empire State Building, and you've had more pizza than you're willing to admit. So you're ready to go. And to get you started, you have access to Airbnb listing data from New York, the property details for your potential purchase, and occupation rates for a similar property in town. And your goal is to leverage your advanced Excel skills to analyse the Airbnb landscape, explore the data at hand, and determine the potential return on your investment. To accomplish this, you're going to need to do a few key things Explore the Airbnb data using advanced formulas and formatting. Use analytics tools to forecast your potential earnings. Create charts and pivot tables to supplement your analysis and configure workbooks for clients. collaboration with other users who are going to back your investment. So with that, let's roll up our sleeves and get started.

2. The Airbnb Dataset

Before moving on to our first objective domain, I'd like to do a quick overview of the Airbnb data set we'll be working with so you can start to get familiar with it. So this is the main data table, which contains listing level data, meaning that each row or record corresponds to a single place listed on Airbnb. And as you can see, each place has an associated host and neighbourhood ID, as well as the room type and several numerical measures like the price and the rating. I'll go over each one of these once we actually jump into Excel. Other than this, though, we do have a few more worksheets. The Host Worksheet is another data table with host-level data, while the Neighborhoods Worksheet has the districts in New York City and their neighborhoods. After that we have the Mortgage Calculator Tab, which has a template containing the data for the property that we're looking to purchase. We also have the 2019 Occupation Worksheet, which has a layout of a calendar with one day for days in which a fictional property was booked on Airbnb and a zero for days it wasn't. Now it may sound a bit confusing now, but we're going to take a look at it shortly, so don't worry. Next we have the amortisation schedule, which really is exactly what it sounds like. It's an amortisation schedule using the information from the property on the Mortgage Calculator Worksheet. And finally, we have the Visualization Worksheet, which A has an annual profit and loss statement, and B has the Airbnb website conversion funnel data along with placeholders for charts that we'll be building in the course. So without further ado, let's jump to Excel and have a real look at these. All right, we're here in Excel in the ExcelExpert Course project file, so you can open that up to follow along if you want. Now, if you don't have it yet, you can download it from the Download Course Resources lecture in the first section of this course. So once you have that up and running, you'll see that we have the same worksheets that we just talked about, starting with the places. So, for each row, we have a separate listing, from Enjoyed Downtown New York City to Green Point Place, which obviously has it all the way down to the last listing, which is Zen Private Bedroom in Hamilton Heights, New York, which is, as you can see, listing number 6000. Now some of you may be thinking that 6,000 seems like a small number for New York City, and those of you would be absolutely correct. The correct number is actually closer to around 50,000 listings, but we trimmed it down for purposes of keeping the file size small without actually affecting the project itself. so no worries there. Press CTRL Arrow Up again to go back up, and we'll see that for each place we have the IDs for their host and the neighbourhood they're in. We have their room type, which can be an entire place, a private room, or a shared room; the number of people that can accommodate its price and rating; the number of reviews; and the dates for their first and last review. And know that this data set goes all the way up until August 2020, which we can see here. Then we have the availability, which is the number of nights in a year that it's available to be booked all the way up to $365, obviously, and whether it can be instantly booked or not. And over here to the right, we have a small table that's going to tell us the service fee that Airbnb is going to charge us depending on the price of each place. Now, this is actually inaccurate. Airbnb actually charges 3% across the board, but we're going to use this when we're working with our approximate matches when we're looking at view lookup functions in this course. Now, moving on to the hosts, you'll see that we start with the host ID, which, if we again press Control arrow down, we'll see that it goes all the way down to $4,141. We have the hostname afterwards, which is fictitious, by the way. We have the date they joined and their actual location, so this is where they relocated and not where their listing is. So as you can see, this actually contains places all over the world. We have the response time, which can be from within an hour all the way up to a few days or more, the response and acceptance rate, and whether Airbnb says there are super hosts or not. And then again, over here to the right, we have a small template that we're going to use to create a custom host dashboard where we're going to select a host ID and then pull all this interesting information using just that. So that's going to be really cool. The Neighborhoods Worksheet is pretty straightforward. We have the districts in New York, so the Bronx, Brooklyn, Manhattan, Queens, and Staten Island, and the neighbourhoods in each district, which again has been a trend for purposes of this course. We have a total of 31 neighborhoods. The Mortgage Calculator is one of the better-looking worksheets. Some of you that have worked with Chris and Chris' courses in the past may have seen something like this before. And here we have the actual property details of the property that we want to purchase for Airbnb, which is in Chelsea, New York. So using the purchase price, we're going to evaluate different scenarios using the down payment, the interest rate, and fixed terms or payments to calculate total monthly expenses and the cash needed to close on the property. And over here to the right, we're going to use the Consolidate feature with the data from the 2019 Occupation Worksheet to fill out this calendar and use it to forecast the average number of nights per month that we'd expect for a property to be booked. And with that, we're going to be able to calculate the optimal price for a property for Airbnb. Now here it is, the 2019 occupation tab. Props to those of you who actually imagined it would look this way. As you can see, it's a standard 2019 calendar from January to December. And this actually represents a property similar to the one we're looking to buy in Chelsea, where the once represent the days when this property was booked on Airbnb in 2019, and the zeros represent the days when it wasn't booked. so simple as that. Now, for the amortisation schedule, what this is is just a rundown of the loan payments that we have due for this property. So this is broken down by principal and interest in the running fields, the payment due, and the balance due at the end of each period, which is monthly. What we're actually going to do is create a yearly summary here of this information, and finally, we have the visualisations tab. Now, I know this looks blank at the moment because it is, but this is actually where we're going to place the advanced charts we create based on our data and are going to end up with a really cool-looking dashboard, and the information is going to be broken down by places, hosts, and neighborhoods. Then at the end we have our annual profit and loss statement and our Airbnb website conversion funnels, so these are the worksheets and the data that we're going to use for reviewing our objective domains and for absolutely crushing our course project. So I hope you guys are ready to get your hands dirty.

Hide

Advanced Formulas & Macros

1. Objective Domain Intro

Time to kick off our first objective domain, which is advanced formulas and macros. In this section, we'll cover advanced formulas and data analysis tools, including logical date, time, financial and lookup functions, auditing tools, and basic macros. The skills we'll cover are the following reference types, which technically aren't part of the skills measured in the test, but they are present in the rest of the skills, so I thought it would be best to do a quick review. Next is logical operations, in which we'll go over Excel's logical and conditional functions based around the if statement. Then there are the date and time functions, which revolve around Excel state values and are used to calculate the passage of time. We'll move on to basic macros, where we'll learn how to record a macro and use form controls. In the lookup functions, we'll use one of Excel's most important functions, which is VLOOKUP, as well as using index and match for more complex scenarios. For the financial functions, we'll use our NPER for the number of periods and PMT for payments. As for data analysis tools, we'll use the Scenario Manager goalcheck and the Consolidate feature. And we'll finish off with Excel's formula auditing tools, which will help us trace precedence, check for errors, and evaluate our formulas. Now, common use cases for these skills are creating dynamic dashboards using conditional statistics functions, creating scheduling tools that update with workbook changes, automating repetitive and time-consuming tasks, solving simpler optimization problems, and auditing or diagnosing complex formulas. So that's our intro to this objective domain. Now, let's get started.

2. Fixed vs. Relative References

It's time to kick things off with fixed versus relative references. This is actually a part of the skills measured in the Excel Associate Exam. But mastering reference types is absolutely essential to working efficiently with formulas, so I just had to include them in this course. In a nutshell, fixed and relative references determine how row and column references change when formulas are copied to new cells. There are three types of references. By default, cell references are relative, and this means that the column and row references will change as the formula is copied or moved to new cells. But here's the thing. You can use a dollar sign to create something called a fixed reference. And you can fix an entire cell or range of cells by inserting a dollar sign before both the column and the row reference. And this means that neither the column nor the row reference can change as the formula is copied to new cells. Finally, by fixing just the column or just the row reference, you can create what is called a mixed reference. So here, the column or row references can change depending on which one is fixed. Now, let's see how this works. Consider a reference and select one that's completely relative by default. If you were to copy and paste that reference or that formula from a one to C three, notice that both the column and the row shift; a will shift to column C, and one will shift to row three, so the entire reference has changed. Now consider a relative column and a fixed row. In this case, column A remains relative. But we've added a dollar sign to fix row one. So if we copy and paste that reference from A-1 to C-3, notice that the A will still shift to column C, but the one remains locked in place, and it's the same case with the reverse. In this case, we fix the column but leave the row relative. So as we copy and paste the C-3, column A, the row will shift from one to three. Finally, if we have a fully fixed column row, this formula will always refer to cell A 1 no matter where we copy and paste it. Now, quick pro tip before we move this over to Excel: you can press F to cycle between the reference types. Now, I know this may still feel a bit abstract and confusing, but trust me, by taking the time and practising using these reference types, you'll be amazed at how practical they are and how much time you can save. I'm actually going to start practising right now. So let's quickly jump to Excel and take a look at an example. Here in Excel, before we jump into the course project file, which is what we'll be using for the rest of the course, we're actually going to do the reference types exercises in the Excel Expert Reference Types workbook. So if you're following along, make sure you have that open to the Fixed versus Relative tab. Now, if you don't have this file yet, you can download it from the Download CourseResources lecture in section one of this course. Now, what we have here is a small template that I built where we have the product for a company, their sales target for the quarter, and the percentages that they need to meet for those sales targets each month so they can accomplish it. And what we want to do is use our knowledge of basic Excel functions and reference types to calculate the sales that they need to have for each product each month to meet the target at the end of the quarter. So let's get started with that. We're going to select our first value here, which is B3, and we're going to start typing our equal sign. And what we want for our smart speaker in October is to multiply our sales target by our percentage. So divide 22,800 by 15%, press Enter, and we get 3420, which is correct. So now we can apply this downward and see what we get for the rest of our product. Sorry about that. Okay, so right off the bat, no pun intended, we're seeing something wrong here because all we're seeing is hash marks. Now, for Excel, this means that the numberin those cells is bigger than the cellitself, so we can't display it. And if we hover over them, we'll see that we have 3,600,000 for the baseball bats, and we have 9 billion for the cowboy boots. I think that's 17 trillion for the vinyl records. So, first and foremost, I don't believe there are seven enough vinyl records on the planet to achieve that sales figure. So either we're going crazy here or there's something wrong with our formula. But don't worry, this is pretty easy to diagnose. So what we can do is either double click on our original cell here or, if we press Escape to back out, we can also use the F-2 shortcut to do the same thing, which is go into edit mode. So here we're looking at the references that we have in our formula, which are also colour coded.So we have E 3, which is in blue over here. So we multiply 22,800 by B 2, which is in red over here. So 15%, which is correct, So our formula in this cell is correct. But let's see what happens when we move over to the next one. So press Enter or F2 again, and now we're starting to see the problem. So E four in blue is fine, but as you can see, our reference in red shifted down to the percentage, or from the percentage down to 3420, which is not what we want. And we can see a pattern for why we're getting such large amounts down here. If we press Enter and look at our formula, we'll seewe have B four, B five, B six, B seven. So these all kept moving down when they should have stayed in row two. So what do we need to do? Let's jump back into original formula and let's say, hey, Excel,how about next time you don't shift this over to thenext row and just stay in row number two? Because this is where my percentages live. So what we can do is use our pro tipand press F four to cycle through our references. So if we press it once, we'll see that we have a fully fixed reference. So I fixed the column and the row, and this is only half right. Right, we do want a fixed row because all of our percentages live in row number two. And as we apply it down, we want it to stay at number two. But we don't want a fixed column because we don't want it to stay in B-2 as we apply these across. We want it to move over to C 2 and then D 2. So we're going to press F 4 one more time, where we have a relative column and a fixed row. Press Enter. Apply this, and it looks like these are perfect. So let's just double-check real quick. And it looks like we're getting the correct references this time, which is perfect. Now let's select these again. and now drag them across. Okay, so something is definitely wrong again because these obviously can't be zeros. They need to be, but they need to be higher than these since the percentages are higher. So what's going wrong? Let's diagnose. So these are still correct. We have 22,000 by 15%, but what happens when we move over to the next one? So what we did last time worked. We did move over to column C and we're referencing the correct percentage, but our sales target also moved across to the right, which we don't want. We want it to always stay in Column E. Let's take a look at another cell. So again, we can confirm that our reference in red is correct, since we're referencing the correct percentage here. But now our sales target, or our blue reference, has moved across to the right again, since now we're in December. So you may have a pretty good idea of what we need to do here. We'll jump back to the original formula, and like we mentioned right now, if we look at E 3, it's completely relative, meaning that if I move it down, our row is going to move down, which is fine. We do want it to move to the rest of the products, but if we move it across, then our column is also going to move across, which we don't want. We want it to always be E. So what do we do? Well, we'll just fix it. Press f four.I'm sorry about these puns, by the way. F four. Now, this is fully fixed, which we don't want This is a fixed row, which we also don't want. We want it to move down to the rest of the products. And this is a fixed column relative to a row, which is exactly what we want. Column E is fixed, but rows can be moved down to all products. Press Enter, apply down, and apply over. And there we go. Now, let's just do a quick double check here. 1080 by 35%, 6050%, 126, 840. Perfect. So that's it. This was a pretty simple example, but I think it's a really good one to illustrate why reference and swipes are so valuable and how they can help you work much more efficiently.

Hide