Deck 10: Imports, xml, and Power Add-Ins

Question 1

Which feature is best suited to split imported text into multiple columns?

A)Text Import Wizard
B)Column Split Wizard
C)Convert Column Tool
D)Convert Text to Columns Wizard

Answer 1

D

Question 2

What would be the return of the following function in C4?

=PROPER(A4)

A)TASHA
B)tasha
C)Tasha
D)Tasha McCall

Answer 2

C

Question 3

An alternative to the CONCATENATE function is to use ________ to join strings.

A)+
B)^
C)&
D)#

Answer 3

C

Question 4

Which of the following actions refreshes data connections in a linked Excel workbook?

A)Click Existing Connection in the Get External Data group of the Data tab.
B)Click Refresh on the File tab.
C)Right-click on the data table and select Refresh all.
D)Click Refresh All in the Connections group of the Data tab.

Question 5

If you use the Text to Columns wizard to separate the Name Column in the table below into First Name and Last name columns,what would happen if you separated the names at the space between first name and last name without making any other changes to the table?

A)Excel would add a new column to accommodate last names.
B)The two new columns would be created in a new worksheet.
C)Excel would return an error message.
D)The new last name column would overwrite the data in the street address column.

Question 6

What would be the return of the following function in C2?

=CONCATENATE(A2," ",B2)

A)jamesturner
B)JAMES
C)james Turner
D)jamesTurner

Question 7

On which tab is the Text to Columns Wizard?

A)Insert
B)Data
C)Formula
D)Developer

Question 8

What would you do to start to manage an existing connection in a linked workbook?

A)Click Manage in the Connections group of the Data tab.
B)Click Connections in the Connections group of the Data tab.
C)Click Existing Connection in the Get External Data group of the Data tab.
D)Click Refresh All in the Connections group of the Data tab.

Question 9

There are a total of ________ text manipulation functions in Excel.

A)4
B)10
C)27
D)7

Question 10

Which of these file types cannot be imported into Excel?

A))txt
B))CSV
C))PDF
D)CAD

Question 11

What do you call characters that separate data?

A)Hollariths
B)Separators
C)Hashes
D)Delimiters

Question 12

If you do not want to create a link to an Access database table ________.

A)use the Embed Access table command
B)copy each line of data from Access and paste into an Excel worksheet
C)open the table in Access and select data and field names and then copy and paste it into an Excel worksheet
D)Excel had no capacity to do this.It must be done in Access.

Question 13

This feature is a productivity tool which interprets patterns and completes data entry based on the pattern you establish.

A)Auto Fill
B)Auto Complete
C)Predictive Loading
D)Flash Fill

Question 14

Which of the following are settings that control how imported data in cells connect to their source data?

A)Import settings
B)Data range properties
C)Link settings
D)Data Control settings

Question 15

Embedded data in an excel workbook is ________.

A)uneditable data linked to a web source
B)data that imported using Power Query
C)any imported data
D)editable data with no link back to its original source

Question 16

Which message displays in the status bar when a connection is being updated?

A)Running background query
B)Connections updating
C)Updating
D)Query in progress

Question 17

A company changes name from Wally's Widgets to Wally's Wonders.Which function would change all Wally's Widgets to Wally's Wonders?

A)SUBSTITUTE
B)TRIM
C)MID
D)LEFT

Question 18

Which of the following functions is not a text string manipulation tool?

A)CONCATENATE
B)MATCH
C)TRIM
D)LEFT

Question 19

Which of the following functions is best suited to replace the word "Doctor" with "Dr." in a text string?

A)TRIM
B)LEFT
C)CONCATENATE
D)SUBSTITUTE

Question 20

What would be the return of the following function in C3?

=LOWER(B3)

A)STEWART
B)Stewart
C)stewart
D)Aaron Stewart

Question 21

The ________ is the area that contains the dashboard data visualizations in Power View.

A)slate
B)canvas
C)palette
D)surface

Question 22

Power Pivot delivers everything in PivotTables plus all of the following except ________.

A)innate web publishing capabilities
B)displaying Key Performance Indicators
C)big data handling and compression
D)the ability to create relationships between tables

Question 23

XML is ________.

A)Excel's version of HTML
B)a method to structure data that's usable industry wide
C)a method to format documents for web browser display
D)a series of user defined markers

Question 24

Power Query is ________.

A)installed by default
B)enabled in Options
C)downloadable Excel add-in
D)on the Data tab

Question 25

A(n)________ is a data file that contains characters,such as letters,numbers,and symbols,including punctuation and spaces.

Question 26

________ is an Excel add-in that enables you to create a visual dashboard.

A)Power View
B)Power Pivot
C)Power Query
D)Power Dash

Question 27

What does XML stand for?

A)Extensible Markup Language
B)Excel Markup Language
C)Extension Markup Language
D)External Markup Language

Question 28

________ is the process of inserting external data created or stored in another format into the current application.

Question 29

What is a relationship in Power Pivot?

A)A continuous stream of XML data to Power Pivot
B)The reference in a DAX function
C)A tool to build key performance indicators
D)An association between two related tables where each table contains an identical field

Question 30

Data stored in tables on websites often produce a lot of errors because of additional web design languages formating the table on the web page.Which tool would be best suited to clean data downloaded from a website.

A)XML Import
B)Text Manipulation
C)Power Query
D)CSV Import

Question 31

Data tables from database programs such as Access and ________ Server can be imported into Excel.

Question 32

Power BI is a ________.

A)Business Integration tool suite
B)Business Intelligence tool suite
C)Business Investment tool suite
D)Business Interconnectivity tool suite

Question 33

What governing body developed XML?

A)NSA
B)ICANN
C)Word Wide Web Consortium (W3C)
D)IEEE

Question 34

Power Query can do all of the following except ________.

A)delete empty records
B)chart data
C)select records matching specific criteria
D)add additional data from multiple sources

Question 35

What application could be used to create an XML document in its native file format?

A)Photoshop
B)Acrobat
C)iTunes
D)Notepad

Question 36

Power Pivot is an available add-in for ________ users.

A)Office Standard
B)Office Professional Plus
C)Office Home
D)Office Student

Question 37

Where can you open XML files in Excel?

A)Open dialog box
B)Developer tab
C)Insert tab
D)Formula tab.

Question 38

Each data field or table added to a Power View canvas is called a(n)________.

A)element.
B)visualization.
C)key indicator.
D)calculation.

Question 39

Which is not true about building PivotTables in Power Pivot?

A)They can contain calculations.
B)They can be built on multiple tables.
C)They can have numerical data,only.
D)They are for Business intelligence analysis.

Question 40

Which of the following is a required in XML?

A)Title
B)Tag
C)Link
D)Anchor

Question 41

When connections are being refreshed in a workbook the status bar displays the message Running ________ query.

Question 42

The ________ is the area that contains the dashboard in Power View.

Question 43

A(n)________ links two tables together for a Power Pivot PivotTable.

Question 44

________ is a standalone desktop application that has Power View functionality outside of Excel

Question 45

Describing XML as ________ means that it can be expanded as necessary to fit the needs of the data.

Question 46

Use the ________ function to combine text from multiple columns.

Question 47

The ________ function will change sam smith into SAM SMITH.

Question 48

________ is an Excel add-in Business Intelligence tool which enables you to visualize and analyze data.

Question 49

The ________ function will replace one string with another.

Question 50

To view XML data in Excel in a columnar format,choose the as a(n)________ option in the Open XML dialog box.

Question 51

________ properties are settings that control how imported data in cells connect to their source data.

Question 52

Importing XML data into Excel enables you to maintain a link and ________ data.

Question 53

Text can be converted to separate columns using the Convert ________ Wizard.

Question 54

The ________ function will change tabitha turner to Tabitha Turner.

Question 55

A(n)________ is a user defined marker that identifies the beginning and ending of a piece of data.

Question 56

________ is a business intelligence tool that provides the user with the ability to import,shape,cleanse,and query data.

Question 57

A(n)________ contains a slash (/)and the name of the element enclosed in <>.

Question 58

The ________ function will change JILL JONES to jill jones.

Question 59

________are data fields and tables added to the Power View canvas.

Question 60

________ is an industry standard for structuring data across applications,operating systems and hardware.

Question 61

The TRIM function removes leading and trailing spaces in a text string but maintains spaces between words.

Question 62

You can import text files into Excel workbooks.

Question 63

=CONCATENATE(PROPER("adam")," ",PROPER("smith"))would return Adam Smith.

Question 64

The return of the RIGHT function is always a number.

Question 65

If you import web data directly into Power Pivot and discover there are a lot of errors and inconsistencies,use ________ to clean it up.

Question 66

A tab character separates data into columns in a CSV.

Question 67

You cannot separate data in a single column into multiple columns.

Question 68

The SUBSTITUTE function has an optional argument that enables you to replace the third occurrence of a string.

Question 69

xml version="1.0" encoding="UTF-8"?
> is an XML declaration.

Question 70

Tab-delimited files use tabs to separate data into new rows.

Question 71

Text string functions cannot be nested.

Question 72

Embedded data has no the link to its original source.

Question 73

An XML comment starts with .

Question 74

There are 27 Text string manipulation functions.

Question 75

You cannot maintain a connection with an XML source file.

Question 76

Flash Fill only fills formulas.

Question 77

Right-clicking in an XML area and selecting XML will start the import or refresh process.

Question 78

XML elements cannot be nested.

Question 79

A CSV file has no newline characters.

Question 80

________ is an Excel add-in that enables you to build a visual dashboard with the functionality of a PivotTable.