MOS Excel Crash Course Part 1: Create and Manage Worksheets and Workbooks - YouTube
Primary Language: English
My Recent Work
You did not do any work on this video
Subtitles in: English
Start End Subtitles
00:00:00 00:00:02 >> Mike: Hey guys, it's Mike and in this video,
00:00:02 00:00:04 I wanna share with you my Excel MOS Exam,
00:00:04 00:00:09 77-727 Crash Course and I'm gonna share the five,
00:00:10 00:00:13 biggest skills in Excel that you're gonna need to know,
00:00:13 00:00:16 for this exam and we're gonna start off with our first one
00:00:16 00:00:20 which is Create and Manage Worksheets and Workbooks.
00:00:20 00:00:24 And that is worth 30 to 35% of your exam,
00:00:24 00:00:26 depending on which version you get.
00:00:26 00:00:30 So the first main skill in that category would be
00:00:30 00:00:32 how to create worksheets and workbooks.
00:00:32 00:00:33 The second one is gonna,
00:00:33 00:00:35 I'm gonna show you how to navigate in worksheets
00:00:35 00:00:37 and workbooks as well and then I'm gonna show you
00:00:37 00:00:40 how to format worksheets and workbooks.
00:00:40 00:00:43 And then I'm gonna show you how to customize options,
00:00:43 00:00:44 in those worksheets and workbooks
00:00:44 00:00:46 and then we're also gonna configure worksheets
00:00:46 00:00:48 and workbooks for distribution.
00:00:48 00:00:52 And knowing these skills help me get 100% on this section,
00:00:52 00:00:55 in the exam and I wanna help you in the same way.
00:00:56 00:00:59 So for create worksheets and workbooks,
00:00:59 00:01:01 there's a skill that you might not know of
00:01:01 00:01:05 and that's how to incorporate a text file into Excel.
00:01:05 00:01:06 So I'm gonna show you how to do that
00:01:06 00:01:09 and that's a very important skill to know for your exam.
00:01:09 00:01:12 So if your instructions tell you to just add,
00:01:12 00:01:16 the information to this worksheet, we can put it anywhere
00:01:16 00:01:19 but I'm gonna assume they want the information right here,
00:01:19 00:01:21 they might tell you a specific cell to put in
00:01:21 00:01:23 and that's fine but they might not,
00:01:23 00:01:25 they might just say in the sales worksheet,
00:01:25 00:01:28 insert this information from this text file.
00:01:28 00:01:31 So the way you do that, I'm just gonna click anywhere,
00:01:31 00:01:35 the logical place to put this information would be here,
00:01:35 00:01:38 in A 24 and then I'm gonna click the Data tab.
00:01:39 00:01:43 And then you're gonna see an icon in the Data tab
00:01:43 00:01:46 that's called From Text and we're gonna click that.
00:01:46 00:01:48 So the term text file, this is how you would locate it,
00:01:48 00:01:50 we have to include the Saskatchewan
00:01:50 00:01:55 and then you see this type Text document.
00:01:55 00:01:57 So I'm gonna double click on it.
00:01:57 00:02:00 Now yours might look like mine it's gonna show up here
00:02:00 00:02:02 or it might look a little bit different
00:02:02 00:02:04 and it might look like this.
00:02:05 00:02:07 So in this example that just popped up,
00:02:07 00:02:11 there is a option to include headers
00:02:11 00:02:13 and if the instructions tell you to put headers,
00:02:13 00:02:15 then make sure that checkbox is clicked.
00:02:15 00:02:17 But the main thing here is what is delimiter
00:02:17 00:02:19 or what is the that means separation.
00:02:19 00:02:24 So how is my information gonna be separated in this area?
00:02:25 00:02:27 It's gonna be the Tab separated
00:02:27 00:02:29 or you could do Comma separated,
00:02:29 00:02:32 those are the most common types comma or tab delimiters
00:02:34 00:02:35 or basically the divider,
00:02:35 00:02:37 whatever separates the information in a column.
00:02:37 00:02:41 So from this word text file,
00:02:41 00:02:44 it's the tab that's the delimiter
00:02:44 00:02:47 and then you wanna make sure whether it's your,
00:02:47 00:02:50 from text wizard that you want to load it onto,
00:02:50 00:02:51 this current page.
00:02:51 00:02:54 So my options it almost creates a new worksheet
00:02:54 00:02:55 so that's an option
00:02:55 00:02:57 but you wanna make sure it's Existing Worksheet,
00:02:57 00:02:59 if that's what the instructions are telling you to do.
00:02:59 00:03:01 And in my version that I have right here,
00:03:01 00:03:04 it just assumes that the header is already placed there.
00:03:04 00:03:09 So I'm just gonna click Ok and my data pops up here.
00:03:09 00:03:12 So you also might be asked to move your worksheets around,
00:03:12 00:03:14 you might be asked to create a new worksheet
00:03:14 00:03:15 or make a copy of one.
00:03:15 00:03:18 So I'll show you how to do those things and to start off,
00:03:18 00:03:19 we're gonna create a worksheet,
00:03:19 00:03:21 we're gonna call this one the stores worksheets.
00:03:21 00:03:26 So hit the plus button down here in your workbook
00:03:26 00:03:29 to create a new worksheet and we'll right click on it
00:03:29 00:03:34 to rename it, let's call it Stores and press Enter.
00:03:34 00:03:37 And then to create a copy of that worksheet,
00:03:37 00:03:39 we're gonna press down the Control key
00:03:39 00:03:43 and then kind of drag that almost like you're dragging
00:03:43 00:03:46 with the Controls key still held down,
00:03:46 00:03:49 almost like you're gonna drag another copy of the worksheet
00:03:49 00:03:50 to the right of it and then drop it
00:03:50 00:03:52 and that's how we create a copy
00:03:52 00:03:54 and then without clicking anything
00:03:54 00:03:56 by just holding down your mouse you can kind of,
00:03:56 00:03:59 decide where you want these worksheets to be placed,
00:03:59 00:04:00 if you have to move them around.
00:04:00 00:04:02 So you'll also be asked to navigate
00:04:02 00:04:05 to certain parts of a worksheet or a workbook.
00:04:05 00:04:08 In this example, I'm gonna navigate to a part of a workbook
00:04:08 00:04:10 that I can't see right now.
00:04:10 00:04:13 So let's say if my instructions are asking me to,
00:04:13 00:04:16 navigate to the locations table,
00:04:16 00:04:18 what I can do is this workbook,
00:04:18 00:04:21 will have kind of an address for that,
00:04:21 00:04:22 right here in this name box.
00:04:22 00:04:24 So if I click the drop arrow,
00:04:24 00:04:27 it'll show me all of the tables that have names
00:04:27 00:04:30 and this location table is what we're looking for.
00:04:30 00:04:33 So if you're asked to go to this location table,
00:04:33 00:04:34 that's how you do it
00:04:34 00:04:37 and it takes you to this location table.
00:04:37 00:04:37 And then it probably asked you
00:04:37 00:04:39 to like change something about the table,
00:04:39 00:04:40 like the color or something but
00:04:40 00:04:45 that's how you would navigate to that part of this workbook.
00:04:45 00:04:48 So another Excel skill that deals with navigation,
00:04:48 00:04:50 is how do you navigate outside of a workbook?
00:04:50 00:04:53 So one way to do that is to create a hyperlink
00:04:53 00:04:54 to a web address
00:04:54 00:04:56 and that's something that you'll be asked on your exam.
00:04:56 00:05:00 So, let's say the instructions are asking me to,
00:05:00 00:05:02 create a hyperlink out of this picture
00:05:02 00:05:04 what I can do is click on the picture,
00:05:04 00:05:08 I'm gonna right click and then click on Link with my mouse.
00:05:09 00:05:13 And this brings up the Insert Hyperlink dialog box
00:05:13 00:05:17 and if it's asking you to create a website link
00:05:17 00:05:18 or a hyperlink to a website,
00:05:18 00:05:20 in the first option where it says Link to
00:05:20 00:05:24 that's the first existing file or web page.
00:05:24 00:05:27 So let's say I wanna navigate to my webpage,
00:05:27 00:05:29 by the way that's my face and latte phone
00:05:29 00:05:34 but anyways it's, we're gonna navigate to
00:05:40 00:05:42 it's my website where you can find practice files
00:05:42 00:05:45 and then I would press okay.
00:05:45 00:05:49 So that would create a link if anyone wanted to,
00:05:52 00:05:53 now if they click on the picture,
00:05:53 00:05:55 it'll take you to that website.
00:05:55 00:05:58 And that's how you'd navigate outside of this workbook.
00:05:58 00:06:02 Another skill you will need to know for your Excel MOS Exam,
00:06:02 00:06:06 is how to format your worksheets or your workbook.
00:06:06 00:06:09 So the first task I'm gonna show you is how to,
00:06:09 00:06:12 change the color of your worksheet tabs
00:06:12 00:06:16 and then secondly how to change the page setup.
00:06:16 00:06:20 So as you can see the sales worksheet has a red color to it
00:06:20 00:06:22 and these worksheets do not.
00:06:22 00:06:25 So to change the color of our worksheet tab,
00:06:25 00:06:26 I'm gonna click on the tab.
00:06:26 00:06:28 So if we wanna change this location
00:06:28 00:06:31 to match the color of this orange table,
00:06:31 00:06:33 we would just click on the worksheet tab,
00:06:33 00:06:36 right click with your mouse and then go to Tab Color
00:06:36 00:06:38 and then you can choose the exact color,
00:06:38 00:06:40 just make sure you've got the right one
00:06:41 00:06:44 and then click that color and now
00:06:44 00:06:46 that worksheet tab has changed colors.
00:06:48 00:06:51 So another part of formatting your worksheets
00:06:51 00:06:56 or workbook is to change the page setup.
00:06:56 00:07:01 So let's say if we were asked to go to our Page Layout view
00:07:02 00:07:05 so that's in this right corner here, our workbook
00:07:05 00:07:08 and then what we're gonna be asked to do,
00:07:08 00:07:12 is separate the Toronto and Montreal locations.
00:07:14 00:07:18 So if I said I only want the Toronto location,
00:07:19 00:07:23 anything above Toronto in this little table here,
00:07:23 00:07:25 is gonna be on the first page and then move,
00:07:26 00:07:28 the entries below that to our second page.
00:07:28 00:07:32 So we're gonna highlight row 14 and what that'll do,
00:07:32 00:07:33 is when we insert Page Break,
00:07:33 00:07:36 it'll insert a page break above that row.
00:07:36 00:07:40 So we'll separate the Toronto and Montreal locations
00:07:40 00:07:43 and make sure the Toronto and Vancouver locations,
00:07:43 00:07:44 are on the first page.
00:07:45 00:07:48 So the way you do that Page Layout tab
00:07:49 00:07:54 and then we're going to under the Breaks drop arrow,
00:07:54 00:07:58 we're gonna select that and then click Insert Page Break.
00:08:00 00:08:02 And now if we scroll up to our first page,
00:08:02 00:08:04 we've cut off the Montreal entries
00:08:04 00:08:08 and they are now, they will now be on the second page.
00:08:09 00:08:12 Another skill you'll need to know for your Excel MOS Exam,
00:08:12 00:08:15 is how to customize options and views,
00:08:15 00:08:16 for worksheets and workbooks.
00:08:16 00:08:19 So the first part of that I'm gonna show you how to hide
00:08:19 00:08:24 and unhide or make worksheets appear and disappear.
00:08:24 00:08:28 So let's say we don't need the stores one,
00:08:28 00:08:29 right now we can hide it.
00:08:29 00:08:31 So the way you would do that is click on the worksheet
00:08:31 00:08:33 that you wanna hide.
00:08:33 00:08:38 And then in the Home tab under the Format drop arrow,
00:08:38 00:08:41 I'm gonna click on that and here are my hide options
00:08:41 00:08:44 so I can hide and unhide and then you can see
00:08:44 00:08:48 so if you have to hide a row, column, a whole sheet
00:08:49 00:08:49 that's what I'm gonna do
00:08:49 00:08:52 so I'm gonna to hide this Stores sheet
00:08:53 00:08:55 and now it's not visible
00:08:55 00:08:57 but you might come to a part of a question where it says,
00:08:57 00:09:00 display the worksheet that's invisible.
00:09:00 00:09:03 And the way you do that is exact same way,
00:09:03 00:09:04 we just hit our worksheet.
00:09:04 00:09:07 So in the Home tab, go to the Format drop arrow
00:09:08 00:09:10 and when I go to hide and unhide this time,
00:09:10 00:09:11 I will unhide the sheet
00:09:13 00:09:16 and then it shows me all of the hidden sheets
00:09:16 00:09:21 and I want the stores one and there it is.
00:09:21 00:09:24 So there's another skill that has to do with customizing,
00:09:24 00:09:27 your worksheets and that is to go
00:09:27 00:09:29 and change the document properties
00:09:29 00:09:31 or add document properties.
00:09:31 00:09:33 So I'm gonna click do that, you click on your File tab
00:09:34 00:09:37 and then in the Info tab in this backstage view,
00:09:37 00:09:39 you have all of your properties here,
00:09:39 00:09:40 you might not see them all.
00:09:40 00:09:43 So it would be nice sometimes you just have to change,
00:09:43 00:09:47 the title, sometimes when you have something like
00:09:47 00:09:50 what is the subject of your worksheet or workbook?
00:09:50 00:09:53 It won't be here so we have to say Show All Properties
00:09:53 00:09:54 and then we see our subject up here.
00:09:54 00:09:57 So maybe we Coffee Shop is our subject
00:10:02 00:10:04 and that's how we would change
00:10:04 00:10:07 or add document properties to this file.
00:10:09 00:10:10 So the last skill I'm gonna show you
00:10:10 00:10:13 that has to do with customizing options for your worksheet,
00:10:13 00:10:17 is the option to show your formulas.
00:10:17 00:10:19 So the best way to do this for your exam
00:10:19 00:10:20 and I say the best
00:10:20 00:10:21 'cause there's multiple ways you can do this
00:10:21 00:10:24 but this is the best one that's gonna get you full marks,
00:10:24 00:10:25 would be to go to the Formulas tab
00:10:27 00:10:29 and then click Show Formulas and we can see that,
00:10:29 00:10:32 our formulas popped up.
00:10:32 00:10:34 I know another way to do that is to,
00:10:34 00:10:37 I believe it's control and then the weird squiggly line,
00:10:37 00:10:40 beside the one on your most but I'm not sure,
00:10:40 00:10:42 if the exam will give you full marks for doing that.
00:10:42 00:10:46 They wanna see that you know how to navigate through,
00:10:46 00:10:48 the Excel window without using shortcuts.
00:10:48 00:10:52 So just to be safe if you wanna get 100% on this skill,
00:10:52 00:10:54 about showing formulas, use the Formulas tab
00:10:54 00:10:56 and go to Show Formulas.
00:10:57 00:10:59 So our last main skill that you have to know,
00:10:59 00:11:03 for your MOS Exam is how to configure worksheets
00:11:03 00:11:05 and workbooks for distribution.
00:11:05 00:11:09 So a key skill here is how to print only a section.
00:11:09 00:11:13 So let's say if I wanna only print my original table,
00:11:13 00:11:14 I would highlight it first.
00:11:16 00:11:18 So or just if my table had a name,
00:11:18 00:11:20 you could just select the name too
00:11:20 00:11:22 but I'm gonna highlight this section
00:11:22 00:11:24 and then I'm gonna go to the File tab
00:11:24 00:11:28 and I'm gonna click Print to reveal my print options.
00:11:28 00:11:31 And instead of saying Print Active Sheets
00:11:31 00:11:33 so you can see on the right, this is a print preview,
00:11:33 00:11:37 I'm only gonna print the selection.
00:11:37 00:11:39 Okay so now that's only gonna print that table
00:11:39 00:11:43 and then I can press back and that's how you would,
00:11:43 00:11:46 print only that section of the worksheet.
00:11:47 00:11:49 So while we're in this backstage view,
00:11:49 00:11:50 another skill that you're gonna have to know,
00:11:50 00:11:54 is how to remove personal information from the workbook.
00:11:54 00:11:56 So the way that you would do that is go back
00:11:56 00:12:00 to this Info tab and then Check for Issues.
00:12:01 00:12:04 And then we're going to Inspect Documents so click that.
00:12:05 00:12:06 Say yes.
00:12:07 00:12:11 Now this pops up your Document Inspector dialog box
00:12:11 00:12:14 and you wanna make sure everything especially this one,
00:12:14 00:12:17 Document Properties and Personal Information is selected
00:12:17 00:12:18 and we're going to inspect
00:12:19 00:12:22 and then this pops up and it says Document Properties
00:12:22 00:12:24 and Personal Information
00:12:24 00:12:26 and it gives you the option to Remove All
00:12:27 00:12:30 and then you see it's already done and we can just Close.
00:12:30 00:12:33 So that's how you would remove personal information,
00:12:33 00:12:35 from a worksheet or workbook.
00:12:37 00:12:38 So in my next video I'm gonna show you
00:12:38 00:12:41 how to Manage Data Cells and Ranges
00:12:41 00:12:42 and some of the skills that you'll need to know,
00:12:42 00:12:44 for your Excel MOS Exam.
00:12:44 00:12:45 But while you're waiting for that,
00:12:45 00:12:48 you can check out this video I have on Excel functions
00:12:48 00:12:49 and I'll see you in the next one.
Copy and paste the embed code above
Copy and paste the embed code above