Advanced Formulas & Macros

18. Recording a Macro

Now that we've finished the date and time functions, we're moving on to basic macros. A macro is a recorded action or set of actions that can be reproduced with the click of a button to save time and automate manual tasks. And there are several ways to create macros, but the most practical and the one on the test is to record them. Now, the first step in recording a macro is to click on the Record Macro button, which can either be on the Developer tab or in the bottom left corner of your Excel screen. Next, you assign the macro name, as well as an optional shortcut and description. Step three is to actually perform the actions that you want the macro to replicate, and then click Stop Recording once you're finished. While you do this, Excel is actually writing the VBA code in the background, so any click or key press that you perform will be registered. And as a note, VBA stands for VisualBasic for Applications and is the programming language that Excel uses to run macros. Finally, step four is to run the macro. You can do this by using the shortcut you selected or by running it from the Macro dialogue box, which you can access from the Developer Tab. It is critical to note that once a macro is executed, its action cannot be reversed. So plan ahead and keep it simple and straightforward. Now, in the Developer Tab, which you may have heard me mention a few moments ago, macro options are found in the Developer Tab, which is hidden by default but can be displayed by clicking on FileOption, Customized Ribbon, and making sure Developer is selected. So let's move this over to Excel, where I'll walk you through enabling the Developer Tab and we'll record some macros of our own. Here in Excel. We're still in the host tab of our course projectworkbook and what we're going to do is enable the Developer Tab, which we don't have right now. Some of you may already have it enabled, but if you don't, you can go to File Options, Customize Ribbon, and here in the main tabs you'll see Developer Unchecked. So we just need to check it and press OK. And here we go. So we have our Developer Tab and we have our macro options over here where we can see the Record Macro button. Now, the macros we're going to record for this exercise involve changing the date format. And you may have noticed that all the dates in the workbook are in an International Year Month Day format, but we want to have a quick way to change them to either month day year or day month year. So that's what we're going to do. First we need to have a cell with a date selected, and then we can click Record Macro, which we can do here in the Developer tab or down here in our bottom left corner, which I'm actually going to hit right now. The next step is to give it a name so we can refer to it as US. Format for this one, since we're going to be using it to convert dates to USformat, which is month, day, and year. Next, we need to give it a shortcut key. Now, pro tip here: if we were to, for example, say Controlc here, this would actually overwrite my existing Control C to copy command on my computer,which we may want to avoid. So a tip here is to actually press Shift and then press Letters, which would make the shortcut Control Shift. Now, in this case, I don't want to see a U, but I'm also going to press Shift. So Control Shift is going to be my shortcut for US format. And we press OK to start recording. And we know we're recording because instead of saying "Record macro," it says "Stop recording." And we also have a stop sign down here. So the only action we wanted to perform was to convert this to a different date format. So we're going to open the Format Cells dialogbox by pressing CTRL One, and here in the dates,we're just going to change it to Month, DayYear, press OK, and we can stop recording. So now if we click into our macros, you'll see that we have our US format macro. To run it, we can either select another cell and run it from here, which, as you can see, changed the format, or I'm actually going to select all of them now by pressing CTRL Shift arrow down and we can use our shortcut, which is Control Shift U. And there we go. Now we want to create another one by turning these into a Day Month Year format. So I'm going to select another cell with a date and press Record Macro. And since this is the formatting that we use in Mexico, I'm going to call it MX Format, and the shortcut is going to be Control Shift M. Press OK. Again, press Control One to open the Format dialogue box. And I'm actually going to change the locale of my dates here to Spanish Mexico. Let's see if we can find it. Here we go. And now we have Day, Month, and Year, so we can select that, press OK, and stop recording. And again, we can jump into our macros, run them, and you'll see we changed the format, and we can again apply it to the rest with our Control Shift M shortcut. Beautiful. Finally, we're going to do one last one to return to the International Year Month Day format. So again, select a date, go to Record macro. We can call this an international format. Maybe I'll use control shift this time. Press okay. Again, Control one. And we have dates to return to the United States. And here's our year-to-date date. So press OK. Stop recording. Now we have our international website and our US format. Select them all with CTRL Shift I and be beautiful. And we can do this in the materials that we have here as well. So, for the first and last time, press CTRL Shift Shift M. The final thing here is that since our workbook now contains some macros, if we wanted to save it, we would get the following window saying that the following features cannot be saved in macro-free workbooks, which is our Visual Basic project, or in this case, our macros. So what's going to happen? Cancel out of this? What we need to do is actually save this as a macro-enabled workbook. So instead of Xlsx, it needs to be Xlsm. And once we do that, we can now save it and keep our macros. So that's it. We've recorded our first macros and we've saved them in a macro-enabled workbook.

19. Modifying & Deleting Macros

Next up, modifying and deleting macros. Once a macro is created, it can be modified or deleted from a macro dialogue box. So if you go to Developer Tabmacros, this dialogue box will open. And as you know, we can run our macros from here, but we can also press Delete to get rid of a macro permanently or press Options to modify the macro shortcut key or the macro subscription. So let's quickly move over to Excel to modify the macros we created here in Excel. We're still in the host tab of our course project workbook. And if we go to developer macros, you'll see that from here we can delete or modify our existing macros. But I really like the ones we have and I don't want to delete them. So what I'm going to do is record someone really quickly and call it Delete, and we'll give it a shortcut key of Control Shift A. Okay, and maybe all we want to do is to make our text italic and centered. We can stop recording down here at our stop sign. And if you go back to developer macros,you'll see that we have our delete macro. Now I'm going to run really quickly before deleting it. So control Shift A and you'll see that it's centred and made text italic. And what I want to show you is how you can't undo an action that was made by a macro. So if I press Control Z to undo, you'll see that it won't allow me to do so. So, just something to keep in mind. Now we're going to go back and we're going to select our delete macro and delete it. It's going to ask us if we're sure which one we are, and if we go back, you'll see that it's no longer there. And what I'm going to do is actually copy the formatting from this, paste it back into the sower, and get it back to ste it back Now as far as modifying our macros, we'll go back to developer macros and if we click on Options, we can modify our international format macro and maybe want to change our shortcut key to Control Shift N. So press OK, close out of here and let's do CTRLShift U. What's our US control shift M? Mexico. And now, instead of controlshifti, let's do control shift. And there we go. So we can move this back if we want to and we can add a description like formats, dates, as, year, month,day, press okay, we have our description and there we go. So here is a quick example of how to modify and delete macros.

20. PRO TIP: Adding Form Controls

Now we're going to cover a pro tip that even though it isn't specifically mentioned in the skills measured for the exam, it was part of the G Matrix practise test I took. And it's also a lot of fun,so I think it's worth reviewing. So, adding form controls. Form controls enable user interaction via workbook objects like buttons, lists, and scroll bars, and are commonly used for building dynamic reports or dashboards. Now, before we go over this in Excel, let's see an example. Form controls are accessed from the developer tab in the Controls menu, and from there you can click the Insert dropdown and you'll see these form controls. Selecting one, such as this spin button, adds it to your worksheet, and you can size it like any other Excel object, such as a shape or a text box. And after placing it, you can press Control One to go to the format control options. And here you can establish different settings, like its starting value, the values it can take,and its incremental change when pressing the buttons. And you can also link it to a cell. So, with these settings, our spin button allows a user to select any year from 2011 to 2015 and sell it too, which will then dynamically update the chart below. So I don't know about you,but I think that's pretty cool. Now let's go ahead and add one of these form controls to our dashboard in Excel. Here in Excel, we're back in the ourhost tab on our course project workbook. And what we're going to do is actually add my favourite part of this host dashboard, which is going to be a button that controls our host ID, so that as we move it around, our entire dashboard will update. And before we actually do that, I want to see what values my host ID can take. So I'm going to move over to our host ID column, and you'll see that we have one as our minimum value. And if you go all the way down,you'll see that 4149 is our maximum value. So she's just going to write that here, 4149, so I don't forget it. And now we can go ahead and insert our spin button and place it here. We can move it around and now we can press Control One to open the format control options. As a result, current value Well, right now our host ID has one minimum value. We know that is also going to be one, and our maximum value is what we have right here. So in 4149, the incremental change is what we want to add or subtract to our current value when clicking the up and down arrows. So in this case, one would be correct that the cell link is the cell in which our numbers are going to live or our values. So in this case, it's going to be p two. Press OK. We can delete this now as it was just for reference. And if we click the down arrow, you'll see nothing happens because we're already at our minimum value. But if we click up, then the magic happens. So we can actually still write numbers down. We can go up to maybe 500 and still use these arrows,as I don't see you guys clicking up all the way to 4000, but it still adds a lot of cool functionality. And in case you need to modify any of the settings, you can just right click and select Format Control and you'll be right back. The format control settings So that's it. As you can see, form controls are fantastic tools to use to revolutionise the way we work with dashboards. And they're actually much easier to use than you think.

21. Lookup Functions

Moving on from basic macros Now, we'll be going over lookup functions, and this is actually one of my favourite parts of this entire course because, honestly, I still remember the first time I was introduced to the VLOOKUP function, and it completely changed my life. Well, it completely changed my Excel life. Now, most lookup functions work by finding a lookup value in another table or range and returning a corresponding value from another column. So let's say we work as analysts for a wholesaler, and we need to find a product description based on a product ID. Well, the process of a lookup function to find it is the following: You enter the ProductID, which is a Lookup value, and it will then look for the Product ID in the Product table or database. It will find the product description associated with thisID in the description column and return the description to the formula cell, which in this case was a product ID for a purple pen. Now, the lookup functions we'll cover in this course are the traditional VLOOKUP and HLOOKUP functions, and the index and match functions, which provide more flexibility when used together. And that being said, let's move on to the next lecture so we can go over these in depth.

22. The VLOOKUP & HLOOKUP Functions

Next up, the famous VLOOKUP function. But being honest, though, if you're not familiar with this function yet, it will change the way you work with Excel going forward. So, to start, the VLOOKUP function looks for a value in the leftmost column of the table and returns the value in the same row from a column you specify. So, syntax-wise, it looks like this. The lookup value is the value you're looking for and are going to use to search for it. The table array is the table that contains the lookup value as well as the value you want. And the column index number is the column in the table array that has the related data that you want. Finally, the range lookup is where you tell Excel if you're looking to match an exact lookup value. So a range lookup of zero or something similar, which is the default range lookup value of one, and you'll be using an exact match 99% of the time. But for some reason, Excel doesn't have this as the default. So you'll have to write "zero" here. And as an example, let's say you want to build a tool in which you type a studentID and extract that student's grade from this table. Well, the VLOOKUP function would look like this. Our lookup value is b2, which is the student ID. Our table array is d one to f nine, where, as you can tell, the student ID is the leftmost column, and the column index number is three, since in our table array,the grade column is the third one over. Finally, the range lookup is zero since we are looking for an exact match with our column ID. So what will happen is that Excel will look from the top down in the studentID column until it finds the lookup value. It will then move over to the third column of that same row and return the value that it finds there, in this case, an A. The H lookup function is essentially the same, but instead of looking vertically, it looks horizontally, hence the name. So the HLOOKUP function looks for a value in the top row of a table and returns a value in the same column for a row you specify. Now, the syntax is the same, so lookup value is the value you're looking for, the table array is the table that contains the lookup value, and the row index number is the row in the table array that has the related data that you want. Finally, the range lookup is where you tell Excel if you are looking to match an exact lookup value or something similar. So, similar example here, we're trying to obtain the math grade from this matrix, so we can use this H lookup function,and the lookup value is B2, or the class. The table array is from e one to i four,where the classes are in the top row. The row index number is three, since the student we want the grade for is in the third row down, and the range lookup is still zero, since we want an exact match. So this time, Excel will look from left to right in our first row until it finds an alookup value, then move to the third row of the array and return the value it finds there. In this case, now, before we put these tests in Excel, I want to go over the key rules for working with VLOOKUP and HLOOKUP functions. I've already mentioned one of these, but I want to make sure that I get the point across. So there are two key rules that govern VLOOKUP and HLOOKUP formulas. Number one, the lookup value must be in the first column or row of the lookup table. So, first column for VLOOKUP, first row for HLOOKUP. Considering the student grade tool example, this VLOOKUP function only works when the student ID column is to the left of the grade column. If it's to its right, VLOOKUP can't be used to find the grade. Number two, if duplicate lookup values exist in the table array, Excel will stop at the first instance. So, considering the student example again, if there were two students with the same student ID, the tool would return the grade for the student in the highest row. So, repeating them one last time, the lookup values must be in the first column or row of the lookup table. And if duplicate values exist in the table array,then Excel will stop at the first instance. Alright, now we're ready to head to Excel and use these. Here in Excel. We're still in the host tab of our course project workbook, and we're finally going to finish off our host dashboard. So we only have the name and the super host fields left, and for the rest of the fields, we got most of the information from the places worksheet, but in this case, the name and the super host for each host is right here in our host table. So we have the host ID in the first column, and then we have the host name in the second column over, and the super host ID all the way here at the end. So what we can do is use the VLOOKUPfunction that we just learned to pull the name and the super host information using the host ID. So let's actually write that down. So in our name field, let's write our VLOOKUP function, open that up, and our lookupvalue is going to be the host ID. Press comma.And where we want to look for that host ID is in our host table. So as you can see, our host ID is already in our leftmost column, so that's perfect. And instead of selecting the table like this, we can just select all the columns in the table. So press A and then drag across to the right all the way to the super host. So, from A to M, comma over to our column index number. And in this case, our host is in the second column over. So that's going to be two. And our range lookup, since we want an exact match, is going to be zero. Close that off, press Enter, and you'll see that for host ID. Number 24. We get the name of Josephine G. Butler. So, host 24. And here it is, Josephine G. Butler. 25 is Andrew D. Barnett. So let's just check that out. Andrew D. Barnett. There we go. So for the super host, we're going to do the same thing, the lookup, same lookup value, same table array. And as we drag, take a look right here next to my pointer, where we're looking at the column. So where you see seven C, you'll see that number grow as we continue to drag, that number grows.So 910, 1112, and 13. And what that 13 tells us is that this table is 13 columns wide. So that's actually going to help us because now we know that our column index number is going to be 13, since superhost is our last column here. So, again, an exact match. So zero. Press Enter and it looks like Andrew is not a super host. So let's verify that number 25. And yep, it looks like two more and we have a super host in the name of Benjamin Douglas. So let's check that out. One, two, and there we go. Benjamin Douglas, one place, 197 reviews, a 4.98 rating, and yes, he is a super host. And that's it. A fully fleshed out and dynamic host dashboard And before we finish this lecture, this host dashboard has made me realise that we actually have a lot of information about the hosts. In fact, 13 columns worth, right? So it would be really cool if we could have some of that information here in our places as well for each host, because we want to be able to analyse all this information together. So since we have a host ID here and we have a host ID here, we'll be able to pull the information for the host for each place using VLOOKUP functions. So going back to our hosts, I think the four most relevant fields are the response time, response rate, acceptance rate, and the super host. So let's go ahead and add four columns here. We can repeat our previous action by pressing insert and F, four. So insert another column, and another and another. And I'm just going to go ahead and copy these headers and paste them right here. And there we go. So now we're ready to use our lookup function. So for the response time, we look up, open thatup, our lookup value is going to be the hostID table array, which is going to be our host table. And it looks like our response time is in the 10th column over. As you can see from our little data call out there, So I chose all of the zeros in the tenth column. And there we go. So it looks like host eight has a response time of within a few hours. Let's check that out. So, host eight right here, Nicole Staphy. And within a few hours, it looked right. So let's rewrite our formula now and look at the rest. Okay, well, that's not what we wanted,so let's diagnose this real quick. Select this, press F two, and it looks like we have C two in our lookup value here. Which again reminds me of how careful we need to be with our reference types because we always want to look up value to be our Host ID, no matter how many times we drag this formula to the right. So I'm going to go back to my original one. I'm going to fix this lookup value. So press four. And we actually don't want it fully fixed because we are going to apply this down and we do want the host ID to move to the next one. So I'm going to press F four.Again, this is a fixed row, so we don't want that and we end up with a fixed column. So I am always referencing my Host ID column, but being able to move down row by row. And I'm also going to fix my table array since it's always going to be from columns A to M. So fix that. Press Enter. Now let's drag this across. We're not getting any errors anymore, but we're getting the same exact result. And if we diagnose again, as you can see, we are always pulling the information from the 10th column over, but really the response time is the 10th column over, but the response rate is number eleven, the acceptance rate is number twelve, and the superhost is number thirteen. So what we need to do is edit here,move this to eleven, edit here, move this to twelve, edit here, and move this to thirteen. Now we just need to edit the formatting on these to a percentage. And what we can do now is apply them and make them beautiful. So let's just double check these right here. So eight is going to be 101 hundred, and no. So eight. Wait, is that eight? Here we go. Eight within a few hours. 100.100 and no. Perfect. And since we're already pulling all this useful information using our host ID, we can go ahead and pull useful information using our neighbourhood ID. So, it's time to explore our Neighborhoods tab. And if you recall from our intro to this dataset, we have the neighbourhood ID, the district it belongs to, and the neighbourhood name itself. So let's pull these two columns into our Places data. So insert two more columns, F four. And this is going to be a district and neighborhood. Let's paste those here and again, use the lookup. Our lookup value is going to be the neighbourhood ID. two. And remember to fix the column. Our table array is going to be neighbourhood specific. That is going to fix that as well. And then our column index number for the district is going to be two. We want an exact match, so zero. And if we drag this across,we should be getting to Manhattan. So we just need to edit this and move our column index number to three to get our neighborhood. And we get a healthy kitchen. Which? Let's see, neighborhood. ID 17, manhattan house kitchen.And we can finally learn to apply these down.And now we have all the more information to analyse for our airbnb data in New York City. So there you have it,the life-changing VLOOKUP function.

ExamSnap's Microsoft MO-201 Practice Test Questions and Exam Dumps, study guide, and video training course are complicated in premium bundle. The Exam Updated are monitored by Industry Leading IT Trainers with over 15 years of experience, Microsoft MO-201 Exam Dumps and Practice Test Questions cover all the Exam Objectives to make sure you pass your exam easily.