77-427 Exam
What Does 77-427 test preparation Mean?
Proper study guides for Down to date Microsoft Microsoft Excel 2013 Expert Part 1 certified begins with Microsoft 77-427 preparation products which designed to deliver the Exact 77-427 questions by making you pass the 77-427 test at your first time. Try the free 77-427 demo right now.
Q1. Rick works as an Office Assistant for Tech Perfect Inc. The company has a Windows-based network.
Rick has to perform the following tasks:
Summarize large amounts of data. Analyze numeric data.
Answer unexpected questions about data.
Which of the following actions will Rick take to accomplish the task?
A. He will create the accounting template.
B. He will use stand-alone slicers.
C. He will create a PivotTable report.
D. He will configure form controls.
Answer: C
Explanation:
A PivotTable report is used to summarize large amounts of data, to analyze numerical data, and to answer unexpected questions about data. Answer option B is incorrect. Stand-alone slicers are referenced from Online Analytical Processing (OLAP) Cube functions. These stand-alone slicers can be connected with any PivotTable in future. They can be added by using the slicer button on the Ribbon. They need to be manually linked into grids as required. Answer option D 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 A 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.
Q2. 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. 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. Which of the following steps will you take to resolve the problem? Each correct answer represents a part of the solution. Choose all that apply.
A. Remove the unseen spaces at the start or end of the first Macro cell.
B. Delete the column having the TRIM function.
C. Copy the contents of the new column and paste them on the top of the original column by using paste > special > values sequence.
D. Create a new column subsequent to the one with the additional spaces in it.
E. Use the TRIM function in the new column.
Answer: B,C,D,E
Explanation:
In this situation, you will take the following steps:
1. Create a new column subsequent to the one with the additional spaces in it.
2. Use the TRIM function in the new column to get rid of extra spaces.
3. Copy the contents of the new column and paste them on the top of the original column by using paste > special > values sequence.
4. Delete the column having the TRIM function. Answer option A is incorrect. This is an invalid answer option.
Q3. You work as an Office Assistant for Tech Tree Inc. You have created a report in a workbook in Microsoft Excel 2013. You have a number of PivotTables in a single report. You want to apply the same filter to all of those PivotTables. For this purpose, you are required to create a slicer in one PivotTable and share it with other PivotTables. 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. In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.
B. Click Insert Slicer in the Sort & Filter group on the Options tab.
C. Hold down CTRL, and then click the items on which you want to filter.
D. Select the Macro enabled checkbox of the PivotTable fields for which you want to create a slicer in the Insert Security dialog box and then click OK.
E. Click anywhere in the PivotTable report for which you want to create a slicer.
Answer: A,B,C,E
Q4. OTSPOT
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.
Answer:
Q5. You work as an Office Assistant for Dreams Unlimited Inc. You use Microsoft Excel 2013 for creating various types of reports. You have created a report in the format given below:
In the A7 cell, you are required to put a formula so that it can fulfill the description provided in the B7 cell.
Which of the following formulas will provide the required result?
A. COUNTIF(B2:C5,"=Yes")
B. COUNTIFS(B2:C5,"=Yes")
C. COUNTIF(B2:B5,"=Yes",C2:C5,"=Yes")
D. COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
Answer: D
Explanation:
In order to get the required result, you will have to insert the following formula in the B7 cell:
COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
Only Sarah and David have exceeded their January and February quotas, therefore the
formula will provide 2 as the result.
Answer option C is incorrect. The COUNTIF function of Excel does not support multiple criteria.
Answer options A and B are incorrect. This formula will count all the cells that have the value
"Yes" in the range B2:C5. As multiple criteria are not applied in the formula, it will provide 6 as the result.
In the A7 cell, you are required to put a formula so that it can fulfill the description provided in the B7 cell.
Which of the following formulas will provide the required result?
A. COUNTIF(B2:C5,"=Yes")
B. COUNTIFS(B2:C5,"=Yes")
C. COUNTIF(B2:B5,"=Yes",C2:C5,"=Yes")
D. COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
Answer: D
In order to get the required result, you will have to insert the following formula in the B7 cell: COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") Only Sarah and David have exceeded their January and February quotas, therefore the formula will provide 2 as the result. Answer option C is incorrect. The COUNTIF function of Excel does not support multiple criteria. Answer options A and B are incorrect. This formula will count all the cells that have the value "Yes" in the range B2:C5. As multiple criteria are not applied in the formula, it will provide 6 as the result.
Q6. RAG DROP
You work as a Help Desk Technician for Net Perfect Inc. You use Excel 2013 to create different
reports for your company. Drag and drop the appropriate methods for efficient data entry in front
of their descriptions.
Answer:
Q7. You work as an Office Assistant for Tech Perfect Inc. You are working in a spreadsheet as shown below:
You want to use Excel Transpose Function for copying the contents of cells B1:B3 into cells A5:C5.
You simply type '=TRANSPOSE( B1:B3 )' into the cells A5:C5. It displays the Excel #VALUE! error message because the cells are working independently and the function will not work for each individual cell. Therefore, it is required to make the cells A5:C5 work together as an ARRAY. For this purpose, you are required to use an array formula. Which of the following steps will you take to input Excel array formulas in the spreadsheet?
Each correct answer represents a part of the solution. Choose all that apply.
A. Type the array formula into the first cell.
B. Highlight the range of cells that you want to use as an input in his array formula.
C. Press CTRL-SHIFT-Tab to remove the error message.
D. Press CTRL-SHIFT-Enter.
Answer: A,B,D
Explanation:
The following steps are taken to input Excel array formulas:
1. Highlight the range of cells that a user wants to use as an input in his array formula.
2. Type the array formula into the first cell or make this cell editable by pressing F2 or clicking in the formula bar if a user has already specified a value into the first cell.
3. Press CTRL-SHIFT-Enter.
Answer option C is incorrect. This is an invalid answer option.
Q8. OTSPOT
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.
Answer:
Q9. OTSPOT
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.
Answer:
Q10. 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.
Answer: C
Explanation:
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.
C:\Documents and Settings\Administrator\Desktop\1.JPG
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.