Class 5 (Objective Domain 2.1) : 77-727 Exam - Techniques for MOS Core Excel Certification | David Murphy | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Class 5 (Objective Domain 2.1) : 77-727 Exam - Techniques for MOS Core Excel Certification

teacher avatar David Murphy, University Lecturer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

7 Lessons (24m)
    • 1. Class Introduction

    • 2. 2.1.1 Replace Data

    • 3. 2.1.2 Cut, Copy or Paste Data

    • 4. 2.1.3 Paste Data by using the Paste Special Command

    • 5. 2.1.4 Fill Cells by using AutoFill

    • 6. 2.1.5 Insert and Delete Cells

    • 7. Domain 2.1 - Exercises Video Solution

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class


Class 5 - Objective Domain Section 2.1 - Insert Data in Cells and Ranges

This is the fifth in a series of 10 classes designed to prepare you to take the 77-727 MOS (Microsoft Office Specialist) exam in Core Excel.

If you are a complete beginner then you should review all of the lectures. 

If you have some level of beginner or intermediate skills in Excel, then you might want to attempt the set project first and discover the gaps in your skills. These can then be addressed by reviewing the lectures.

So this series of classes is designed for everyone who wants to get independent certification of their Excel skills.

This class covers section 2.1 of the Objective Domain for the 77-727 exam.

In addition, if you want to test yourself under exam conditions, then a further set of classes will be available (77-727 Practice Exams and Video Solutions) which provide you with 3 practice exams, comprising 105 project tasks, with which you can test yourself under exam conditions and time constraints. A full set of video solutions are available with these classes also.

When you complete this series of classes you will be fully prepared to obtain this valuable certification and obtain independent certification of your Core Excel skills from Microsoft, a qualification that is highly valued throughout the world.

Meet Your Teacher

Teacher Profile Image

David Murphy

University Lecturer


With over twenty years experience as a systems analyst and project manager with significant experience in the design and implementation of major projects for some of the worlds largest companies, Dave Murphy changed career track in 2000 and has been a professional university educator for over eighteen years. His main interests are in Microsoft Certifications, Project Management and Business Process Analysis. Dave has a B.Sc (Hons) from Trinity College Dublin and an M.Sc Degree in Computing. In addition to obtaining PRINCE2 Practitioner status, Dave is also a Microsoft Certified Professional in Managing Projects with Microsoft Project, has multiple Microsoft Office Specialist certifications and is a MOS examiner.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Class Introduction: Hi, My name's Dave Murphy Anime University lecture myself certified professional on a mosque examiner. This is wool class in a series of classes designed to prepare you to take the Microsoft certification exam. Examine 77 77. In Core Excel, the class is arraigned of people who are relatively new to excel on, particularly at beginners. However, if you're already in Excel User and would like to get official recognition for your skills , then this is the right class for you. If you're starting to learn except from scratch, I've included lectures in class War with Siri's, which will introduce you to accept to the Excel User interface had to navigate within excel until information is entered. Worksheets. If you have some familiarity with software, then you can skip the introductory lectures and go directly to the lectures on the specific elements of the curriculum court. In each class, which Microsoft called the objective domain, I've structured each class of that. You can go directly to the project and test your skills. That way. You know what it is that you have to brush open in order to get a little certification. The most important piece of information that you need to review at this point is the objective domain for the exam. This is a corrected and provided by Microsoft, and you need to ensure that you're proficient and every element of that objective domain. If you are to succeed in the exam, I've included a pdf off the objective remain with the project for this class so printed down have available as recovery rejection off the objective remain completely exercise. You can take each one off as completed. Looking at the contents, you can see that the objective remain is divided into five main sections and that each section have forever silt sections 1.11 point 21.3 and so on. Each of the classes in this series covers one or more elements off the objective domain on the lectures air number to indicate which L'm is being covered. The class projects are specific to the skill within that section of the demand covered in class. The most exams, based on real practical experience with software in this case itself, has to be used during the test, their normal with choice type questions. It's real experience with the product that being examined So if you pass than people know that you're proficient in the level which is being tested, they can be sure of your skills. Some of you may believe that your skills in a particular aspect of the objective remains a strong enough to pass already on that. You don't need to call me the lecture material. If so, fine. Just go to the exercises, calling techniques with that part of the off the domains on test your skills. Gaining more certification is a real advantage on a great way to enhance your CV. It's the number 1 90 certification in the warmth, with over a 1,000,000 exams taken every year. These classes will ensure success and gain that qualification. So go ahead and give yourself on edge in the market place. Get certified. 2. 2.1.1 Replace Data: you find as you become more used to excel at your worksheets and workbooks will increase in size, and the amount of data which is storing will also increase. You need a man easily navigate through it. You walk cheating more books to find information of interests. You can search a worksheet for every occurrence of a value or sell reference by using the find to which is available on the home ribbon in the find and select group here. To find out of a place. Tylo Box has a number of options, which you can display or hide, depending on what you want to find. If we hide the options, you can see that you simply type whatever it is that you're looking for into the find what box on, then, specify if you want to find all conscious or just the next one. If you specify, find all. Then the cell addresses of the matches will be displayed in a list like this. You condone navigated the addresses by clicking. Unless it is, um, the options button provides a range of additional things you can specify, such as searching within the current worksheet, already concerted in the entire workbook. You can search by rolls off a Collins. If you specify by Rose, then sales should be from left to right across each worksheet or books starting at the top row. If you specified by Column to search, will go down each column starting from the left. Most one. The look in option allows you to specify values only on the line formulas or any comments attests to a sale. The match case means that you can specify that the search should exactly match. See opera and lower case characters in search specified. So if you've ticked this box than Dave in all lower case, that won't be found. If you specify Capital D, A V E as the search, if you're searching for text data, then you can use the match entire cell contents to only find exactly what you type into the fine dwarf box. In other words, if I type Dave and Sell has Dave Murphy, it won't make much. The next useful option on the home Final Select menu is the replace option Here. You can specify that you want to replace for the sound in the search with a new set of characters for example, I could specify that if David Sound then places with David, then you cannot replace all matches or just to find them first and then a group place and one by one in order to make sure you're happy with the replacement. 3. 2.1.2 Cut, Copy or Paste Data: all mike self products allow you to copy existing data simply by selecting the data you want. Right clicking on the mercy on selecting the copy command. Nothing appears to happen, but in actual fact, the data has been copied into the memory of your computer can be accessed by you in the paste command. So if you want to take a copy and then place that information, that's where in the worksheet you just click on the first cell in the new position on select Paste again from the right mass problem. The information you copied would remain in the computer memory until you copy something else so I can pace the information repeatedly. If I wish. Now I can copy the content of once out on taste of contents into multiple sends all of once . So, for example, I can copy the content of this out on. If I want doubt to appear in the range of self from de 10 2 d 16 then I just select that rain yourself on, then pace. This is true of anything that you copy. It doesn't have to be text. It could also be a formula. The sale references will be automatically adjust. You can also copy between worksheets so I can copy the days of here. Go to the maintenance, says your worksheet. I'm pasty information here. The copy command doesn't affect the reason of data. It remains where it is. There is, however, another command that could command which all supposed to select information into memory on computer. But in this case, the original information is removed from the worksheet. You can, of course, they're pace two days anywhere you like. But that's the important difference between copy and cause. In effect. If he used to cook command, you're moving the original data from one position to another. I noticed that when you use copy or coat, the selected sales are indicated by the highlight of border, which is highlighted on moving. If you want to de select it, just take on the escape key. Otherwise, this highlighting disappears the next time you under any information into the worksheet. No results of that thes commander available on the ribbon from the home tab on the clipboard group. The clippers is also the name given to the area of memory, which is used to store information which have been caught a copied The paste command is not selective. It pays exactly for awas that you called her copied into the areas specified pay special, on the other hand, provide you with many more options. On is the subject of the next lecture. Rather than using corn paste, you can move data around the worksheet youth in the most. Just place the cursor anywhere in the edge of said. You want to move, and you can see that the kirsch is changes shape now. If you hold down the left mouse button, you can drive the data anywhere you want. Me let go of the wooden. The days has dropped into new location drag and drop. Finally, you should learn her tears to keyboard shortcut for these commands, because you'll find out as you become more experience in using yourself, you're going to use Could copy and paste command many, many times over. So what's a good idea to learned? A short cut for these commands controlled plus C causes a copy to happen Control plus X, because I could to happen on controlled closely because the pace toe happen, it's well worth wise. Learned is Shah cups 4. 2.1.3 Paste Data by using the Paste Special Command: pay special provides a much greater level control as to what exactly you want to pay for after you've copied or could some data and you work sheets? The standard paste command would pace a surge of select caught a copy into the target cell . Our cells, including data, form the formerly on any comments. However, in some cases you may not want to pace all of this together. At the same time, the Pace special menu gives you the full range of all options available for patients selected cell contents to a targets at a range. This man, your neighbors, you selectively choose to pace only the data values, formulas, formatting comments or a combination of these. So you have a lot more control over selectively deciding what it is that you want to pace. Once you've copy some data, you can get the pay special options by position in the course of where you want to pace the data and then by right clicking on the most. You can also get her to pay special menu by selected from the Paste Command in the triple group. As I moved closer ovary jobs and you can see the abbreviated explanation for each of the options. Alternatively, I could invoke the Pai Special dialog box here. These options are exactly the same as those of Ebola. On the right, I'm s book. You can see that you can choose to be selective in March to pace for example. You could decide on Lee to pay some values in your copy data and now honey formulas, which may be used in that data. Alternatively, you could chance to only pace the formulas on the results of executing the former's. For example, if I put in a simple addition for ministers to this and then copy it if I specify values only in the pay special option, you can see in the form new bar that the original formula was not paces on Lee The value. If I change the former of days it in this cell, let's increase the text size and make it bold. Now, even copy of his usual would specify that I only want to pace the formatting like so now anemia put into this set will have the same former of the original. It's work wise noticed that the Pace Fashion dialog box is categorized the pay section specifies what components of the data in the clipboard or paste into the target cells. We've seen some examples of these commands already. The operation section specifies how many days in the target cells would be three years, for example, if multiply selectors than any existing data will be multiplied by the days of pasted into the target cells. So, for example, say I have to number 10 in this cell. I'm 20 in this cell now I copied it. 10. A news pace. Best special to specify that. I want to use a multiply operation when I paste the results to hundreds because 10 will blow over 20 years. 200 skip blanks. If selected, this will avoid overriding any existing data in the target cells. If there is no data from the clipboard people into that cell. For example, if a copy did Asia in B one to B 12 you can see that there are blanks. Now I pay special the data into a one on selective skip blanks. You can see that the blinds in the copy days that do not override the data in the paste range transpose. If selected, this will rotate the data. So if I select this Rangers says and copy them now we can go to this cell and use pay special with the transpose option selectors, You can see that the data are moved from being a veritable column toe horizontal row there for the data in the clipboard listed down and call you would now be, listen across a role in the target cells. Similarly, days that listed across cells in row will be listed down cells in a column. 5. 2.1.4 Fill Cells by using AutoFill: Excel can recognize patterns of data as you're entering the information to work sheets. For example, if I entered Jan Feb into these two says, then I can use. Also failed to complete the other month of the year automatically will help by having to type the information in manually. I just select the first to says and go to the right hand corner of 2nd 1 You can see that the curse of shape changes at this position now on a hole and left most button on dry to the right. You can see Excel is showing me the days it's going to enter if I let go the button, so I'll drag until I get the December and then let go. That's also feel very, very youthful utility. This will work for any sequence of passion of data. It doesn't always work, but it can be very useful and can save you time and effort. As another example, say, I'm putting in a heading for years, 2017 did have an 80 and up to 2025 again, Auto Feel will detect the pattern like so if amusing formulas excel can also detect patterns so set up a 20 into this cell in a Jason, I put a simple formula equals this out, multiplied by two. Now I can also feel across the worksheet by replicating the formula. Auto fill doesn't work with text data generally, except for special cases such as the names of months or days of the week that there are usually no sequence of patrons in a piece of text. Flash Feel is a slightly different feature, which works more or less by example. So if you look at the information entered here, we have people surname, middle name and first name blisters. If I type in the full name into the first strong the list, I can use Flash feel to complete the others and save me a lot of time. Just going to the next cell down on all you have to do is type the first letter off the next Paris and forced name. You can see that Excel is picking up the pattern will automatically completes the information without you having to type is in notice that Excel is showing what it thinks. You want to have, what you need to confirm the correct, but pressing the enter key. You can also get the flash field command from the data tab on the data to group once again . If I select the 1st 7 the passion and click on flash fill the rest of the days it is completed for me automatically. 6. 2.1.5 Insert and Delete Cells: when you have a cell or a range of self selected within the body of the worksheet, as opposed having entire row all kinds of like this, then when you right click on select inserted these you see this menu, which options select will affected worksheet. So you need to be careful. If you just have one self selected like this, then when you choose the shifts out, up or down, you can see that that's what happens if you select in tight row or column. And this is the effect. If you have more than one self selected, then the effect is magnified. So here have selected this group of four sells over two columns and rows. Now, when I select to insert entire row to Rosen surges, it's the same with the columns to Colin Variance characters. This works the same for the lease option, obviously in reverse. So if I select the same group of cells and selected the lease option, you can see the two rows of leisure. If it's like just one cell in the least, I can elect to have the other says, moved up or left, and so on 7. Domain 2.1 - Exercises Video Solution: Okay, so we have the monthly revenue open on. We want select B three and use on field. Get January down to December. Very simple. White in the kinds of in. See everything in double quick on the in, section or dragon? No. Put the title January answer early, too. Make it bold on next. We're gonna all of feel out across the spreadsheet. You see, in a couple of instances here, we're going to have to a wide in a couple of columns. Well, actually, no. It worked fun. We've just white admitted to it anyway, Never going to q three and also feel to queue for 2 to 14. So it all Phyllis formula at e 15 We're gonna do the same thing forward going toe. Also feel of across the spreadsheet. Now. We're gonna copy information me 15 p 50 into the clipboard for going to the copy command. We have selected 50 e 15 to p 15 and copy. Now we're going to pay special. We only select value's ongoing transposed. So pay special values only. And transpose That's OK. And you can see the effect. Next we're gonna put in Titan Sallie one. So the title is monthly revenue by Airport 2017. Type that in and actually gonna marriage and center that title over the range from E one. Q. One. So you have to select evil. The queue on Dan Press the marriage and central change the font. A few is show like 16 and bold. Yes, we're going to use off it a couple of spark line for sparked Lifeco razors in cell or tree , and we're going to all fill out a range or fort all 14 just making this type of bold. But now we're going to go to that spark line graph on making on Phil Spark Lines, and they were going create a name brand. Shall we select from D to two Q 15 and call it monthly revenue Remembered that you can't have a space in the name Phil. I put in an underscore between Muncie on revenue. Next, we're going to use the name manager. So when you enter form this a name manager give you all the rains you have respect when we want him to go to editors and you can see here that you can change the range of cells which are applied to that particular name. Let's say the new range of cells click and drag and everything okay on that range there being changed. No, Lex, select comfort V one paces into a one, but only using the formatting. So we just want to know we could've used the former painter here, by the way also, So you can see all these paste options now. I just made mistakes, So I go back and I do it again. A copy about taste, only the for money. You can see it looks exactly the same. Nellis, do find, replace that we go to find or we play. So here we are with the dollar boss put in Padua. It's I'm going from Padua and replace it with pants stock Ana tells you have many occurrences it finds. So when you say we play so that you go, there's only one replacement never gone. Use flash feel. So in a four, we have already generator coat. We're going to go to data on Flash Ville and all the Matthew generates those cold codes for the rest of self. We take that woman a two instructors. Now we're going to coat the spark lines of selective and course I would go to insert those could says a D two. So you go to D to of course, No, we go two d two. We have to go to the home button on insert amino insert coat selling city appear very knife . Next, we got a copy. Two days for me won't e 14. I'm gonna paces into a different worksheet. Just so you know you don't have to do in the same worksheet Tingle through different watching the results and revenue summary to a one on a paste.