Microsoft 77-427 Online Practice Questions and Exam Preparation

Microsoft 77-427 Online Questions & Answers

• Question 41:

  You work as an Office Manager for Blue Well Inc. The company has a Windows-based network. You have a formula that uses one or two variables or multiple formulas that all use one common variable. You want to examine a range of possibilities at a glance. Which of the following will you use to accomplish the task?

  A. Goal Seek
  B. Solver add-in
  C. Data table
  D. Scenario
  C. Data table

  ---

  A data table is used to see all the outcomes in one place. It is defined as a type of what-if analysis tools. It is used if a user has a formula that uses one or two variables or multiple formulas that all use one common variable. It is used to examine a range of possibilities at a glance and since the user focuses on only one or two variables, results are easy to read and share in tabular form. If automatic recalculation is enabled, it is possible to recalculate the data in data tables immediately and as a result, the user always gets fresh data. Answer option D is incorrect. A scenario is defined as a type of what-if analysis tools. It is a set of values saved by Excel and can be substituted automatically in cells on a worksheet. A user can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. Scenario reports are not automatically recalculated. If the user changes the values of a scenario, those changes will not be displayed in an existing summary report and he must create a new summary report to show the reflection of the changed report. Answer option A is incorrect. Goal Seek is defined as a type of what-if analysis tools. It is used if a user knows the result that he wants from a formula, but he is not sure what input value the formula needs to get that result. It works with only one variable input value. Answer option B is incorrect. The Solver add-in is used if a user knows the result that he wants from a formula, but he is not sure what input value the formula needs to get that result. It is used for more than one input value. It works with a group of cells related to the formula in the objective cell.

• Question 42:

  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.
  C. Choose one of the pre-built themes.

  ---

  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 43:

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

• Question 44:

  You work as an Office Assistant for Tech Perfect Inc. You are working in a spreadsheet. You observe that while trying to look up or match a lookup_value within an array, Excel is not able to recognize the matching value. Which of the following are the reasons that are causing the above problem?

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

  A. The lookup_value or the array you are searching resides in a cell containing unseen spaces at the start or end of that cell.
  B. The contents of the cells that are being compared may have different data types.
  C. Excel is trying to reference an invalid cell.
  D. You are inserting a new column, next to a column that is already formatted as text.
  A. The lookup_value or the array you are searching resides in a cell containing unseen spaces at the start or end of that cell.
  B. The contents of the cells that are being compared may have different data types.

  ---

  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 C 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 D is incorrect. 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.

• Question 45:

  You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You are creating a chart in Excel 2013. You want to show the slope of data points in the chart to know the trend of the Business Intelligence data range. Which of the following will you use to accomplish the task?

  A. Trendline
  B. Sparkline
  C. Backstage view
  D. Line charts
  A. Trendline

  ---

  A trendline is defined in Excel 2013. It is a data visualization tool to display the trend of a particular data range. It is used to show the slope of data points in a chart. There are various types of trendlines, such as linear trendlines, exponential trendlines, and linear forecast trendlines. Answer option B is incorrect. A sparkline is defined as a tiny chart in the background of a cell. Unlike charts on an Excel worksheet, sparklines are not objects. It is used because data presented in a row or column is useful, but patterns can be hard to spot at a glance. It is used to display a trend based on adjacent data in a clear and compact graphical representation by taking up a small amount of space. The benefit of using the sparklines is that these can be printed when a user prints a worksheet that contains them. This feature is not available in charts. Answer option C is incorrect. The Microsoft Office Backstage view is used to replace the traditional file menu with a new approach that uses In and Out features for efficiency. The improved Ribbon enables users to access their preferred commands rapidly and create custom tabs to personalize the way they work.Answer option D is incorrect. Excel provides a column chart facility to display data that is arranged in columns or rows on a worksheet. Line charts are used to display continuous data over time, set against a common scale. In a line chart, category data is distributed along the horizontal axis, and all value data is distributed along the vertical axis. Line charts are ideal for showing trends in data at equal intervals.

  

  A line chart should be used where category labels are text, and are representing evenly spaced values such as months, quarters, or years.

• Question 46:

  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
  C. =IF(E2>=1200,"Well Done","Improve in Next Quarter")
  D. =IF(E2>1200,"Improve in Next Quarter","Well Done")
  C. =IF(E2>=1200,"Well Done","Improve in Next Quarter")

  ---

  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.

• Question 47:

  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!
  C. #NULL!

  ---

  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 48:

  Jack wants the rows containing the name Tom and product Jam. There are thousands of entries in the log. Plowing through all the records will be time taking. Which option can be used to view only the relevant records?

  A. Autofilter
  B. Find
  C. Sort
  D. All filter
  A. Autofilter

  ---

  Autofilter is used to filter the record as per the condition by hiding the rest. Defining different filters, you can compare data in multiple ways, so you can glean vital information without wasting vital time.

  Answer options B and D are incorrect. There are no such options defined in Excel to filter the records.

  Answer options C is incorrect. The Sort function is used to arrange the records in ascending or descending order.

• Question 49:

  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.
  C. He will create a PivotTable report.

  ---

  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.

• Question 50:

  Which of the following steps will you take to merge copies of a shared workbook? Each correct answer represents a part of the solution. Choose all that apply.

  A. In the Select Files to Merge into Current Workbook dialog box, click a copy of the workbook containing changes to be merged, and then click OK.
  B. Click Compare and Merge Workbooks on Quick Access Toolbar.
  C. Open the copy of the shared workbook to merge the changes.
  D. Save the workbook if prompted.
  E. Add Compare and Merge Workbooks.
  F. Click Compare and Merge Workbooks on Macro Enabled Access Toolbar.
  A. In the Select Files to Merge into Current Workbook dialog box, click a copy of the workbook containing changes to be merged, and then click OK.
  B. Click Compare and Merge Workbooks on Quick Access Toolbar.
  C. Open the copy of the shared workbook to merge the changes.
  D. Save the workbook if prompted.
  E. Add Compare and Merge Workbooks.

  ---

  Take the following steps to merge copies of a shared workbook:

  1.

  Add Compare and Merge Workbooks.

  2.

  Open the copy of the shared workbook to merge the changes.

  3.

  Click Compare and Merge Workbooks on Quick Access Toolbar.

  4.

  Save the workbook if prompted.

  5.

  In the Select Files to Merge into Current Workbook dialog box, click a copy of the workbook containing changes to be merged, and then click OK.