mis 112 final
What does Excel display when you enter the formula =DATE(2014,13,1)? "1/1/2015" "1/1/2014" "1/31/2014" "1/31/2015"
"1/1/2015"
Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned? #REF! Excel returns a "No value was found" error message #N/A An approximate match is returned
#N/A
Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected. #NUM! #REF! #N/A #NAME?
#NUM!
What is the file format (file extension) used when you save an Excel workbook as a single file webpage? .html .mht or .mhtml .xlsx .txt
.mht or .mhtml
What value should be given to the 'match_type' argument in the MATCH function if you want the MATCH function to find the first value exactly equal to lookup_value? 0 1 -1 *
0
If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format) 9/20/2014 1:00:00pm 9/19/2014 1:00:00am 9/19/2014 1:00:00pm 9/20/2014 1:00:00am
9/20/2014 1:00:00am
Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years. =6500+EXP(4.55%*4) =6500*EXP(4.55%*4) =6500/EXP(4.55%*4) =6500+EXP(4.55%+4)
=6500*EXP(4.55%*4)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1? =A1>=$A$1 =A1>=A1 =A2<=$A$1 =A2>=$A1
=A1>=$A$1
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive? =HYPERLINK("C:\TEST\example.xslx"B5) =HYPERLINK("C:\TEST\example",B5) =HYPERLINK("C:\TEST\example.xslx",B5) =HYPERLINK("C:\example.xslx",B5)
=HYPERLINK("C:\TEST\example.xslx",B5)
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. =HYPERLINK("[Example.xlsx]E2", "Hello") =HYPERLINK("[Example1]E2", "Hello") =HYPERLINK("[Example.xlsx]E12", "Hello") =HYPERLINK("[Example.xlsx]E2", "")
=HYPERLINK("[Example.xlsx]E2", "Hello")
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". =HYPERLINK("[Example.xlsx]E2", "Quarterly Profit Report") =HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") =HYPERLINK("[Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") =HYPERLINK("QrtlyProfits", "Quarterly Profit Report")
=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report")
Create a new workbook and enter a date in A1 that is NOT a weekend. Which of the following formulas will generate a series of weekdays only (no Saturdays or Sundays)? =IF(WEEKDAY(A1)=6,A1+3,A1+1) =IF(WEEKDAY(A1)=6,A1+4,A1+2) =IF(WEEKDAY(A1)=6,A1+4) =IF(WEEKDAY(A1)=6,A1+3)
=IF(WEEKDAY(A1)=6,A1+3,A1+1)
Converting a string (e.g., an email address) to purely lowercase, can be done using which of the following functions? =CONVERTCASE() =LOWER() =CASECHANGE() =EMAIL()
=LOWER()
Which Excel function returns the serial number of current date and time? =TODAY() =NOW() =DAY() =DATE()
=NOW()
To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use? =NOBLANKS() =SPLICE() =PROPER() =TRIM()
=TRIM()
Suppose you have a column of file names such as: 001-Chapter1.txt, ..., 015-Chapter15.txt, ..., 104-Chapter104.txt, etc.You want to use a formula to consistently remove the prefix numbers, e.g., make the file names into: Chapter1.txt or Chapter12.txt. Which of the following text functions can you use to make this change? A combination of: TRIM(), SUBSTITUTE(), REPT() A combination of: FIND(), INDEX(), CHAPTER() A combination of: FIND(), LEN(), MID() A combination of: REPT(), PREFIX(), REMOVE()
A combination of: FIND(), LEN(), MID()
What does it mean when Excel displays the following error? #DIV/0! A number is divided by zero! A number is divided by infinity! unrecognized text none of the options provided is correct
A number is divided by zero!
Which of the following are TRUE in the context of using VLOOKUP? You use a range or table with data in columns; the lookup_value is found in the leftmost column of the lookup table The displayed result comes from a column number specified as a parameter You can indicate whether the matches are approximate or exact All answers provided are correct
All answers provided are correct
Which of the following actions can be performed on objects placed on the drawing layer with no effect on any other elements in the worksheet? move resize delete All of the options provided are correct
All of the options provided are correct
Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Style: allows you to change formatting rules for a color scale. Which of the following formatting options are available? 2-color scale, but not 3-Color Scale 3-Color Scale but not Data Bar Data Bar, but not 2-Color Scale All options: 2-Color Scale, 3-Color Scale and Data Bar are available
All options: 2-Color Scale, 3-Color Scale and Data Bar are available
Which of the following file formats can be opened using Excel 2016? All the other options are correct XLS XLT XLA
All the other options are correct
Assume you have a shape (e.g., a rectangle) on your worksheet. Then, you can use the: "Drawing Tools -> Format -> WordArt Styles group" for which of the following? Convert the shape into a chart Change the shape from a rectangle to a circle Alter text formatting within the shape Insert a Sparkline graphic within a shape
Alter text formatting within the shape
Which of the following is TRUE about icon sets? You can supply your own icons. Excel provides ten icon sets to choose from. An icon can be used to display a trend graphically. Arrow-style icons are not available for use, but bow-shaped icons are.
An icon can be used to display a trend graphically.
Which of the following concepts are useful in understanding relationships between cells and formulas? Cell Credits Blue-on-Green Precedents Cell Dependents The One-Time Rule
Cell Dependents
If your worksheet is a bit too wide to print on a single page, but you want to shrink it to fit on a single printed page, which of the following should you choose? Change the print orientation from Landscape to Portrait Print on A4 paper instead of Letter paper Choose the print scaling option of "Fit All Columns on One Page" All of the above will work
Choose the print scaling option of "Fit All Columns on One Page"
What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells? Alter Rulebook / edit Alt-F4 Clear Rules New Rule Clear All
Clear Rules
_____________ interest refers to an interest calculation where interest is credited to the investment balance (and the investment then further earns interest on the interest credited). Simple Compound Future value None of the above
Compound
Which of the following is NOT one of the buttons in the Spelling dialog box in Excel? Ignore All Add to Dictionary Autocorrect Conditional Formatting
Conditional Formatting
What is the shortcut key to create hyperlink when you select an object or a cell? Ctrl+H Ctrl+I Ctrl+J Ctrl+K
Ctrl+K
Which key combination can a user press to toggle between formula view and normal view within Excel? Shift-Tab Shift+` Ctrl+` Ctrl-Tab
Ctrl+`
From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a date such that it falls on a Sunday? Custom Date Time Text length
Custom
If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views. Design Views Tailor Views Custom Views Redesign Views
Custom Views
If you are trying to display trends with a Sparkline, you may have to work with irregular time periods in the underlying data. To properly do so, you can click on the Group (command group), then click on "Axis", and choose _________ to format the shape of the chart. Date Axis Type Date Value Type Character Axis Type Find and Replace
Date Axis Type
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: &[Path]&[File] ? Displays the workbook s complete path Displays the workbook s complete path and filename Displays the workbook s filename None of the above
Displays the workbook s complete path and filename
Which older version of Excel file can Excel 2016 NO LONGER open? Excel can open all of these files. Excel 4 Excel 97 Excel 2000
Excel can open all of these files.
To calculate the future value of an investment based on periodic constant payments and a constant interest rate, you use this function: PMT() CALCFUTURE() FV() MORT()
FV()
Assuming you have a report that contains multiple products from XYZ company. What Pivot table feature would you use to see the data of any one product? Filter Find & Replace Conditional Deletion High Low Close charts
Filter
Which of the followings steps are to be followed in order to identify the cell that contains an error? Formulas -> Formula Auditing -> Error Checking -> Trace Error Formulas -> Formula Auditing -> Remove Arrows Formulas -> Formula Auditing -> Show Formulas Formulas -> Formula Auditing -> Evaluate Formula
Formulas -> Formula Auditing -> Error Checking -> Trace Error
To print a worksheet in a Landscape orientation, you: Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape You must create a Table in Excel that is Landscape shaped (i.e., width is greater than height) to use this feature. Right Click on any cell in the worksheet and select Print -> Landscape Both A and C
Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape
Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query? Tables to select from an external database Criteria to select specific records from a table Sorting of results from a table Graphing of data from tables
Graphing of data from tables
If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline. Blank Vertical Diagonal Horizontal
Horizontal
When (or why) does Excel draw arrows to indicate an error source? For finding dependencies between two images pasted in different worksheets In Tracing Error Values Error-checking the sheet Background Because Functions Must Have Arrows
In Tracing Error Values
Which of the following is one of the ways to create a Pivot Chart? Insert (tab) -> Charts (group) -> PivotChart Insert (tab) -> Sparklines (group) -> PivotChart Insert (tab) -> Tables (group) -> PivotChart Insert (tab) -> Illustrations (group) -> Shapes -> PivotChart
Insert (tab) -> Charts (group) -> PivotChart
Which of the following best explains the MATCH function? It returns 'True' if an item is present in an array It returns 'True' if an item is present in any order in an array It returns the relative position of an item in an array 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 function returns the periodic interest rate of a loan, given the number of payment periods, the periodic payment amount, and the loan amount? CUMIPMT() RATE() PMT() NPER()
NPER()
Which function is used to calculate depreciation in Excel using the straight line method? DB() DDB() SYD() None of the above
None of the above
Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)? June 18 2014 Jun-18 2014 Jun-18/20 None of the answers provided is correct
None of the answers provided is correct
______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox. Unchecked All Checked None of the options provided is valid
None of the options provided is valid
How do you clear/remove a Print Area that has already been defined on the worksheet? On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Custom Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area. None of the above
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area.
How can you define a Print Area after selecting the required cells on the worksheet? On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Custom Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area. Press the keyboard shortcut of: Ctrl+!
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.
If you need to take a printout of a report, how can you specify the paper size you re using? Page Layout (tab) -> Page Setup (group) -> Breaks Page Layout (tab) -> Page Setup (group) -> Background Page Layout (tab) -> Page Setup (group) -> Size None of the above
Page Layout (tab) -> Page Setup (group) -> Size
Which of the following is TRUE about Web queries? Performing Web queries creates a new HTML file Performing Web queries copies information from an HTML file into the current workbook Web queries are not useful in Excel because an external data can change None of the options provided is true
Performing Web queries copies information from an HTML file into the current workbook
What is the syntax for the RATE() function? RATE(nper,[fv],[type],[guess]) RATE(nper,pmt,pv,[fv],[type],[guess]) RATE(rate,nper,pmt,pv,[fv],[type]) RATE(nper,pmt,pv,[guess])
RATE(nper,pmt,pv,[fv],[type],[guess])
Which of the following is NOT a summary function for Pivot tables? StdDev Varp Var Regression
Regression
When you have several drawings on a worksheet, you may want to align or evenly space these objects. What is the best way to do it? Drag the objects and adjust spacing with your mouse and with the aid of a ruler Use Navigation arrow keys to move the selected object, one pixel at a time Select the appropriate command from: Drawing tools -> Format (tab) -> Arrange (command group) -> Align (drop-down list) This is not possible in Excel, instead you must use Photoshop to achieve such effects
Select the appropriate command from: Drawing tools -> Format (tab) -> Arrange (command group) -> Align (drop-down list)
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? Show data markers in the Sparkline Show letter markers in the Sparkline Show no markers in the Sparkline Apply a different color to negative values in the Sparkline
Show data markers in the Sparkline
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list? XML data Single File Web Page Web Page Excel Binary Workbook
Single File Web Page
______ is an interactive control that makes it easy to filter data in a pivot table. Field Slicer Row None of the answers provided is correct
Slicer
If you want to use commands to customize Sparklines to highlight certain parts of the data, which of the following options would you select? Sparkline Tools -> Design (tab) -> Type (group) Sparkline Tools -> Design (tab) -> Show (group) Sparkline Tools -> Design (tab) -> Design (group) Sparkline Tools -> Design (tab) -> Draw (group)
Sparkline Tools -> Design (tab) -> Show (group)
The ____ is a function that will allow you to display a value (after conversion to a string) in a specific format, for example with a thousands comma separator and a currency ($) symbol. TEXT() SPECIFIC() CUSTOMFORMAT() FMT()
TEXT()
What function removes leading and trailing spaces from a cell? TRIM CUT COMPACT CLEAN
TRIM
Which of the following returns a #REF! error value? MS Excel cannot recognize the text in the formula (it may be written in a foreign language) The formula contains an invalid cell reference Calculation doesn't include all data None of the options provided is correct
The formula contains an invalid cell reference
What is NOT correct about headers and footers? The header appears at the bottom of each page while the footer appears at the top of each page. The footer appears at the bottom of each page while the header appears at the top of each page. The headers and footers consist of three sections: left, center, right. The headers and footers can be specified using the Header/Footer tab of the Page Setup dialog box.
The header appears at the bottom of each page while the footer appears at the top of each page.
If a pivot chart is created and the underlying table is deleted, what occurs? The pivot chart is also deleted. The pivot chart remains. The data the Chart is based on is invalid since the Pivot Table has vanished. The chart is not deleted, but it no longer contains the data from the underlying table.
The pivot chart remains.
In Excel when calculating loan information, what does the PV() function return? The time-adjusted value of the original loan amount The interest part of a loan amount for a single period The periodic interest part of a loan None of the above
The time-adjusted value of the original loan amount
When working with data returned from a Query Wizard, refreshing a query occasionally produces undesired results. If this occurs, you can use the _____ button to reverse the change. In other words to "un-refresh" the data. Unrefresh Cycle Undo Cancel
Undo
Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'? Use a formula: =LEFT(A1)="a" Use a formula: =RIGHT(A1)='a' Use a formula: =UPPER(A1)='A' None of the options provided is valid
Use a formula: =LEFT(A1)="a"
Which is NOT a feasible option to prevent cell contents from being printed? Hide rows or columns. Hide cells or ranges. Mask an area of a worksheet by covering it with a rectangle Shape. Use the custom number format that consists of two colons.
Use the custom number format that consists of two colons.
Which function will you use to calculate depreciation for multiple periods in Excel? VDB() DEPREC() CONCATENATE() MULTIDEPREC()
VDB()
You ask your customers to provide you with inventory usage data to help you plan your production schedule. Which of the following file formats will you ask them to provide data in (so that data can be easily imported into Excel)? Microsoft Word XML Microsoft PowerPoint PDF
XML
Which one of the following is NOT a formatting option in the Web Query Options dialog box? None Rich text formatting Full HTML formatting XML formatting
XML formatting
Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true? If you're looking for information in the worksheet, click the Find tab. If you want to replace existing text with new text, use the Replace tab. You can use the Options button to display additional options. You cannot replace a type of formatting in a cell with another type of formatting.
You cannot replace a type of formatting in a cell with another type of formatting.
In Step 2 of the Query Wizard, you specify which data rows should be included in your query output. How is the criteria entered? a criterion for each column you drag and drop the rows you want you use a checkbox to pick criteria criteria are automatically copied from cell $A$1 of your current workbook sheet
a criterion for each column
In Excel, a parameter query is a query that prompts for ________ every time it is run. an input a password an image a fingerprint
an input
While creating a parameterized web query the question mark (?) is used to ______. begin the URL query string delimit the name from the value delimit each name/value pair delimit the parameter from the prompt
begin the URL query string
To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List. clear check fill drag
clear
Input messages and error alerts (for data validation) appear only when ___. a user enters data in the cell by copying or filling data is typed directly into the cells a formula in the cell calculates a result that is not valid a macro enters invalid data in the cell
data is typed directly into the cells
A pivot table calculated field consists of a calculation that involves other _______. fields chart example None of the answers provided is correct
fields
simple interest
investment amount x annual interest rate x investment term (yrs)
What is the value to be found in the first row of the table while using the HLOOKUP function? index of row range lookup lookup value table array
lookup value
By default, Page Breaks __________ displayed in the Normal view of worksheet. are exclusively may or may not be are always None of the above
may or may not be
Consider a Column Sparkline with three columns. A wide area of blank space between two columns in a sparkline corresponds to which of the following? boolean data missing data a right aligned cell a value formatted as currency (not supported by sparklines)
missing data
When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data? prevent Excel from using GETPIVOTDATA function make use of GETPIVOTDATA function add GETPIVOTDATA function to the pivot table enable GETPIVOTDATA function for the pivot table
prevent Excel from using GETPIVOTDATA function
Step 3 of the Query Wizard lets you specify how you want the ______. records to be filtered record selection criteria named records to be sorted query to be saved
records to be sorted
The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date. serial number military style formatted time combination of hours and minutes None of the answers provided is correct
serial number
A Sparkline is best described as a _____. small table small chart typically displayed in a single cell chart displayed in multiple cells chart that displays multiple series of data
small chart typically displayed in a single cell
The real power of data validation becomes apparent when you use data validation formulas. The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, what does it mean? a message box appears that displays an error message the data is considered valid and remains in the cell nothing happens ovals or squares appear around cells that contain incorrect entries.
the data is considered valid and remains in the cell
A _____ data table in Excel shows the result of a single calculation for different values of two input cells multiple one-way two-way None of the above
two-way
What form of LOOKUP is used when you have a large list of values to look up or when the values may change over time? array form static form vector form dynamic form
vector form
Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly. correct, trends visualize, trends extract, macros visualize, conditions
visualize, trends
