Excel Chapter 9
Formatting an XML document is saved with the _________ file extension
.XSL
A. CSV File B. Tab Delimited File C. Metadata D. Delimiter E. Text File
A. A text file that uses commas to separate data B. A text file that uses tabs to separate data C. Key words, instructions, and captions D. A character that indicate the beginning and end of a text segment E. A simple container of text data
A. Rational Database B. Primary Key C. Foreign Key D. Access Database E. Microsoft Query
A. Collection of tables linked by common fields B. Unique identifier for a record C. A field that is linked to a primary key in another table D. Uses the .accdb or .mdb file extension E. A tool used to import data fields into Excel
A. .ACCDB B. .HTML C. .CSV D. .TXT E. .XML
A. Import data from relational tables created in Access B. Link to data stored in tabular form on websites C. Data stored in a comma-delimited format D. Exchange data between mainframes and other systems stored in a text format E. Data stored in the Extensible Markup Language format
A. CLEAN B. LOWER C. PROPER D. TRIM E. UPPER
A. Remove nonprinting characters B. Converts to lowercase C. Capitalizes only the first letter in each word D. Removes all spaces from text except spaces between words E. Converts to uppercase
A. DAY B. DATE C. WORKDAY D. DATEVALUE E. WEEKDAY
A. Returns the day of the month B. Returns the sequential serial number for a particular date C. Calculates the number of standard working days (less holidays and weekends) before or after a given date D. Returns the serial number of the date represented E. Returns the day of the week as a number (1-7) for a given date
A. RIGHT B. LEN C. LEFT D. CONCATENATE E. MID
A. Returns the specified number of characters from the end of a text string B. Returns the number of characters in a text string C. Returns the specified number of characters from the start of a text string D. Joins two text strings into one text string E. Returns the specified number of characters starting at the character you specify
A. Markup Language B. XML Schema C. HTML D. XML Element E. XML
A. Uses tags to tell the processor what to do with the document B. Describes the structure of an XML document C. Defines the way web page data will be displayed by the browser D. Includes the XML start and stop tags as well as data E. Defines the content of data so it can be stored or transported
The ________________ function removes the first 32 nonprinting character codes
CLEAN
To remove any nonprinting characters from a text strong, use the __________ function.
CLEAN
The _________________ function could be used to make SANDBOX out of the two words SAND and BOX
CONCATENATE
Data ________ is a process that converts data into a useful format by fixing obvious errors.
Cleansing
The following is an example of the _____________ data type. ProductNum,ProductName,DateShipped,QuantityShipped 59313,XL Golf Shirts,3/15/13,35 72316,Men's Shoe,2/5/13,10 47423,Head Covers,3/6/13,20
Comma Separated
To save a web query in Excel, use the ________ on the Data tab.
Connections command
The ______________ Wizard separates text files into their respective columns.
Convert Text to Columns
The ______________ function is use to convert a date represented by text to a serial number
DATEVALUE
Jerry used a function to convert the following data 1/31/2012. The function he used returned the number 31. Jerry likely used the _______________ function
DAY
Eternal data connections for Excel can be found on the __________ tab.
Data
To import a text file int Excel with all the text import features, click the _____________ tab.
Data
Which one of the following statements is FALSE?
Data cleansing is very costly both in time and money
A(n) ____________ indicates the beginning and end of a text data segment
Delimiter
A(n) ______________ is a way of indicating the beginning and end of a text data segment.
Delimiter
An XML document uses ____________ that include start tags, end tags and the data itself.
Elements
Excel users can import data from a variety of sources including ________________, which is anything that is neither stored in an Excel format nor stored locally
External Data
Anything that is NOT stored in an Excel format is __________.
External data
A web query will always be able to access information even when the URL changes
F
Access database tables are the only element that can be imported into Excel
F
Commas are the only delimiter allowed for text files
F
Copy and pasting data from a website and using a web query have the same result
F
Data cleansing is the process of validating that the data is correct and accurate
F
Excel cannot connect to an Access database; rather, data must be copied and pasted into a spreadsheet
F
Excel cannot create permanent connections between an Access database and an Excel spreadsheet
F
Excel import file types are limited to only two types of files, HTML and XML
F
Flash Fill involves more typing than traditional text functions and can be more difficult to use on touch-enabled devices
F
HTML describes the content of the data between tages
F
Most organizations keep their information in one single data source
F
Most people prefer to use Access over Excel to chart and analyze data
F
One popular way to integrate information from web pages is to use a Data Connection Wizard
F
Some companies use Internet queries on their websites to make their data available to users
F
Text files can contain fronts, images, and hyperlinks.
F
The DATEVALUE function can be used to calculate the number of available work days between two given dates
F
The MONTH function returns the name of the month. For example, 11/11/2018 would return November
F
With XML, the content, format, and structure are contained in a single document
F
___________ works by examining the pattern of data in the cell next to where you are typing.
Flash Fill
_____________________ recognizes patterns in data as you type and automatically fills in values for text and numeric data
Flash Fill
When a primary key from one table is connected to another table, it is said to be a __________ key.
Foreign
With ____________, tags are prespecified and are used to tell the browser how to display the web data
HTML
_______________ is a web language that was created to tell the browser how to properly display a web page.
HTML
If cell A2 contains the text string "red shorts", then =_________(A2,3) will return the word "red".
LEFT
If cell A2 contains the string "Excel 2016", the =_______(A2) will return the number "10".
LEN
The ____________ function returns the total number of characters in a string
LEN
_____________ data from a permanent connection can be imported as a simple table, a PivotTable report, or PivotChart.
Live
HTML and XML are both examples of _______________.
Markup languages
__________________ is data about data such as source information
Metadata
______________ is a special tool that makes it easy to connect Excel to an Access database.
Microsoft Query
All of the following statements are TRUE about importing XML data EXCEPT:
Most web pages are programmed in XML
You can import tables and queries from Access by dragging the object from the __________ and dropping it into the desired starting cell.
Navigation Pane
Which one of the following are common formats cannot be imported into Excel?
ODBC
The _____________ function capitalizes the first letter of each word and leaves the remaining letters as lowercase
PROPER
To change the text string JaMEs sMITh to James Smith, use the ______________ function.
PROPER
A social security number is commonly used as a ____________ key.
Primary
An Access database uses a ___________ field to uniquely identify a record.
Primary Key
A(n) _____________ is a question that you would ask a database to search for information.
Query
By definition, a(n) _____________ database is a collection of tables linked together by shared fields
Relational
If the _____________ function does not find the string supplied for the old_text argument, it will not return an error
SUBSTITUTE
To change the word apple with orange with a string of text, use the ______________ function.
SUBSTITUTE
The following is an example of the _____________ data type. PNum,PName Shipped Quantity 59313 XL_Golf_Shirts 3_15_13 35 72316 Mens_Shoe 2_5_13 10 47423 Head_Covers 3_6_13 20
Space Delimited
A delimiter with a way of indicating the beginning and end of a text data segment
T
Anything that is not stored in an Excel format or not stored locally is considered to be an external data set
T
Conditional formatting can aid in removing duplicate data
T
Excel has a query wizard built into its Microsoft Query function that can be a very powerful aid for linking a worksheet to an Access database
T
Excel has the ability to consolidate diverse types of data
T
Flash Fill recognizes patterns in data as you type and automatically fills in values for text and numeric data
T
Text files are imported into Excel in a similar manner as XML and HTML files
T
Text files have no metadata associated with them
T
Text functions help to extract and standardize data
T
There is not a prespecified standard for XML tags
T
Two of the most common text file types that use delimiters are .csv and .txt
T
You can import tables and queries from Access by dragging the object from the Navigation Pane and dropping it into the desired starting cell
T
To remove all spaces from text except for single spaces between words, use the _____________ function.
TRIM
Use the _____________ function when you want to remove spaces at the beginning of end of a string
TRIM
In a tab delimited file, ____________ separate the data.
Tabs
_______________ are markup indicators in an XML document used to identify the data.
Tags
______________ are functions that manipulate data to clean it up and make it useful.
Text Function
The _________ does have file size limitations, and occasionally you will get a text file that is too big to import into Excel
Text Import Wizard
A(n) _______________ is a file structured by the use of delimiters
Text file
Which one of the following is NOT true about text files?
Text files contain formatting
Because Flash Fill involves less typing than traditional text functions, it can be easier to use on _______________ devices
Touch Enabled
To change the text string James Smith to JAMES SMITH, use the _______________ function.
UPPER
Data _________ is a process that validates data so that it is accurate.
Verification
An Excel ____________ creates a connection to the original web page so that you will have the most up-to-date data
Web Query
Which one the following is NOT an advantage of a web query?
Web queries update the URL automatically when the URL changes
One popular way to integrate information from the Internet is to use a(n) ________________.
Web query
Data on web pages is not stored in an organized manner; therefore, XML was created to give structure to web page data
XML
Which one of the following is NOT a text file?
XML
With ______________, tags are customized for use with content of a specific industry
XML
_______________ is a markup language that was created to give structure to web page data so that it could be searched more efficiently.
XML
_______________ is the standard for transmitting data between applications and systems
XML
A(n) ___________ document is saved as an .xsd file
XML Schema
< revenue > is an example of a(n) ____________.
XML tag
