MIS 111 Lab Final

The lookup value can include wildcard characters if the lookup_value argument is ______ and range_lookup argument is ________

"text,false"

With a single IF() function, how many actions can you ask Excel to carry out?

(i.e., an action if the criteria is true, or an an action if the criteria is false)

Which of the following is a VALID format for a URL query string in Excel?

---URL---?<Name>=<Val>

What is the file format (file extension) used when you save an Excel workbook as a single file webpage?

.mht or .mhtml

By default, what is the extension used when you save a workbook in Excel 2016?

.xlsx

Please refer to the attached workbook. What is the TOTAL amount of Sales in the west regions of Arizona (AZ) and California (CA)?

2200 and 4100 respectively

What is the minimum number of characters a worksheet can contain?

31

What is the result of the following expressions (formulas), respectively? = (10 - 6) * 10 and = 10 - 6 * 10

40 and -50

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

Examine the attached worksheet. Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe?

=(1-(F6/D6))*100

In the attached Excel workbookPS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams?

=B4 + C4

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

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($H$13:$H$19,H19)=1

Please see the attached workbook. In the Student Rank table, the points column represents the points awarded to students. If the total points that can awarded in the class cannot exceed 350, what data validation setting would you use for cell I19, to make sure that the entry does not exceed the required total of 350?

=SUM($I$13:$I19)<=350

What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the look_up vector, but no entry for a result_vector parameter?

A call from the lookup_vector

Based on the class lecture, which of the following is true about table renaming?

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

Which of the following statements is true?

A workbook can have many worksheets

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?

AA

The SUM() function does which of the following in Excel?

Allows you to total up numbers in a specified range of cells

Which of the following options moves the active cell right one screen?

Alt + PgDn

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

Which of the following functions can be used to count specific types of errors?

COUNTIF

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

Which of the following actions should be taken while creating a table to display Table Headers?

Check 'My table has headers'

What command would you use to import a CSV file?

Choose Data --> Get External Data --> From Text

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

Clear Rules

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

What option in the Table Tools / Design tab removes a table without losing the data in it?

Convert to Range

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 the shortcut key to create hyperlink when you select an object or a cell?

Ctrl + K

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

Ctrl + N

Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?

Ctrl + Shift + :

Which of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards?

Ctrl + X

When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box?

Data (tab) -> Connections (group) -> Properties

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

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?

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?

Excel applies currency formatting to the new value in that column cell.

Examine the screen-capture from Excel. Which cell is currently active in the figure?

F4

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

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty.

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 (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that has 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 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

Please refer to the attached workbook. In Table 1, the Pivot table shows which of the following:

Gender is a Coumn Label

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

HYPERLINK()

To make Data Bars easier to view, we can ________ the numerical values in the cell.

Hide

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

__ lookup will search for a value in top row of a table. Once a match is found in some column, it returns a value from that table and column, where the row is chosen based on a parameter you specify.

Horizontal

Which of the following functions could be used if you want to display "NOT FOUND" when you lookup for an item that is not part of the lookup_vector?

IFERROR

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()

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Input range and Bin range

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________.

Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.

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 is the MOST compelling motivation for using HLOOKUP()?

It usually reduces duplicate data storage

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

Which type of chart does not allow format changes to the Plot area?

None of the (other) options provided is correct

Automatic grouping of Pivot Table items is possible when a field contains which of the following types of values?

Numbers, Dates, Times

What do you use to toggle the display of the field headings in Pivot Tables?

PivotTable Tools (tab) -> Analyze (tab) -> Show (group) -> Field Headers

The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places.

ROUND()

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

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

Which of the following is a shortcut key used to calculate the formulas in the active worksheet?

Shift + F9

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?

Shift + S

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?

Shift + Tab

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

While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?

The contents of cell A1 do not comprise four digits

Please see the attached workbook. To round off B11 cell to four decimal places what formula would you use?

The formula: =ROUND(B11,4)

If a pivot chart is created and the underlying table is deleted, what occurs?

The pivot chart remains.

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.

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

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

Please see the attached workbook. The highest 5 salaries for employees can be viewed using which of the following options under Conditional Formatting?

Top 10 items under top/bottom rules

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"

Please see the attached workbook. Which LOOKUP function would be used in order to get the Tax Rate (in cell B3) for the entered income (in B2)?

VLOOKUP

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

Is it possible to convert a cell with text data into a .png format ?

Yes

Would you be able to apply the "watch window" option in multiple sheets at the same time ?

Yes

The #VALUE! error can occur in the following conditions EXCEPT for:

You cut a cell and paste it to a cell that is referenced by other formulas

What characters are NOT allowed in sheet names ?

[ ]

What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted)

absolute

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

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

In Excel, a parameter query is a query that prompts for ________ every time it is run.

an input

Which of the following concepts are useful in understanding relationships between cells and formulas?

cell dependents

In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query.

columns

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 number) or blank cell

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 ____________.

counting

Please see the attached workbook. The formula in D2 that is copied down the column is "=IFERROR((C2 - B2) / C2, "")". Excel displays a / an _______ string if the formula results any type of error.

empty

What type of chart is shown in the figure?

line chart

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character

non visible

For a range called Data, what does the function =COUNTIF(Data, "<>0") return?

number of calls not equal to 0

A chart in an Excel worksheet provides a visual representation of which of the following?

numeric values

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 NOT a method of creating frequency distributions?

use the review (proofing) tool of spelling error frequency per worksheet

Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively.

weekday, amount

Please refer to the attached images. Go to the "View" tab on your Excel ribbon. Which command changes your view from what's seen in "worksheet_image1.png" to "worksheet_image2.png"?

zoon to selection