Class 1 (Objective Domain 1.1 and 1.2) : 77-727 Exam - Techniques for MOS Core Excel Certification | David Murphy | Skillshare

Playback Speed


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

Class 1 (Objective Domain 1.1 and 1.2) : 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

14 Lessons (1h 28m)
    • 1. Class Introduction

      3:32
    • 2. Course Structure

      3:01
    • 3. How the exam is conducted

      5:42
    • 4. Introducing the Excel User Interface

      8:37
    • 5. Essential Navigation Skills

      10:00
    • 6. Entering Information

      17:09
    • 7. 1.1.1 Save a Workbook as a Template

      2:53
    • 8. 1.1.2 Importing Data from a Delimited Text File

      5:03
    • 9. 1.1.3 Add a Worksheet to an Existing Workbook

      2:00
    • 10. 1.1.4 Copy and Move a Worksheet

      3:01
    • 11. Solution to Objective Domain 1.1 Exercises

      7:53
    • 12. 1.2.1 & 1.2.2 Search & Navigate to a Named Cell, Range, of Workbook Element

      7:47
    • 13. 1.2.3 Insert and Remove hyperlinks

      3:47
    • 14. Solution to Objective Domain 1.2 Exercises

      7:51
  • --
  • 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.

58

Students

1

Project

About This Class

d5087bcc

Class 1 - Objective Domain Sections 1.1 Create a Presentation and 1.2 - Insert and Format Slides

This is the first 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. In particular, in this first class only, I've included three lectures which provide an overview of Excel and its main components.

If you have some level of beginner or intermediate skills in Excel, then you might want to attempt the set exercises 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.

Every aspect of the Objective Domain (Microsoft term for curriculum) is covered in detail through a comprehensive series of lectures developed and presented by a university lecturer and Microsoft Certified Professional. At the end of each class you will have the opportunity to complete a series of tasks designed to test your knowledge and skills for that section(s) of the Objective Domain. If you find that you are proficient in those exercises then you should move onto the next class in the series. 

When you have covered the material in all of the classes covering the complete Objective Domain, then you will be ready to take the certification 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

Teacher

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!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
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.

Transcripts

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. Course Structure: things courses aimed at people who are relatively new to excel on, particularly as beginners. However, if you are are already an excel Utor, I would like to get official recognition for your skills. Then this is the right course for you. Also, if you're starting to learn excel from scratch, I have included electors which introduce you today. Sal User interface, how to navigate Within Excel and how information is entered into the worksheets. If you some familiarity with software that you can skip these introductory lectures and go directly to the lectures on the specific elements of the curriculum covered in each class, I've struck Kurdish class so that you can go directly to the projects and test your skills . That way, you know what it is that you have to brush up on in order to get that valuable certification. The most important piece of information, which you need review at this point, is the objective domain for the exam. This is the correct and provided by Microsoft, and you need to ensure that you're very proficient in every element of the objective domain . If you are to pass, I've included a pdf of the objective remain in the project for this class for printed down now aunt have available. As you cover each section of the objective lo mein and completely exercises, you can take each one off as completed. Looking at the contents, you can see that the objective domain is divided into five main sections and that each section has forded subsections 1.11 point two and so on each of the classes. In this Siri's covers, one or more elements of the objective domain on the lectures are numbered to indicate which element is being covered, the class projects are specific to the skill within the section of the domain covered in the class. The mosque exams are based on real practical experience. The software in this case, Excel, has to be used during the test. There are no multiple choice type questions. It's really experience with a product that is being examined. So if you pass, then people know that you're proficient in the level which have been tested. They could be sure of your skills. Some of you may believe that just steals in a particular aspect of the objective domain are strongly off the past. Already on that you don't need to cover the lecture material. If so, that's fine. Just go to the exercises covering the techniques for that part of the off the domain on, then test your skills. So without further delay, let's get started with the first class you. 3. How the exam is conducted: the muscles. ERM, they're all conductors. Through a sympathized test at Unauthorized Testing Centre chose to your location. If you go to the following website, you find authorized testing centers close to where you live. Unlike other Mike self exams, you cannot take the Marcy's Andre multi. You have to go to unauthorized testing center, so when you go into this site, you can specify a location and find out which is the most convenient for you. That's the first thing you have to decide. Now let's look at what the test will look like. Well, the first thing to notice that Microsoft made a very significant change to the test than amounted to Microsoft Office 2016 version of the software. If you've already tried to 2013 version of the tests, just be aware that they are very different. The now all project based neither five or seven different projects on 35 questions. In total, you get 50 minutes to complete the test. That's actually a fairly tight timeframe. Urinate to know the techniques involved in order to do well on by the end of this course, you're going to be well prepared. One thing you need to be careful about is exam anxiety that is one imponderable in the equation. The very best weight over commonly difficulty is to practice beforehand. Be well rested on believed that you can do it. If you've covered my exercises, you won't be surprised by what should be asked to do on the day. The test is a fully practical one. There are no multiple choice questions. Passion View, who have mastered the test on behalf of Microsoft, had developed a product called Consul H, which is the interface through ritual complete. The test person via Roy Food may provide information about the user interface for the test at the following site. It will be well with your want to have a good look at this, but I think it's a good idea to familiarise your staff for this. So let's have a look. Now. You can see here that they're five or seven projects. They'll be 35 questions in total. At the end of each many project, you submit your work and then continue to the next project. If you see something on the test that you're unsure of, they just move on and come back to it at the end. That's the best approach. You're an amazing be able to complete lots of the test says. But there will be some that you need to think about carefully, and they're the ones who deserve more time and energy. Remember that every correct answer you get the beans Kloster 700 passing mark so it's worth your while to use your time wisely. Has an example of the test interface. So in this area, and you see a Microsoft Excel Worksheet Island, this area you see information about the project you have to complete notice. A jury engaged. I'm all help features in the problem you're being you're using our disables some of the project and then the questions besides summary, and you just walked your way through each question. When you're finished, you submit for review any question of which are uncertain. Commune marked for later review, so you can continue with the other questions and then come back later to get more difficult ones. Some more thought. This window provides detailed information about the buttons available on the controlling a portion of test plane so you can submit your work go to next project see the countdown timer, which let you know how much time you have left completed test mark taxes from Peter that you can keep track of what you've done and so on. This is a fairly intuitive interface, which will get used to very quickly. This tells you have to interpret instructions where there was text involved, so you can see that underlined text in quotation marks is text that to be typed or copied and pacers text in quotation marks. Mayfair's specific text in the project Fight you're walking on. When you're finished, you begin the summary of detached completes on Bells. Which of March review. These are the ones which you were uncertain off, and you can go back and having a little look at them. This you tore it is available to you before the test so that you'd be fully familiar with the test interface before you start. Essentially, though, it's just a Excel file or files take out of instructions, which comply with the requirements of the objective domain. By the Turning Finishes course, you'll be completely ready Studio exam. So don't worry days of just a few sites, which are worth visiting and or familiarise is have with the exam requirements. Different is the official Microsoft site. For months, This link brings to specific information published by Mark Self emulation to the Core Excel tests on distinct shows in a YouTube video prepared by Pearson Vue E, which provides an overview of consolation testing interface again, It's what wanted to have a look at this, so that should be familiar with the testing environment. Before you sit the exam in the next section, we're going to begin our exploration of the Microsoft Excel User interface. 4. Introducing the Excel User Interface: this lecture is Premier Li, under students who are new to Microsoft Excel. My purpose instruction because in this way, so that people notice software will receive an overview of the techniques involved in the domain elements, which are Kovar in details Step by step intellects, which follow. If you're familiar with Excel, Della suggested you go straight to the lectures, which include exercises designed to test your ability, then reverse the process and brush up on those aspects of Excel, which you need to work on for a Miller's. It's best to follow along in the order down 5% the lectures. So let's start depending on which version of windows you're using on how it's being compared will determine where your clinic. So, however, if you go to the start button and click on this down arrow, you get a list of available lapse in alphabetical order. You can see Excel here and just click on the icon. If you don't fund here, then kick the O power and then go to the search option here. Type in Excel on Wonders were located for you. Won't you have X eloping running? Then you can penetrate. The time sparked by right clicking on the icon here on selecting the pin to task bar option . You will all of the available from this position after that, whichever meant that you use to run except the programmer bolos onto screen like so this is the first screen you see. There's a lot going on here, but for now I just want you to open up a blank Excel workbook but clicking on the blank workbook template. So cases here. When you do so, you'll be presentable to scream. It should be identical to the one that I have. No, let's explore the user interface from Microsoft Excel before we get into the listen balls with worksheet itself, we need to explore those elements of the user interface, which are common to all of the new generation of myself products. These are the ribbon, the foil tub, the title bar, the quick access to a bar, the maximize and restore minimized and close buttons, the ribbon display options on disco bars. The woman is a collection of tubs, each of which performs a specific group of functions. In Excel, we have the home insert page. There's formulas, data review and view tabs As you select each of these terms, you can say that their growth of functions available within the top. For example, in the review tab we have throughout the option to proof our father by spazz checking we cannot comment to the file and so on. Each of the majors had a functions within a ribbon top separated into groups. So, for example, proofing comments and changes are all examples of ribbon groups. It's Ribbon Group is to find and tear into subgroups about the time I've finished with discourse. You be very familiar with all of them. All current Microsoft products have the same type of ribbon tap interface say, may already be familiar with meeting word or PowerPoint. That's the nice thing about the current suite of my exhaust office products. They're very good consistency in the interfaces on between all of the available products, and it cuts down significantly on the learning time involved. The final time is different to the others on the river, in that the command you see in the file tab are not growth in the same way as the other terms. You can see that you're offered to set of commands which are mostly to do. It's saving your work or sharing it with others. Can we look at all of these in due course? Push for a moment, I want to be aware one in particular, and that's the save as option. This is incredibly important of a function because it provides you with the means to save your work. Of course, if you don't save your work, it very lightly. That you're going to lose is so right now is a good turn few to save your first file. If you've been following along with me on, have Excel openly desktop, then click on the file. Save as option on you See the following dialogue box. If you haven't been following alone, just pause the video now on Open Excel under Death Type desktop. As I explained, that starts lecture. So this is the same as dialogue box, and it's very important feature of all Microsoft products. The first you have to specify is where you want to save your work. The location to issue information you're crazy will be written that locations that appear here with different depending on where you've been saving your work in the past from ice cream will look different to yours. However, you should have the this PC option available to select from on. When you click on us, you're going to get this dialog box, which allows you to sculpt up and down and select from the different locations available on your PC. Right now, I want you to save your work to the desktop on the PC. So select desktop open this area. We can see that the location displayed is PC desktop. Next, you need to tell the system what name are going to give The Excel file were about to create . We call this one helpless airlines. No notice that we have a box here, which indicates the type of five are going to save. The defiled file type is an Excel workbook, and that's the one I want you two years. Just notice wherever that when they click on the pull their main to side of the box, there are many different file types that you can select from Massie this in detail later on the course. For now, just make sure that the Excel workbook type selectors no presses sable. They were brought back to the Excel worksheet screen and noticed that the title bar here has the name that's used to say the fire. This is important. The default file. Every convention is for Excel to use. Live book on book to book three on DSO on, But that's of little use. Annual. Always give your father name. That means something relevant to the work you're going to include in the file. We're going to be building a workbook for a company called Helpless Airlines, so it makes sense to name it appropriately now. At this point, we haven't actually entered on the information into the workbook, but it is. A file has been unique. He named Andi, is available for years. As you have the information into the workbook you don't need to use to save ours. Option on file Tub. This small disk icon here saves your work to the location of finance you specified every time you click on it. It's a good idea to get into the habit of doing so at reasonably frequent interest. If you're adding a lot of information to file to make sure that you don't lose your work before we complete, this lecture noticed the minimize restore, but When the next, however, most book is open and you click on this icon, Excel is minimized. The menu bar hair. You can see the icon of any clicking this. The worst book is once again restored to the screen. This sheik icon is useful allows you control the way that the ribbon is displayed. The art or hide option allows you to hide the ribbon from the desktop and give you more space to work. So we're like, second this option and click anywhere in the work she's. You can see that the rhythm disappears. When I moved the course back to the top of the screen, Green Bar appears. I'm taking the ribbon it once more displayed the show times only option Hisey on the line groups within the Times again, taking up much less space in the screen. The show Tubs on Commands takes up the most 1,000,000 readers states Ondas the default. It just flips. Know that you can change this display whenever you want to. So to summarize, we've seen some of the most important elements of the Excel interface learned her to save an Excel workbook on how to manage the display of the ribbon on screen. In the next picture, we're going to continue to explore the interface on, put some information into a New Excel file. 5. Essential Navigation Skills: so the last actually began oil exploration off the external user interface. I mean, I had to say before the desktop on helps neighbor fathers. Now we're going to continue our exploration on. That's like you were going to Sirte at information into file we saved previously, So the first thing you need to do is open for you. Saved. There are no revised to do this, but the easiest ways. Open Excel, and you should see if I listen here in the recent list. Just click on the find their little open like So now let's have a look at what the next walk she actually is. The first thing to notice, said the walkies comprise of rows, which run horizontally across the screen, have remembered from one to a very large number indeed over a 1,000,000. In fact, its value, like you're ever going to need them all. When you click on one of the Roman with notice that the rose highlighters so that you know it is currently selected. Role the top off the worksheet. We have Collins, who should go vertically down the screen. There are over 16 tales and canyons in the next year. Worksheets on again. You're never going to need them all. But you might be wondering how Excel gives such a number of columns names? Well, we can see that the column sounded a, B, c D, etc. On. If you use the scroll by here and just keep clicking in it that you can see that the names become a a a B, A C and so on, then you get to be a B C, etcetera. So that's a naming convention. Youth kinds in Excel. That's essentially what work she's is rows and columns where a row and the kind of intersect is called a self. So if we scroll back to start of the worksheets and click on the first cell, we can see that it's name as seen in this area called The Name Box is a one. The intersection between Roman and Columns a all 1000 in this way, the intersection pin current expected role and canyon so electric hair you can see firstly , that the satisfy lawyers. Secondly, that cell address the places currently selectors is given in the name box. It fundamentally important to get this concept because information place into cells on the information in those says is manipulators. Universities available in Excel everything operates and says you must understand the concept of a cell address just to start. You often entering some information, Excel Click into the Aim One cell and type marketing plan for 2018 2022 into the set like this, and then press the return key on the keyboard. You noticed the swelling press, the return key. The curse. He goes on to the next cell down, which is a two. This is a default way that the course of moves in excel. If you've made a mistake in entering the information, they just tricked back into the cell and you can Amanda information. But clicking into the formula bar here change in the days of using the backspace or delete kiss once again pressing the enter a return cave notice now that the information you just entered seems to be going across B, call you as well click back into the able, and so I noticed that the information just typed is also spade in the formula bar. This parable almost display whatever isn't currently selectors self so if you click into the be warm So you say by looking at the formula bar that there's actually nothing in this , even though it looks that way on the screen, the Thurman a bar will always tell you exactly what is in a cell address. On many occasions, what you see under screen of what is actually there would be different. So be aware that the formula bar is very important in days information consumed to span multiple Canyon Road. But in many cases, that is not actually the case. Now look at this term here, which is the worksheet name, the default name for work. She's his sheet room, and you can add in more walk sheets into the workbook for taking on this Plus son. There will be many instances where you have multiple worksheets in one workbook. That's the difference between a walk book, which is the fire that you save on the work she's, which is one spreadsheet with them to save workbook. You can take that as being like chapters in a book. So remember that the name bucks tells you where you are currently in the worksheet cell address on the Formula Bar attend you. What information is at that address. Remember the click on this icon and save your work as you go along. Move around the north cheese. You can use a few different methods so you can trick into a cell. With best, you can use the term key on the keyboard to time. From cell to cell, you can use the directional arrows to move one cell at a time. The combination of the control key together with the home key, will always bring you back cell a one using the control key with the N key will love bring it back to the last cell in the worksheet, which has data in it. Finally, a yes want to know is had issues the F side function key, which would bring you to the go to dialogue box. Next, just type in senators and go to wherever you want without having to scroll. This is protecting the Ansel I want to be aware of, especially when you spend she's get bigger. The combination control K and G will have the same effect. We want to prepare the marketing plan for helpless airlines from 2018 to 2022. I need to put three years into our work. She's so that you can easily see which is the figures were going to enter, refer to. So let's go to sell a three and type in 2018. Press the tab key, Lauren said. P three Make it Enter 2019 Mr Tub Key. Enter 2020 under and 2021 so on to get 2022. If you press the return key and said the top K, the curse will have moved into the A four cell rather than to be three. So just click into the correct size and make sure that your information looks like mine. Then make sure that you had to save. I can't before we conclude this lecture, I want to mention Alex Al uses different cursor shapes to Peniel, where your position to Carson in the cell. It's a really good idea to be aware of the different curses shape from the outset. For example, if for selected sabot clicking on this and I moved the mass course of older cell, you can see that's in the shape of a thick white cross. When we moved it closer anywhere to the edge of select cell shape changes. It is four hours cursor if I move it to the right hand corner shape changes to this dark black cross, like so you can also change the width of kinds of rules. But placing the cursor at the intersection of the row and dragging notice the shape the course of changing where they marvit to the intersection of two roads now and I hold down the left mouse button I can drag down to change the width of the road. This is also true for condoms, so I can increase the with of Konya may. And now you can see clearly that there is no data inside be warm, even though it looked this over where you can also select in Tyre or Collier by clicking on the road number or Carlin letter like so if I want to change information, said, then liken editors. So if I want to change the tax and they want to marketing, but just 2017 to 2022 I can edit it, but you're selecting sell on now, going to the formal bar. Now I can change that. The information within that particular cell. You can see that the curses changes, shape and blinks at the incision point from editing, and they're like connected the text to summarize what we've learned in this lecture. We've seen how to open Save your book, stroked over work, cheating tear rolls. And Collins had to navigator in the work she's I had to change with of roles and canyons, that the formative I was show you what information is in the sale, how the curse shape changes, depending where replaces and sell how we can edit data already entered. How we could add worksheets into a workbook on what the default naming convention is for these worksheets on, Of course, Remember to hit that save icon every so often, never close workbook. Pick using this close. If you haven't saved file changes you made in this lesson, you would have been asked to save at this point. But it's always a good idea save as you go along in the legs lecture. We're going to start entering some data for a marketing Buddhist 6. Entering Information: exile is all about the manipulation of data. So what kind of data are we dealing with? In the last lecture, we enter some data in the form of the years to its marketing plan. We fares before I answer any more information. I want to briefly describe the main types of data that could be managed in Excel. We're since she talking about three types of data. Numeric things are the core of all work she's processing. Take me numbers, dates or time values, which you enter directing cells in a moment. We don't end some numeric data, which will represent the Bushes, figures for the help of sailors Marketing Bushes for 17 4022. Text text data are alphabetic and numeric characters on the keyboard. Such information is normally used to put labels into your worksheets. So test, for example, the heading to be entered for the helpless airlines. Marketing butchers formless formative use manipulate data which has already been entered into a work. Cheese Farmers are Y Indians. Machi calculations on the core Excel Service requires that you become proficient using a small subset of all of the available formulas off which there are many, but once you learn the approach of formulas, it becomes relatively easy to use any of the available ones. In Excel. We can't start informers and Definite Trip, introducing the sun formula in a little while with saying that the information being entered its in two places in the saddle for two days of being typed on in the formula bar Press the enter key, the carcinomas off Marshall to the next cell down. So my mom start entering sum of the numeric data, which is relevant to the marketing plan for 2017 to 2022 when I saw in Exacted Sam manner of the enter heading for the work. She's some will enter 31,000 for 2017 48 hours for 2018. 45,000 for 2000 and 19 52,000 for 2020. 59,000 2021 on finally 65 for 2000 and 22. If he has the time, Kaine enough to return or enter que. When you finish typing in a number, then the carcel go to sell to write of the one urine instead of moving down, so that's some numbers entered. But now we need to categorise what these numbers actually mean. And to do so, we're going to let a couple of new techniques. The 1st 1 is the insertion undulation of roles and canyons. We want to put a cat renamed beside the figures because they actually represent the estimated budgets for television advertising only. The problem is, I have no room left of the numbers I need to insert and now condoms so I can enter the category title. The right to do so is selected kind that you want to be to the right of where you need to into the information in this case, call you A to select the column. You just click on this column title or header. In this case, a once the common selectors, I can click on the right mouse button. I consider one of the update presented is to insert them. When they select this, everything moves to the right. I noticed that the cell references also changed automatically. So Paris we have the worksheet heading and sell a one. It's now moved to being one Madam. I have the extra space we can put into category heading for a data television advertising. So I click into sell A for trying to capture of advertising that these numbers represent. Don't be alarmed when you see that the information you're typing is making another day to disappear from this plane. It's not actually doing that. You see in the second press enter when you've finished entering to text. Now the figures can be seen, but the text for television advertising is partially hidden. This is simply because the worth of the acres is Tim narrow to spay all of the information . So we need a new technique. Had to widen the Collier. Can I just positioned? Of course, they're here between the A and B claims on. Do you concede that the cursor shape changes? Now hold down the left mouse button and trying to the right until you have the with you require. I'll just drag it back for a moment to show you a second technique. If you want to composition, of course, between the two columns, headings on double click on the left. Merciful quickly, the world of the column will be automatically changed to accommodate the largest piece of information in the Coliseum like so there were also returns. When you want to the lace rolls of condoms, if I select a little column saying D on right, click on the mess, I also have the option to delete a call. You you can see that the days I had entered is gone now because some point you're going to delete something by mistake. I want to draw your attention to decline due, but in here, this won't land on each action. You've taken one step at a time variance to know about when things go wrong just to show you. So you're aware it looks exactly the same when you have a role selectors, so I could insert underneath Wells in the same way. Then let's put in some more casual of expenditure into our walk cheese so it have radio and a five Internet in a six I'm math paper advertising and a seven more browned lunch parties in a aircraft. Living changes in a 1,000,000. The staff ours in a 10 and that's about it. Chance of worth of economy. If you need more space, it'll be nice to make the head were granted, looked a little better So let's handle new technique called marriage and center. I'd like to heading to be spread equally across to call him from A to J and that also like it takes to be bigger to chance. This you select a size from a mom to Jean one. Now click on this button the Marriage Incentive book. The selected sounds of our marriage together under text is centers never can change the font size we clicking here and increasing the size selectors Well, writers, the murder made to text bones. Well, next we want the world of couldn't be to be the same as the other years. So I changed with appropriately the budget plan for each of these cars with is no Sherman's clean pause the video and complete your cheese, so that is exactly the same as mine. And remember to save as you go along. Now all of these numbers mean something because we know it's our budget for next five years . But what's the currency? We need to show what currency these figures refer to because happens Erlend, a base in Dublin, Ireland. The currency want to use is the euro. So how did we do that? well, we're now going today and not a new technique, which is very useful on. Once again. It's all about the right master. First, we need select all of the information which you want to make yours or part time male with select only one Selves. Time Never once select group of says, starting at P four and finishing a G 10 season where all our marketing data has to strike the group of cells you click into the 1st 1 in selection before hold on the Left Mass. Putin and I'm trying to cause it to the last cent in the selection, which is G Tim like, so you can see that the selected says they're highlighters. Now click the right mouse button on. You can see that the quite a number of options to choose from the warm I Want you to choose is a former says option. It just means that the former it says dialogue box like older like boxes. I can move this world wherever I want under screen but quick and to the title bar, holding down the left mouse button on dragging to where I want the box to bay. There are a lot of ups to choose from here, but the room want to use is the currency one. This now allows us the chance of waiting with the currency be to be displayed so we can click on this polar menu. Select the currency symbol we want, in this case, the euro. You can also set number of decimal places you want. The day should be displayed, too, because we're entering Bush Tree figures here. We don't really need any decimals. You can also decide how you want negative figures to be displayed. Sank a chance to help repair in my head if you wish. This firmer says Dialogue box is one that you're going to use time and again in the course . Percio capable another walk. She's is beginning to look correct. Scenario work. She should be looking like this. It's getting quite nice, I think. Now ask yourself the following question. What is the content of said before? It looks like 31,000 euros? Doesn't but remember that I told you the malaria lecture to be careful about this year me, that what you see in the screen is actually what's the North? She's. I click on the cell. But look at the former bar. There are no euros, just the number. That's the content of the cell. The euro symbol is just that Assemble the data are numbers the four money, which he just did waas, Onley formatting, making the contents locus certain way. But it didn't change the fact that they're just numbers, something aware This is an important point because you will have situations as you progress where I get all expected errors. I don't often relate be related to this issue, so it's just as well be aware of it now. Next, we want to total on the numbers in each year stop below the overall anyone pushes book signing for our marketing efforts, this country introduced the concept of using formulas in Excel First Island and a new category total. And now we want to put in a first formula in Excel. This is where Excel comes into its own, but making it very easy for the user. There are a lot of New Miracle Forman in, except on a test level, but we're going to cover only a small fraction of them. But the approach feeding formula in Excel is pretty much the same irrespective of her complex performers are. It's just explored. It's a little force, so I could see it. I'm going to add Update in says from before to be 10 so that we can see what the total mint of the planned budgeted for the year 2017. We want the answer to appear in this cell, so that's where we put a formula. You're honest with the form into the said. We want the answer to bay. That has a long way to do this on a short way to do this. Let's start with the long way so that you know you're going to appreciate difference. All formerly require and equals to sign force that's of Excel knows that you're going to enter formula. So we have equals two on that. We can do the addition, so we type in B four plus B five plus p six and so on. A never pressed return Kate. And that's your first formula, and it works if you've typed in before and so on. There is a slightly better way I'm one which you should get used to doing in Excel, and that is to click on sale you won't re fair to rather than typing in cell reference, which is prone to error. Now let's just remove the content of the formula cell by taking into it unprecedented, leaky. We should remove the contents, not tried t equals to sign again on. Instead of typing in the salad dresses, click on each one type two plus sign and then kick on the next and t up all of the dresses , informed them. Press enter. It's the same for me with the same answer, but it's the better right put in cell references into a formula because it makes it less likely that you're going to make and they're in typing. But there is still a better way to do this, which reduces all that typing on that steers the some function in the formula. Excel coded hundreds of functions, which do preset operations to make life easier for her press users. But the one we want two years is this some function I'm making Titus straight into the south, where we want the answer to appear. We stand made the meekness to sun because we're using a formula. But now we put into full schmuelling two years, some on as you type notice that a list of all the functions in except starting with word some is displayed. We want the forces now. Like most functions, we have to put an opening black. It's like so then we put in the name themselves which hold the data we want to add up there . We could type in the names of the serve you want to, including in the addition to way we did before. But that's a slow way, the better way. It's selective cells that you want to include in the in the addition using the mouse. So started the first cell on holding down the mouth putting marketed last cell. In this election. Notice the form in the bar here, Xlf put on a colon between first under last sell in the range. You selectors, this indicates, indicates a range of cells. In this case, from before to be 10 sold would include all of the cell references in between. Also, now we have to close the brackets like so press return on, we have the answer, so that's our first formula. Now we also need to totals for the other years in our plan. We couldn't paint the exercise and tried the same formula indeed to cells yet again. Except give us a quick great to do this and save some time. If you select a cell with the form of a no place the course at the right hand corner of the cell, you can see that the cursor shape changes to this dark black cross. When you're in this position, you can now hold down the left mess button on drag across the other side's where you want the toque to appear. No, let go, and you can see that the formulas have automatically been entered into your into the appropriate, says This feature is called Auto Fill. Exhale assumes that you want to use the same formula on it automatically changes to sell references. As you try to the right notice that we aren't changing the row references. Only the college emergence is that has emoted right Excel automatically adjust the column references to take account of this. This is called relative referencing because the relative column address is all mostly changed. If any of the kind of not wide enough to accommodate total figures, you'll see that these symbols will be shown in served result. Just widened the condom on you. See the answer like so for the moment, ignore the small green Simmons in left hand corner of the cells become back to that later on in the course. So that's quite a lot we've done in this lecture. Entering data using the former Cells option in certain canyons. Using the first foreigner allows singing all fulfill, if you're new to excel that this a lot of information to absorb again. Don't worry. We'll cover every single thing are discussed in detail during the course. Annual have plenty of opportunity to master the techniques involved in the last three elections. I've covered many of the essential techniques which needs no north past the mosque or Excel exam. Now we're going to explore each of the objective, the main elements in specific detail. So make sure that you've printed out the pdf file. This in all of these elements on, Let's Start Objective demand 1.1. Create worksheets and workbooks 7. 1.1.1 Save a Workbook as a Template : the essential scale test in this part of the domain is how crazy and no workbook based on a template. I've seen this in a previous sector, which explored the Excel user interface. But now let's ensure that you're fully aware of the requirement for the test. So when you're Onek said, you see this screen on icons on the right hand side, which represents some of the template available for use. Craigan. No workbook from a template is very straightforward. You just shaken required templates on thank Sick to create. Put the new workbook is Creator's. Of course you want to save it, but using to save as button on the file tub. So we go to the file tab select, save us, and they specified the location, said file. Give it a name here on actually specify optionally. Specify the file type by selecting from the pull down menu. This generally is not required as the default is always an Excel workbook. It is straight forward of that, However, in the test, you'll be asked to create the workbook based on a specified template, which may not be visible on the screen. So in the spirit to navigate the where the template is available. You did this by entering the names template into the search box. Be careful, type in the name specified in the question. So, for example, if you're were asked to crazy new workbook based on the expense report template, then that is what you type into the search box. No press return K on Excel was searched for the available templates. You can see that it's found quite a few because their number, which have the words in the title or which have the world's in the description from the template. No news here that we haven't called expenses report plural, among which is expense report singular. These are different templates, and you have to be careful to select the correct one as specified exactly in the question pulls in the test. So the expense report singular is the one I have to use based on the question. After that, it's always just the process of creating the walk book by clicking the Great Book Indies. Um, you're most Lighty will not be asked to save the walk book if crazies you're being tested on whether you know, helped create the workbook. But of course, you should by now be comfortable with save our dialogue box on her to save your workbooks 8. 1.1.2 Importing Data from a Delimited Text File: objective. The main 0.1 point 1.2, requires us to be able to import days into an Excel workbook. If you're new tricks out, you may not understand the point of actually doing this in the first place, but the main reason is that would be many situations where companies, by date from outside organizations who specialize in collecting information such as cosmic profiles, email addresses, conquered contact details and so on didn't sell this information or the companies who may wish to market the product for service instead of individuals. So these companies purchased the information. The data, which is purchased, may not be in the former, with which is immediately recognizable by excel. So you need to be able to import information and also its former to be compassionate with that used by Excel on the data group, we have a group called Guess External Data. There are quite a few lobster choose from here, but the warning with concentrate on is the from text option, which is examined in the 77 727 exam. The from text option provide you with a wizard which guides you through the import process just to make sure that you actually understand what a text file actually is. I'm gonna create when using myself work. So I could just type in some names, first name and surname. I'm going to separate the atoms of names using Commerz at the end of the surname. I used to return Kate to move to the next line in the file. So here we have the information typed in. I never want to save the file of text file, so give the file name on the location. And now it's like the plain text option from the savers pulled their menu. When the press okay, we'll have a tech fire saved, which is D limbs it by commerce. In other words, each of the pieces of information will to recognize its separated or delivered by a comma. Each road would be recognized by the character which represents the return key. Now we can go back to excel and start the import process. So I go to the days of tab under get external Data group, from which I select the from text option. Now I can navigate to where I created the text file on Well, selectors, I'm brought to the text import with dialogue box You can see that the first thing in the Terex out is how the data you want to import R D limiters. Well, we've seen that we use comes to limited Asia. So I select that you can also see here that you could have used a tab key rather than a comma to delimit each field of information. Fear is just a term used to describe each element of data. We're importing first name and surname in the example the first name of the field on the surname of field. If the data is fixed with, it means that each feeling the original text file was given a set number of Carter fit into which is not very effective in terms of using Vermeule the computer because you believe you a lot of blank space in many cases. So it's Farmall using to use the limited text sauce, such as the one I created area so we specified comes of the limiter. Sometimes a text file will have a header who subscribed the information such as first name comma, surname, Mitch case. You need to check this check box and most instances there won't be a header. No repressing expert. I'm about to step to the process. Hey, can specify which the limiter is used in the text file. In select, more than one type of delivers her. We used to commerce or select that the days of previous section shows you how the data will appear on the walk. She's using the delivery, you selectors, so we can see that there would be to kind of data one for the first sent on or for the surnames, which is exactly what we want. They actually get to the third and final step in the process. Harry dislike each column and turn specify the type of day who could be expected in each. The general option will normally suffice. Since he converts a number into a number A name, she looks like a data into a daze and treat all of the data as text. You also have the choice to be specific about each column if you wish to do so. And that's the way that you import a text for in these, um, you'll be told, held following through on all you have to do it. Follow the steps Indication in the Wizard 9. 1.1.3 Add a Worksheet to an Existing Workbook: the default will you create? A new workbook is for it to have one work. She's for that walk Chief be named with same name is that associated with the template? But you can add and remove more, chief mere workbooks. The number is only lived by the Obama member you have in your computer in this part of the off the domain, your ass. Create a new worksheet in an existing workbook. You'll also be asked positioned worksheet into a specific position so that sound some worksheets into a new expense report. Workbook. All you have to do is click on this. Plus Ember on a new blank worksheet would be crazy. Note the naming convention. The original worksheet was named after the template Name each of the new of the named sheet one sheet two and so on. Worksheets cannot be repositioned within the workbook. By selecting the worksheet, you want to move on, dragging it to the position required. So if I want sheet three to be the first worksheets and workbook, I just click it selected. Hold down the left mouse button on dry to where I wants to be positioned. You consider as I drag a small black indicator shows where it would be positioned when I let go. With most book, You can also insert a new worksheet by selecting inserts from the right mouse button options in this case you 100% with a number of different options. But the only one you need to be aware of at this point is the worksheet ups. The new rock shoes and Surges on is named with the next available default cheat name in this case sheet For because we've already years, she two on three on It is simple of that. 10. 1.1.4 Copy and Move a Worksheet: in this section of the objective remain RBS use move or copy options available. Right Math book When your position the mass coarser over walkie tap like cell. There are a number of issues here to need to explain before showing you the type of questions which would be posing the exam. What you can do with these up since depends on whether you have one or more workbook open at the point in your sights. Do remove a copy command. Let's place explore this first so we have our single workbook open. Let's select the expense report. Worksheet on right click on the mess. No, select the move or copy command this style of boxer levers to move, which means that we're going to remember from the workbook or copy, which means I'm going to take a copy. I was, but leave the existing one in place. Let's try both. It's great Copy. You have to click on this check box, that hand comptel, except where you want the copy to visit. To be positioned in the workbook. My put it at the end. You can see how it's crazy. Accompany given is a name expense report to in brackets on place. It last in your of worksheets in the workbook Mavi workbook Doesn't many make with sense unless you're moving. Walkie. Two different workbook. Now I have only had one workbook open. Open the lift point, but you can have many workbooks open at the same time. The first cell open. Second workbook happens. Airless profit forecast. Thank you. Have more than one more. Open the floor menu on the river. Very useful. Have the option to switch windows between the open workbooks. Viewed of side to side on a lot of other things with you. Cover in detail later in the course. The point, though, is that we have to work with open and then go back to the move or copy option. They can see that you can select the workbook. We want to move the work she's into. I want immunity expense report into the helpless workbook. So I highlight that workbook name here. No, that's where that will come on to place the moved worksheet. I said it moved. End option. If I left the copy check box unchecked, then this worksheet will be moved to the help of workbook. I'm removed from the current workbook, but I want to have the work she in both. So I'll check this box. And when the press okay, we'll have a copy of the walkies in both or books. So that's moving and copying more. Keats. Next, I want you to complete the exercises. The main objective. 1.1 and make sure that you understand the techniques involved. 11. Solution to Objective Domain 1.1 Exercises: So in the first instance, we have to run up Excel. So I wrote, like so open a new workbook. I'm searching for a project tracker in the search area here. Now I get to answers, get the right one Project tracker and create Simple is that? No, I'm going to save it in the mosque exam. You won't be asked to save your files. But I've asked you this question just to make sure that you know how to say so. I'm going to save it into my core solutions. I'm going to give the name my project tracker on. I'll press safe. It's a simple as that. No question. Number two Now here I have to work books open already. They can do that is just going to have you arrange. And I've arranged his heart thumping on the screen. So I've got one above the other. I know. I got to copy one walkie to another work cheese. So the first you have to select the worksheet I want right Click on the name of the tab. A ripe tick Move our copy. I'm going to create copier. I have to have this checkbook selects Is selectors then I have to say where I want to move it to. I'm going to move to flight details Press. OK, okay. I'm saying Moved to the end. Press OK on it. As you can see here has moved it to the end. So that's it. Question number three. I'm going to open my project Tracker workbook again. Crazy new work. She's so are you. Click on the plus side, Create a new work She's on writing right clickers on Rename us. It's already before the set of worksheet so I don't have to move us. So this is new projects now. If I did have moved, obviously I can just drag it to where I want to. So I drive to in front of Settled on that question Krishna Before and Sarah to new worksheet name Participatory Self click on the plus button rename its you to call a pilot study on No, I have to move it so I just drag it to my new position. That's it. Never going to dio question five. We're going to copy again, So I go back into the to worksheet areas. No, I'm going to copy the main thing Cheshire so I have to select us. Right? Dick? Andi, Then selectee mover. Copy. I want to create a copy. So I do that press. Ok, well, I have to go where? I want to copy to trick in the pull down menu. And you feel the open workbooks sector when you want on the position and press, OK? And that's that have made conceptual in that workbook. Question six Creation. The work book based on the travel expense log templates. We'll have to find that force. So it's a new and that was searchers Travel expense Log Press the return key on Dhere. We have quite a few of them. As you can see, travel expense log is over here to the right. Create. I'm here this on. I'm going to say this is my travel expense in the usual location. So that is questioned. Six. Not difficult. Question seven. Open a new blank Excel workbook. So we'll open a blank workbook, and then we've got to import data. What? You go to the data tab on, Then we go to from text files their souls a text file. Now I have to go and find that file again in the mosque exams. The fire would be there for you. You'll have to go searching around for the way I do. This is a comma delivers a text file so that open the file first. Okay, where have it does okay? Except followed. Here we are helpless. Details A txt. That's how we know it's a text file that this is common limiters. It has head received. Got to make sure you click in the My data has had a check box press. Next in the case for the D limiter is in this case, it's a comma. You can see the days already set up, then just finish. We know how all the data imported into, except very nicely. Sometimes that importing can cause people difficulty. But it's not really very difficult. So we're going to do it again. Now, in a moment in question, April just saved work. Workforce as helpless Details Question eight. Once again, we're going to import data. In this case, it's a tab delimited text file. So data from tech falls off. The check file on staff details is when we want it doesn't have headers, so we don't have to select the head of box we path next. I now indicate that it is top on top of the default. Now I've picked and coming here. Would it make a difference? It certainly will. So we've got to make sure Top. It's like there's not comma press. Next. Important data on saved Fired A staff details. So in the most exam, it even easier than this. You don't have to do to saving it to save it for you. And finally, after I saved this stuff, Detailed file Question nine were moving a worksheet. You must know the different. Remove and copy. When you move or keys. Move them out of the existing more cheese into the one that you specify. So I'll opened the marking plan Book book Select the one. I want to move the maintenance schedule. Right, Because copier move on this time, do not create a copy. That way it will be moved out of this workbook and into the new one. We specify which in this case is this workbook be moved to the end. But you could move t and if you wish that the place at the end. It says that in the question. And here we are. That's it dumb. Moved. Remember, there's a difference. Remove and copy and basically checking on that copy both, and that's test subjective 1.1 solution complete. 12. 1.2.1 & 1.2.2 Search & Navigate to a Named Cell, Range, of Workbook Element: you find that you can more usefully itself that your worksheets and workbooks will increase in size on the amount of data which is storing will also increase. You need a means to easily navigate throughout your worksheets and workbooks to find information of interest. There are a number of techniques that you need to be aware of in this respect and the all of em from the home tab on the signed and select humans in the editing group, you need to know all of these with 77 77 exam and so we'll go through each one in turn. Well, the purpose of this section of the brain is ensure that you confine data in your workbooks . The test questions are usually phrase in such a way that weren't you found a day spent fighting the question. You'll also be asked to do something to that days, such as delays in the leisure or changed in some way. In the example which follows, I demonstrate some of the type of things you may be asked to do, some of which you will love of court. Ah, yes, and of course, But what you will do so in detail at a later point, but the find and finally replace options. Bring up the same dialog box called a find and replace dialog box. This is two tabs, the second of which three place up displays an additional two buttons the replace and replace all bottoms under replaced with box. We look at the fine tab for us. This allows you to search a walk she's or workbook for every occurrence of a value or sad reference by typing the data you're searching for into the find watch box. Here you have a number of options to choose from. Also, you can find all occurrences of the search Tim by clicking on this button. If there is more than one, the currents down there will be listed like so, and you can just click to the item in the list to highlight that value in the workbook. Alternatively, you can to find next option, which will highlight each occurrence of the value as it finds us. If you click on replaced have, then we get some additional options. Now we can specify that we want to replace the search, share with something else so I can put in figure three place occurrences of diagram. When they're found, I place him one by one as they found, or I can replace Marla Moon. Go. If I choose the latter, I get an information warning telling me how many have been replaced. A chance. Let's note that the undo button will do all of replacements. Moon go just in case you ensure that you've done the right thing. The fun and replace dialog box has a number of other options, but you can display or high depending on what you want to find such a search within the current worksheet or a concerted in the entire workbook. You can search by rows or columns if you specified by Rose, and the search would be from left to right across each worksheet or what books starting at the top row. If you specified by Collier, the surgeon golden each Kalyan starting from the left, most one. The look in option allows you to specify values only online formals Already Collins A. Test yourself that we haven't looked at comments up to now in the course, so given that you now know you can search within them travel, Look at how to er comes, which is a very simple technique with a very youthful. If you make any selection, expect the right Merced part. You can see that you haven't add current option here. You can type in any additional information related himself. Selectors, You consider this to be serious, propulsive, milled, attests to sell. It's very useful if you want to explain the contents of somebody else who may be viewing your worksheet. If you've added a comment to sell, then you can edit the leases and hide or displays returning to the search functions. The match case means, as you can specify, the researchers exactly mercy opera and lower case categories in search specified. So if you have this ticked, then Dave all in lower case won't be found If you specify Capital D, a V A search. If you're searching for text data thanking, use the much entire cell contents to only find exactly what you type into the find. What box? In other words, if I type days other south of Dave Murphy, it won't make a much so they find on the find and replace commands Covert. The next month review is to go to bottom if you notice specific address of a cellular to navigate to that, Excel has a very useful navigation feature called it Go to Command. You can access access under the find and select commands, or eat more easy by taking the F five key on the keyboard, or indeed, by passing control and J at same time. Either will bring up the go to dialogue box. It's good to know the shortcut to this command because you eventually uses repeat junior normal course normally is a big shell. So in the reference Bakshi, specify where you want to go by providing a cell address. If I type in a tree, that's where the course would be positioned with a pressing okay book. You can almost also specify a named range in the workbook to go to again. We haven't covered named Ranges at this point in the course, but a short reviews years well before we move on, the name range is simply a name that you give to a range of cells. So, for example, if I select this group of Selves, I can click into the name box Teicher name Press the return key. That group of says now has name. No matter where I am in the workbook. If I click in the name Buck on the name box pulled down, I can select the name on I'll be brought directly to the group says this also worked in the go to dialogue. Instead of putting in the cell address, I confessed. Fight unnamed range instead. Like so. We also have a special bulletin available on this style of box, which is to go to special option also available and find a new place commands here. This is a very useful function when you're looking for areas of the workbook, which is special features such as Commons Conditional formatting data validation on others . We haven't covered many of these features, yes, but you need to know the mechanics of using to go to special options. But in a stranger's using the comments which are a fair to ensure fair to earlier on society journalists in a test question to find all sounds which have comments associated that all you needs to go to go to special dialogue and check the Commons option and press OK, you can see that those says they have comes and else highlighters, so if you're asked to move comments from the worksheet, then you just have to click into the appropriate sound, right? Like underneath the comments. The rest of the options on the finance sector are actually included in the go to special options over here, because there you quite a lot. So searching for these five options will be very common when you begin to use, except in the rarest. And that's everything you need. Snow but navigating except 77 727 exam. 13. 1.2.3 Insert and Remove hyperlinks: Hi peelings late, great connections from within Your Excel worksheets to other documents, websites or the part of your workbooks and more. All you have to do is specify the place you wanting to on a clickable link is created by Excel, thread, hyperlink, even go through the insert tab, a dent trick hyperlink from within the Links group Alternatively, and more easily just right click on the cell. You want to create the hyperlink in and go to the hyperlink option. Therefore, main options to from existing file a Web page to place the high pulling in the current document crazy new documents or to link to an email address. Let's have a look at each in turn. So if I want to link to a website, I just highlight this option and type in the text I want to display in the cell so the user knows there's a link available. This is a non optional choice. The alternative is just a special fight. The Web address down here on that is far will be displayed. You can also add in a screen tip, which will appear when the mariscos over that cell I noticed a change in the shape of the mouth Corsa, which changes to a finger shape. Of course, when you click in that cell, you'll be directed to the address that you specified. This option also alleged to link to an existing father. Now, instead of specifying a Web address, you can browse it. Fire, which you want to create a link to the fire, will then open when you click on the cell. Vice elect this Excel file telling to you can see that the hyperlink is activators on that the final opens when I select hyperlink. The next option is to link to an address within the workbook that's open. Dis compete with Salander. Current worksheets are 20 work. She's in the workbook, so once again we can specify what text you want to display on. You can have a screen tip. If the sound you want to link to within the same worksheet, then you just specify the cell address in this box. Alternatively, if there are multiple worksheets in the workbook, then you can combine the saddle reference with the name of the worksheets. For example, a 14 in mark sheet named Sheet three. This will link you directly to this cell address. The create new document option allows you to create a new walk, argues in the name and address specified here so I can change the default path to save the new workbook anywhere I wish. Finally, you can use a hyperlink to lunch. Whatever is your preferred email program on your local computer. The email address that would be used is the warn you put into the email address box. You may find that you have configured a link your email program the first time you use this type of hyperlink, but once you've don't sell, the connection will work automatically. So those are the hyperlink options available in Excel. When you created a hyperlink, you can edited by right, clicking on the cell and selecting at a hyperlink. If you no longer need the hyperlink, you can remove it by selecting the remove hyperlink option on the right mouse button. Alternatively, both of these options come the access from the insert time the links group. We should bring up the Addis Hyperlink Dialog box. You can see that you can also remove the link using this dialogue 14. Solution to Objective Domain 1.2 Exercises: So first thing is we open the appropriate work. So if the marketing plan budget open and now we want to use find selector, we got to fund select on look for condition formatting. You can see the South highlighted. That's the only one condition for mating. Apply to us. Now we go to a three American insert comment. Fallen right both. So you can put in these countries may be changed as the comments comes, a very useful. Now let's out hyperlinks. So I click on the title in a one. And when I right click of the option to hyperlink on, we can hyper into the address which this case of Ryanair's website And if you want to display text, so just show her works up it in help us get you there. In effect, I removed the title, but up to the point you can put in whatever text you want. No, I'm going to at use, find on select again and I want to find the South with comments granted, the new worksheet go to finance Select Answer late comments to the lead to comment. So just right click and say the league comments. You can see the twos over highlighted. So I delete comments and comments and have gone now in the main in four cheese. I see. While have any said that data validation applied so I can go to the final select on this, I must specify data validation. And there are no moving here which do have validation on. So you know those No, I'm going to go to the air marketing plan. I'm going to go to Sir be to have on the gold at hyperlink which links to a tree off the work. She's in the same What book? Right click and then select Hyperlink. And now I specify world hyperlink to so I say place in the existing document. So now I say I want to go to high 83 but I won't go to a three in a different worksheet. So I have to select the work she's impress. Okay, Next I'm going to go to mate and shed, won't cheat on you to go to specialty would find our comments. So we have go to, I mean confined the commons and go to special price. Okay on their comments in there, complete my right technology on pressing Delete Commons. Now we're going to get rid of the hopefully that's and cell that in cell a one of the flight. So speaking to that seven right, take If there's a hyperlink there, you always have the option to removed hyperlink so it's gone next in the flight. Will chief. We are crazy high pulling, so it creates a new document called Hamper Flight Tracker. One says new document means a new workbook, so I give it a name, but I want to create a new document, the name of the document school we called help with flight tracker. We've donor of the auction to leave the edging off that doctrine of the later on, so it's not going to bring it directly. Is that documents? But the hyperlink being creative, never going to do replace. So we want to go to the replace option, which is contained in the fine of select a group. Eso doing we've put in Dublin to me won't replace Dublin to with Dublin Main Terminal. When we say we place all it could tell you her many replacements, it's actually made 33 replacements Next four want to is linked tax in tight budget plans. We've text already in here. We want to put in. Hyperlink comes and we want to linked a text. The Bushehr plant help assailant marking plants Dozen 17 to tell Isn't wants to workbook. This is no linking to a different work. Work entirely given the name. And then we select the workbook while selling to which is helpless. Airlines Marketing plan. I'm press OK there . We want to link to an email address for me. High pulling. Specify email address. I know we can put in the mail adjustments linked to this case. Info. What? Ryanair to come. I mean, put in a subjective we want. So we put in contact, helpless as a subject now and finally we want to replace only the first occurrence of sevens have toward advertising. It's we want to replace word advertising with adverts. So we go to replace put in what we're looking forward to the advertising put in. We want to play the wit which is adverts, and then just say we play. So to find the force pecans or find next, how about it