MIS 111L Final Exam Review

This error code tells us that a problem with a value exists; for example, you specified a negative number where a positive number was expected.

#NUM!

Consider the phrase: "This is Excel". [bold with strikethrough] Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)?

(Control + B) + (Control + 5)

By default, Excel uses __________ font.

11 point (pt) Calibri

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)

Worksheet names can have up to ____ characters.

31

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

What does the following formula return? =FIND("m","Big Mama Thornton",1)

7

How do you group Pivot Table items manually?

After creating the pivot table, select the items to be grouped and then choose Options (tab) under PivotTable Tools and select Group (group) -> Group Selection

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?

All of the above

Which of the following is TRUE about a Watermark in Excel?

All of the above statements are TRUE.

In context of the MATCH() function, which of the following is FALSE?

All of the options are false.

When you save an Excel workbook for viewing on the Web, you have two options. What are they?

An HTML File and a single web page file

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

What is a keyboard shortcut for creating a table? (hint: you can test the options in Excel)

CTRL + L

What would you do, while creating a table, to display your Table Headers?

Check 'My table has headers'

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 is the purpose of the function TIME()?

Converts the input hours, minutes & seconds to a serial number

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ .

Ctrl + Alt + Shift + F9

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?

Data Bars

In the Excel Backstage view, which of the following pieces of information about your workbook are available?

Date the workbook was created

Which of the following is *NOT* a type of cell reference?

Dynamic

What does XML stand for?

Extensible Markup Language

To close an Excel workbook, you can click on ____ followed by _____

File, Close

To save a copy of your workbook with a different name, you can click on ____ followed by _____.

File, Save As

Which of the following is NOT considered a style attribute?

Formula

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

With regard to Depreciation Calculation functions in Excel, which of the following are true about the function SYD?

It allocates a large depreciation i the earlier years of an asset's life.

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What does the following code do: &[SheetTab] ?

It is not a valid code.

The ___ function can be used to find the number of characters in a word or text string.

LEN()

What is the correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,result_vector)

Which of the following reviewing features does Excel offer to you?

A proofing tool to check spelling

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

A. !NULL# B. ?NULL? C. ~?NULL~? D. ~NULL~ Correct Answer: B

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

A. ":" (colon) B. " " (space) C. "," (comma) D. ";" (semi colon) Correct Answer: C

Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______.

A. #DELETEDROWS B. Sheet1!#INVALIDREF! C. Sheet1!#REF! D. #VALUE! Correct Answer: C

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?

A. #N/A B. #REF! C. #VALUE! D. #NULL! Correct Answer: A

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

A. Ctrl + V B. Ctrl + Z C. Ctrl + C D. Ctrl + X Correct Answer: D

What is the shortcut to open the Find and Replace dialog box with the "Find" tab open?

A. Ctrl+H B. Ctrl+A C. Ctrl+F D. Ctrl+Alt+F Correct Answer: NOT (A) SO IDK :(

Adding comments to a cell is available through which tab of the Excel ribbon?

A. Data B. Review C. Home D. Formulas Correct Answer: B

Which of the following option sequences will arrange all open non-minimized Excel workbook windows side-by-side on the screen?

A. Data --> Text to Columns B. View --> Freeze Panes C. Page Layout --> Breaks D. View --> Arrange All Correct Answer: D

Which of the following does the Formula Autocomplete feature in Excel take into account?

A. Excel built-in functions B. all of the options provided for this answer are valid C. user-defined functions D. defined names Correct Answer: B

Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value?

A. ISERROR B. ISNA C. ISLOGICAL D. ISERR Correct Answer: C

Which of the following function(s) can be used to count error values in a range?

A. ISERROR() B. all of the provided choices are valid C. ISNA() D. ISERR() Correct Answer: B?

Which of the following is a VALID sequence of options for selecting a different chart layout?

A. Insert --> Charts --> Other Charts B. Design (from Chart Tools) --> Type group C. Design (from Chart Tools) --> Chart Styles D. Design (from Chart Tools) --> Chart Layouts Correct Answer: D

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.

A. Set a fill color of dark red for the sales figure cell B. Add a comment to the sales figure cell, and explain to your assistant what you want him to do C. Delete the sales figure for Monday (seeing the the blank cell will make things clear to the assistant) D. Make a pie-chart to explain the ratio of sales of Monday to other days in the week so the assistant sees which day has the highest sale Correct Answer: B

Which of the following is NOT a reason to name a range of cells?

A. The name of a cell (or a range of cells), is often easier to remember than the cell reference itself (e.g., H57) B. 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 C. 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 D. A named cell, e.g., 'Sales_Revenue' can be used in formulas that require an absolute reference (and will allow copy-pasting without error) Correct Answer: B

A formula in Excel can consist of which of the following?

A. all of the choices provided are valid B. mathematical operators C. text D. cell references Correct Answer: A

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

A. area B. column C. line D. surface Correct Answer: B

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

A. cells containing the current date B. current date C. number of cells containing the current date D. cells not containing the current date Correct Answer: C

After you create a chart, which of the following actions can you perform on it?

A. change the chart type, but not the data series B. change the chart type, formatting, and data series C. change the chart formatting and colors, but not the data series D. modify the data series, but not change the formatting Correct Answer: B

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

A. number of cells containing a negative value B. number of cells not equal to 0 C. number of blank cells D. number containing a positive value Correct Answer: B

When a range of cells is selected, which of the following occurs?

A. the selected cells are all highlighted in the same color B. none of the cells is highlighted in any color C. a box marks the boundary of the selected cells, but none of the cells are highlighted D. all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded Correct Answer: D

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is true?

Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4"

Which of the following is a correct syntax for VLOOKUP function?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Charts are dynamic, i.e., they change as the underlying data changes. Is this an accurate statement?

Yes, by default they are dynamic.

The formula: =CHAR(CODE("A")+32) returns which of the following?

a

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria?

a tilde followed by an asterisk

Which of the following is TRUE about Pivot tables?

all of the above are true

Data validation on a cell or range can do which of the following?

can do all of the above

The Data (tab) -> Data Tools (group)-> Data Validation drop-down list contains an item named Circle Invalid Data. What will happen if you select this item?

circles appear around cells that contain incorrect entries.

Grand Totals in Pivot Tables are used to _____.

display totals for all cells in column (or row) of a pivot table

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference

invalid

In Excel, dates are stored as _____.

numbers

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab).

quick

The HLOOKUP function searches for data in ______.

rows

Which of the following options can be used to quickly remove data validation for a cell?

select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

How do you copy a Pivot table to a new worksheet?

select the entire table and choose Home (tab) -> Clipboard (group) -> Copy (or, press Ctrl+C). Then select a new worksheet and choose Home (tab) -> Clipboard (group) -> Paste -> Paste Values

A(n) _________ is an interactive control that makes it easy to filter data in a pivot table?

slicer

Which of the following is NOT a way to add a new worksheet to a workbook?

Press Shift + F12

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 true about web queries?

Performing web queries copies information from an HTML file into the current workbook

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 function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

HYPERLINK()

To hide rows or columns you can click on ____ then ____

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)