MIS112 Module 5-8

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

invalid

By default, Page Breaks __________ displayed in the Normal view of worksheet.

may or may not be

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?

prevent Excel from using GETPIVOTDATA function

Please see the attached workbook. The result of the following formula =VLOOKUP("Apple",A2:B5,2,FALSE) is which of the following?

red

When do you get a "#NAME?" error value?

text in a formula is not enclosed in quotation marks

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

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

use the custom number format that consists of two colons

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

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

weekday, amount

Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query?

Graphing of data from tables

You can control how a sparkline handles empty cells in a range (and thus how the sparkling is displayed) by using the ______ dialog box.

Hidden and empty cell settings

Please see the attached workbook. Which of the following options can you choose with a Sparkline if you want to highlight the budget for the third year?

Highlight the : Low point

If you want to find the cells on the worksheet that have data validation, which option would you select?

Home (tab) -> Editing (group) -> Find & Select -> Data Validation

Which of the following steps are to be following in order to display the Go to Special dialog box which is an Excel Auditing Tool?

Home -> Editing -> Find & Select -> Go to special

The first field you add to a PivotTable report that does not contain numbers will automatically be added in the _______ part of the report.

Row Labels

______ locates a substring in another text string and returns the start position of the substring.

SEARCH( )

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

Send using E-mail

To avoid printing more pages than necessary in Excel, you can use the following command:_______.

Set print area

Data validation on a cell or range can do which of the following? Calculate what is allowed based on the content of another cell Use a formula to calculate what is allowed Restrict entry of time values (to be within a specified time frame)

all of the options provided are valid

What form of LOOKUP is used when you have a small list of values and the values remain constand over time?

array form

How will you return a cell formate as a date or time, back to its default or original format? Home (tab) -> Number (command group) ->Number Format ->General Press Ctrl +Shift + ~

both a and b

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

With regards to depreciation calculation functions in Excel, which of the following are true about the function DB()?

computes depreciation at a fixed rate and its arguments are: cost, salvage, life, period, [Month]

Please see the attached workbook, Sheet1. The Format Painter tool functionality is best described by which of the following?

copy the format of one cell to another

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

data in a table

By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data____.

does not appear in the Sparkline

In Excel, data displayed in a Sparkling is assumed to be at ____.

equal interals

Which rule category does the following conditional formatting rule belong to? "Highlight cells containing a value greater than 25"

highlight cell rules

By default, Microsoft can search for clipart online using which 2 search engines?

1) Office.com Clip art 2) Bing

Please see the attched workbook. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,500?

31%

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

32000

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

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 begins with the letter P and contains exactly four characters?

=COUNTIF(A1, "P???")=1

20130315 represents a date March 15, 2013. To convert this string to an actual date, which of the following formulas can be used? (Assume the data 20130315 is in cell A1)

=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))

To generate a series of dates 1 year apart in Excel, which of the following formulas is correct? (assuming the first date is entered in cell A1, and you are trying to compute the next year in the series)

=DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))

Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result?

=HLOOKUP("color", A1:B5,3,False)

Please see the attached workbook. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4?

=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)

Please see the attached workbook. How would you avoid the displayed error created by =B2/C2?

=IF(C2=0," ", B2/C2)

Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use?

=INDEX(J4:L8,3,3)

Which of the following formulas calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate.

=NPER(6%/12,117.43, -5000)

Which of the following formulas can you use to change a text string of military time (e.g., 21:12) in cell Y3 to a standard value of time?

=TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use?

=TRIM()

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY

=WEEKDAY(A1)=1

How will you refresh the query after moving the cell pointer anywhere within the external data table in the worksheet? Right-click and choose Refresh from the shortcut menu Choose: Data (tab) -> Connections (command group) -> Refresh All. Click "Refresh" in the Workbook Connections dialog box (displayed by choosing: Data (tab) -> Connections (command group) -> Connections ).

All the options in this question are valid

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

Apply a different color to negative values in the sparkline

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

Apply a different color to the lowest data point in the Sparkline

Which of the following provides correct syntax for the MATCH function?

MATCH(lookup_value, lookup_array,match_type)

In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____.

Find What, empty

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

In Excel, the set of worksheet functions related to dates can be accessed via:

Formulas (tab) -> Function Library (command group) -> Date & Time

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 (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value 'Tucson' for 'City' column.

From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list

Please see the attached workbook. Using the formula =B2/C2 what error is displayed?

#DIV/0!

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned?

#N/A

Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected

#NUM!

Which of the following error codes indicates that a formula refers to a call that is invalid? This type of error can occur if a referenced call is deleted from the worksheet

#REF!

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

Gender and summation of values as column labels

Which of the following is the correct output of the formula '=TRIM(" A B C D ")'?

A B C D

Please see the attached workbook. The formula in cell C4 is "=B4+ (B4*$C$1)". Formulas in range C4:C7 use a(n)_______ reference to cell C1.

Absolute

What is NOT true about Autocorrect options?

Changes made by the autocorrect feature cannot be undone

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a decimal number within limits?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum.

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: &[Time] ?

Displays the current time

What is the purpose of the text to columns command?

It parses strings into their component parts

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

Link_location ir required, friendly_name is optional

Which of the following is FALSE about Pivot Table Slicers? slicers can be moved and resized the effects of filtering by a particular slicer can be removed to use a slicer to filter data in a pivot table, you can click one of the slicer value buttons

None of the answers provided is false

If you accidentally create a circular reference formula, Excel displays which of the following warning messages: N/A Unique constraint violation ####

None of the options provided is correct

______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox. unchecked all checked

None of the options provided is valid

Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales does not equal 5000.

Nothing happens. both the records remain

Which of the following is true for printing a worksheet that contains objects/shapes?

Objects are printed along in the worksheet by default and you can avoid printing the object by making changes to the properties of the shape

What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments)

PMT(rate,nper,pv)

A calculated item, unlike a calculated field, does not appear in the _________ Field List.

PivotTable

By default, Excel prints worksheets in _____ orientation

Portrait

Why would we use Sparkline charts (instead of regular charts or some other Excel feature)?

Quickly spot time-based or variations in data in a very compact manner

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

Text import wizard

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

The pivot chart remains

What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters?

This removes all the filters at once

From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a number between the range of 1-250?

Whole Number

Please see the attached screen capture. What you see is an example of which of the following Excel features:______

Word art

Which one of the following is NOT a formatting option in the Web Query Options dialog box?

XML formatting

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