Question 1

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?

Accepted Answer

A)  Home 
B)  Insert 
C)  Formulas 
D)  Developer

Question 2

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?

Accepted Answer

A)  AutoFit 
B)  Pixel Space 
C)  Swap the X and Y axis 
D)  Lock current coordinate values

Question 3

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

Accepted Answer

A)  Macros 
B)  Visual Basic 
C)  Record Macro 
D)  Use Relative References

Question 4

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

Accepted Answer

A)  Layer Pane 
B)  Editor Pane 
C)  Center Pane 
D)  Tour Editor Pane

Question 5

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?

Accepted Answer

A)  Assign Macro… 
B)  Format Shape… 
C)  Set as Default Shape 
D)  Size and Properties…

Question 6

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.

Accepted Answer

A) True 
 B)False

Question 7

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

Accepted Answer

A)  Macro 
B)  Analyze 
C)  Move Data 
D)  Relationships

Question 8

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.

Accepted Answer

A) True 
 B)False

Question 9

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?

Accepted Answer

A)  E-mail Address 
B)  Create New Document 
C)  Place in This Document 
D)  Existing File or Webpage

Question 10

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?

Accepted Answer

A)  Advance 
B)  Language 
C)  Customize Ribbon 
D)  Quick Access Toolbar

Question 11

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

Accepted Answer

A)  F5 
B)  F8 
C)  F11 
D)  F12

Question 12

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?

Accepted Answer

A)  X- View Macros,Y- Run 
B)  X- Record Macros,Y- Run 
C)  X- Pause Recording,Y- Step 
D)  X- Pause Recording,Y- Edit

Question 13

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.

Accepted Answer

A) True 
 B)False

Question 14

What is the file extension of macro-enabled workbook?

Accepted Answer

A)  .xls 
B)  .xlx 
C)  .xlsx 
D)  .xlsm

Question 15

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

Accepted Answer

A)  12 
B)  20 
C)  39 
D)  56

Question 16

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.

Accepted Answer

A) True 
 B)False

Question 17

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?

Accepted Answer

A)  Insert 
B)  View Code 
C)  Run Dialog 
D)  Design Mode

Question 18

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

Accepted Answer

A) True 
 B)False

Question 19

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?

Accepted Answer

A)  Warning Dialog box 
B)  Format Cells dialog box 
C)  Create Relationship Dialog box 
D)  Edit Relationship Dialog box

Question 20

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?

Accepted Answer

A)  Macros 
B)  Visual Basic 
C)  Record Macro 
D)  Macro Security

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?

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?

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?

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?