MIS 112 Final
Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)?
"," (comma)
The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.
"horizontally, vertically"
Which of the following is the file format of the Excel 97-2003 workbook?
.xls
With a single IF() function, how many actions can you ask Excel to carry out?
2 (i.e., an action if the criteria is true, or an an action if the criteria is false)
The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text
3
Which of the following formulas, when entered in cell B10, will give the average quantity of all the items purchased? =AVERAGE(B2:B8) =AVG(B2:B8) =AVERAGE(C2:C8) =AVG(C2:C8)
=AVERAGE(B2:B8)
Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters? =COUNTIF(B2:B23,"6*?") =COUNTIF(B2:B23,"{6?}") =COUNTIF(B2:B23,"??") =COUNTIF(B2:B23,"??????")
=COUNTIF(B2:B23,"??????")
Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose names begin with S?
=COUNTIF(B2:B23,"S*")
Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)? =COUNTIF(E2:E8, 0) =SUMIF(E2:E8, 0) =COUNTIF(E2:E8, Discount=0) =SUMIF(E2:E8, Discount=0)
=COUNTIF(E2:E8, 0)
Which formula can be used to find: How many songs were sung by Madonna and were three to four minutes long (inclusive)? =COUNTIFS(B2:B23,"Madonna",D2:D23,"<4",D2:D23,">3") =COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3") =COUNTIFS(B2:B23,'Madonna',D2:D23,'<=4',D2:D23,'>=3') =COUNTIFS(B2:B23,"Madonna",D2:D23,<=5,D2:D23,>=3)
=COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3")
Which of the following IF() functions evaluate as being TRUE?
=IF(B10>100,"true","false")
We want to display in cells H2 to H8 the names of the items whose quantity purchased is greater than the Average quantity reported in cell B10. If the quantity purchased is equal or lower, we want to display 0. Which formula, entered in H2 and extended to H8, will achieve this? =IF(B2>$B$10, A2,0) =IF(B2>B10, A2,0) =IFERROR(B2>B10, A2,0) =IFERROR(B2>$B$10, A2,0)
=IF(B2>$B$10, A2,0)
The grade of the student should be "Pass" if his/her score is greater than 65. Otherwise, the grade should be "Fail". Which of the following formulas could you use for this scenario?
=IF(D2>65,"Pass","Fail")
If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?
=PROPER(A1)
What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?
=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY
=WEEKDAY(A1)=1
Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?
?NULL?
You are reviewing a Spreadsheet that records daily sales for Burgers, Hotdogs and Chili. Monday of last week shows exceptionally high sales for Burgers. You want to email the spreadsheet back to your assistant so he can look into whether the data was correctly entered. Which of the following methods should you use to explain your request to your assistant?
Add a comment to the sales figure cell, and explain to your assistant what you want him to do
Which of the following is one of the conditional formatting rules? Highlight Cell Rules All the other options are correct Data bars Color Scales
All are correct
An Excel formula may return which of the following errors? #REF! #DIV/0! #VALUE! All of the above
All of the above
In context of the MATCH() function, which of the following is FALSE? 1) The syntax of match is: MATCH (lookup_value,lookup_array, row_offset) 2) MATCH returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents 3) A #VAL error value is returned if the MATCH function is unsuccessful in finding a match 4) All of the options are false
All of the options are false
How are some icons hidden in Icon Set conditional formatting?
By choosing "No Cell Icon" in the Edit the Rule Description box.
Which of the following functions can be used to count specific types of errors?
COUNTIF
Which of the following concepts are useful in understanding relationships between cells and formulas?
Cell Dependents
What is NOT true about Autocorrect options? It can automatically correct words with two initial uppercase letters. It can correct the accidental use of the Caps Lock key. It can automatically change incorrect words as you type. Changes made by the Autocorrect feature cannot be undone.
Changes made by the Autocorrect feature cannot be undone.
Which of the following actions should be taken while creating a table to display Table Headers?
Check 'My table has headers'
Which of the following is a way of accessing the "Evaluate Formula" dialog box that helps debug a formula?
Choose Formula ->Formula Auditing ->Evaluate Formula
To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:
Click anywhere on the table
Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers?
Count how many options are under category
Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?
Ctrl + Shift + :
What is the shortcut key to create hyperlink when you select an object or a cell?
Ctrl+K
Which of the following keyboard shortcuts can be used in Excel 2016 to create a table?
Ctrl+L
What format option is used in cell C6 to make the value stand out?
Custom (combination of rules) format
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?
Data --> Data Tools --> Flash Fill
Which of the following Excel tabs is NOT visible in a worksheet by default?
Developer
When you select a cell in the Total Row in a table, a drop-down arrow appears in the cell. Click the arrow and you can select the "Count" formula. What does this formula do?
Displays the number of entries in the column.
Which of the following does the Formula Autocomplete feature in Excel take into account?
Excel built-in functions user-defined functions defined names
While using cell references in Excel, what key is used to change the type of the reference?
F4
Which of the following command sequences will allow you to save a copy of your workbook with a different name?
File, Save as
The Analysis Toolpak can be activated through which sequence of steps?
File-->Options-->Add ins -->Manage Excel Add-Ins --> Go
Which of the following rule types does not apply to color scale formatting?
Format cells differently based on the font styles in each cell.
Which keyboard shortcuts can be used to move the active cell one column to the left?
Left arrow or Shift + Tab
Which of the following provides correct syntax for the MATCH function?
MATCH(lookup_value,lookup_array,match_type)
In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting.
Manage Rules
Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?
Moving to areas of the worksheet is controlled
When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? range lookup lookup_value row_care_param None of the options provided is correct
None of the options provided is correct
How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?
One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab
Which of the following is NOT a reason to name a range of cells? 1) A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems 2) Naming a range of cells (e.g., 'Order_Total', for a range J2:J100) allows us to conveniently re-use the referenced cell range in multiple formulas 3) A named cell, e.g., 'Sales_Revenue' can be used in formulas that require an absolute reference (and will allow copy-pasting without error) 4) The name of a cell (or a range of cells), is often easier to remember than the cell reference itself (e.g., H57)
A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems
Which of the following statements is true?
A workbook can have many worksheets.
In an Excel worksheet, what column name comes directly after Column Z?
AA
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"
Which of the following is an advantage of using Excel tables?
Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas
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 number between the range of 1-250?
Whole Number
You can use a data Form to add, find, and edit rows in a range or table. Is this correct?
Yes, all these are valid form operations
Charts are dynamic, e.g., they change as the underlying data changes. Is this an accurate statement?
Yes, by default they are dynamic.
What characters are NOT allowed in sheet names ?
[ ]
What does the small red triangle on the upper right corner of cell B1 denote?
a comment exists for that cell
When a range of cells is selected, which of the following occurs?
all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded
Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .
arguments
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following?
content other than text (e.g., a date or a number) or a blank cell
Which of the following does the function =NOW() return?
current date and time
A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________
delete a record/row
Which of the following is NOT a type of cell reference?
dynamic
Which of the following is NOT a state an Excel workbook can be in?
expanded
What does the Excel keyboard shortcut Ctrl+Alt+F9 do?
forces a complete recalculation of all formulas in all open workbooks.
What is the use of the following data validation formula: =ISTEXT(A1) ?
forces the cell A1 to accept only text
A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.
letter, number
For a range called Data, what does the function =COUNTIF(Data, "<>0") return?
number of cells not equal to 0
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?
passwords are case sensitive
The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.
range (and) criteria
Which of the following is NOT one of the VLOOKUP function arguments? lookup_value table_array col_index_num row_index_num
row_index_num
In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______.
select a column
The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________
table style, conditional formatting
For VLOOKUP() and HLOOKUP(), the lookup_value argument can include wildcard characters if the lookup_value argument is ______ and the range_lookup argument is ________
text; false
Which of the following chart-related items identifies general trends in your data?
trendline
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.
undo
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?
vector form
Would you be able to apply the "watch window" option in multiple sheets at the same time ?
yes
Adding comments to a cell is available through which tab of the Excel ribbon?
Review
Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ?
Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?
Selects a rectangular range of cells around the active cell
To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.
Shift+F2
Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden?
Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.
While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called ?
Synchronous Scrolling
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.
Table
When importing a text file into Excel, the _____________ helps specify how the data will be imported.
Text Import Wizard
Which of the following options is TRUE about the different lookup functions? 1) The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values. 2) The HLOOKUP() function searches for a value in the first column of a table and returns a value in the same row from a column you specify in a table. 3) The MATCH() function returns one value if a condition you specify is TRUE, and returns another value if the condition is FALSE. 4) The IF() function returns a value (or the reference to a value) from within a table or range.
The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values.
Excel formatting tools are available at the following locations:
The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar
The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted?
The word "budget" can appear anywhere within the cell.
Which function returns the contents of a cell at the intersection of a specified row and column from a range?
INDEX()
Which of the following function(s) can be used to count error values in a range?
ISERROR() ISERR() ISNA()
Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value? ISERROR ISNA ISLOGICAL ISERR
ISLOGICAL
Which of the following statements is TRUE?
If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet
Which of the following is an object that contains the actual data in the chart?
Plot area
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'.
From 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
The HLOOKUP and LOOKUP functions can be found under Formulas (tab) ->_________.
Function Library (command group) -> Lookup & Reference
The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ______ on the _________ tab.
Function Library group (on the) Formulas
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?
Graphing of data from tables
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?
HYPERLINK()
After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?
Home
