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.

Accepted Answer

Get & Transform (Power Query) feature allows users to create a connection to an existing Excel table and refresh that connection when changes are made to the data in the table. This enables users to have access to real-time data without manually updating the connection.

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

Accepted Answer

In the given sequence, after selecting "View" and then "Macros," the next logical step to stop recording a macro would be to view the macros that are currently being recorded or available, which is done by selecting "View Macros." The other options (Record Macros, Pause Recording, Stop Recording) do not fit the sequence described, as they are actions to start, pause, or stop recording, not to navigate to where you can stop a macro.

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.

Accepted Answer

This is the correct process for updating external links in Excel.

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.

Accepted Answer

The answer of Avery wants to transform data from data...

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

Accepted Answer

The "ScreenTip Text box" is where Tim can type the information to be displayed when the pointer rests on the hyperlink. The other options are not relevant to this process.

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

Accepted Answer

The answer of Esperanza wants to record a macro to...

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

Accepted Answer

The answer of Bella wants to insert a hyperlink to...

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.

Accepted Answer

This is true, Chris can remove rows containing errors by selecting the columns and using the "Remove Errors" function.

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.

Accepted Answer

This is the correct way to remove columns in Query Editor.

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

This accurately describes the steps needed to use Microsoft Query to retrieve data from databases and files.

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

Accepted Answer

The question is asking for the point where one needs to make a selection to edit the relationships between Pivot tables. The series of clicks eventually leads to the "Edit Relationship" dialog box where one can make changes to the existing relationships. Therefore, the correct choice would be D.

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

Accepted Answer

The answer of Jean-Michelle wants to record the steps of...

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

Accepted Answer

The answer of Which of these will you select as...

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

Accepted Answer

The correct formula syntax for GETPIVOTDATA usually includes the data_field (the name of the data field to get data for), and the pivot_table argument (a reference to any cell, range of cells, or named range within the PivotTable). The formula provided in the question does not follow the correct syntax and does not specify a reference to the PivotTable. A more accurate formula might look something like =GETPIVOTDATA("Sum", $A$3, "Items", "A4 sheets"), assuming "Sum" is the data field and $A$3 is a reference within the PivotTable.

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

Accepted Answer

To insert a hyperlink to an e-mail address, Amelia needs to choose the option "E-mail Address" in the "Link To" section. She can then type in the e-mail address in the "E-mail Address" box and fill in the subject line in the "Subject" box.

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

Accepted Answer

Calista must have pressed Ctrl + K to bring up the Edit Hyperlink dialog box, where she can add the ScreenTip to the hyperlinked cell.

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

Accepted Answer

The answer of Which of these series of clicks would...

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

Accepted Answer

Pressing the "Delete" key will remove the hyperlink completely.

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

Accepted Answer

Pressing Alt + F11 will open the VBA window in Excel. From there, you can create and run macro codes.

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

Accepted Answer

When Kahir selects Tools > Macro, he will be presented with a list of options including Macros, Visual Basic Editor, and Stop Recording. Since he wants to delete a macro, he will select Macros as X, which will allow him to select the specific macro he wants to delete and then click the - sign to remove it. Excel Add-ins is not relevant in this scenario because it pertains to third-party tools and Visual Basic Editor is for editing and creating macros, not deleting them.

Quiz 10: Data Analysis With Power Tools and Creating Macros

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.

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?

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.

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.

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?

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.

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.

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?

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?

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?

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

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

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

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?