Microsoft Microsoft Certifications 77-427 Questions & Answers

• Question 11:

  You work as an Office Assistant for Tech Perfect Inc. Your responsibility includes preparing sales reports of the company. You have created a sales report in a workbook of Excel 2013.

  Your workbook includes several worksheets as shown in the image given below:

  You want the sheet tabs of the sheets containing charts to appear in red color. Which of the following steps will you take to accomplish the task?

  A. Select the sheets containing charts. Select the Colors option in the Themes group on the Page Layout page.

  B. Select the sheets containing charts. Right-click on the sheet tabs. Choose the Tab Color option from the shortcut menu.

  C. Select the sheets containing charts. Right-click on the sheet tabs. Choose the Sheet Color option from the shortcut menu.

  D. Select the sheets containing charts. Select the Effects option in the Themes group on the Page Layout page.

  E. \Documents and Settings\Administrator\Desktop\1.JPG

  Correct Answer: B

  According to the question, you want the sheet tabs of the sheets containing chart to appear in red color. In order to accomplish the task, you will have to take the following steps:

• Question 12:

  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 at the step of adding the following lines of code to the procedure:

  "Private Sub Workbook_Open() MsgBox Time Worksheets("Sheet2").Range("A2").Value = Time End Sub"

  Which of the following are the next steps that you will take to accomplish the task?

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

  A. Under Macro Settings in the Macro Settings category, click Enable all macros, and then click OK.

  B. Switch to Excel and save the workbook with the .xslm extension as a macro-enabled workbook and close it.

  C. Reopen the workbook.

  D. Click OK in the message box.

  Correct Answer: BCD

• Question 13:

  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 summarize a large amount of data to analyze numerical data and to answer unexpected questions about your data in the Business Intelligence (BI) report that you are working with. For this purpose, you are required to create a 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 OK.

  B. Choose Select a table or range under Choose the data that you want to analyze.

  C. Select the Macro-enabled checkbox in the Insert Security dialog box, and then click OK.

  D. Click PivotTable or click the arrow below PivotTable, and then click PivotTable in the Tables group on the Insert tab.

  E. Click a cell in the cell range that contains data for using worksheet data as the data source.

  Correct Answer: ABDE

• Question 14:

  You work as an Office Assistant for Tech Perfect Inc. You are working in a spreadsheet.

  You are facing a problem that when you type in a function and press Enter, the cell shows the function as you typed it, instead of returning the function's value as shown below:

  Which of the following is the reason that is causing the above problem?

  A. You are inserting a new column, next to a column that is already formatted as text.

  B. Excel is trying to reference an invalid cell.

  C. You are inserting a new column, next to a column containing Dates or Times.

  D. The lookup_value or the array you are searching resides in a cell containing unseen spaces at the start or end of that cell.

  Correct Answer: A

  The Excel Won't Calculate My Function error occurs when a user types in a function and presses Enter, the cell shows the function as the user typed it, instead of returning the function's value. The reason that causes this problem is that the cells containing the formula are formatted as 'text' instead of the 'General' type. This happens when the user inserts a new column, next to a column that is already formatted as text due to which the new column inherits the formatting of the adjacent column. Answer option D is incorrect. The Failure to Look Up Values in Excel error occurs when a user gets an unexpected error while trying to look up or match a lookup_value within an array and Excel is not able to recognize the matching value. If the lookup_value or the array the user is searching resides in a cell, the user can have unseen spaces at the start or end of that cell. This will create the situation where the contents of the two cells that the user is comparing look the same but extra spaces in one of the cells cause the cells to have slightly different content. The other reason is that the contents of the cells that are being compared may have different data types.

  Answer option B is incorrect. The Lookup Function Won't Copy Down to Other Rows error occurs when a user uses a function in one cell and it works perfectly but when he attempts to copy the function down to other rows, he gets the #REF error. The #REF! error arises when Excel tries to reference an invalid cell. This error occurs if the user has referenced an entire worksheet by clicking on the grey square at the top left of the worksheet. For Excel, this reference range is 1 to 1048576. Since the references are Relative References, Excel automatically increases the row references when this cell is copied down to other rows in the spreadsheet. Answer option C is incorrect. The Cell Shows a Date or Time Instead of a Number error occurs because the cell that contains the formula is formatted as a 'date' or 'time' instead of a 'General' type or a number. This situation arises because a user has inserted a new column, next to a column containing Dates or Times, the new column has 'inherited' the formatting of the adjacent column.

• Question 15:

  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.

  Correct Answer: C

  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.

  

  Answer option A is incorrect. No such option is provided in Excel 2013.

  

  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.

• Question 16:

  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.

  Correct Answer: B

  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.

• Question 17:

  You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. Some employees have changed some data in the worksheet of the company. You want to identify changes that were made to the data in the workbook and then take a decision whether to accept or reject those changes. For this purpose, it is required to access and use the stored change history.

  Which of the following will you use to accomplish the task?

  Each correct answer represents a complete solution. Choose all that apply.

  A. History tracking

  B. Onscreen highlighting

  C. Slicer-enabled highlighting

  D. Reviewing of changes

  Correct Answer: ABD

  The following ways are provided by Excel to access and use the stored change history:

  1.

  Onscreen highlighting: It is used when a workbook does not contain many changes and a user wants to see all changes at a glance.

  2.

  History tracking: It is used when a workbook has many changes and a user wants to investigate what occurred in a series of changes.

  3.

  Reviewing of changes: It is used when a user is evaluating comments from other users. Answer option C is incorrect. This is an invalid answer option.

• Question 18:

  You work as an Office Assistant for Media Perfect Inc. You are creating a report in Microsoft Excel 2013. The report worksheet will include the sales made by all the sales managers in the month of January.

  

  In the worksheet, you are required to place a pictorial representation displaying the percentage of total sales made by each sales manager in January. Which of the following charts will you use to accomplish the task?

  A. Bar

  B. Line

  C. Column

  D. Pie

  Correct Answer: D

  In order to accomplish the task, you will have to insert a pie chart in the worksheet. Excel provides a pie chart facility to display data that is arranged in one column or row on a worksheet. Pie charts display the size of items in one data series, proportional to the sum of the items.

• Question 19:

  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 and 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.

  Correct Answer: CD

• Question 20:

  You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You want to quickly 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 required to use a slicer. Which of the following are the ways that you can use to accomplish the task? Each correct answer represents a complete solution. Choose all that apply.

  A. Create a copy of a slicer connected with the PivotTable.

  B. Create a slicer connected with the PivotTable.

  C. Create a slicer by disabling Macros associated with the PivotTable.

  D. Use an existing slicer connected with another PivotTable.

  Correct Answer: ABD

  The various ways to use or create slicers to filter PivotTable data in an existing PivotTable are as follows:

  1.

  Create a slicer connected with the PivotTable.

  2.

  Create a copy of a slicer connected with the PivotTable.

  3.

  Use an existing slicer connected with another PivotTable.

  Answer option C is incorrect. This is an invalid answer option.