Gain in-depth knowledge for passing your exam with Exam-Labs MO-200: Microsoft Excel (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 MO-200 practice test questions and answers, study guide and exam practice test questions. Unlike any other MO-200: Microsoft Excel (Excel and Excel 2019) video training course for your certification exam.

Level 1, Section 2 - Create worksheets and workbooks

2. Menus and toolbars

Right, now let's have a quick word about the menus. You can see that apart from the colour scheme, there are a lot of similarities between Excel 2010 and Excel 2016, and indeed, between 2007 and 20 13.If we just go back over the history, we started off with Excel version two, which was never version one for Microsoft DOS or for Windows. And then from two, we went to versions 345, 95, 97, 2000, 2002, and 2003. In each one of those issues, more commands are added, and the menu structure is getting very complicated, and you really do have to be an expert to know what is hidden where. So, when it came to Excel 2007, and this isn't just for Excel; it's also for Word and PowerPoint, Microsoft decided to completely replace the old-style menu structure with a completely new one. And this is the result. It's one that groups things together in perhaps more of an orderly fashion. So starting with Home, we've got things like the clipboard, copy, paste, and cut. We've got font alignment numbers, styles, sales, and editing. Now, we'll be using quite a lot of these as we go along, but I just wanted to point out that there's this little icon here, and this is your settings. So we have font settings here and alignment settings here. It's a little arrow that brings a dialogue box up. So if you ever can't find something, have a look to see if there's this dialogue box. It's not there for everything. Now, a lot of these have got dropdowns and dropdowns within dropdowns, and some, like the paste, have got half dropdown boxes, which means that if you click the top half, you get a different result from the bottom half. The bottom half gives you options, whereas the top half is just the default option. In this case, if you just paste the bottom, you can see you've got some paste options, and you can see the tooltip also changes depending on what you're looking at. Now, these menus are also designed to be adaptive based on how much space you've got on your screen. Now, a lot of people have got 1080 resolution, the latter being called 1080, and that gives the maximum amount of width for your menu. Now, I'm recording this at 1280 x 720, a much smaller screen resolution. And so some things are compressed together. For instance, these would often be next to each other, not on top of each other. Now, if I reduce the amount of screen size that I've got, you can see that instead of it saying "Auto sum," for clarity, it now just has the icons. So you can see, it's very clever. It tries to be compact but still gives you the maximum amount of information. So for instance, we used to have these styles next to each other, and now they're on top of each other like that. So all the way down to ridiculously thin So they are adaptive or reactive. There are also menu options that are only shown under certain circumstances. Now we've got home. Insert page layout, formulas, and data review and view. You may or may not have Developer. If you don't, don't worry about it. You only need it if you are doing Visual Basic applications, and that's a completely different course. We will be touching on bits of that in this course. But notice we've also got another menu called Table Tools Design. That's because I'm in what's known as a table, which we'll look at in level four. But as soon as I click outside of the table, the menu disappears. So if I were to go to a graphic, for instance, I think there's a graphic in the other ars. So iWe got a text box. When we click on it, we get Picture Tools. We're paying, and we have new options there. So basically, don't assume that what you see is literally everything you get for a particular item until you click on it. So, I believe, we'll go through each of these throughout the course. "Home is where you've got your most frequently used items." Inserts are all about adding new objects. These are graphs or charts. These are sparklines. Page layout is how something prints; formulas could help with individual formulas and also finding out any errors. Data allows you to insert additionaldata and from other sources. Fairly advanced some of this stuff andallows you to group in on group. We'll be doing that in this course review. We received spelling and comments, as well as protected sheets and permission to use your workbook. Normally, spreadsheet workbooks are only allowed to be used by one person at a time, but these are only allowed to be editable by one person at a time. And then View allows it instead of page layout, which is how it prints. "View" is how it looks to you on screen. So those are the basic topics that you've got all grouped together. So now when there are new things, like, for instance, if you go to Insert, you can see that the pivot table has been expanded in 2016. But they can put it in. Microsoft can put it into the right section. So you still go to the same section. You go. Oh, there's a new option for me. So as we go through the course, you get a better idea of what each section does. Just keep in mind that you have these dialogue boxes on some and these split dropdown boxes on others. So one does the default, and one does additional options.

3. Managing multiple workbooks

Now at the moment we've only got one workbook open, but let's create another two workbooks. So we've got one, two, and now we've got three workbooks open. How are we going to move from one to the other? Well, there are several methods. For instance, right here in the Taskbar, you can see that if I hover over the Excel icon, you can see all of the different spreadsheets that have been opened. So I can go from one to the other by using this method. But what if I wanted to show more than one spreadsheet at a time? Well, each of these spreadsheets has its own window with its own set of menus. So what I can do is use this restore icon up here. So we've got to restore or maximize. We've got Minimize, and we've got Close. So if I click on Restore, then I now have a spreadsheet and a window that I can manipulate. So I can make it a bit smaller. I can make it a bit smaller this way as well. So reduce its width and reduce its height. Or if you've got Windows 10, I can drag it to the left-hand side of the screen and fill up half the screen, allowing me to choose what I want to fill up the other half. Now, if I wanted to get a particular spreadsheet all the way to the front and fill the entire sheet, I could fill the entire screen. I can do that. Just click on whichever spreadsheet it is, and if it is restored or if it is not at the full width, then I can click Restore Maximize. So this is a maximise and restore button. So it's currently maximize; now it's restore. So restore undo maximization. Now, I've been clicking on Excel using the left-hand mouse button. But what if I clicked on the Excel icon atthe bottom in the Taskbar using the right hand button? Then I get a list of all the most recently used spreadsheets. They're blurred out because their names are largely irrelevant. But that's not helping me actually organise these workbooks. So instead if instead of just right and clicking onthe Excel icon, I hold down the shift key first. So I'm holding down the shift key on my keyboard, and now I write and click. You can see a different set of menus come up. So we can cascade all of the windows downwards like a waterfall. I can show all windows stacked so that they are on top of each other, so they are stacked from top to bottom. Or I can show them side by side so you can see them going from left to right. I can minimise them all so they all go to the bottom toolbar, the Taskbar. or I can restore all windows. Now, I'll show you another way of doing this. So if you go into the view menu and you go to switch windows, Here, you can switch to another window. So wherever it happens to be hiding, So I want to go to book one. So I now have View windows and Section switch windows, and I can click on any particular window that I want. So this is how you can go from one workbook to another.

4. Importing files and Opening non-native files directly in Excel

Right, let's open up my first workbook again. Now, you'll see that currently we have three spreadsheets in this workbook, and we filled a number of cells in the first one to add additional spreadsheets. What we need to do is go to the bottom, where we've got sheet one, sheet two, and sheet three, and click on the little plus sign. So here we have Sheet 4, Sheet 5, and so forth. So it inserts it after the sheet that you're currently on. So if you want it near the end, click on the last sheet and then click on the plus sign. Now, in addition to adding spreadsheets, you can also delete them. Now, bear in mind that if you delete a sheet, you delete all of the data that might be contained there. For example, it is not just the data that is visible, but also data that may be hidden away. So you see that I have put some data further down, and you have to scroll all the way down for it to delete a sheet right.And click on the tab at the bottom and go to "Delete." So here is a warning. Microsoft Excel will permanently delete the sheet. I do want to continue, and I'm going to click delete. Now, if you want to move to another sheet, it's very easy. Just click on that sheet. So you can see we've got data on sheet one and no data on the others. Now, it may be that you've got a workbook that's got a very high number of spreadsheets. So I'm just going to click "add" and "click this plus button" for quite a while. So I ended up with about 25 sheets. Now, you can scroll through all of these sheets because only the first ten are visible, not by using this drawbar on the bottom right, but by using these two buttons on the bottom left. If you hover over it, you can see you get a tool click and a tool tip. Now, what it's not telling you is that I just left a click on it. It moves the list of sheets to the right, and if I leave and click on the left, it moves them to the left. Now, what it was telling me was that if I press CTRL and left click, that will get me to the first or last sheet. It's very useful if I've got a workbook with lots of different spreadsheets. Similarly, if I wanted to get to a very specific sheet, I can write and click, and that gives me a list of all of the sheets, and I can say I want to go to that one and click OK. And that will get me to that exact spreadsheet that I want. So this is how you can navigate through spreadsheets and add, delete, and navigate through spreadsheets at will.

5. Adding worksheets to existing workbooks

Now, let's have a look at opening workbooks. If you go to File and Open, you can see a list of the most recently used documents. So if I wanted to open up any particular document, I could just click on it. So maybe open up my first workbook. There we go. But suppose it wasn't one of my most recently used documents. Well, I can go file open browse.So here I can go through the folder structure so I can go, say, into level one of the session two folder. If I didn't like that and wanted to leave, I can either click on a folder if it's visible on the left side, or I can click on a previous folder. So Excel one documents for instance. So if I wish to open up my practise workbook, I can open it up from here as well. So let's close that down. Now, when I went into the folder, level one, session two, you'll have noticed it seemed to be blank, and there are other files in there. It's just that at the moment, what you are viewing is all Excel files. Let's change that. We'll select all Excel files by clicking the box at the bottom right. This is the filter for what we can see. And if I change that to all files, you can see that we have got text files and PDF files as well. So let's open up this musiclist text file. I'll click "open" so we have a text import wizard. Now, text files are probably one of the most common files that you'll be opening up in Excel that aren't native Excel spreadsheets. So it's probably worth spending a bit of time looking at this. So we've got two different types of text files. There are delimited and fixed widths. What's this all about? Well, let's have a look at this. in the preview. In row one we've got six live twelve, one MP3. The six are now in one cell, and twelve are alive; MP3 is the name of a file in another cell. But how is the computer going to know that? I mean, it looks right at this minute as if it is in just one big cell. So, if I open Notepad, one thing you can do to roughly align things together is to add a tab. So six tabs, a live tail, one file name, and then line seven, which lines it up even when I get to double digits or triple digits. The next cell, the file name, is in the same place. Now that is called a delimited file. And it's called that because something, in this case the tab that I just pressed, separates each field. Now, the alternative to this is fixed width. This doesn't use things like tap. Instead this uses sort of spaces toseparate out the words the individual cells. So let's put in the number six and press a few spaces to get up to the same sort of position. So the number of spaces that you'll be typing is variable. Here I'm typing 123-4567 because I was typing in a one-digit number, but if I typed in a really long number, then I'd be typing in fewer spaces, 1234. So this is called "fix to width." Previously, when we used some sort of delimiter, it could be a tab, the pound sign, or the hash sign, commas, or semicolons. That is called unlimited. Now, you'll notice right now on the screen that both of these look identical. However, this one is delimited because it is separated by one big tab. This is a fixed width; it is separated by lots of little spaces. So if you're at all unsure what your textfile is, my advice is to try one and then try the other and see which one works. So I'll say this is a delimited file; click Next, and the delimiter is assumed to be a tab. So other guesses could be semicolons, comma spacers, and so forth. And you'll notice that it is now in the data preview, aligned correctly so that all of these numbers, which were in the first column, are separated from the file names. So if I just click back, you can see that in the original preview, these cells weren't aligned at all. But now that we've got the tad delimiter, I'll click on the next step to get to step three or four of the text import wizard. This often isn't used; it's just used to say, if you want to say this column is a text, this column is a date, which is very rare, you would use it. The only real time I would use it, to be honest, is if I was importing a date and I was here in Britain and I was importing dates in the American date format or vice versa. So you're in America and you're importing some in the British or maybe the Japanese file format, but this is very rare. So just leave that alone most of the time and click Finish. And here, you can see the end result. We have in column A all of the numbers and in column B all of the file names. So what we have done is import what was a notepad document, which you could open up in Word, and separate it into two different columns. So let's just close that down and open up the second text file that we have. So, music list two: So let's look at it on the screen. Do you think it is delimited or fixed in width? Have a look at the preview. It looks like it's fixed width because, previously, when delimiting the actual lines, the different columns were not aligned to each other. Now they are. So they have been aligned using lots of little spaces. So we need to say it is fixed for good this time. So I'll click on "next step." Two out of three is a bit different this time. It's not asking you where your delimiters are, it's asking you where the column breaks are. So at the moment it's got a column break here after the second character. Let's scroll down. Now, you won't see the right at the bottom, but later on, we may have three- and four-digit numbers that the computer hasn't recognized. So what I'm going to do is I'm going to drag this arrow two spaces to the right so it more closely aligns with the start of the file name. If you wanted to create a new brake line, you would double click, click at the desired place, and delete it, or double click and move it. As I say, you just track it. So I'll click next, I'll click finish. I don't often use this final step. And here you can see exactly the same result, except that the source data was different. So we have the source data fixed with it, imported it, and given the same information because it is the same data, just presented differently. Now, there was a third file in my folder, and it was a PDF file. Now. PDFs are not openable in Excel. If I try and click on it, we'll get an error message saying, "I don't know what PDF is." It looks like a text file to me. Do I want to open it? So, if you want to open a PDF file that was created in Microsoft Office, you must first open it in Word. So if I click "Browse," I go to the right folder. There's my PDF. I can open it in Word. It says it will now try to convert it to Word. And now it's opening Word. And so we can now copy and paste it into Excel. We haven't yet encountered copy and paste, but we will soon and will be able to import the data into Microsoft Excel as long as it opens correctly in PDF. Now, there are other types of files that youcan open over file types, but quite frankly, theyaren't often used, certainly not on a regular basis. The only one I might have to open is an XML file, but it does a fairly good job at opening them. So really, those are your main ones. You would be opening up native Excel files, and you would be opening up text files. The vast majority of the others you won't need to open, butit's nice to know you can do if you want to.

6. Copying and moving worksheets, and changing spreadsheet order

Now we've got all of these workbooks. We can add and delete spreadsheets. We can also copy them as well. So if I go back to the first one I've controlled and left, I can click the left arrow in the bottom left of the screen and select Sheet One. Now what I can do is copy this sheet. Why might I want to copy a sheet in the workbook? Well, maybe I'm going to make an adjustment. For example, in one sheet I might say that a spanner costs $20, and I want to see what would happen if it cost $30. So I can make a copy of the original and then either change the original or change the copy. But how can I do that? So, if I write and then click on the sheet name currentlySheet One, I can see that I have Move or Copy options. Now if I click on Move or Copy, we get this dialogue box and it says, "Where do you want it to move to?" If I didn't want it to move and instead wanted to make a copy, I needed to check the Create a copy checkbox. So let's see what it's saying right now. I want to move sheet one before sheet 1. Well, that doesn't make much sense because it will be staying in exactly the same place. Instead, if I wanted to move Sheet One to the very end, I could scroll down. The very last sheet isn't actually a sheet. It says to move to the end. Click OK. It's now moved Sheet One all the way back to the end, and I can move it right back to the beginning in a similar fashion. If I want to copy it, I can do that in exactly the same way. Except I would check for "click a copy" or "create a copy" first. So what's happening is that I'm going to be making a copy of Sheet One before Sheet One. Now that does make sense this time because I'm now not just moving; I'm actually creating something new. So click OK, and you can see that a new sheet has been created called Sheet One. Open brackets, two close brackets So that is my copy. So I can make changes to my original or my copy as much as I wish to without affecting the other one. I suppose I wanted to make a copy of more than one spreadsheet at once. First of all, you'd have to select more than one spreadsheet tab, and the way to do that is to click on the first spreadsheet tab. hasn't done anything yet. You go to the last spreadsheet—don't click, hold down, or shift. And now, click. You can see at the bottom that all of these tabs from Sheet Eight to Sheet 13 are now in white. It's not just one tab. And also notice right at the top, right next to the file name, we have the word "group." So now I can make a copy or I can move this entire group. So let's say I wanted to move it all the way to the end. All of these sheets are now moved from the very last original spreadsheet. Now, you may have noticed when I wrote and clicked on these tabs, I could do other things. For example, I can rename it so I can give a spreadsheet a better name. As an example, a sheet one brackets a sheet two. Not very descriptive, but I can rename this and call it "Scanner 30," for instance, and I can call my original "Scanner 20," and I can call this one "spreadsheet." There are now additional ways to move spreadsheets. With these tabs, I can literally just drag them from one place to another, and that would move them as well. There are very few times that I actually have to move or copy spreadsheets when I do.Quite frankly, I genuinely like using a dialogue box that allows me to think and go, "I want it there." No, I want it there. Now, one word of caution. Earlier, we showed how to show how you can select groups altogether. So select the first one, hold down Shift, and select the last one. It may be that you don't want to select an entire range of sheets. You wanted to select, say, Sheets Four and then Sheet 14. You can do that instead of holding down Shift by holding down CTRL. and that will just add individual sheets or take away individual sheets from the selection. However, do be warned. You have now selected a group of sheets, and you can see the word group at the top. Anything you do to one sheet, you do to all of them. For example, if I type in my new text into Sheet Two, because Sheet Three and Sheet Seven have been highlighted as well, you'll see that my new text has been inserted into each one of them. even if you didn't want that to actually happen. So it could override existing data. So using groups is very dangerous. So my advice is not to use the group function unless you have to. When you've finished using the group, you must exit it by clicking on a different spreadsheet, a different tab. So clicking on one of those that are grouped won't actually get you out of the group. You have to click on something completely different to stop selecting the group. So group is very dangerous because, as you've just seen, I could have overwritten some previous text. So why does it say "Group" right at the top? Be on your guard. Use it only when absolutely necessary.

Pay a fraction of the cost to study with Exam-Labs MO-200: Microsoft Excel (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-200: Microsoft Excel (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