MIS 112 Final Exam practice preparation
When importing a text file into Excel, the _____________ helps specify how the data will be imported. -
correct answersCSV Wizard
Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its
decimal equivalent? - correct answers=HEX2DEC("3BC")
Please use the attached workbook as an external data source. The workbook has a table called
'Employee'. When you import data, after selecting all the columns from the table using the query wizard
(Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you
filter the records to retrieve those that have a value that ends with 'm' for 'Name'. - correct
answersFrom the Column to Filter list, select Name. In the right panel, select 'ends with' from the first
drop-down list and then type 'm' in the second drop-down list box
What is the shortcut key to create hyperlink when you select an object or a cell? - correct answersCtrl+K
To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to
define the location you want to jump to in the document. Which of the following is the right option to
create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at
http://example.microsoft.com. The link text to be displayed is "Quarterly Profit Report". - correct
answers=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly
Profit Report")
When working with data returned from a Query Wizard, you can adjust a variety of properties of an
external data range. This can be done using the ______________. - correct answersExternal Data
Properties Dialog Box
Step 3 of the Query Wizard lets you specify how you want the ______. - correct answersrecords to be
sorted
Which of the following is TRUE regarding the HTML files that Excel creates? - correct answersChanges to
graphs and formulas cannot be made to an HTML file created from Excel.
While using web queries Rich text formatting is typically used if we wish to ______. - correct
answersreproduce web page data in a format close to that of the original
While creating a parameterized web query the question mark (?) is used to ______. - correct
answersbegin the URL query string
What is the purpose of the Text to Columns command? - correct answersIt parses strings into their
component parts.
Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first
name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set
of steps to achieve that? - correct answersData --> Data Tools --> Flash Fill
, Please use the attached workbook as an external data source. The workbook has a table called
'Employee'. When you import data, after selecting all the columns from the table using the query wizard
(Data (tab) -> Get External Data (command group) -> From Other Sources -> From Microsoft Query), how
will you filter the records to retrieve those that have a value 'Tucson' for 'City' column. - correct
answersFrom the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-
down list and then select 'Tucson' from the second drop-down list
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory
called "TEST" in the "C:\" drive? - correct answers=HYPERLINK("C:\TEST\example.xslx",B5)
If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to
that cell as the link_location in the HYPERLINK formulas? - correct answers=HYPERLINK($C$1)
When you have imported data from an external data source, you can change the properties of the
'external data range' by using the External Data Properties dialog box. How will you open this dialog
box? - correct answersData (tab) -> Connections (group) -> Properties
Which is one of the ways to refresh a query used to import data from external data source using the
query wizard? - correct answersData (tab) -> Connections (group) -> Refresh All
What is the file format (file extension) used when you save an Excel workbook as a single file webpage? -
correct answers.mht or .mhtml
If there are two or more name / value pairs passed in a URL query string, separate them with the
following character: _____________ . - correct answersampersand (&)
While importing data from a web page into Excel, creating a refreshable Web Query is used to ______. -
correct answerskeep data dynamic or updateable
Which of the following is one of the ways to create a Pivot Chart? - correct answersInsert (tab) -> Charts
(group) -> PivotChart
If a pivot chart is created and the underlying table is deleted, what occurs? - correct answersThe pivot
chart remains.
Examine the attached workbook. The data represents the amount of money a customer has spent, in a
quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet.
For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. For the
product 'Carnarvon Tigers', what is the average revenue (sales in dollars) for Qtr 1? - correct answers150
Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column,
Rows and Values sections respectively. - correct answersbranch, date and amount
How do you remove a filter in the PivotTable Field List? - correct answersMove the cursor over the filter
icon next to the field name you want to remove the filter from. Click the arrow that appears, and then
choose the Clear Filter From option.
When importing a text file into Excel, the _____________ helps specify how the data will be imported. -
correct answersCSV Wizard
Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its
decimal equivalent? - correct answers=HEX2DEC("3BC")
Please use the attached workbook as an external data source. The workbook has a table called
'Employee'. When you import data, after selecting all the columns from the table using the query wizard
(Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you
filter the records to retrieve those that have a value that ends with 'm' for 'Name'. - correct
answersFrom the Column to Filter list, select Name. In the right panel, select 'ends with' from the first
drop-down list and then type 'm' in the second drop-down list box
What is the shortcut key to create hyperlink when you select an object or a cell? - correct answersCtrl+K
To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to
define the location you want to jump to in the document. Which of the following is the right option to
create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at
http://example.microsoft.com. The link text to be displayed is "Quarterly Profit Report". - correct
answers=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly
Profit Report")
When working with data returned from a Query Wizard, you can adjust a variety of properties of an
external data range. This can be done using the ______________. - correct answersExternal Data
Properties Dialog Box
Step 3 of the Query Wizard lets you specify how you want the ______. - correct answersrecords to be
sorted
Which of the following is TRUE regarding the HTML files that Excel creates? - correct answersChanges to
graphs and formulas cannot be made to an HTML file created from Excel.
While using web queries Rich text formatting is typically used if we wish to ______. - correct
answersreproduce web page data in a format close to that of the original
While creating a parameterized web query the question mark (?) is used to ______. - correct
answersbegin the URL query string
What is the purpose of the Text to Columns command? - correct answersIt parses strings into their
component parts.
Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first
name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set
of steps to achieve that? - correct answersData --> Data Tools --> Flash Fill
, Please use the attached workbook as an external data source. The workbook has a table called
'Employee'. When you import data, after selecting all the columns from the table using the query wizard
(Data (tab) -> Get External Data (command group) -> From Other Sources -> From Microsoft Query), how
will you filter the records to retrieve those that have a value 'Tucson' for 'City' column. - correct
answersFrom the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-
down list and then select 'Tucson' from the second drop-down list
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory
called "TEST" in the "C:\" drive? - correct answers=HYPERLINK("C:\TEST\example.xslx",B5)
If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to
that cell as the link_location in the HYPERLINK formulas? - correct answers=HYPERLINK($C$1)
When you have imported data from an external data source, you can change the properties of the
'external data range' by using the External Data Properties dialog box. How will you open this dialog
box? - correct answersData (tab) -> Connections (group) -> Properties
Which is one of the ways to refresh a query used to import data from external data source using the
query wizard? - correct answersData (tab) -> Connections (group) -> Refresh All
What is the file format (file extension) used when you save an Excel workbook as a single file webpage? -
correct answers.mht or .mhtml
If there are two or more name / value pairs passed in a URL query string, separate them with the
following character: _____________ . - correct answersampersand (&)
While importing data from a web page into Excel, creating a refreshable Web Query is used to ______. -
correct answerskeep data dynamic or updateable
Which of the following is one of the ways to create a Pivot Chart? - correct answersInsert (tab) -> Charts
(group) -> PivotChart
If a pivot chart is created and the underlying table is deleted, what occurs? - correct answersThe pivot
chart remains.
Examine the attached workbook. The data represents the amount of money a customer has spent, in a
quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet.
For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. For the
product 'Carnarvon Tigers', what is the average revenue (sales in dollars) for Qtr 1? - correct answers150
Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column,
Rows and Values sections respectively. - correct answersbranch, date and amount
How do you remove a filter in the PivotTable Field List? - correct answersMove the cursor over the filter
icon next to the field name you want to remove the filter from. Click the arrow that appears, and then
choose the Clear Filter From option.
