MIS 111 LAB Problem Sets 1-13 Q&A's
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) ":" (colon) ";" (semi colon) " " (space)
"," (comma)
Assume the "range_lookup" parameter in the 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 characters is VALID for use in an Excel workbook file name? Correct Response _ : " \
-
Which of the following is a VALID format for a URL query string in Excel? ---URL---?<Val>=<Name> ---URL---?<Name>=<Val> ---URL---<Name>=<Val>? ---URL---?name?value
---URL---?<Name>=<Val>
Which of the following is the file format of the Excel 97-2003 workbook? .xls .xlsx .xlsb .xltm
.xls
By default, what is the extension used when you save a workbook in Excel 2016? .xlsx .xslx .xls .xsl
.xlsx
Please refer to the attached workbook. What is the total new deposit amount on Sep - 08 in North County branch? 139196 62787 196188 77674
196188
For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters. 24 26 255 125
255
What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000? 6750 4750 4500 500000
4500
What would you type in the Find and Replace dialogue box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2? 5?2 5??2 52? *2
5??2
Please see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed? =COUNTIF(H13:H19)=1 =COUNT($H$13:$H$19,H19) =COUNTIF($H$13:$H$19)=1 =COUNTIF($H$13:$H$19,H19)=1
=COUNTIF($H$13:$H$19,H19)=1
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) =DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2)) =DATE(RIGHT(A1,4), MID(A1, 5, 2), LEFT(A1,2)) =DATE(LEFT(A1,2), MID(A1, 3, 2), RIGHT(A1,2)) =DATE(RIGHT(A1,2), MID(A1, 3, 2), LEFT(A1,2))
=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))
Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits? =FV(3.5%/12,12,-100,,1) =FV(3.5%/12,12,100,,1) =FV(3.5%,1,-100,,1) None of the above
=FV(3.5%/12,12,-100,,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)
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 of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2? Cell C8 is the wrong cell to calculate the Quarter 2 sales total =SUM(B5:E5) =SUM(B4:B7) =SUM(C4:C7)
=SUM(C4:C7)
Please look at the attached workbook. The worksheet contains a table of student data (starting in row 13) named as "Students". If you were to use a lookup formula to get the exact value for salary field in row 2, which of the following formulas would you use? =VLOOKUP(C2,Students,4,FALSE) =VLOOKUP(C7,Students,4,FALSE) =VLOOKUP(C2,Students,3,FALSE) =VLOOKUP(C5,employee,4,FALSE)
=VLOOKUP(C2,Students,4,FALSE)
Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet? ?, * *, / ?, / ?, ^
?,*
Which of the following statements is true? -A workbook can have at most 3 worksheets. -A worksheet can have many workbooks. -A workbook can have many worksheets. -A worksheet can have at most 3 workbooks.
A workbook can have many worksheets.
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 is 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.
Automatic grouping of Pivot Table items is possible when a field contains which of the following types of values? Numbers Dates Times All of the answers provided are correct
All of the answers provided are correct
There is a type of investment in which you make a stream of fixed payments (e.g., as a series of investment deposits into an account). What is this type of investment called? Annuity Loan Payment Interest
Annuity
In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively? Format Cells Quick Access toolbar Autofill =SUM() formula
Autofill
Please refer to the attached workbook. When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill? Top 5 Below average Top 7 Icon set
Below average
Which of the following is the default font type and font size in Excel 2016? Calibri, 11 point Arial, 12 point Times New Roman, 10 point Cambria, 14 point
Calibri, 11 point
A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following? entity component element cell
Cell
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
What is NOT true about Autocorrect? 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 is TRUE regarding the HTML files that Excel creates? Excel cannot open an HTML file. Formulas and charts in an HTML file created from an Excel workbook can be edited. Changes to graphs and formulas cannot be made to an HTML file created from Excel. In the HTML files, formulas can be edited, but the charts appear as static graphic images.
Changes to graphs and formulas cannot be made to an HTML file created from Excel.
Which of the following actions should be taken while creating a table to display Table Headers? Uncheck 'include names' Check 'My table has headers' Check 'Display headers' Uncheck 'Hide table headers'
Check 'My table has headers'
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a decimal number within limits? Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum. Home (tab) -> Editing (group) -> Find & Select -> Data Validation Formulas (tab) -> Calculation (group) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length'.
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum.
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length? Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than'. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. Home (tab) -> Editing (group) -> Find & Select -> Data Validation
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.
In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query. columns records rows sort order
Columns
The Pivot Table Field List shows which of the following? Columns from the source data only Columns and Rows from the source data Columns from the source data and Groups defined in the Pivot Table Names of available charts
Columns from the source data and Groups defined in the Pivot Table
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? Correct Response -Ctrl + N -Ctrl + E -Ctrl + W -Ctrl + B
Ctrl + N
Which of the following keyboard shortcuts can be used to activate a different sheet? Alt + PageUp Alt + PageDown Ctrl + PageUp Alt + F4
Ctrl + PageUp
How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated? Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning Formulas (tab) -> Insert function -> Data Validation -> Warning Insert (tab)--> Ink None of the options provided is valid
Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning
Which of the following Excel tabs is NOT visible in a worksheet by default? -Developer -Review -Home -Insert
Developer
When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell? Currency formatting is not applied to the new cell. Excel applies currency formatting to the new value in that column cell. A special type of formatting other than currency formatting is applied to the new value. Currency formatting is temporarily applied to the new value (but only persists until the Workbook is closed).
Excel applies currency formatting to the new value in that column cell.
True or false: Excel can only import HTML files from your local computer. False, Excel can import HTML files from the local computer or the web. False, Excel can only import HTML files from the web. True, Excel can only import HTML files from your local computer. False, Excel cannot import HTML files.
False, Excel can import HTML files from the local computer or the web.
The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty. Extract Values Flash Fill Extract Data Data Mapper
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 has a value 'Tucson' for 'City' column. 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 From 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 From the Column to Filter list, select City. In the right panel, select 'ends with' from the first drop-down list and then type 'T' in the second drop-down list box From the Column to Filter list, select Age. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list
From 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
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 Formulas group (on the) Functions Functions group (on the) Insert Calculations group (on the) Formulas
Function Library group (on the) Formulas
What command brings up the format cells tab?
Holding Command+1 down at the same time
Which of the following is a VALID method of hiding rows or columns? Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list) Click on the Row/Column header, (then) right-click and choose Delete Right-click on the cell, (then) choose "Insert" Home (ribbon tab), (then) Paste (in Clipboard group)
Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)
Which function returns the contents of a cell at the intersection of a specified row and column from a range? INDEX() MATCH() LOOKUP() IFCELL()
INDEX()
Which of the following statements is TRUE? The Analysis Toolpak lets you generate random numbers and histograms only, hence not very useful You use the FREQUENCY() function in Excel to find Fourier wave frequencies The main difference between SUMIF() and COUNTIF() is that the former is used with multiple criteria, but the latter (i.e., COUNTIF() ) only allows a single criteria 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
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
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
Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"? It prints only the chart on that sheet It prints the whole worksheet including the chart It asks if you want to print all charts on all worksheets in that Excel workbook It prints all worksheets with data linked to the current chart
It prints only the chart on that sheet
The _________ function is used to return a specific number of letters or characters from the start of a text string. FROMRIGHT() LEFT() MID() SUM()
LEFT()
Which of the following functions provides the correct syntax for the LOOKUP function? LOOKUP(lookup_value,lookup_vector,[result_vector]) LOOKUP(lookup_vector,result_vector) LOOKUP(lookup_value,answers_range) LOOKUP(lookup_value)
LOOKUP(lookup_value,lookup_vector,[result_vector])
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE? Link_location is required, Friendly_name is required Link_location is required, Friendly_name is optional Link_location is optional, Friendly_name is required Link_location is optional, Friendly_name is optional
Link_location is required, Friendly_name is optional
How do you remove a filter in the PivotTable Field List? Move 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. At the top of the window, on the Ribbon, click the Analyze tab under PivotTable Tools. In the Actions group, click Clear, and then click: Clear Filters. Right Click on a cell and select: Remove Filter from PivotTable Field List. Use the Grand Totals feature from the Pivot Tables "Design" tab.
Move 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.
Which of the following is NOT an advantage of using defined range names to work with ranges in Excel? Entering a name is less error-prone than entering a range address Formulas become more understandable Creating formulas is easier Moving to areas of the worksheet is controlled
Moving to areas of the worksheet is controlled
Must a chart be embedded in the same worksheet as its data source? Yes, this is a limitation of Microsoft Excel but leads to easier linking between charts and the data. Yes, and this holds true for graphs as well. No, unless the chart is a histogram. No, a chart an be embedded or placed in a sheet separate from that of its data source.
No, a chart an be embedded or placed in a sheet separate from that of its data source.
Can you print data in an Excel Form attached to a table? No, you cannot Yes, you can always perform an action like Printing in Excel You can print forms with the "Print" button on the Form only Printing is disabled unless you buy the "Print Form" Excel Professional feature app from Microsoft
No, you cannot
Which of the following is FALSE about Pivot Table Slicers? Slicers can be moved and resized. The effects of filtering by a particular slicer can be removed. To use a Slicer to filter data in a Pivot Table, you can click one of the slicer value buttons. None of the answers provided is false
None of the answers provided is false
Please see the attached workbook. What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.i., value looked up) of 1001 and a range-lookup parameter of FALSE? 3% 6% 12% None of the other answers are correct
None of the other answers are correct
Which of the following types of data may NOT be contained in a cell in an Excel worksheet? objects formulas text numeric
Objects
What command under the Table Tool /Design Tab can you use to add two more rows to the table? Resize table Change rows Add rows Add Record
Resize table
______ locates a substring in another text string and returns the start position of the substring. SEARCH() COUNTPOSITION() DEADLOCK() SUBSTRING()
SEARCH()
What will the result of the following formula be? ="Section " &mysection Section B #ERROR "Section" & "mysection" Section mysection
Section B
In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. select a row select a column select a range select multiple rows or columns
Select a column
In Excel, Shift + Spacebar (keyboard shortcut) is used to _______. select a row select a column select a range select multiple rows or columns
Select a row
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 C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
Please see the attached workbook image. What sequence of steps will allow you to format the data into a table? Select data, then click "Ctrl + 1" and choose the "Table Creation"option Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon Select data, then click on conditional formatting command in Excel Select data, then click the "Convert to Table" option under the Table Tools / Design tab
Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon
What does selecting the option "Current region" within the: Go To Special Dialogue Box (Home (tab) -> Editing (command group) -> Find & Select) do? Selects the entire array Selects all graphic objects on the worksheet Selects a rectangular range of cells around the active cell Selects all empty cells
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: __________. Ctrl+Alt Shift+F2 F5 Ctrl+A
Shift+F2
Highlight Cell Rules and Top/Bottom Rules use a single type of formatting highlight _________ cell(s) based on the condition or requirement. Pivot Specific No A single/one
Specific
The __________ function will be used as part of a formula to calculate how many days old you are today. YESTERDAY() TODAY() BIRTHDAY() HOWOLDAMI()
TODAY()
When do you get a "#NAME?" error value? The wrong argument is used The wrong operand is used Text in a formula is not enclosed in quotation marks A nonnumeric argument is passed to a function when a numeric argument is expected
Text in a formula is not enclosed in quotation marks
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
Please refer to the attached workbook. What would you do if you want to filter records in the report so that you get information only for the state of AZ? To filter the report, click the arrow next to Row Labels and check the 'AZ' option from the menu that appears. To filter the report, click the arrow next to Row Labels and check the 'CA' option from the menu that appears. To filter the report, click the arrow next to Row Labels and check the 'select all' option from the menu that appears. None of the answers provided is valid
To filter the report, click the arrow next to Row Labels and check the 'AZ' option from the menu that appears.
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
To find a working date that falls after a number of days (e.g., 12 business days after some date), you typically use this function: WEEKDAY() TODAY() WORKDAY() NETWORKDAY()
WORKDAY()
What of the following is FALSE about filtering a table? You can filter by multiple values in a column by using multiple check marks. You can filter a table using any number of columns. You can choose "Text Filters" or "Number Filters" to display only the rows you are interested in. When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.
When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.
When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows Wide Narrow Transformed Empty
Wide
Is it possible to change the direction of the bars in the DATA BARS conditional formatting? Maybe Feature not available in Excel 2016 No Yes
Yes
Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate? Yes, the statement is accurate No, this is a false statement The statement is accurate only for the first 7 styles previewed. After that, to conserve memory, Excel will apply a style and you can start the preview process over again. Yes, previews are possible as long as the Total Row option is not checked
Yes, the statement is accurate
What characters are NOT allowed in sheet names ? Correct Response [ ] , ! All options are correct
[ ]
If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________. a series of hash (#) marks a #VALUE! Error a series of question (?) marks a #REF! error
a series of hash (#) marks
Which of the following types (chart types) is a VALID option in the Charts ribbon group on the Insert ribbon tab? all of the options provided are valid area scatter column
all of the options provided are valid
Which of the following function(s) can be used to count error values in a range? all of the provided choices are valid ISERROR() ISERR() ISNA()
all of the provided choices are valid
A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______. also changes remains unchanged shows a sparkline graphic to highlight the change can be eliminated to avoid duplicates
also changes
What does the WEEKDAY function return when you provide a date as an argument? an integer between 1 and 7 an alphabetical representation of the day of the week, e.g., "MON" / "TUE an integer between 0 and 7 either 0 or 1
an integer between 1 and 7
Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ . function signature dependent variables cell references arguments
arguments
Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you cannot sum non-numbers, this technique involves ____________. rounding-off counting subtracting division
counting
If you click outside of the layout area (of a PivotTable report), the PivotTable Field List ________. increases in size goes away decreases in size turns blue
goes away
A chart in an Excel worksheet provides a visual representation of which of the following? numeric values formulae text values objects
numeric values
A _____________ data table shows the results of any number of calculations for different values of a single input cell. For example, using a single loan amount, you can calculate a table of monthly payments each for a different interest rate. one-way two-way three-way None of the above
one-way
Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do? pastes values and number formatting paste values in color pastes values with a percentage formatting pastes contents with values and formatting
pastes values and number formatting
Data retrieved from Web Queries do not include __________ and contents of __________. text, data text, pictures pictures, scripts text, tables
pictures, scripts
A _________ is a graphical data representation of data displayed in a pivot table. pivot chart reverse pivot pie chart slicer sparkline
pivot chart
A group of cells is referred to as which of the following? group range pack class
range
The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ________?
range (and) criteria
Which one of the following is NOT one of the VLOOKUP function arguments? lookup_value table_array col_index_num row_index_num
row_index_num
What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? to prevent blank cells in the copied range from overwriting existing data to skip pasting content into the blank cells to copy the blank cells from the copied range into the paste area None of the above
to prevent blank cells in the copied range from overwriting existing data
Please see the attached workbook. The formula in B1 which is copied down the column is "=TRIM(A1)=A1". The formula returns false for (B3) because the text in that cell contains? leading space trailing space multiple spaces between 2 words all of the options provided are correct
trailing space
Which of the following is NOT a method of creating frequency distributions?
use the Review (Proofing) tool of Spelling Error Frequency per Worksheet