Pass Microsoft Excel MO-200 Exam in First Attempt Easily

Introduction

1. Curriculum

Hello and welcome to this course on Microsoft Excel. It would help you. First of all, I explained the rationale behind this course and why I have put certain things into this course, and you can see already that it is a really long one. Well, what I'm wanting to do is get you and somebody else to take the cost from a level that might be quite good, a level that might be right at the beginner level, and get it up to a particular point. But the question is what point? And for that, I defer to Microsoft. Microsoft has a series of examinations and certifications you can take. One of those is on Microsoft Excel; in fact, there are very many more than one of those. And one of the exams is 77 420.But I'm just using that because it is a very quick way for me to get to the actual certification I want to talk about. So if I go to Microsoft Office, you can see that there are three levels that Microsoft can certify you at. So what do you mean by certify? I mean, you take an exam, you pass it, and then you're allowed to put it on your CV and proclaim it to anybody you want; you want Microsoft to say you have these skills. Now just think what that could mean for future jobs. It doesn't mean that future employees won't have to use your skills. By trust, you can actually say, "Microsoft, say I am this." And you can see, first of all, there are three different levels. Microsoft office specialist, which is this course? And then there's expertise. Master is the expert course. Plus, Excel has other office applications. I have taken this master's and passed. So I can call myself that on my CV. a Microsoft Office specialist (MOS master). But let's have a look at the MicrosoftSpecialist which is the focus of this course. And you can see that there are four different examinations. One for 2007, 2010, 2013, and 2016. Now just have a look at what Microsoft says. Holding a Microsoft Office Specialist certification can earn an entry-level business employee as much as $16,000 more in annual salary than their uncertified peers. And they refer to this particular document, which you can read, which talks about the skill sets that are wanted by 2020 and how important they are in so many different occupations. It's quite a good read. So if you do want some convincing that this will be useful knowledge of Excel, then this is a good document to read. Now, 2007 and 2010 there werevery few changes in these two. In 2013, there were quite a number of changes, especially with the basics of opening and saving files. But the properties dialogue box also becomes a property pane, or something along those lines. And 2016 just adds on to 2013. There aren't any major changes. So to pass these exams, there is one specific exam for each. So for the 2007 system, it's called "and Seven Two Seven" for the latest version as of the time of this recording. Now, when you click into any of these, you'll see that there are various skills that are being measured. And if I click Show All, you can see there are a lot of subskills. So what I have done in this course is to take all of these and put them into the course. For example, when creating tables, create and manage tables. We've got the items here—manage tables, styles, and options. We've got a section for that, a photo, and sorting a table. I've now made some minor changes between versions 2000 and 710-1316. The terminology sometimes changes, so I've used terminology from one exam in another and another in another, but they cover the same concepts. So, for instance, perform calculations using some min-max count average. You'll find that here in level three, section four. So I've incorporated all of the specific requests from Microsoft into this course. And so with all of the practise skills and practise activities that we've got, I'm fairly confident that if you do well in all of these skills and these practise tests, you will be able to pass this exam. So it may look like a lot, but I've broken it down. Basically, there is one lecture on each, where there is a semicolon in each aspect. So you can skip ones that you already know about, and you can then continue and take the particular videos for ones that you don't. So by taking the practise activities, you can then test yourself, and hopefully by the end, if you so wish, you can take the exams. So why do you want to start? Well, it depends where you are at the moment. For instance, are you completely new to Excel? Then I would start right at the beginning, but can you create worksheets? Can you import, say, text files? Can you save in Excel 2003 format? Can you do basic formulas? If you're saying I'm not too sure about some of these, then start at section one. Can you navigate through worksheets? Can you change the tab of particular worksheets? Undo/redo in the third column; select multiple ranges; record macros; use zoom; split the window; configure your page; configure with headers, footers, and watermarks. And can you use the dollar sign in formulas? I don't mean the dollar sign is in currency; I mean like dollar b, dollar four; that's a really important one. If you're saying I'm not too sure, then start at level two. But this one, copying and pasting basics and utilising references, is very important. So I recommend everybody do level two, section five. What about using Paste Special, using the Autofill tool, changing fonts, using the Format Painter, using conditional formatting, adding spark lines, and having some count min. and max. Okay, level three might be for you if you're too far ahead. Then, in section four, all about charts, line charts, graphs, bar areas, pies, adding legends, modifying the parameters, smart art, clipart, cropping, changing workbook themes, making corrections to images, and then using text functions. Well, that's what we cover in Level Five. So hopefully, from this, you can see that the basic plan is from Microsoft. So we're going to try and go through everything that's in your particular version of Excel, and we're going to do it in logical order. Sometimes this is not completely logical; we just needed to move some things into different sections just to make it flow a bit better. You should also know that if you aredoing this in a nonenglish format, that Ido actually convert functions into like a dozendifferent, maybe even 15 different, largely Western Europeanizedlanguages, including Spanish and Portuguese. And so when I do a formula at the end of each video, or even during the middle of the video, I'll be showing you this, and so it will translate it into your particular locale, and I'll continue talking about these particular functions. So I'm just giving a bit more detail. So if you are in a French-speaking country or German, Spanish, or Portuguese-speaking country, this is a good course for you because I will be translating the functions as I go. So without any further ado, let's start right at the very beginning and create a new blank worksheet.

Hide

Level 1, Section 1 - An introduction to Excel

1. Creating new blank workbooks

Right, so the first thing we need to do on level one is to create a new blank workbook. So go to the Start menu. This is the Start menu from Windows 10, and we'll go look at Excel, starting with an E. So if you've got an older version of Windows, then you might need to go into the programme section, or you could use a search bar to look for Excel. So this is the starting page for Excel, and you can see it's fairly simplified if you're used to previous versions. Now, we have a blank workbook. So if I click on that, that will open up a completely new blank workbook for us to work on. Now you could open a second blank workbook, and to do that, we go to File over here on the left-hand side, and we can click on New. And when we click on the blank workbook, that gets us a second blank workbook. This one is called Book Two, and there was a previous one called Book One. Now, one way you can switch between them is to go to the bottom, to the toolbar down here, the taskbar, and click on Excel, and you'll see we've got Book One and Book Two, and you can select whichever spreadsheet or whichever workbook you want to work on. Now, while we're here with this new workbook, I just want to talk a little bit about terminology. For instance, I've been calling this a "workbook," but other people have been calling things like this "spreadsheets." So what's the difference between these terminologies? Well, a workbook consists of a file. So it's something you might open from Windows Explorer, or it's a file that you can create. So we have a workbook within the workbook. We have worksheets, which are also known as spreadsheets. And each of these tabs at the bottom—you can only see one at the moment—is a spreadsheet. But there might be multiple spreadsheets in one workbook. So the next time somebody emails you across the spreadsheet, you can actually think, "Actually, that's not a spreadsheet." That's a workbook that contains one or more spreadsheets. So that concludes our first subjective task. To create a blank new workbook, you go to File > New and click on Links >Quotebook.

2. Entering data

Right. So we have a new workbook, which contains one or more spreadsheets. So what are we going to do with Excel? Well, we can enter data, but what kind of data? Well, you see these lines? We've got lots of lines goingdown and lines going across. And each of these makes up a rectangle. Well, these rectangles are called cells. So for each individual cell, there's one cell, and there's another. You can get from one to another just by clicking. You can also use the arrow keys on your keyboard to go up, down, left, and right. And you can use other keys such as home, end, and page down and page up. Now, you'll note that the numbers and the letters will always give you a reminder of where you are, but we'll come onto those a bit later. Now, you know how to navigate this green box, which is the cursor. What can you do? Well, you can enter some data. And I'm going to do that by literally entering data. So you can enter data, and maybe you can enter some numbers. You can enter dates and you can enter currency ($20, £20, or €20) depending on how your computer is set up and what particular currency you use. Now, everything else that I'll be teaching you in this course is how to reduce the amount of time it takes to do the other things. But data and entering of data is insome ways the hardest job of the spreadsheet. Now, we'll get into how best to present data in a spreadsheet later. But for now, we're just entering data. We've got arrow keys, we've got page down, page up, home, and end, and we've got something else. So, if I just wanted to enter some data, I could use the tab key on the left side of your keyboard to move to the next cell. So here is where the tabkey is on my particular keyboard. Now, I can enter more Data tab, yet more Datatab, and each time, Tab takes me to the right. And you can say, well, that's exactly what happens when you press the right arrow key. But what happens next is, instead of pressing the downarrow key, I press the Enter or Return key. So here's the Enter key on my keyboard. And if you've got a number pad, you might find a return key about here, just to the right of the numbers. So let's see what happens when I press the Enter Return key. Now, if I had pressed the down key, I would just be going straight down. But now that I've pressed the Enterkey, it's like a typewriter. It goes all the way back to the left of where you started and then down one row. And it's the same when you keep entering more data and pressing Tab between each one. And then press Enter. It gets you back to the left-hand moulds column where you started. And it's quite convenient if you're entering data in a semi-structured format, like maybe a table. Now, incidentally, if you want to go backwards with all these tabs you can do, you can hold down Shift and then press Tab and release Shift, and that gets you going backwards. Now, it may be that you've realised you've made a mistake and you're going, Oh, I'm going to need to change that. So if you press Shift in Tab, you can correct it and then move on. Now, you'll notice when I'm entering data: as soon as I press the letter E, what happens? It's a bit like the phones that you have on telephones nowadays. So the phone goes. What do I think the next word is? Well, no, actually it's nothing like that. Instead, rather than looking at your local language and suggesting what the next word is, it's just looking straight up in the columns of data and going, "Okay, you previously said Enter Data, so I'm guessing, and it's only a guess, that the next data you're going to enter is going to be Enter Data." Now, you can see that as soon as I press a different letter, like the letter M, it's not going to try and guess this line of text because there is no previous text in this column that starts with em. So I'm just going to cancel the changes that I've just made. In this one cell, I'm still editing it. To cancel my changes, I press Escape, which is at the top left of my keyboard. So if I were to press E again, then it would Enter Data." I could press Tab to accept or any of the arrow keys. But as soon as I type something that is not part of the suggestions, it stops any further suggestions. Now, if you're still editing and you made a mistakein the cell, you can press backspace just to getrid of the very last character that you have typed. So what is the advantage of this autocomplete? Well, let's say I was doing a table with parts in it, and let's say somebody ordered a scanner and then another, and then a printer, and then another scanner, and you've got a huge order. Then things will start naturally repeating themselves. And it would be nice if the computer were to help you with this typing, which, as I said at the beginning of this video, is actually the hard part of the spreadsheets. And this allows for consistency because she might accidentally make a typing error or try to put things in a different way; just try to speed you up if possible. Now, it could be that you've got so many fields and so many sales going across the country, and you've got a company like Toshiba that is doing all of these. So I'll just type Toshiba, and now I want the total. So I'll type T, or it's still trying to autocomplete with the word dashiba, but as soon as I type the letter T, it goes, "No, it's nothing to do with dashiba." If I later go down to type T, it doesn't know whether I want Toshiba or Total. It will only suggest one thing, and it will only suggest that when it's fairly certain. So as soon as I type in the letter O, no S, I think you mean Toshiba. So Excel is intelligent enough to not suggest something until it's fairly certain that it is actually what you want. But in this case, I'm going to cancel what I'm entering in this cell. So how do I cancel? I press escape for any of the current edits that you are making. If you decide you don't want any of them, press Escape and it will revert back to where you are. So we've done a lot in this video. We entered numbers. We've entered data, and we've entered currency. Now, there's just one more thing to do at this stage, and I'll explain more once we start getting into formulas later. And there's one thing you can't easily do, which is enter text that starts with an equal sign or a plus sign. Now, once we get into formulas, I'll explain what formulas are. Then I'll explain what those equals and plus signs are. But if I was trying to type this in and press Enter, you can see the computer is very bemused. It thinks it's something called a formula. It's not. And therefore, it just holds his hand up and says, "I'm sorry, I've got no idea what you're talking about." So at the moment, please don't start any cells with the equal sign or the plus sign. Now, you can put those signs in later. So two plus two equals four, which is absolutely fine, but just don't start cells with the plus or equal sign just for now. And that's how you enter data into spreadsheets.

3. An introduction to fonts and highlighting

Now, so far, everything we've entered is in black and white, or specifically black, but we can colour everything that we've done. There are two sets of colors. There is a font colour that colours the text, also known as a forecolor fore.And then there is your fill color, which colours the entirety of the cell apart from the text, also known as a back color. And these are available from these menus here in the "Font" section of the Home tab. So if you're not seeing the Home tab, click on Home. And then you'll see these forecolors and back colors. Now, these menus—I just want to talk about them first because you might not actually be seeing them. These menus are collapsible. Now, just to talk about the menus, as you can see, we've got Home Insert, page layout, formulas, and so on. Some of these you might not have; for instance, you might not have the Power Pivot or the Team Toolbars. That's perfectly fine. Within each of these, we have got various groups—clipboard, font alignment—that group together various buttons such as the font colour or the fill color. But it could be you can't actually see them. And that's because you can collapse these buttons so that you have more space for your spreadsheet. Now, to collapse them, you click on this little up arrow here, and you can see you can also press CTRL and F1 to collapse the ribbon. And all of those menu items have gone away except the very top line. So if I wanted to see one of those, I would click Home. And that temporarily brings back all of these menus. If I click away, they disappear. Now, to keep them in place, you have a pin on the right-hand side exactly where that up arrow was, and that pins it into place permanently. Now, as I said, you can change the forecolor, the front color, the font color, the back color, or the fill color. and these are all contained in the Home menu. Now, the whole menu contains all of the items that are most frequently used for general purposes. So you can tell your fill colour because it's got the letter A, the font color, that is, it's got the letter A and a colour underneath it. Now, there are two parts to this. There's this button here with the A, and then there's a drop-down menu right next to it. Now, if I click on the drop-down cell, I get a choice of 56 colors. So we have 48 colours here, which are standard for the theme. We'll be looking at changing the theme much later on, but for now, these are just the standard themes and you have your eight standard colors. So a standard green, a standard yellow, a standard red And if that was not sufficient, then you've got more colors. Clicking on more colours down here, you can see there are three dots after it: dot dot. That means there's a dialogue box coming up, so clicking on that will take you to yet more colors. So we've got around 100 colours on the standard tab. And in the Customs tab, we have an array of colors, and they're around 16 million different colors. And I believe your eyesight can only detect about 4 million of them. In other words, there are more colours here than we can see. So if I click here, you can see what that colour will be. At the moment, it's all very dark. On this light scale, I can lighten it on this light scale.So you can see if I click over here instead, we now have this purplish color, and I just want to make it a bit darker or a bit lighter. Now, what all of this colour wheel does is translate into numbers for red, green, and blue. These are numbers between 0 and 255. But you don't really have to look at them. You just click to find the colour that you want. So if I click okay, you can see any text I write will now go into that color, and similarly, I can change the back color. So let's change the back colour to a standard yellow. Now, if I wanted to go back to the colors, then I would have to go back to an automatic colour and to a no fill color, which is the default. Nor fill is not quite the same as white, notthe same as white, because white is actually a color. "No fill" means transparent. But if you want to go back to your custom colour, don't worry, it's down here in your recent colors. So here we are. We are back to our standard text, and this is how you can do your standard formatting with font colours and fill colors, also known as forecolors and back colours or text colours and highlighting.

4. Saving and closing your workbook

Right? So now we've entered the data. We need to save the spreadsheet, and then we'll close it. So we'll go to the File menu and click on Save. Now, here we can see some folders that I've previously saved to. And if you want to save in one of those folders, you just click on that to go to that folder. But if not, we'll click on the browse button. And this takes me to a familiar dialogue box. So what I'm going to do is I'm going to create a folder. So I'm just going to write and click and go to the New folder, and I'm going to create a folder called Excel. And now I'll double click inside of that folder, and now I'm going to save my spreadsheet. So I'm going to call it my first workbook. Now, you may also notice the Save As type. It's been called an Excel workbook. It doesn't call this a spreadsheet. So workbooks are the name of the files, and they contain spreadsheets. So let's now click preadsheetsWell, I'll go back into my first workbook, and I'm going to type some more text. Now I'm going to close it by clicking the X button up here. Now. I could also go to File > Close. Either way, that closes the workbook. However, we get a download box if you do that. Do you want to save your changes to My First Workbook? So what does that mean? We've already saved it and the computer is going. You've made some changes since you last saved it. Do you want these changes saved so we can say Save, Don't Save, or Cancel? "Cancel" means to stop the closing process. So I'm going to click Save to continue. Now, because I was closing the spreadsheet, it now saves and closes. So here's another workbook. So I'm just going to make some changes. I'm just going to hit Save. I'll go to my Excel one. And here is another way of getting to this smaller download box. So I said I wanted to save it in the Excel One folder. And now I'm going to save this as my First Workbook Part Two. So click Save or press Enter, and you'll notice at the top that when you have saved a workbook, it says Saved. As soon as you make more changes, that save at the top disappears and is replaced by the word Excel. So in this way, you know if there are any unsaved changes. So if I was to go to File > Close and there weren't any unsaved changes, it would just close. But if I go to File Close now, then it asks me, do you want to save the changes? So I can say yes, no, or stop; don't close. So I'll click Save, and there we go. We have closed all of the workbooks in Excel. Excel is still open. It's just that there are no workbooks open in Excel. So we have open spreadsheets. We have entered some text. How do we open it up again? Well, if I go to file, you will see the last-used workbooks. So I can just click on one of those workbooks. Alternatively, I can click on Browse to get to another workbook. So here I've opened it, and now I'll close it again. So we have created, we've entered text, we've added color, we've saved it, we've closed it, and we have found where they have been saved. and this is your first step into Microsoft Excel.

Hide