77-427 Exam
Amazing excel 77-427 secrets
Proper study guides for Regenerate Microsoft Microsoft Excel 2013 Expert Part 1 certified begins with Microsoft 77-427 preparation products which designed to deliver the Best Quality 77-427 questions by making you pass the 77-427 test at your first time. Try the free 77-427 demo right now.
2021 Jan laser stanley 1-77-427:
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 Media Perfect Inc. You have created a report in Excel. You have inserted the following formula in a cell:
VLOOKUP(12, A2:C10, 3, FALSE )
Which of the following actions will be performed by the formula?
A. Search for the value 12 in the first column of the range, and then returns the value that is contained in the third column of the range and on the same row as the lookup value.
B. Wrong syntax used.
C. Search the value 3 in the last column of the range, and then returns the value that is in the column of the range and on the same row as the lookup value.
D. Search the value 12 in the third column of the range and then returns the value that is contained in the 12th column of the range and on the same column as the lookup value.
Answer: A
Explanation:
The formula will search for the value 12 in the first column of the range, and then returns the value that is contained in the third column of the range and on the same row as the lookup value.
Q3. You work as an Office Assistant for Blue well Inc. You want to record a macro and save it with the name "Auto_Open" so that the macro will run whenever you open the workbook containing the macro.
While doing so, you observe that the Auto_Open macro fails every time. Which of the following are the causes of this?
Each correct answer represents a complete solution. Choose all that apply.
A. The default workbook or a workbook loaded from the XLStart folder is opened before running the macro.
B. The workbook is opened programmatically using the Open method.
C. The workbook where a user wants to save the Auto_Open macro already contains a VBAprocedure in its Open event.
D. The workbook is based on the record button that is disabled by default before running the Auto_Open macro.
Answer: A,B,C
Explanation:
The various limitations of recording an Auto_Open macro are as follows:
1. If the workbook where a user wants to save the Auto_Open macro already contains a VBA procedure in its Open event, then all actions in the Auto_Open macro are overridden by the VBA procedure used for the Open event.
2. The Auto_Open macro will be ignored when a workbook is opened programmatically using the Open method.
3. The Auto_Open macro runs before any other workbooks are opened. Therefore, if the user records actions he wants Excel to perform on the default workbook or a workbook loaded from the XLStart folder, the Auto_Open macro will fail when the user restarts Excel because it runs before the default and start-up workbooks are opened. Answer option D is incorrect. This is an invalid answer option.
Q4. You work as an Office Assistant for Blue Well Inc. The company has a Windows-based network. You have changed some data in the worksheet of the company. You are submitting the sell workbook of the company to the Administrator of the company for comments. Now, you want to merge input that you receive into a copy of that workbook, including the changes and comments that you want to keep. 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?
A. Slicer-enabled highlighting
B. Onscreen highlighting
C. Reviewing of changes
D. History tracking
Answer: C
Explanation:
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 A is incorrect. This is an invalid answer option.
Q5. You work as a Sales Manager for Maini Industries. Your company manufactures and sells construction equipment. You have to create a report that will show the highest selling items of each month. The report will be in the following format:
In a workbook, you create a table named Item_table as shown below:
You fill in the required entries in the workbook.
For the third column of the sheet, you want to use formulas to fill in the values. You want the cells in the third column to get automatically filled with the names corresponding to ItemID in the second column. You want the exact values to be filled in the column. You select the C2 cell in the third column.
Which of the following formulas will you enter in order to accomplish the task?
A. =VLOOKUP(B2,Item_table,2,FALSE)
B. =VLOOKUP(B2,Item_table,2,TRUE)
C. =VLOOKUP(2,Item_table,B2,FALSE)
D. =VLOOKUP(2,Item_table,B2,TRUE)
Answer: A
Explanation:
In order to accomplish the task, you will use the following formula:
=VLOOKUP(B2,Item_table,2,FALSE)
Answer option B is incorrect. According to the question, you want the exact values in the column.
The TRUE value in the range_lookup argument provides the approximate match.
Answer options C and D are incorrect. The VLOOKUP functions used in the formulas have wrong syntaxes.
Update expert core exams 77-427 and 77-428:
Q6. 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.
Q7. 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.
C:\Documents and Settings\Administrator\Desktop\1.JPG
A line chart should be used where category labels are text, and are representing evenly spaced Values such as months, quarters, or years.
Q8. Rick works as an Office Assistant for Tech Perfect Inc. He is creating a user form through 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: A,B,D,E
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.
C:\Documents and Settings\Administrator\Desktop\1.JPG
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.
C:\Documents and Settings\Administrator\Desktop\1.JPG
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.
Q9. 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 select the Encrypt with Password option as a protection measure for the document you are sharing. Now, you have to specify a password in the Password box in the Encrypt Document dialog box appeared for editing the document. But you have forgotten the password. What will happen in this situation?
A. You will use a new password.
B. You will ask Microsoft to retrieve the password.
C. You will fetch the password from the list in which you have saved it.
D. You will create a new document.
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.
C:\Documents and Settings\Administrator\Desktop\1.JPG Answer option D is incorrect. You can create a new document with the same details but it will increase the overhead and is a very time consuming process. Answer option D is incorrect. You can create a new document with the same details but it will increase the overhead and is a very time consuming process.
Q10. 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: