77-427 Exam
Top 10 testing engine 77-427 for client (11 to 20)
Exam Code: 77-427 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Microsoft Excel 2013 Expert Part 1
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 77-427 Exam.
Q11. 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!
Answer: C
Explanation:
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.
Q12. You work as an Office Assistant for Peach Tree Inc. Your responsibility includes creating sales incentive report of all sales managers for every quarter. You are using Microsoft Excel to create a worksheet for preparing the report. You have inserted the sales figures of all sales managers as shown in the image given below:
You have to calculate the first quarter incentives for all sales managers. The incentive percentage (provided in cell B3) is fixed for all sales managers. The incentive will be calculated on their total first quarter sales. You have to write a formula in the cell F8. Then you will drag the cell border to the cell F12 to copy the formula to all the cells from F8 to F12. In the first step, you select the F8 cell. Which of the following formulas will you insert to accomplish the task?
A. =&B&3/100 * E8
B. =B3/100 * E8
C. =B3/100 * &E&8
D. =$B$3/100 * E8
E. =B3/100 * $E$8
Answer: D
Explanation:
In order to accomplish the task, you will have to insert the following formula: =$B$3/100 * E8 According to the question, the formula will be inserted in cell F8 and then the cell's border will be dragged to the F12 cell. Furthermore, the incentive percentage is fixed for all sales managers and the value is provided in the cell B3. You will have to insert a formula that refers to the B3 cell as an absolute reference. For this you will have to type currency symbol ($) before the row name and column number. In order to accomplish the task, type the following formula in the cell F8: =$B$3/100 * E8 When absolute reference is used for referencing a cell in a formula, dragging cell's border to another cell does not change the cell's reference. Answer options B and E are incorrect. This formula references the B3 cell as a relative reference. After inserting the formula, when the cell's border is dragged, it will change the cell reference relatively. Answer options A and C are incorrect. Ampersand symbol (&) is not used for referencing cells in Excel.
Q13. You work as a Sales Manager for Tech Perfect Inc. You are creating a report for your sales team
Using Microsoft Excel. You want the report to appear in the following format:
You want the Remark column to be filled through a conditional formula. The criteria to give the
remark is as follows:
If the sales of the First Quarter are greater than or equal to 1200, display "Well Done" If the
sales of the First Quarter is less than 1200, display "Improve in Next Quarter"
You have done most of the entries in a workbook. You select the F2 cell as shown in the image given below:
Which of the following conditional formulas will you insert to accomplish the task?
A. =IF(E2>=1200,"Improve in Next Quarter","Well Done")
B. =IF(E2<=1200,"Well Done","Improve in Next Quarter")
C. =IF(E2>=1200,"Well Done","Improve in Next Quarter")
D. =IF(E2>1200,"Improve in Next Quarter","Well Done")
Answer: C
Explanation:
In order to accomplish the task, you will have to insert the following formula in the F2 cell: =IF(E2>=1200,"Well Done","Improve in Next Quarter") Answer option A is incorrect. This will display the wrong messages for the given conditions. The first expression after the logical condition is returned by the IF function when the condition is TRUE. Answer option B is incorrect. This formula will not accomplish the task as the logical condition is not correct. The specified condition in this formula is testing for values less than or equal to 1200. Whereas, the question's requirement is to evaluate values greater than or equal to 1200. Answer option D is incorrect. This formula will not accomplish the task because of the two
reasons.
First, the equal sign is missing in the condition. Second, the expressions are not in the correct order.
Q14. You work as an Office Manager for Blue Well Inc. The company has a Windows-based network. You want to change values in cells to observe the way in which those changes are affecting the outcome of formulas in the worksheet. Which of the following will you use to accomplish the task?
A. Form control
B. Trust Center
C. Accounting template
D. What-if analysis
Answer: D
Explanation:
What-if analysis is defined as the process of changing values in cells to observe the way in which those changes are affecting the outcome of formulas in the worksheet. There are three types of what-if analysis tools that are named as scenarios, data tables, and Goal Seek. A user can also install the Solver add-in to accommodate more variables than Goal Seek. Answer option A is incorrect. A form control is an original control that is compatible with old versions of Excel, beginning with Excel version 5. 0. It is designed for use on XLM macro sheets. It can be used when a user wants to simply interact with cell data without using VBA code and when he wants to add controls to chart sheets. By using form controls, the user can run macros. He can attach an existing macro to a control, or write or record a new macro. These controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages. Answer option C is incorrect. The accounting template is used for numbering months of a financial year to period numbering. It is used to compare month to month, actual v budget, quarter to quarter, year to year variances. It is the initial point for other reports that need the use of months. Answer option B is incorrect. Trust Center is where a user can find security and privacy settings for Microsoft Office 2013 programs.
Q15. You work as a Help Desk Technician for Dreams Unlimited Inc. Martha, a Sales Manager, is creating a sales report in Microsoft Excel. The report contains many worksheets. Martha has used many formulas in her report. She wants to monitor a cell which contains a formula. The formula refers to many other cells that are located in different worksheets in the report. She wants to know the effects on the cell when the values in different sheets change. She requests that you help her out to accomplish the task. Which of the following steps would you suggest she take in order to accomplish the task?
A. Click the New Window option in the Window group on the View tab. Click the Arrange All option in the Window group on the View tab. Select the Vertical option.
B. Select the cell which is to be monitored.
On the Formulas tab in the Formula Auditing group, click Watch Window. Click Add Watch.
C. Click the New Window option in the Window group on the View tab. Click the Arrange All option in the Window group on the View tab. Select the Cascade option.
D. Select the cell which is to be monitored.
On the Formulas tab in the Formula Auditing group, click Evaluate Formula.
Answer: B
Explanation:
In order to accomplish the task, she should take the following steps: Select the cell which is to be monitored. On the Formulas tab in the Formula Auditing group, click Watch Window. Click Add Watch. Answer option D is incorrect. Evaluate Formula is the formula examination tool provided by Microsoft Excel. This tool is useful for examining formulas that do not produce an error but are not generating the expected result. Answer options A and C are incorrect. Clicking the New Window option in the Window group on the View tab opens an instance of the workbook within the Excel window. This is useful when you work on different worksheets, and at the same time you want to navigate among them.
Q16. Rick works as an Office Assistant for Tech Perfect Inc. The company has a Windows-based network.
Rick is creating a project through Microsoft Excel 2013. The project on which he is working has 98 project tasks and 57 team members. Rick wants to check the progress of his project quickly and easily. Which of the following will Rick use to accomplish the task?
A. Gantt Chart Template
B. Form control
C. Trust Center
D. Accounting template
Answer: A
Explanation:
The Gantt Chart Template Deluxe Edition for Excel is used to generate quick and easy Gantt charts that define the progress of a user's projects containing up to 100 project tasks and team members. Answer option B is incorrect. A form control is an original control that is compatible with old versions of Excel, beginning with Excel version 5. 0. It is designed for use on XLM macro sheets. It can be used when a user wants to simply interact with cell data without using VBA code and when he wants to add controls to chart sheets. By using form controls, the user can run macros. He can attach an existing macro to a control, or write or record a new macro. These controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages. Answer option D is incorrect. The accounting template is used for numbering months of a financial year to period numbering. It is used to compare month to month, actual v budget, quarter to quarter, year to year variances. It is the initial point for other reports that need the use of months. Answer option C is incorrect. Trust Center is where a user can find security and privacy settings for Microsoft Office 2013 programs.
Q17. You work as an Office Assistant for Tech Tree Inc. You have created a report in a workbook in Microsoft Excel 2013. You have included various subtotals in a worksheet in the report to verify data accuracy. You want to remove all subtotals from the sheet before you send it to your manager.
Which of the following steps will you take to accomplish the task with the least administrative effort?
A. Select all the subtotals. From the shortcut menu, click the Remove All Subtotals option.
B. Hide the rows containing subtotals.
C. Open the Subtotal dialog box by clicking the Subtotal option in the Outline group on the Data tab. Click the Remove All button.
D. Delete all the rows containing subtotals.
Answer: C
Explanation:
According to the question, you have to remove all subtotals from the worksheet with the least administrative effort. You can remove all subtotals from a worksheet at a time. For this, take the following steps: Open the Subtotal dialog box by clicking the Subtotal option in the Outline group on the Data tab.
Click the Remove All option.
C:\Documents and Settings\Administrator\Desktop\1.JPG
Answer option A is incorrect. No such option is provided in Excel 2013.
C:\Documents and Settings\Administrator\Desktop\1.JPG
Answer option B is incorrect. Although this will hide the rows containing the subtotals, the
contents will remain there and can be made visible by using the Unhide row option.
Moreover, the question specifically specifies to remove all subtotals from the sheet.
Answer option D is incorrect. Although this step will accomplish the task, it will involve a lot of administrative burden of selecting each row and deleting it manually.
Q18. You work as an Office Assistant for Tech Perfect Inc. You are working in the spreadsheet of the company's high selling products. You want to find out the matching values in the columns. While doing so, you get an unexpected error that when you attempt to look up or match a lookup_value within an array; Excel is not able to recognize the matching value. You want to store sets of values as text and convert sets of data to text by using Excel's Text To Columns tool. Which of the following steps will you take to accomplish the task?
Each correct answer represents a part of the solution. Choose all that apply.
A. Select the Text to Columns option from the Data tab at the top of the Excel workbook.
B. Select Text and click the Finish button in Column Data Formats.
C. Select the Delimited option and click Next
D. Make sure that all delimiter options are unselected, and then again click Next.
E. Remove the unseen spaces at the start or end of the first Macro cell.
F. Select the cells to convert their data to text.
Answer: A,B,C,D,F
Explanation:
In this situation, you will take the following steps:
1. Select the cells to convert their data to text.
2. Select the Text to Columns option from the Data tab at the top of your Excel workbook.
3. Select the Delimited option and click Next.
4. Make sure that all delimiter options are unselected, and then again click Next.
5. Select Text and click the Finish button in Column Data Formats.
Q19. You work as an Office Assistant for Tech Tree Inc. You have created a report in a workbook in
Microsoft Excel 2013. You want to export XMLdata to your Excel 2013 worksheet. On exporting the XML data, the following error message appears:
"XML MAPS IN MY WORKBOOK ARE NOT EXPORTABLE"
Which of the following are the reasons that are causing the above error? Each correct answer
represents a complete solution. Choose all that apply.
A. The maxoccurs attribute is not equal to 1.
B. The relationship of the mapped element with other elements is not preserved.
C. The element is a recursive structure.
D. The XML map associated with this XML table contains one or more required elements that are not mapped to the XML table.
Answer: A,B
Explanation:
The various issues with exporting XML data are as follows:
1.
MAP CAN BE EXPORTED BUT SOME REQUIRED ELEMENTS ARE NOT MAPPED:
This error occurs under the following situations:
The XML map associated with this XML table contains one or more required elements that
are not mapped to the XML table. The element is a recursive structure.
The XML table contains a variety of mixed content.
2.
EXCEL CANNOT SAVE THE WORKBOOK BECAUSE IT DOES NOT CONTAIN ANY
XML MAPPINGS: Excel cannot save the user's workbook in the XML Data file unless one
or more mapped ranges are created.
3.
XML MAPS IN MY WORKBOOK ARE NOT EXPORTABLE: An XML mapping cannot be
exported if the relationship of the mapped element with other elements is not preserved
due to the following reasons:
The mapped element's schema definition is contained within a sequence with the following
attributes:
The maxoccurs attribute is not equal to 1.
The sequence has more than one direct child element defined or has an extra compositor
as a direct child.
Nonrepeating sibling elements with the same repeating parent element are mapped to
dissimilar
XML tables.
Various repeating elements are mapped to the same XML table and the recurrence is not
defined by an ancestor element.
Child elements from various parents are mapped to the same XML table.
XML schema constructs a list of lists, denormalized data, or a choice construct.
Q20. You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to make a slicer available for use in another PivotTable. Which of the following steps will you take to accomplish the task?
Each correct answer represents a part of the solution. Choose all that apply.
A. Click the Insert Slicer arrow, and then click Slicer Connections on the Options tab in the Sort & Filter group.
B. In the Slicer Connections dialog box, clear the check box of any PivotTable fields for which you want to disconnect a slicer.
C. In the PivotTable Connections dialog box, select the check box of the PivotTables in which you want the slicer to be available.
D. Click the slicer that you want to share in another PivotTable.
Answer: C,D