MIS112 Final

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

If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format)

9/20/2014 1:00:00am

Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years.

=6500*EXP(4.55%*4)

If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas?

=HYPERLINK($C$1)

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

Suppose you have a column of file names such as: 001-Chapter1.txt, ..., 015-Chapter15.txt, ..., 104-Chapter104.txt, etc. You want to use a formula to consistently remove the prefix numbers, e.g., make the file names into: Chapter1.txt or Chapter12.txt. Which of the following text functions can you use to make this change?

A combination of: FIND(), LEN(), MID()

Which of the following statements is true?

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 was 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

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

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

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DDB()?

Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. Its arguments are: Cost, Salvage, Life, Period, [Factor] (Both)

When customizing a sparkline, which of the following is NOT one of the options?

Convert the Sparkline into a table.

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

Convert to Range

If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views.

Custom Views

Which is one of the ways to refresh a query used to import data from external data source using the query wizard?

Data (tab) -> Connections (group) -> Refresh All

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.

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

Excel built-in functions user-defined functions defined names (all)

If you want to print a copy of a worksheet, which of the following would you choose?

File (tab) -> Print and then choose the PRINT command.

The Analysis Toolpak can be activated through which sequence of steps?

File-->Options-->Add ins -->Manage Excel Add-Ins --> Go

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

Flash Fill

How do you change the calculation mode while dealing with a calculation intensive data table?

Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables

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 of the following function(s) can be used to count error values in a range?

ISERROR() ISERR() ISNA() (all)

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

It usually reduces duplicate data storage

Which of the following options provides correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,[result_vector])

When choosing a page orientation, ____ is one of the two valid options.

Landscape

What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

Link_location is required, Friendly_name is optional

Vector based images are represented by?

Mathematical equations

Which of the following is NOT one of the options for highlighting certain data points in a Sparkline?

Middle point

When you right-click a graphic object and choose Size and Properties from shortcut menu, Format Shape pane opens. If you expand the Properties section which of the following choices you get?

Move and Size with cells Move But Don't Size with cells Don't Move or Size with Cells (All of the options provided are correct)

If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return?

Mr. John Q. Public

In Excel, the ___________ function calculates the difference between 2 dates excluding weekend days.

NETWORKDAYS()

Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?

NPER()

What command is used to *create your own rule* in conditional formatting?

New Rule

The _____________ function is used to return a specified number of letters or characters from the end of a text string.

RIGHT()

The function ______________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location.

SUBSTITUTE()

How do you create a Sparkline?

Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create.

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

In the Share tab of Backstage View, which of the options is available?

Send using E-mail

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

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

Shift+Tab

If you want to apply a date format to serial numbers, which of the following choices will work for you? Assume you have already done the following steps: selected a cell with the serial number, gone to the Home tab, within that the Number group, clicked the drop-down arrow in the Number format box)

Short date Long date More number formats (all)

There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Markers. What is its purpose?

Show data markers in the Sparkline

Which command helps you to override the automatic behavior and control max and min value for Sparklines?

Sparkline tools-> Design-> Group -> Axis

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

When importing a text file into Excel, the _____________ helps specify how the data will be imported.

Text Import Wizard

With respect to Sparklines, what is FALSE?

The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range.

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.

What is NOT correct about headers and footers?

The header appears at the bottom of each page while the footer appears at the top of each page.

What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule)

When the first date is changed, the others update automatically

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.

Is it possible to get the data back after deleting it using keyboard?

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

What characters are NOT allowed in sheet names?

[ ]

Which of the following characters is VALID for use in an Excel workbook file name?

_

Which of the following can be retrieved from a web page using web queries?

data in a table

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?

filter data

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

hide

In Excel, what does the MODE function in a range return?

most frequently occurring value in a range

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

Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________.

onscreen, printed

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

passwords are case sensitive

All manual page breaks are shown as _______ lines instead of ____ lines (when you use the view mode of Page Break Preview).

solid, dashed

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

A _____ data table in Excel shows the result of a single calculation for different values of two input cells

two-way

The ____ command is used to display text on multiple lines in a cell.

wrap text

What does Excel display when you enter the formula =DATE(2014,13,1)?

"1/1/2015"

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.

"horizontally, vertically"

What does Excel return when your MATCH() function uses an exact match option and searches for an item that is not part of the lookup_array or lookup_vector?

#N/A