Braindump2go Microsoft 77-427 Practice Tests Latest Questions Free Share
Braindump2go New Released Microsoft 77-427 Practice Tests Sample Questions Free Download! 100% Same Questions with Actual 77-427 Exam! Guaranteed 100% Pass!
Vendor: Microsoft
Exam Code: 77-427
Exam Name: Microsoft Excel 2013 Expert Part 1
QUESTION 1
You work as an Office Manager for Blue Well Inc. The company has a Windows-based network. You have two budget situations in which one is the worst case and the other is the best case.
You want to create both situations on the same worksheet and then switch between them with the change in requirements. Which of the following will you use to accomplish the task?
A. Goal Seek
B. Data table
C. Scenario
D. Solver add-in
Answer: C
Explanation:
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 D 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. Answer option B is incorrect. 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.
QUESTION 2
Which of the following 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?
A. Trendline
B. Line chart
C. Sparkline
D. Backstage view
Answer: C
Explanation:
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 D 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 A is incorrect. 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. 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 3
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.
QUESTION 4
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 protect the worksheet and locked cells by permitting or prohibiting other employees to select, format, insert, delete, sort, or edit areas of the spreadsheet.
Which of the following options will you use to accomplish the task?
A. Mark as Final
B. Encrypt with Password
C. Protect Current Sheet
D. Protect Workbook Structure
Answer: C
Explanation:
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 5
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.
Answer: ABDE
Explanation:
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 6
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
Answer: D
Explanation:
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 7
Rick works as an Office Assistant for Tech Perfect Inc. He is creating a report through Microsoft
Excel 2013. Rick wants to trace the project with the actual requirements.
For this purpose, he is required to compare month to month, actual v budget, quarter to quarter, year to year variances.
Which of the following will Rick use to accomplish the task?
A. Evaluate Formula
B. Accounting template
C. Form control
D. Trust Center
Answer: B
Explanation:
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.
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 anexisting macro to a control, or write or record a new macro. These controls cannot be added to User Forms, used to control events, or modified to run Web scripts on Web pages.
Answer option A is incorrect. Evaluate Formula is the formula examination tool provided by Microsoft Excel. This tool is useful for examining formulas that do not produce any error but
are not generating the expected result.
Answer option D is incorrect. Trust Center is where a user can find security and privacy settings
For Microsoft Office 2013 programs.
QUESTION 8
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
Answer: C
Explanation:
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.
All 80 Microsoft 77-427 Exam Dumps Questions are the New Checked and Updated! In recent years, the 77-427 certification has become a global standard for many successful IT companies. Looking to become a certified Microsoft professional? Download Braindump2go 2015 Latest Released 77-427 Exam Dumps Full Version and Pass 77-427 100%!