Deck 10: Data Analysis With Power Tools and Creating Macros

Question 1

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.

Answer 1

True

Question 2

Joyce wants to stop recording the steps of a task as a macro.She clicks on the following series: View > Macros > X > Stop Macro > Type a file name in the dialog box > Save.Which of the following can X be in the given steps?

A) View Macros
B) Record Macros
C) Pause Recording
D) Stop Recording

Answer 2

C

Question 3

To update the data associated with external links,close all workbooks and open the one that contains the links.Then click Options on the Trust Bar and then click Enable this Content.

Answer 3

True

Question 4

Avery wants to transform data from data sources in order to analyze it.To do this,she can click Data > Get Data > Navigator window > Select table (or tables)to use in the query > Edit > OK.

Question 5

Tim wants to insert a hyperlink to a new file for quick access to related information on a web page.Which of these can be X in the following series of clicks he can use: Cell to insert hyperlink > Insert tab > Links group > Hyperlink > Link to > Create new Document > Name of New Document (Type a name for new file )> When to Edit > Select an option > Text to display box > ( Type text that you want to use to represent the hyperlink)> X > (Type information to be displayed when pointer rests on hyperlink in the box)> OK?

A) Style dialog box
B) Dynamic Text box
C) ScreenTip Text box
D) Edit Hyperlink dialog box

Question 6

Esperanza wants to record a macro to automate a repetitive task.Which of the following combinations of keys should she press to open the Macro name box?

A) Ctrl + Z
B) Alt + F8
C) Ctrl + Shift
D) Alt + T + M + R

Question 7

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?

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

Question 8

Chris wants to remove the rows with data errors in a query.To do so,he can right-click on the selected columns from where he wants to remove the rows containing the errors and select Remove Errors.

Question 9

Derek wants to remove columns from Query he doesn't need.To do so,he can select the columns he wants to remove with Ctrl+Click.Then he can select Remove Columns > Remove Columns in the Query Editor ribbon.

Question 10

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

Question 11

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?

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

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?

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

Which of these will you select as X in the following series of clicks to insert a hyperlink to a webpage: Select text or picture to display as hyperlink > Insert tab > Hyperlink > Insert Hyperlink box > X > Type or paste link?

A) Bookmark
B) ScreenTip
C) Address box
D) Text to display

Question 14

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

Question 15

Amelia wants to insert a hyperlink to an e-mail address.Which of these can be X in the following series of clicks she can use: Select text or picture to display as hyperlink > Insert tab > Hyperlink > Link To > X > Subject box (Type subject )> OK?

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

Question 16

Calista wants to add a screentip to a hyperlink she inserted into a cell.She selects the cell with the hyperlink,then presses a certain combination of buttons,then selects ScreenTip,types in a message,clicks OK,then clicks OK again to save the link.Which of these combination of buttons must she have pressed during this procedure?

A) Ctrl + Z
B) Ctrl + K
C) Alt + E
D) Alt + F4

Question 17

Which of these series of clicks would you select to remove hyperlink from a cell?

A) Home tab > Editing group > Clear > Remove Hyperlinks
B) Home tab > Editing group > Delete > Delete Hyperlinks
C) Home tab > Hyperlink group > Remove Hyperlinks
D) Home tab > Styles group > Delete > Remove Hyperlinks

Question 18

Which of these keys will you press to remove a hyperlink completely?

A) Esc
B) Delete
C) Ctrl + F9
D) Ctrl + Shift + F7

Question 19

Which of the following combinations of keys will you press to open the VBA window and run a macro code?

A) Alt + F9
B) Alt + F11
C) Alt + F4
D) Alt + Tab

Question 20

Kahir wants to delete a macro.To do this,he selects Tools > Macro > X > Select Macro > - sign > Yes on confirmation message dialog box.Which of the following will he select as X in this series of selections?

A) Macros
B) Excel Add-ins
C) Stop Recording
D) Visual Basic Editor

Question 21

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

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

Question 22

What is the default value (in seconds)for a transition between scenes in a tour?

A) 1
B) 3
C) 10
D) 12

Question 23

Johan wants to create a PivotTable using Power Pivot.During the procedure,to add a Table to PowerPivot,he selects a cell in a Table and clicks Power Pivot tab > Tables group > X.What is X in the procedure?

A) Add to Data Model
B) Update All
C) Measures
D) KPIs

Question 24

What is the file extension of macro-enabled workbook?

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

Question 25

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

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

Question 26

Which of the following will you select in Excel Options under File tab add the Insert Power View Report button to the ribbon?

A) Customize Ribbon
B) Advanced
C) Language
D) Proofing

Question 27

Edward wants to edit a relationship between two tables.To do this,he places the cursor pointing to the relationship line and then clicks the line to select it.Then,he clicks on tab "X" > Group "Y" > Edit Relationships.What are X and Y in the procedure?

A) A-Design tab,B-Tools group
B) A-Developer tab,B-Code group
C) A-Format tab,B-Arrange group
D) A-Design tab,B-Relationships group

Question 28

Which of the following series of clicks will you follow after clicking on a column to create a measure in the Power Pivot Window using AutoSum?

A) Calculations > AutoSum
B) Home > View> Calculation Area
C) PivotTable Fields > (Drag a field into)Values
D) Power Pivot > Calculations > Measures > New Measure

Question 29

Vasalicki wants to create a query linked to a table.In which row should she enter field criteria in the query design grid?

A) Criteria row
B) Show row
C) Field row
D) Sort row

Question 30

Which of the following scene effects move in a repetitive circular motion?

A) Figure 8
B) Dolly
C) Rotate Globe
D) Push In

Question 31

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

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

Question 32

Finley wants to create a 3-D map scene in a tour.To do this,she selects a range of cells containing her data in an Excel table.She then clicks on Insert tab > X > 3D Maps > Open 3D Maps.What is X in the procedure?

A) Tours group
B) Scene group
C) Tables group
D) Sparklines group

Question 33

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?

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

Question 34

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.

Question 35

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?

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

Question 36

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?

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

Question 37

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?

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

Question 38

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?

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

Question 39

A worksheet has the following data in it: Cell range A2 to A10 in column A lists the years from 2010 to 2019.Cell range B2 to B10 in column B lists the types of food items sold during each year: Tomatoes,Oranges,Pears,Pineapples,Potatoes,and Onions.Cell range C2 to C10 in column C lists the sale of each item in each year.If we insert the formula =COUNTIFS("=Onions","B2:B10")in cell A11,it will give us the count of the number of times "Onions" has been listed in the worksheet.

Question 40

In which of the following boxes under Home > Scene Options will you insert the name for the scene name of a tour?

A) Effect
B) Scene Name
C) Scene duration (sec)
D) Transition duration (sec)

Question 41

How can you rename a column in Excel?

A) Double-click on the column in the Query Editor and enter a new name.
B) Right-click on the column in the Query Editor and enter a new name.
C) Left-click on the column in the Query Editor and enter a new name.
D) Right-click on the column in the Query Editor,select Edit enter a new name.

Question 42

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

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

Question 43

In which of the following boxes under Define target value will you type a numerical value while creating a key performance indicator (KPI)?

A) Absolute value
B) Select icon style
C) Define target value
D) Define status thresholds

Question 44

To add a title to a Power View report,one should click on the placeholder "Click here to add a title" at the top of the Power View.

Question 45

To change the size of text to a particular percentage of the original text size in a Power View report,click on Power View tab > Themes group > Text Size.

Question 46

If Power View is enabled,one can create a new Power View report page by clicking Insert tab > Power View button.

Question 47

To open the Power Pivot window,one should click Power Pivot > Manage.

Question 48

The buttons on the Design tab within the Switch Visualizations button group allows us to change the type of data visualization.

Question 49

Which of the following will you click on in the Workbook Queries pane to check the "Add the data to the Data Model" box while adding a query to a Data Model?

A) Load To…
B) Duplicate
C) Reference
D) Move to Group

Question 50

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

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

Question 51

To load the Analysis ToolPak and select Anova: Single Factor for statistical analysis in Excel,one should click on the following series: File tab > Options > Add-Ins category > Manage box > Excel Add-ins > Go .select Analysis ToolPak option on the appearing dialog box > OK > Data > Analysis > Data Analysis dialog box > Anova: Single Factor > OK.

Question 52

To open the Manage Relationships dialog box in a Power View report,click the Design tab in the Power Pivot window,then click Manage Relationships in the Relationships group.

Question 53

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?

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

Question 54

To change the background colors in a Power View report,click the POWERVIEW tab > Themes group > Background > Select a color.

Question 55

Portia wants to create a calculated measure in Excel.To do this,she creates a PivotChart report using the data stored in an Analysis Services cube.Next,she clicks on a tab "A" and then in group "B," she chooses OLAP Tools > MDX Calculated Measure to open the New Calculated Measure dialog box.What can A and B be in the procedure?

A) A-Design tab,B-Data group
B) A-Developer tab,B-Code group
C) A-Analyze tab,B-Calculations group
D) A-Design tab,B-Table Style Options group

Question 56

Nathan wants to create multiple charts for a table Power View,beginning with a pie chart.What should he select as X in the series of clicks to do this: Design tab > X > Other Charts > Pie Chart?

A) Tiles
B) Options
C) Other Chart
D) Switch Visualization

Question 57

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.

Question 58

To create a video with High-definition video display,select 720p from the video quality settings.

Question 59

There are two Card Styles for Card visualization: Card and Callout.

Question 60

In which of the following tabs will you change the Table Name field in the Properties group while adding a table to a internal Data Model?

A) Inquire
B) Design
C) Power Pivot
D) Developer