MIS 111L FINAL EXAM

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

"horizontally, vertically"

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

#N/A

In the attached image, examine the formula in cell "E9." What result will this formula produce? =B7:C16

#VALUE!

Please see the attached workbook. The formula: =OFFSET(A1,MATCH("Mary",A2:A5,0),2) returns what value?

22

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

For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.

255

In Excel, the DATE function takes _____ arguments.

3

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text

3

What is the maximum number of characters a worksheet name can contain?

31

A single cell in Excel can hold up to approximately ________________ characters.

32000

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)

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?

=A1*Sheet2!A2

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

Which formula can be used to find: How many songs were sung by singers whose names begin with S?

=COUNTIF(B2:B23,"S*")

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 formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting)

=ROUND(B2*1440,0)/1440

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 changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?

=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

What is one of the DRAWBACKS of using a pivot table?

A Pivot Table does not update automatically when information changes in source data

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

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 value from the lookup_vector

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

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

Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________

Axis Titles

Please see the attached workbook. When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill?

Below Average

How are some icons hidden in Icon Set conditional formatting?

By choosing "No Cell Icon" in the Edit the Rule Description box.

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

Clear Rules

Which of the following is NOT one of the buttons in the Spelling dialog box in Excel?

Conditional Formatting

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

Convert to Range

A group of cells is referred to as which of the following?

Correct range

Which of the following is the keyboard shortcut to enter the current date into a cell in Excel ?

Ctrl + ; (semicolon)

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 can be used in Excel 2013 to create a table?

Ctrl+L

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

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

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

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 is the purpose of the following code: &[Tab] ?

Displays the sheet's name

In Excel 2013, when you open multiple workbooks, how does the workbook show?

Each workbook always appears in its own window.

What are the #REF! and #NUM! that may appear in a cell?

Excel error codes

What is the result of the formula =2^3 * (,10*10

Excel tells you that the formula contains an error

What would happen if you used a formula to perform arithmetic operations on dates, but one of the dates was typed in a format that Excel does not recognize as a date?

Excel will display an error value

When working with data returned from a Query Wizard, you can adjust a variety of properties of an external data range. This can be done using the ______________.

External Data Properties Dialog Box

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

Flash Fill

Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width)

Format

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 lesser than 30000 for 'Salary' column.

From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list

To print a worksheet in a Landscape orientation, you:

Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape

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

HYPERLINK()

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 is a VALID method of hiding rows or columns?

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

Please examine any Excel 2013 (Windows) workbook to better answer this question: You can create your own style through which of the following steps?

Home (tab) -> Styles (command group) -> Cell Styles

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

If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?

Insert (tab) -> Text (command group) -> Text Box

How does the Excel search/replace function work for dates?

It searches for a date as displayed in a cell, and then does a replace

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

LOOKUP(lookup_value,lookup_vector,[result_vector])

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

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

Link_location is required, Friendly_name is optional

At which locations can you find the following formatting features? Increase Decimal, Format Painter and Font Color

Mini Toolbar

How do you remove a filter in the PivotTable Field List?

Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

NOW()

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

NPER()

Can you print data in an Excel Form attached to a table?

No, you cannot

Which of the following is FALSE about Pivot Table Slicers?

None of the answers provided is false

Which of the following options would you choose if you want to remove all manual page breaks in the worksheet?

Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)

Products!F2:F50

What is the syntax for the RATE() function?

RATE(nper,pmt,pv,[fv],[type],[guess])

Which function can be used to make crude/simple vertical separators between cells?

REPT()

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

Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.

Which of the following is NOT a summary function for Pivot tables?

Regression

Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1. Which command can you use to navigate to the comment in cell B5?

Review (tab) -> Comments (command group) -> Next

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.

Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?

Select the data, then click "Table" under the "Insert" tab of the Excel 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

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

Shift + S

To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?

Single File Web Page

Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?

Single-factor without replication

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

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

Text Import Wizard

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

The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT:

The second to last cell in a selected column that contains data or formatting

Which is NOT a feasible option to prevent cell contents from being printed?

Use the custom number format that consists of two colons.

Which of the following is an advantage of using Excel tables?

Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.

Web Query

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 does not hold true for Watch Windows?

You can see all the formulas in a worksheet and print them for later reference

Which of the following is a VALID range name in an Excel workbook?

_2ndyear

Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?

a comment exists for that cell

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

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .

ampersand (&)

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .

arguments

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

In the Color Scales method of Conditional Formatting, color ________ are used to compare values.

gradients

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

hide

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

The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

range (and) criteria

The HLOOKUP function searches for data in ______.

rows

The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date.

serial number

The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________

table style, conditional formatting

The real power of data validation becomes apparent when you use data validation formulas. The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, what does it mean?

the data is considered valid and remains in the cell

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

two-way

What best describes the connection between a Pivot Table and a Pivot Chart?

two-way link (updates made in the Pivot Chart are reflected in the PivotTable)

Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet?

use Freeze Panes

Which of the following is NOT a method of creating frequency distributions?

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet

Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.

visualize, trends