Microsoft Microsoft Certifications 77-427 Questions & Answers

• Question 51:

  Rick works as an Office Assistant for Tech Perfect Inc. He is creating a user form through Microsoft Excel 2013. While creating forms for a number of users, he is required to repeat some of the actions multiple times. It is a very time consuming process. To resolve the issue, he has created a macro to record the sequence of actions to perform a certain task. Now, he wants to run the macro to play those exact actions back in the same order. Which of the following steps will Rick take to accomplish the task?

  Each correct answer represents a part of the solution. Choose all that apply.

  A. Click on the 'Macros' icon in the 'Developer' tab under the 'Code' category to run a Macro.

  B. The Macro will be run in any worksheet of the Workbook.

  C. Put the workbook in a trusted location.

  D. The Macro dialogue box appears on the screen which contains a list of Macros in it. Select the Macro to run and click the Run button.

  E. Run the created Macro by using the shortcut key specified while creating the Macro.

  Correct Answer: ABDE

  Take the following steps to run a Macro:

  1.

  Click on the 'Macros' icon in the 'Developer' tab under the 'Code' category to run a Macro.

  2.

  The Macro dialogue box appears on the screen which contains a list of Macros in it.

  Select the

  Macro to run and click the Run button.

  3.

  The Macro will be run in any worksheet of the Workbook.

  4.

  A user can run the created Macro by using the shortcut key that he has specified while creating the Macro. The macro records the user's mouse clicks and keystrokes while he works and lets him play them back later. The macro can be used to record the sequence of commands that the user uses to perform a certain task. When the user runs the macro, it plays those exact commands back in the same order. Answer option C is incorrect. The benefit of connecting to external data from Microsoft Excel is that a user can automatically update Excel workbooks from the real data source whenever the data source is updated with new information. It is possible that the external data connection might be disabled on the computer. For connecting to the data source whenever a workbook is opened, it is required to enable data connections by using the Trust Center bar or by putting the workbook in a trusted location.

  

  

• Question 52:

  You work as an Office Assistant for Media Perfect Inc. You have created a spreadsheet in Excel 2013 and shared it with the other employees of the company. You want to select password protection and select options to prevent other employees from changing, moving, and deleting important data.

  Which of the following options will you choose to accomplish the task?

  A. Mark as Final

  B. Protect Current Sheet

  C. Encrypt with Password

  D. Protect Workbook Structure

  Correct Answer: D

  The various Protect Workbook options are as follows:

  1.

  Mark as Final: This option is used to make the document read-only. When a spreadsheet is marked as final, various options such as typing, editing commands, and proofing marks are disabled or turned off and the spreadsheet becomes

  read-only. This command helps a user to communicate that he is sharing a completed version of a spreadsheet. This command also prevents reviewers or readers from making inadvertent modifications to the spreadsheet.

  2.

  Encrypt with Password: When a user selects the Encrypt with Password option, the Encrypt Document dialog box appears. In the Password box, it is required to specify a password. Microsoft is not able to retrieve lost or forgotten

  passwords, so it is necessary for a user to keep a list of passwords and corresponding file names in a safe place.

  3.

  Protect Current Sheet: This option is used to select password protection and permit or prohibit

  other users to select, format, insert, delete, sort, or edit areas of the spreadsheet. This option

  protects the worksheet and locked cells.

  4.

  Protect Workbook Structure: This option is used to select password protection and select options to prevent users from changing, moving, and deleting important data. This feature enables a user to protect the structure of the worksheet.

  5.

  Restrict Permission by People: This option works on the basis of Window Rights Management to restrict permissions. A user is required to use a Windows Live ID or a Microsoft Windows account to restrict permissions. These permissions

  can be applied via a template that is used by the organization in which the user is working. These permissions can also be added by clicking Restrict Access.

  6.

  Add a Digital Signature: This option is used to add a visible or invisible digital signature.

  It

  authenticates digital information such as documents, e-mail messages, and macros by using computer cryptography. These signatures are created by specifying a signature or by using an image of a signature for establishing authenticity,

  integrity, and non-repudiation.

• Question 53:

  You work as a Sales Manager for Maini Industries. Your company manufactures and sells

  construction equipment. You have to create a report that will show the highest selling items of

  each month. The report will be in the following format:

  

  In a workbook, you create a table named Item_table as shown below:

  

  You fill in the required entries in the workbook.

  

  For the third column of the sheet, you want to use formulas to fill in the values. You want the

  cells in the third column to get automatically filled with the names corresponding to ItemID in the second column. You want the exact values to be filled in the column. You select the C2 cell in the third column.

  Which of the following formulas will you enter in order to accomplish the task?

  A. =VLOOKUP(B2,Item_table,2,FALSE)

  B. =VLOOKUP(B2,Item_table,2,TRUE)

  C. =VLOOKUP(2,Item_table,B2,FALSE)

  D. =VLOOKUP(2,Item_table,B2,TRUE)

  Correct Answer: A

  In order to accomplish the task, you will use the following formula:

  =VLOOKUP(B2,Item_table,2,FALSE)

  Answer option B is incorrect. According to the question, you want the exact values in the column.

  The TRUE value in the range_lookup argument provides the approximate match. Answer options C and D are incorrect. The VLOOKUP functions used in the formulas have wrong syntaxes.

• Question 54:

  You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to filter data in a PivotTable report without opening drop-down lists to find the items that you want to filter. For this purpose, you are using a slicer. After filtering the data, you want to disconnect the slicer. You have clicked anywhere in the PivotTable report. Which of the following steps will you take next to accomplish the task?

  Each correct answer represents a part of the solution. Choose all that apply.

  A. Clear the check box of any PivotTable fields for which you want to disconnect a slicer.

  B. Right-click the slicer, and then click Remove .

  C. Click the Insert Slicer arrow, and then click Slicer Connections on the Options tab in the Sort and Filter group.

  D. Click the slicer, and then press Delete.

  Correct Answer: AC

• Question 55:

  You work as an Office Assistant for Tech Perfect Inc. You are working in the spreadsheet of the company's high selling products. You are trying to intersect two ranges that do not intersect each other. Which of the following errors will be occurred in this situation?

  A. #VALUE!

  B. #NAME?

  C. #NULL!

  D. #DIV/0!

  Correct Answer: C

  The #NULL! error is produced by Excel when a user attempts to intersect two ranges that do not intersect each other. This error can be resolved by reviewing the formula and then either by changing the variables to ensure that the user is getting a valid intersection or by using the Excel Iferror function to identify a null range and take the required action. Answer option D is incorrect. Excel #DIV/0! is produced when a formula attempts to divide by zero and a division by zero produces infinity that cannot be represented by a spreadsheet value. This error can be corrected or overcome by using the Excel IF function to identify a possible division by zero and produce an alternative result. Answer option A is incorrect. The #VALUE! Excel formula error occurs when any of the variables in a formula is of the wrong type. To correct this error, it is required to check each individual part of the formula to make sure that the correct argument types are used. If the function contains nested functions, the best way is to copy each of the arguments into a separate cell to check what they evaluate to. If the formula contains a number of terms, break down each term further to find out its components, until the source of the error is found. Answer option B is incorrect. The #NAME? error occurs when Excel encounters text in a formula and tries to interpret that text as a reference, a named range, or a function name but is not able to recognize the text as any of these. The best way to approach the #NAME? Excel function error is to check the correct spelling of the function names, references, and named ranges and to check that the variables used as text values are entered in double quotes. If the formula contains nested functions, it is required to check the results of these individually, until the source of the error is identified.

• Question 56:

  You work as a Sales Manager for Rainbow Inc. Your responsibility includes preparing sales report of the Sales department. You use Microsoft Excel 2013 to prepare sales reports. You have created a quarterly sales report of the department. After entering required data and inserting charts, you want to give a professional look to the document. You want to produce the document with soothing graphic effects, soft fonts, and light colors. Which of the following steps will you take to accomplish the task with least administrative burden?

  A. Use the SmartArt option.

  B. Create a new workbook through a template. Copy all data and charts of the report to the new workbook.

  C. Choose one of the pre-built themes.

  D. Select all the text of the document. Change font style to Verdana and font color to light gray. Use vibrant colors for charts.

  Correct Answer: C

  In order to accomplish the task, you will have to choose one of the pre-built themes.

  Document

  themes work on the overall design of the entire document. It enables users to specify colors,

  fonts, and variety of graphic effects in a document. Themes change the look and feel of the document.

  

  Microsoft Excel 2013 comes with a large collection of pre-built themes. Users can create their own themes for a document. Themes can be specified through Page Layout > Themes.

  Answer option A is incorrect. The SmartArt option does not produce soothing graphic effects, soft fonts, and light colors.

  Answer options B and D are incorrect. These options involve manual processing.

  Therefore, any formatting done manually will increase the administrative burden.

• Question 57:

  You work as an Office Assistant for Blue well Inc. The company has a Windows-based network. You want to create a VBA procedure for the open event of a workbook. You are required to set the security level temporarily for enabling all macros. Mark the option that you will choose first to accomplish the task.

  

  Hot Area:

  

  Correct Answer:

  

• Question 58:

  You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to insert a signature line that specifies the individual who must sign. Mark the option that you will choose to accomplish the task.

  

  Hot Area:

  

  Correct Answer:

  

  The following figure shows the option used to insert a signature line that specifies the individual who must sign.

• Question 59:

  You work as a technician in Tech Perfect Inc. The Network Administrator of the company installs the Microsoft Excel 2013 application on the network. You open the application and see various changes in Excel 2013. You have to open an existing worksheet through the application. You are unable to find the Office button.

  

  Mark the option that you will click to accomplish the task.

  Hot Area:

  

  Correct Answer:

  

  You will have to click File button to accomplish the task. Clicking the File button will open the full- screen Backstage View of the sheet.

• Question 60:

  Rick works as an Office Assistant for Tech Perfect Inc. He is creating a report through Microsoft Excel 2013. He wants to fill the active cell with the content of the adjacent cell. Mark the option that he will choose to accomplish the task.

  

  Hot Area:

  

  Correct Answer: