MIS 111 Lab Final
In an Excel Form, when finding a row by entering search criteria: Will the question mark symbol "?" find or match any single character?
Yes, the "?" symbol is used to find/match a single character
Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet.
#REF!
Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the yea
$24,914
What is total widget sales for the West Region for the year?
$5,129
By default, what is the extension used when you save a workbook in Excel 2016?
.xlsx
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
In the attached spreadsheet, please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total?
14787.45
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
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?
4500
Please refer to the figure. How many worksheets are contained in the workbook shown?
5
What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2?
5??2
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
To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple)
=C20/F8
Use the attached spreadsheet to answer the following question. 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")
Which function returns the relative position (in a range / table) of a cell corresponding to a specified value?
=MATCH()
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?
=NOW()
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)
Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2?
=SUM(C4:C7)
Please see 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)
Which of the following statements is true?
A workbook can have many worksheets.
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
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
Which rule category does the following conditional formatting rule belong to? "Highlight cells containing a value greater than 25"
Highlight Cells rules
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
Which of the following functions looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one-column range (result_vector).
LOOKUP
Which keyboard shortcuts can be used to move the active cell one colunn to the left?
Left arrow or Shift + Tab
Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______
Name box. The number of rows and columns in the selection is displayed here.
Please examine the attached workbook. The chart was created based on data in cells A1 through C6. If new data is added in cells A7 through C7, will the chart include the new data automatically?
No
______ 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.
None of the options provided is valid
Please see the attached workbook. What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.e., value looked up) of 1001 and a range-lookup parameter of FALSE?
None of the other answers are correct
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
What of the following is FALSE about filtering a table?
When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.
Which of the following is the difference between a Pie and Doughnut chart in Excel?
a doughnut chart can display more than one series of data
You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry?
a negative number
If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key)
absolute
Which of the following does the Formula Autocomplete feature in Excel take into account?
all of the options provided for this answer 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
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
Suppose you have a range of cells with numbers and a few blank entries. If you sort the range in ascending order (smallest to largest), where will the blank cells be located after sorting?
at the end
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?
autofill
Which of the following concepts are useful in understanding relationships between cells and formulas?
cell dependents
After you create a chart, which of the following actions can you perform on it?
change the chart type, formatting, and data series
Which of the following option is NOT available after right clicking on an Excel worksheet tab?
changing the font color in the worksheet
In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____.
find what, empty
Examine the attached workbook. To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action?
hold the "Ctrl" key while dragging the chart to the new location
Which of the following chart types does NOT support the Data Table chart element?
treemap
Which of the following is NOT a method of creating frequency distributions?
use the Review (Proofing) tool of Spelling Error Frequency per Worksheet
Which option provided in the View ribbon tab is best to compare two open Excel workbooks simultaneously in Excel 2016?
view side by side
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
An Excel workbook can contain one or more of which of the following?
worksheets
Would you be able to apply the "watch window" option in multiple sheets at the same time ?
yes
Which of the following characters is VALID for use in an Excel workbook file name?
"_"
Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?
AA
Data validation on a cell or range can do which of the following?
All of the options provided are valid
Which of the following is one of the conditional formatting rules?
All the other options are correct
Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel?
All the other options are correct
In the image shown below, identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts.
Chart 1: Column, Chart 2: Bar
Which of the following functions can be used to count specific types of errors?
COUNTIF
There are two charts, Expenses_1 chart represents the data in cells A1:B13 and Expenses_2 chart represents the same data but the missing values are replaced by ZEROS. Which of the following options is the correct set of steps to obtain Expenses_2 chart from Expenses_1 chart?
Chart Tools --> Design --> Data --> Select Data --> Hidden and Empty Cells --> Zero --> OK --> OK
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?
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 of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards?
Ctrl + X
Which of the following options scrolls the screen so that the active cell is visible?
Ctrl+Backspace
What is the shortcut to open the Find and Replace dialog box with the "Find" tab open?
Ctrl+F
What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open?
Ctrl+H
Which of the following shortcuts allows you to enter the same data into multiple selected cells?Example: In a new Excel worksheet select cells A1:B4 and type "MIS111". The text is initially entered into cell A1. What could you do to replicate the same text "MIS111" in all the selected cells?
Ctrl+enter
What is metadata?
Descriptive information about a workbook
Which of the following is a VALID sequence of options for selecting a different chart layout?
Design (from Chart Tools) --> Chart Layouts
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.
Examine the screen-capture from Excel. Which cell is currently active in the figure?
F4
Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?
F9
Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such a Total Row be added to a table?
Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."
For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: What keys do you need to press to select the newly created table so that it can be copied or moved to a different worksheet?
Press Crtl+A
Please refer to the figure. What Excel element is shown in the red box?
Quick Access toolbar
What happens if you try to store a number greater than 15 digits in Excel ?
Replace the digits exceeding 15 with the number zero
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 ?
Toggle or Synchronous Scrolling
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
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu?
Table column above
Please see the attached workbook, Sheet1. What happens when you apply a theme on Sheet1 of the Excel sheet by going to Page -> Layout -> Themes
The theme is applied to all the sheets in the workbook
Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram, and compared it with the histogram in the image, which of the following options best describes what would you see?
Three of the Histogram bins will have a different count
Please refer to the figure. The address of the active cell consists of which of the following?
column letter, row number
Which of the following is NOT one of the buttons in the Spelling dialog box in Excel?
conditional formatting
When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula).
ctrl+shift+enter
Which of the following does the function =NOW() return?
current date and time
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?
developer
A #REF! error occurs in Excel when a formula uses a / an ________ cell reference
invalid
Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on).
layouts
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.
letter, number
Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?
no
Can you print data in an Excel Form attached to a table?
no you cannot
You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE?
only the contents of the cell are removed
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?
passwords are case sensitive
Which of the following is an object that contains the actual data in the chart?
plot area
Adding comments to a cell is available through which tab of the Excel ribbon?
review
Please refer to the figure. What Excel element is shown in the red box?
ribbon
Which of the following is NOT one of the VLOOKUP function arguments?
row_index_num
The HLOOKUP function searches for data in ______.
rows
In Excel, Shift + Spacebar (keyboard shortcut) is used to _______.
select a row
Please see the attached workbook. What is the scope of the range name 'months'?
sheet 1
