Pass Microsoft Excel Certification Exams in First Attempt Easily

Microsoft Excel Certification Practice Test Questions, Microsoft Excel Exam Practice Test Questions

Want to prepare by using Microsoft Excel certification exam practice test questions efficiently. 100% actual Microsoft Excel practice test questions and answers, study guide and training course from Exam-Labs provide a complete solution to pass. Microsoft Excel exam practice test questions and answers in VCE Format make it convenient to experience the actual test before you take the real exam. Pass with Microsoft Excel certification practice test questions and answers with Exam-Labs VCE files.

Create Worksheets and Workbooks

4. 1.1.4 Copy and Move a Worksheet

Of the objective domain. You'll be asked to use the Move or Copy options available on the right mouse button when you position the mouse cursor over a worksheet tab, like so. There are a number of issues here that I need to explain before showing you the type of questions that would be on the exam. What you can do with these options depends on whether you have one or more workbooks open at the point where you decide to use the Move Copy command. So let's briefly explore this first. So we have our single workbook open, and let's select the expense report worksheet and right click on the mouse. Now we can select the Move or Copy command. This dialogue box allows us to move, which means that we're going to remove it from the workbook, or copy, which means that we're going to take a copy of it and leave the existing one in place. Let's try both. To create a copy, you have to click on this checkbox. And here you can tell Excel where you want the copy to be positioned in the workbook. We put it at the end. You can see how I've created a copy, given it the name "expense report," put two in brackets, and placed it last in the order of worksheets in the workbook. Moving a worksheet doesn't really make much sense unless you're moving the worksheet to a different workbook. Now, we've only had one workbook open up to this point, but you can have many workbooks open at the same time. So first I'll open a second workbook. Helpless Airlines. Profit forecast. When you have more than one workbook open, the View menu on the ribbon is very useful. Here we have the option to switch windows between the open workbooks, view them side by side, and a lot of other things which we'll cover in detail later in the course. The point, though, is that we have two workbooks open, and we can go back to the Move or Copy options. Now we can see that you can select the workbook that we want to move the worksheet into. I want to move the expense report into the Helpless workbook. So I'll highlight that workbook name here. Now it asks where in that workbook I want to place the moved worksheet. I click the "move to end" button. If I leave the Copy Check box unchecked, then this worksheet will be moved to the helper's workbook and removed from the current workbook. But I want to have the worksheet in both. So I check this box, and when I press OK, we'll have a copy of the worksheets in both workbooks, so that's moving and copying worksheets. Next, I want you to complete the exercises for Domain Objective One and make sure that you understand the techniques we've discussed.

5. Exercises Domain 1.1

So in the first instance, we have to run Excel. So I'll run Excel, open a new workbook, and I'm searching for a project tracker in the search area here. Now I get two answers: "Get the right project tracker and create. simple as that. Now that I'm going to save it in the math 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 save. So I'm going to save it into MyCore Solutions, give it the name "project tracker," and then press Save. It's as simple as that. Now question number two: I already have the two workbooks open here. You can do that by just going to View>Arrange, and I've arranged these horizontally on the screen. So I've got one above the other. And now I've got to copy one worksheet into another worksheet.So the first thing I have to do is select a worksheet I want and right-click on the name of the tab. So right click, move, or copy? I'm going to create a copy. So I have to have this checkbox selector. Then I have to say where I want to move it to. I'm going to move it to Flight Details; press Okay. Okay. I'm saying move to the end and press OK. and 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 and create a new worksheet. So all you do is click on the plus sign that creates the new worksheets and right-click on them and rename them. It's already before the setup worksheet, so I don't have to move it. So this is a new project. If I do need to move it, I can simply drag it to where I want it. So I'll drag it up front for setup and that question. Question number four: Insert a new worksheet named Pilot Study." So click on the plus button and rename it to call it a Pilot Study. And now I have to move it. So I just drag it to my new position, and that's it. Now we're going to do Question Five. We're going to copy again. So I go back into the two worksheet areas. Now I'm going to copy the maintenance schedule. So I have to select it, right click, and then select move or copy. I want to create a copy, so I do that and press OK. Well, I have to say where I want to copy it to.So I click on the pulldown menu, and you see all the open workbooks. Select the one you want and the position, then press okay. And that's it. Now I have a maintenance schedule in that workbook. Question six: Create a new workbook based on the travel expense log template. So, I'll have to find that for Newand. I will search for it travel expense log, press return, and here we have quite a few of them. As you can see, travel expense logging is over here to the right create.And here it is. And I'm going to save this as my travel expense in the usual location. So that is question six—not difficult. Question seven: Open a new blank Excel workbook. So we'll open a blank workbook, and now you've got to import data. Well, you go through the data tab, and then we go to the text files. It sold the text file. Now I have to go and find that file. And, once again, the file would be available to you during the math exam. You don't have to go searching around for it the way I do. This is a comma dealing with a text file. So let's open the file first. So, where did I get it? Okay, excellent files. Here we are. Helpless details of TXT That's how we know it's a text file. This is a common limit. It has headers. So you've got to make sure you click on the My data has headers checkbox. Press Next and indicate what the delimiter is. In this case, it's a comma, and you can see the data already set up and then just finish. We now have all the data imported into Excel very nicely. Sometimes that importing can cause people some difficulty, but it's not really very difficult. We're going to do it again now in a moment in Question Eight. But I'll just save the workbook first as "helpless details." Question eight. Once again, we're going to import data. In this case, it's a tab-delimited text file. So the data from text files or the text file with staff details is the one we want. It doesn't have headers, so we don't have to select the headers box. We press Next, and they'll indicate that it is a tab. Tab is the default. Now I've clicked on a comma here. Will it make a difference? It certainly will. So we've got to make sure a tab is selected, not a comma. Press Next, import the data, and save the file as Staff Details. So, in the Moss, examination is even simpler. You don't have to do the saving. It's a save for you. And finally, after I saved this staff details file, question nine We're moving a worksheet. You must know the difference between move and copy. When you move a worksheet, it moves it out of the existing worksheet into the one that you specify. So I'll open the marketing plan workbook and select the one I want to move, the maintenance schedule. Right click on it, copy, or move. And 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 moving to the end. Well, you can move to the end if you wish. place at the end. It says that in the question. And here we are. And that's it. Done. Moved. Remember, there's a differential move and copy, and it's basically clicking on that copy button, and that's test objective 1.1.

Navigate in Worksheets and Workbooks

1. 1.2.1 & 1.2.2 Search & Navigate to a Named Cell, Range, of Workbook Element

of techniques which you need to be aware of in this respect, and they are all available from the Home tab and the Find and Select commands in the editing group. You need to know all of these for the 77-727 exam, so we're going to go through each one in turn. Although the purpose of this section of the domain is to ensure that you can find data in your workbooks, the test questions are usually phrased in such a way that once you find the data specified in the question, you'll also be asked to do something with that data, such as delete it or change it in some way. In the examples that follow, I'll demonstrate some of the types of things you'd be asked to do, some of which you won't have covered as of yet in the course but will at a later point. Both the Find and the Find and Replace options bring up the same dialogue box called the Find and Replace dialogue box. This has two tabs, the second of which, the Replace tab, displays an additional two buttons to replace and replace all buttons, and the Replace With box. We look at the Find tab first. This allows you to search a worksheet or workbook for every occurrence of a value or sale reference by typing the data you're searching for into the Find What box. Here, you have a number of options to choose from. Also, you can find all occurrences of the search term by clicking on this button. If there is more than one occurrence, then they will be listed like so, and you can just click on the item in the list to highlight that value in the workbook. Alternatively, you can use the Find Next option, which will highlight each occurrence of the value as it finds it. If you click on the "Replace" tab, then we get some additional options. Now we can specify that we want to replace the search term with something else, so I can put in a figure to replace the currents of a diagram that are found. I can replace them one by one as they're found, or I can replace them all in one go. If I choose the latter, I get a warning telling me how many have been replaced. It's worth noting that the Undo button will undo all of the replacements at once, just in case you're not sure you did the right thing. The Find and Replace dialogue box has a number of other options, which you can display or hide depending on what you want to find, such as searching within the current worksheet, or you can search within the entire workbook. You can search by rows or columns. If you specify by rows, then the search would be from left to right across each worksheet or workbook, starting at the top row. If you specify by column, the search will go down each column starting from the leftmost one. The Lookin option allows you to specify values, only underlying formulas, or any comments attached to a sale. Now, we haven't looked at comments up to this point in the course. However, given that you now know you can search within them, let's have a look at how to add comments, which is very simple but very useful. If you make any selection and click the right mouse button, you can see that you have an Add Comment option here. You can type in any additional information related to the sale selected.You could think of it as a posted note attached to the sale. This is very useful if you want to explain the contents to someone else who might be viewing your worksheet. If you've added a comment, then you can edit it, delete it, and hide or display it. Now, returning to the search functions, The "match case" option means that you can specify that the search should exactly match the upper- and lower-case characters in the search specified. So if you have this checked, then DaveAll (in lowercase) won't be found if you specify capital Dave as the search. If you're searching for text data, then you can use the Match entire cell contents option to find exactly what you type into the Find What box. In other words, if I type Dave with a capital D and the cell has Dave Murphy, it won't make a match. So that covers the Find and Find and Replace commands covered.The next one to review is the "Go To" button. If you know the specific address of Excel you want to navigate to, then Excel has a very useful navigation feature called the Go To command. You can access it under the Finding and Select command, by clicking F5 on the keyboard, or by pressing CTRL Plus. G. Either will bring up the "Go To" dialogue box. It's good to know the shortcut to this command because you'll eventually use it repeatedly during the normal use of Excel. So, in the Reference box, you provide an email address to indicate where you want to go. If I type in a three, that's what, of course, will be positioned when I press the okay button. You can 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 the short review is useful before we move on. A named range is simply a name that you give to a range of cells. So for example, if I select this group of cells, I can click into the Name box, type a name, and press the return key. That group of cells now has a name, no matter where I am in the workbook. If I click on the Name box pull-down menu, I can select a name, and I'll be taken directly to that group of cells. This also works in the GoTo dialog. instead of putting in a cell address. I can specify a named range instead, like so. We also have a special button available on the dialogue box, which is the Go to Special option, also available on the Find and Replace commands here. This is very useful when you're looking for areas of the workbook that have special features such as comments, conditional formatting, data validation, and others. We haven't covered many of these features as yet, but you need to know the mechanics of using the Goto Special options. So illustrators, using the comments that were referred to earlier in the lecture, Assume you're asking a test question to find all cells with comments associated with them. Then all you need to do is go to the "Go to Special" dialogue and check the "Comment" option and press OK. You can see that those are the comments now highlighted. So if you're asked to remove comments from the worksheet, then you just have to click into the appropriate cell, right click, and delete the comment. The rest of the options on the Find and Select menu are actually included in the Go to Special options, and they're here because they're quite a lot.So searching for these five options would be very common once you began to use Excel in earnest. That's all you need to know about navigating in Excel for the 77, 72, and 7.

2. 1.2.2 Named Ranges

that your workbooks quickly become very large and that it can be very difficult to keep track of all the cells. One very useful feature to help you manage large amounts of data is the ability to create a meaningful name for a cell or a range of cells. These names can be used instead of using cell references. For example, it's much easier to understand the formula equal to total revenues minus total expenses than a cryptic formula such as equal to C minus C 18. With meaningful names, it becomes easier for users to understand the purpose of these cells. To define a range with the name, select the range of cells and then use one of the following methods: Fill in the Name box with your name. This is the easiest way. On the Formulas tab, in the Defined Names group, click Define Name. Or right-click on Selected Range, and then click Define Name. Once you define the name for the set or range of cells, you can use that name when creating formulas. You can quickly jump to a name range no matter where you are in the workbook by using Go Toor and selecting the name range from the Name boxlist to the left of the formula bar. When you're creating formulas, you notice that as you begin to type a name, a list appears beneath the text showing you the available choices. This symbol indicates a named range. You can use this information instead of entering the cell references. Range names also reduce errors if you change the sales included in the range. For example, if you change the sale range for the range name expenses from C 10 to C 18 to C 10 to C 19 by changing the sale range once, then every formula in the workbook that uses the expenses range name will be automatically updated. If you didn't use the range name, then you must ensure that the sell range C 10 to C 18 is changed everywhere it's used in the workbook, and you're very likely to miss one or more of them. Range names can be from one to 255 characters in length. They can contain alphabetic or numeric characters, underscores, backslashes, periods, and question marks. You cannot use spaces in named ranges. The first character must be alphanumeric, an underscore, or a backslash. The Name Manager tool available in the Formula Tab allows you to manage the named ranges in your workbooks. You can use the Name Managers to modify and delete range names or change the cell range references. Be cautious when deleting named ranges. When you delete a named range, any formula that refers to this name no longer displays the correct value. Deleting a range name may also cause a domino effect with other formulas that indirectly refer to this formula. To modify a named range, go to the NameManager, and you'll see a list of all the existing named ranges in the workbook. Here you can delete the named range or add a new one. If I add a new named range, I get this dialogue box. Now, I can provide a name for the range of sales selected, which can be done in this area. The scope can be for the entire workbook or just for the worksheet that is currently active. If you specify that the named range scope is for the worksheet only, then the named range will only be visible when you're in that worksheet. Otherwise, it will be available throughout the workbook. So if I create this name range called "Test" and specify the scope to be a worksheet, you can see that if I min any other worksheet, I can't access that name range. If I want to edit a named range, I can also do so from the Name Manager command. This dialogue allows me to change the cell range associated with the named range, or I can change the name "It's."

3. 1.2.3 Insert and Remove hyperlinks

tab and click "Hyperlink" from within the links group. Alternatively, and more easily, just right-click on the side where you want to create a hyperlink and go to hyperlink. There are four main options to choose from. listing file or web page insert into this document, New Document, or email address Let's take 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 sale so that the user knows there is a link available. This is an optional choice. The alternative is just to specify the web address down here, and that is what will be displayed. You can also add in a screen tip, which will appear when the mouse goes over that cell. Notice the change in the shape of the mouse cursor, which changes to a finger shape. Of course, when you click on that cell, you'll be directed to the address specified. This option also allows you to link to an existing file. Now, instead of specifying your web address, you can browse through the file that you want to create a link to. The file will then open when you click on the cell. So if I select this Excel file to link to, you can see that the hyperlink is activated and that the file opens when I select the hyperlink. The next option is to link to an address within the workbook that is open. This can be to a cell in the current worksheet or to any worksheet in the workbook. So once again we can specify what text you want to display, and you can add a screen tip. If the cell you want to link to is in 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 cell reference with the name of the worksheet. For example, a 14 on worksheet sheet three. This will link you directly to the cell address. The Create New Document option allows you to create a new workbook using 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 launch whatever your preferred email programme is on your local computer. The email address that will be used is the one you put into the email box. You may find that you have to configure the link to your email programme the first time you use this type of hyperlink, but once you've done so, the connection will work automatically. So those are the hyperlink options available in Excel. Once you've created a hyperlink, you can edit it by right clicking on the cell and selecting Edit Hyperlink. If you no longer need the hyperlink, you can remove it by selecting the Remove Hyperlink option on the right mouse button. Alternatively, you can access both of these options from the Insert tab links group, which will bring up the Edit Hyperlink dialogue box. You can see that you can also remove the link using this dialog.

4. Exercises Domain 1.2

So, first and foremost, we open the appropriate work. So the marketing plan budget is open, and now we want to use Find Select, so we go to Find Select and look for condition formatting. You can see the sales highlighted. That's the only condition formatting applies to us. Now we can insert comments; they're all on the right button, so we can put in these categories, which can be changed in the comments. Comments are very useful. Now let's add a hyperlink. So I click on the title in one, and when I right click, I have the option to hyperlink, and we can hyperlink to the address, which is Ryanair's website, and if we want to, we can display text. So just show how it works. I'll put it in to help us get you there. In effect, I've removed the title, but you can put in whatever text you want. Now I'm going to use Find and Select again, and I want to find the sales with comments. Go into the new worksheet, go to Find and Select, and select comments. To delete the comments, just right-click and say "Delete Comments." You can see 2000 highlighted. So I deleted the comments. So the comments are now gone. Now in the maintenance worksheet, I see: "Have any sales got data validation applied?" So I can go to the final select, and this time I specify data validation, and there are a number of them here that do have validation on them. So you know those. Now I'm going to go to the marketing plan, and I'm going to go to set B twelve, and I'm going to add a hyperlink that links to three of the worksheets in the same workbook. So right click and then select "Hyperlink." I also specify where on the hyperlink now. So I say, place it in the existing document. So now I say I want to go to a 3, but I want to go to a 3 on a different worksheet. So I have to select the worksheet and press Okay. Next, I'm going to go to the maintenance schedule worksheet and use the "Go to Special" button to find all comments. So we need to go to and find the comments and then go to special press, okay? And if there are comments in there, we can delete them by right clicking on them and pressing Delete comments. Now we're going to get rid of the hyperlink that can sell one of the flights. So click into that cell and right-click if there's a hyperlink there. You always have the option to remove the hyperlink. So it's gone. Next in the slide worksheet, we want to create a hyperlink. So it creates a new document called Helper Slide Tracker. When it says "new document," it means a new workbook. So I'll give it a name, but I want to create a new document. The name of the new document is going to be called Helpless Flight Tracker. When you've done that, you have the option to leave the editing of that document until later on. So it's not going to bring it directly to that document, but the hyperlink has been created. Now we're going to do the replacement. So we want to go to the Replace option, which is contained, and find and select a group. We put in Dublin 2, and we want to replace Dublin 2 with Dublin's main terminal. When we say "replace all," it can tell you how many replacements are actually made. There are three replacements. Next, what we want to do is link the text-and-type budget plans we've already texted in here. We want to put a hyperlink on it, and we want to link that text to the budget plan workbook. This is now linking to a different workbook entirely. We give it a name and then select the workbook it wants to link to, which is the Helpless Airlines marketing plan and press. Okay, now we want to link to an email address. So we hyperlink and specify an email address. And now we can put in the email address you want to link to; in this case, [email protected]. We can put in a subject if you want. We put in "contact helpless" as a subject. And finally, we want to replace only the first occurrence of sales that have the word "advertising" in it. And we want to replace the word advertising with "advert." We go to replace, but then we put in what we want to replace it with, which is advertising, and then just say so to find the first occurrence or find next, and that's it.

So when looking for preparing, you need Microsoft Excel certification practice test questions and answers, study guide and complete training course to study. Open in Avanset VCE Player & study in real exam environment. However, Microsoft Excel exam practice test questions in VCE format are updated and checked by experts so that you can download Microsoft Excel certification exam practice test questions and answers files in VCE format.