MIS 112 MIDTERM REVIEW
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 Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?
#N/A
Examine the attached file: PS1_formulas.xlsx What is the total widget sales for the West Region for the year?
$5,129
Please refer to the figure. The address of the active cell consists of which of the following?
(A1 is Selected) Column letter, Row number
Which of the following statements is true?
A workbook can have many sheets.
Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?
AA
charts_example.xlsx Examine the attached workbook. If you want to remove chart labels, where do you find the options to do so within the Design tab (Chart Tools?)
Add Chart Element -> Data Labels
A formula in Excel can consist of which of the following?
All of the choices provided are valid
What is the keyboard shortcut to clear the formatting in a cell?
Alt -> h -> e -> f
Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the _____.
Arguments
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?
Ctrl + N
Which of the following options scrolls the screen so that the active cell is visible?
Ctrl+Backspace
Which of the following keyboard shortcuts can be used in Excel 2016 to create a table?
Ctrl+L
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
Which of the following statements is true? They all relate to the formula: =IF(OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )
In this formula, the second statement in the OR (i.e. checking for the category ="Soups") is only checked/evaluated if the first part (i.e. checking if category ="Pasta") evaluates to FALSE.
Yous 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
Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do? (% 123)
Pastes values and number formatting
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
Which of the following methods will keep a heading at the top of a visible while you scroll through rows lower down in the worksheet?
use Freeze Panes
Which of the following is NOT a tool in Analysis ToolPak for Excel?
y-Test
DA_Test.xlsx Examine the attached worksheet. Using the Correlation Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the correlation coefficient for Relaxation Therapy and Music Therapy?
0.56
What will be the result of this formula =IF(A<100000,A1*5%,A1*7.5%), if the cell A1 has a value of 90000?
4500
In the attached Excel workbook PS1_Comments_Calculation.xlsx , Which of the following formulas can be used to calculate Sophie's total score in both the exams?
=B4+C4
Formulas_Test_v2.xlsx Examine the attached worksheet. 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)
Advanced_aggregation.xlsx 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,">=3")
IF_formulas.xlsx You may use the attached spreadsheet to answer this question. Which of the following IF() functions evaluate as being TRUE?
=IF(B10>100,"true","false")
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?
=NOW()
Please examine the attached image. Which of the following formulas will calculate the total number of stores across the four states? (Number of stores)
=SUM(B2:B5)
Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question? (Microsoft ticker symbol)
COUNT()
A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?
Cell
Which of the following is an object that contains all other elements in an Excel chart?
Chart area
Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a DESCENDING order, which of the following steps is correct?
Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button
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
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?
Developer
(Sales ($)) Please refer to the figure above. What are the small bars highlighted by the orange ovals known as?
Error bars
While using cell references in Excel, what key is used to change the type of the reference?
F4
(Sales by Region) Please refer to the figure above. What type of chart is shown in the figure?
Line Chart
Can you print data in an Excel form attached to a table?
No, you cannot
A chart in Excel worksheet provides a visual representation of which of the following?
Numeric values
Which of the following is an object that contains the actual data in the chart?
Plot area
tables_moving_tables.xlsx 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
What is the primary purpose of "Protected View?"
Protect you from malware
Please refer to the figure. What Excel element is shown in the red box?
Quick Access toolbar (top left)
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 (center above formula bar)
PS1_Sheets 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
rng_range_names.xlsx Please see the attached workbook. What is the scope of the range name 'months'?
Sheet 1
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
Which of the following is TRUE about sorting a table?
Sorting a table re-arranges the rows based on the contents of a particular column.
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
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
formatting.xlsx Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?
The data stored in the two cells A4 and B4 are identical
A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?
The formula returns an error.
examle_COUNTIF.xlsx Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))
The number of cells containing the mode within the cell range A1-A10
Is it possible to get the data back after deleting it using keyboard?
Yes
Would you be able to apply the "watch window" option in multiple sheets at the same time?
Yes
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ____ and row ___.
letter, number
Fill in the blank: The COUNTIF() function is used for single-criterion counting formula takes two arguments (or parameters) ____and _____.
range (and) criteria
