77-882
Excel 2010 Exam: 77-882 Demo Edition
CERT MAGIC 1
http://www.certmagic.com
77-882
QUESTION: 1 You work as an Office Assistant for Tech Perfect Inc. You create a worksheet in Excel 2010 that contains various tables, headings, charts, and SmartArt objects. In order to give it a professional look, you choose Office theme as shown below:
You like the appearance of the sheet in this theme; however, you want to use the Lucida Calligraphy font for heading and Arial font for the body text. You often use this font combination for formatting office sheets. You are required to accomplish the following tasks: Use the font combination in your current document. Enable Excel to provide this combination in other office documents too. Choose the required steps to accomplish the tasks.
Answer:
2
http://www.certmagic.com
77-882
Explanation: In order to accomplish the tasks, you will have to take the following steps: Choose the Fonts option in the Themes group on the Page Layout tab.
In the dialog box, provide the required font names for header and body texts, provide name for the new theme.
3
http://www.certmagic.com
77-882
These steps will create a custom theme, which will be available for other office documents too.
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 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.
QUESTION: 2 Alan works as a Sales Manager for MVP Technologies. He is creating a sales report in Microsoft Excel 2010. In the report, he needs to enter the names of sales executives in one column and the sales target (in dollars) achieved by them in the next column as shown in the image below:
4
http://www.certmagic.com
77-882
He wants to ensure that the color of a cell changes automatically to orange when the sales figure entered in the cell is more than $10,000. Which of the following actions will he take to accomplish the task?
A. Use the conditional formatting feature. B. Use a style. C. Use the AutoFormat feature. D. Use the AutoCorrect feature.
Answer: A Explanation: According to the question, the cell color should automatically change to orange when sales figure is more than $10,000. In order to accomplish the task, you should use the conditional formatting feature of Excel.
5
http://www.certmagic.com
77-882
In Excel, conditional formats are format settings that are applied by Excel only when contents of a cell meet the specified criteria. The conditional formatting feature of Excel provides the facility to implement conditional formats. It allows users to automatically apply formatting on cells depending on the values of cells or the values of formulas in the cells. Your worksheet will be formatted automatically as shown in the image below:
In Excel, conditional formats are format settings that are applied by Excel only when contents of a cell meet the specified criteria. The conditional formatting feature of Excel provides the facility to implement conditional formats. It allows users to automatically apply formatting on cells depending on the values of cells or the values of formulas in the cells. Your worksheet will be formatted automatically as shown in the image below: Answer option C is incorrect. The AutoFormat feature is applied on a range of cells to change the formatting of the cells according to the pre-designed format. Answer option B is incorrect. A style is a group of predefined formatting features, for e.g. font, font size, font color, alignment, etc. The group of formatting features, which creates a style, is stored by using a particular name. By creating a style, different formats can be applied in a single step. It is useful to maintain consistent formatting in a workbook. However, it does not support formatting based on conditions. Answer option D is incorrect. The AutoCorrect feature is used to correct misspelled words automatically as they are typed.
QUESTION: 3 You work as an Office Assistant of Mark World Inc. You have created a report in a workbook in Excel 2010. You want to change the layout of the worksheet from portrait to landscape. Mark the option that you will choose to accomplish the task.
6
http://www.certmagic.com
77-882
Answer:
Explanation: The question specifies that you have to change the layout of the worksheet from portrait to landscape. The Portrait layout is the default configuration of Excel. The Orientation option in the Page Setup group of the Page Layout tab is used to switch the pages in portrait of landscape layouts. In order to accomplish the task, click the Orientation option in the Page Setup group of the Page Layout tab, and then click the Landscape option.
QUESTION: 4 You work as an Office Assistant for Peach Tree Inc. You have created a report in a workbook using Microsoft Excel 2010. The report includes a worksheet that contains sales data of the company. You want to use complex criteria to filter the list. For testing, you have inserted criteria rows in the sheet. The fragment of the worksheet is shown below:
7
http://www.certmagic.com
77-882
The list range is defined as A9:D44. For testing, you want to filter the list for all the records of Mark. You want to ensure that the filtered list does not provide duplicate rows. You also want to display the filtered list at a new location at the G9 cell position. You open the Advanced Filter dialog box by clicking the Advance option in the Sort & Filter group on the Data tab. Fill the Advanced Filter dialog box options to fulfill your requirements.
Answer:
8
http://www.certmagic.com
77-882
Explanation: According to the question, you have to accomplish the following tasks: Display all the records of Mark. No duplicate rows should exist in the filtered list. The filtered list is inserted and displayed in the new location at the G9 cell position. In order to accomplish the task, you will have to provide entries as shown in the image given below:
Opting for the Copy to another location radio-button will enforce to display the filtered list in the specified location (in this case G9). You will have to provide A9:D44 in the List range option to specify the list to be filtered. Provide range C5:C6 for the Criteria range option. The Copy to option specifies the location where the filtered data has to be displayed. Provide G9 to accomplish the task. As the question states that no duplicate records should appear, check the Unique records only check-box. These steps will copy the filtered data at the G9 cell position as shown in the image given below:
9
http://www.certmagic.com
77-882
QUESTION: 5 You work as an Office Assistant for Dreams Unlimited Inc. You use Microsoft Excel 2010 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. COUNTIFS(B2:C5,"=Yes") B. COUNTIF(B2:B5,"=Yes",C2:C5,"=Yes") C. COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") D. COUNTIF(B2:C5,"=Yes")
Answer: C 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. Fact What is COUNTIFS? COUNTIFS is a Microsoft Excel function. It counts the number of cells that meet multiple criteria within a range. It is the extension of COUNTIF function that uses only a single condition. Each
10
http://www.certmagic.com
77-882
cell in a range is counted only if all of the corresponding criteria specified are true for that cell. Syntax COUNTIFS(range1, criteria1, range2, criteria2) Here, range1, range2, etc., are ranges in which Excel will evaluate the associated criteria. Criteria1, criteria2, are criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. Cells in each range must be numbers, names, arrays, or references that contain numbers. Blank and text values are ignored. The wildcard characters, question mark (?) and asterisk (*), can be used in criteria. Example COUNTIFS(B2:B5,"=Done",C2:C5,"=Done") This function will count all the cells that contain the value "Done" (without quotes) in ranges B2:B5 and C2:C5. Answer option B is incorrect. The COUNTIF function of Excel does not support multiple criteria. Answer options D and A 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.
11
http://www.certmagic.com