Exam 10: Data Analysis With Power Tools and Creating Macros

On which of the following tabs of the Power Pivot window will you select Refresh All under Refresh command to refresh all Power Pivot tables?

Willa wants to insert a 3-D Map to explore data.Which of the following options should she click in Custom Maps Options box to get the best fit?

Which of the following would you select on the Developer group to record a relative reference macro?

Which of the following panes in the Power Map window contains a 3-D globe on which the data is mapped?

Arlo creates a rectangular shape and inserts the text 'Color Box' in it.Then,he right clicks on the shape and selects a button to assign a macro to it.Which of the following can his selection be?

One can use Get & Transform (Power Query)feature of Excel to create a dynamic connection to an existing Excel table and refresh the connection when the data in table changes.

In which of the following groups on the Database Tools tab will you click Relationships while creating a relationship between two queries?

A worksheet has the following data in it: Cell range A2 to A10 in column A lists the categories of food items in a store: Vegetables and Fruits.Cell range B2 to B10 in column B lists the types of food items for each category: Cucumbers,Corn,Pears,Lettuce,Potatoes,and Scallions.Cell range C2 to C10 in column C lists the sale of each item during the month.If we insert the formula =SUMIFS(A2:A10,B2:B10,=P* )in cell A11,it will give us the total sale of products that begin with the letter P.

Bella wants to insert a hyperlink to a location in the current file.Which of these can be X in the following series of clicks she can use to solve her purpose: Select text or picture to display as hyperlink > Insert tab > Hyperlink > Link To > X > Select heading (current document only),bookmark,slide,custom show,or cell reference to link to in the list?

Grace wants to insert a Form control button into a worksheet.Which of the following will she select as X in the following series of clicks/entries/selections to display the Developer tab: File menu > Options from drop down menu > X in Excel Options Window > Developer checkbox under the list of Main Tabs > OK?

Which of the following keys will help you single step through your code in a macro?

Jean-Michelle wants to record the steps of a task as a macro.She clicks on the following series: Click View > Macros > X > Macro Name > Select list to run > Y.Which of the following can be X and Y in the given steps?

Rachel wants to use Microsoft Query to retrieve data from her corporate databases and files so that she doesn't have to retype the data that she wants to analyze in Excel.To do so,she can click and/or enter the following information in the following series of clicks: Data tab > Get External Data group > From Other Sources > From Microsoft Query > Databases tab (To specify a data source for a database,text file,or Excel workbook)> <New Data Source> OK > Create New Data Source dialog box > Type a name to identify the data source > Click a driver for the type of database for use as data source > Connect (Provide the information needed to connect to the data source)> OK.

What is the file extension of macro-enabled workbook?

From how many theme options can you choose to change the theme of a Power View report?

To create a relationship between two tables,the first step would be to click on Relationships in the Relationships group on the Database Tools tab.

Which of the following should be selected as X in the series to assign a macro to a button: Developer tab > Controls group > X > Form Controls > Button Form Control > Worksheet location where you want the upper-left corner of the button to appear > Type in the Assign Macro popup window?

Amin wants to extract data from a Pivot table he has created for items sold in the East region of his company in a year.The table contains the following entries: Cells A1,B1,A3 and B3 have the headings "Region," "East","Items" and "Sum" in them respectively.Cells A4 to A6 has the names of Items: A3 sheets,A4 sheets and A5 sheets in them,respectively.Cells B4 to B6 had their respective selling prices.Cells B7 has the title "Grand Total" and cell C7 has the grand total of all selling prices in the table.To calculate the total for the item,A4 sheets,Amin can use the formula: =GETPIVOTDATA("Total","Product","A4 sheets").

Which of these will you click select as X in this series of clicks/entries/selections to edit relationships between Pivot tables: Power Pivot window > Design tab > Manage Relationships command > Manage Relationships dialog box > Relationship > Edit > X > Use the drop-down and list box controls on this form to select the appropriate table and field names?

Nathan wants to run a macro from the developer tab.He enables the developer tab by clicking File > Options > Customize Ribbon > Developer tab > Code > X > Macro name box > Run.What should he click as X in this procedure?