MIS 112 Final Exam
What steps would you take if you wanted to adjust the outline of a rectangle shape that has already been inserted into your worksheet? A) 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Outline group, change the settings. B) 1) Click the shape. 2) Click Home (tab) --> Font (group) --> Fill Color C) 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline. D) 1) Click the shape. 2) Hold shift while pressing the up/down arrow keys to increase/decrease the size of the outline.
1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline.
For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters. A) 24 B) 26 C) 255 D) 125
255
What does the following formula return? =FIND("m","Big Mama Thornton",1) A) 8 B) 6 C) 5 D) 7
7
If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format) A) 9/20/2014 1:00:00pm B) 9/19/2014 1:00:00am C) 9/19/2014 1:00:00pm D) 9/20/2014 1:00:00am
9/20/2014 1:00:00am
20130315 represents a date March 15, 2013. To convert this string to an actual date, which of the following formulas can be used? (Assume the data 20130315 is in cell A1) A) =DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2)) B) =DATE(RIGHT(A1,4), MID(A1, 5, 2), LEFT(A1,2)) C) =DATE(LEFT(A1,2), MID(A1, 3, 2), RIGHT(A1,2)) D) =DATE(RIGHT(A1,2), MID(A1, 3, 2), LEFT(A1,2))
=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))
If you want to use the HYPERLINK function to jump to a cell E2 in the same workbook and display "Hello" as the link text, which of the following functions would you use? Assume that the workbook name is Example.xlsx. A) =HYPERLINK("[Example.xlsx]E2", "Hello") B) =HYPERLINK("[Example1]E2", "Hello") C) =HYPERLINK("[Example.xlsx]E12", "Hello") D) =HYPERLINK("[Example.xlsx]E2", "")
=HYPERLINK("[Example.xlsx]E2", "Hello")
Which of the following formulas calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate. A) =NPER(6%/12,117.43,-5000) B) =NPER(8%/12,117.43,-5000) C) =NPER(6%,117.43,-5000) D) =NPER(6%/12,117.43,50000)
=NPER(6%/12,117.43,-5000)
Which of the following is TRUE for Pivot Tables? A) A Pivot table can be sorted by any column selected in the Pivot table field list B) The main drawback of Pivot tables is that you cannot sum up values (e.g., Sales for a region) C) The main difference between Pivot tables and regular Tables is that Pivot tables allow more columns D) Pivot tables are a new feature in Excel 2016
A Pivot table can be sorted by any column selected in the Pivot table field list
How do you group Pivot Table items manually? A) After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection B) After creating the pivot table, select the items to be grouped and then choose Design (tab) under PivotTable Tools and select Group (group) -> Group Selection C) Select columns in the pivot table and right click on them and choose the option to "Cluster Group" D) None of the answers provided is valid
After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection
An Excel formula may return which of the following errors? A) #REF! B) #DIV/0! C) #VALUE! D) All of the above
All of the above
#NAME? error occurs under which of the following conditions? A) Formula contains an undefined range or cell name B) Formula contains text that Excel interprets as an undefined name C) Formula uses a worksheet function that's defined in an add-in or the add-in is not installed D) All of the options provided are correct
All of the options provided are correct
Which of the following is true for printing a worksheet that contains objects / shapes? A) Objects are printed along in the worksheet by default B) You can avoid printing the object by making changes to the properties of the shape C) Both of the above D) None of the above
Both of the above
This function combines its arguments into a single text string. It is an alternative to using the ampersand operator. A) COMBINE() B) SINGLESTRING() C) CONCATENATE() D) DAYSADD()
CONCATENATE()
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time? A) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. B) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time. C) Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and enter the end time. D) Home (tab) -> Editing (group) -> Find & Select -> Data Validation
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time.
Which is one of the ways to refresh a query used to import data from external data source using the query wizard? A) Data (tab) -> Connections (group) -> Properties B) Data (tab) -> Connections (group) -> Refresh All C) Refresh D) Data (tab) -> Connections (group) -> All
Data (tab) -> Connections (group) -> Refresh All
When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[File] ? A) Displays the workbook name B) Displays the sheet's name C) Displays the workbook's complete filepath D) None of the above
Displays the workbook name
How do you change the calculation mode while dealing with a calculation intensive data table? A) Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables B) Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic C) Formulas (tab) -> Calculation (group) -> Calculation Options -> Manual D)Press Ctrl+C, followed by: Alt+P
Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables
When (or why) does Excel draw arrows to indicate an error source? A) For finding dependencies between two images pasted in different worksheets B) In Tracing Error Values C) Error-checking the sheet Background D) Because Functions Must Have Arrows
In Tracing Error Values
What is the purpose of the Text to Columns command? A) It combines several columns of text into a single column. B) It creates several columns within a single text cell. C) It parses strings into their component parts. D) It adds commas to text to separate values.
It parses strings into their component parts.
Which of the following best explains the MATCH function? A) It returns 'True' if an item is present in an array B) It returns 'True' if an item is present in any order in an array C) It returns the relative position of an item in an array D) It returns the relative position of an item in an array that matches a specified value in a specified order
It returns the relative position of an item in an array that matches a specified value in a specified order
Which of the following is TRUE about the color scale formatting option? A) It varies the background color of a cell based on the cell's value relative to other cells in the range. B) Color scale conditional formatting does not use a gradient. C) This formatting option will cause high value and low value cells to have the same color. D) The color scale cannot be customized.
It varies the background color of a cell based on the cell's value relative to other cells in the range.
When choosing a page orientation, ____ is one of the two valid options. A) Landscape B) Narrow C) Wide D) Tall
Landscape
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE? A) Link_location is required, Friendly_name is required B) Link_location is required, Friendly_name is optional C) Link_location is optional, Friendly_name is required D) Link_location is optional, Friendly_name is optional
Link_location is required, Friendly_name is optional
To apply specific formatting to a sparkline, use the Sparkline Color or the ___________ commands. A) Sparkline Editor B) Sparkline Style C) Marker Color D) Sparkline Developer
Marker Color
Which of the following is NOT one of the options for highlighting certain data points in a Sparkline? A) High point B) Low point C) First point D) Middle point
Middle point
In Excel, the ___________ function calculates the difference between 2 dates excluding weekend days. A) EDATE() B) DATEVALUE() C) EOMONTH() D) NETWORKDAYS()
NETWORKDAYS()
Which of the following options would you choose if you want to remove all manual page breaks in the worksheet? A) Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks B) Page Layout (tab) -> Page Setup (group) -> Orientation -> Reset All Page Breaks C) Page Layout (tab) -> Page Setup (group) -> Resize -> Reset All Page Breaks D) Page Layout (tab) -> Scale to Fit (group) -> Breaks -> Reset All Page Breaks
Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks
If you need to take a printout of a report, how can you specify the paper size you re using? A) Page Layout (tab) -> Page Setup (group) -> Breaks B) Page Layout (tab) -> Page Setup (group) -> Background C) Page Layout (tab) -> Page Setup (group) -> Size D) None of the above
Page Layout (tab) -> Page Setup (group) -> Size
Which of the following is TRUE about Web queries? A) Performing Web queries creates a new HTML file B) Performing Web queries copies information from an HTML file into the current workbook C) Web queries are not useful in Excel because an external data can change D) None of the options provided is true
Performing Web queries copies information from an HTML file into the current workbook
What is the general formula to calculate compound interest (i.e., the future value of an investment that includes both Principal and interest)? A) Principal * (1 + periodic rate) ^ number of periods B) Principal * (1 + periodic rate) ^ 10 C) Principal * (1 + 2) ^ number of periods D) Principal * (periodic rate) ^ number of periods
Principal * (1 + periodic rate) ^ number of periods
Why would we use Sparkline charts (instead of regular charts or some other Excel feature)? A) Quickly spot time-based trends or variations in data in a very compact manner B) Better visualization of data C) It has become the default chart option since Excel 2016 D) Compare sales amounts in different years
Quickly spot time-based trends or variations in data in a very compact manner
Which function returns the periodic interest rate of a loan, given the number of payment periods, the periodic payment amount, and the loan amount? A) CUMIPMT() B) RATE() C) PMT() D) NPER()
RATE()
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()? A) Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify. B) It refers to the Visual Depreciation Barchart method, and uses a bar chart to display the falling amounts of asset values C) This method of calculating depreciation uses random numbers to fairly distribute depreciation over the lifetime of an asset D) None of the above
Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.
The function ______________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location. A) FIND_AND_REPLACE() B) SUBSTITUTE() C) FIND() D) SEARCH()
SUBSTITUTE()
The last step in the Query Wizard, "Finish", lets you do which of the following? A) Save a query so that you can reuse it B) Determine the order of sorting the output C) Choose hyperlinks that will appear for the query D) Decide whether the query will use relative or absolute cell references
Save a query so that you can reuse it
In the Share tab of Backstage View, which of the options is available? A) Enable Google Indexing B) Collaborate in Real-time C) Publish to Word Services D) Send using E-mail
Send using E-mail
There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Markers. What is its purpose? A) Show data markers in the Sparkline B) Show letter markers in the Sparkline C) Show no markers in the Sparkline D) Apply a different color to negative values in the Sparkline
Show data markers in the Sparkline
After you create a Sparkline, changing color is easy. Use the controls in the _______________. A) Sparkline Tools -> Design (tab) -> Type (group) B) Sparkline Tools -> Design (tab) -> Style (group) C) Sparkline Tools -> Design (tab) -> Color (group) D) Sparkline Tools -> Design (tab) -> Font (group)
Sparkline Tools -> Design (tab) -> Style (group)
When importing a text file into Excel, the _____________ helps specify how the data will be imported. A) Text Import Wizard B) External Data Wizard C) CSV Wizard D) Text Preview Panel
Text Import Wizard
While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result? A) The contents of cell A1 do not comprise four digits B) An error is returned in all cases C) Presence of a symbol such as . Instead of a : in the time, e.g., 16.24 (instead of 16:24) D) The presence of the character ':' (colon) in the time, e.g., 15:25
The contents of cell A1 do not comprise four digits
Which of the following returns a #REF! error value? A) MS Excel cannot recognize the text in the formula (it may be written in a foreign language) B) The formula contains an invalid cell reference C) Calculation doesn't include all data D) None of the options provided is correct
The formula contains an invalid cell reference
If a pivot chart is created and the underlying table is deleted, what occurs? A) The pivot chart is also deleted. B) The pivot chart remains. C) The data the Chart is based on is invalid since the Pivot Table has vanished. D) The chart is not deleted, but it no longer contains the data from the underlying table.
The pivot chart remains.
_______ lookup functions will look up a value in the first column of the lookup table (or range) and return a corresponding value from the specified table column (within the same row). A) Vertical B) Horizontal C) if else D) Table display
Vertical
What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule) A) When the first date is changed, the others update automatically B) When the first date is changed, the others are not affected C) A formula cannot be used to create a series of dates D) Autofill uses a formula, so these two options are equivalent
When the first date is changed, the others update automatically
What is the use of the following data validation formula: =A2>A1 ? A) accepts values for cell A2 that are greater than the value of cell A1 B) accepts values for cell A2 that are lesser than the value of cell A1 C) accepts values for cell A2 that are lesser than the value of cell A5 D) accepts values for cell A2 that are lesser than the value of cell A8
accepts values for cell A2 that are greater than the value of cell A1
The Linked Picture paste option in Excel ________. A) allows you to link to a clip-art, instead of saving it within your workbook B) is a dynamic / live picture of the range you copy C) Both options (A) and (B) D) is primarily used with WordArt
allows you to link to a clip-art, instead of saving it within your workbook
By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data _____. A)appears in the Sparkline B) does not appear in the Sparkline C) sometimes appears in the Sparkline D) is changed to unhidden data
does not appear in the Sparkline
When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set. A) remains unchanged B) is updated C) needs to be manually reset D) usually displays an #ERR message
is updated
What happens when you click the icon in a Slicer's upper-right corner? A) it adds new filtering B) it removes the effects of filtering C) it copies the filtering conditions D) the slicer window closes
it removes the effects of filtering
What is the value to be found in the first row of the table while using the HLOOKUP function? A) index of row B) range lookup C) lookup value D) table array
lookup value
A _________ is a graphical data representation of data displayed in a pivot table. A) pivot chart B) reverse pivot pie chart C) slicer D) sparkline
pivot chart
While using web queries Rich text formatting is typically used if we wish to ______. A) preserve cell formulas B) reproduce web page data in a format close to that of the original C) return none of the formatting D) return hyperlink formatting
reproduce web page data in a format close to that of the original
A date filter in Pivot Tables is used to ____________. A) return the date of the transaction B) specify a time period for which you need the data C) change the date of a transaction (e.g., a sale) D) modify the date in your underlying table
specify a time period for which you need the data
The =TODAY() function is used: A) to insert the current date which is updated each time a workbook is opened or recalculated B) only on Windows PCs, because we do not have access to the iDAY() function available on Apple Macs C) to find the news events for the current day from the Excel website D) All of the answers provided are correct
to insert the current date which is updated each time a workbook is opened or recalculated
The data source for a pivot table can reside in a _________ or in a / an ____________. A) worksheet, external data file B) database, lookup list C) bar chart, lookup list D) database, textbox
worksheet, external data file
