EL2 C1 C2

Question 1

How will numbers display when the format code is [Red];[Blue]?

Answer 1

Positive numbers will display in red; negative numbers in blue

Question 2

To create your own number format, select _____ in the Category list box at the Format Cells dialog box with the Number tab selected.

Answer 2

Custom

Question 3

Suppose you have a worksheet in which you enter monthly expense figures and compare them to monthly budget figures. To have an expense figure automatically display in red if it exceeds the budgeted amount, you should use

Answer 3

conditional formatting

Question 4

Which of the following would you use to set up a filter to display the names of all US states beginning with the letter M?

Answer 4

M*

Question 5

_____ formatting converts numbers to exponential notation (E + n).

Answer 5

Scientific

Question 6

Which of these formatting Type options is not available for US English at the Format Cells dialog box with the Number tab selected?

Answer 6

Credit Card Number

Question 7

Icon sets allow you to…

Answer 7

classify data into three to five categories

Question 8

Which of these custom number format codes aligns numbers vertically on the decimal point?

Answer 8

????.??

Question 9

Which of the following numbers is represented by 2.60E + 05?

Answer 9

260,000.00

Question 10

Which of the following buttons is not available when you click Manage Rules at the Conditional Formatting button drop-down list?

Answer 10

Copy Rule

Question 11

In the conditional formatting icon sets gallery, the available icons are organized into four sections. Which of the following is not one of the sections?

Answer 11

Alphabetical

Question 12

When applying conditional formatting using predefined rules, the first step is to…

Answer 12

select a range of cells

Question 13

How can you tell that a filter has been defined for a column?

Answer 13

A filter arrow appears in the column heading

Question 14

How can you format a cell based on the value in a different cell?

Answer 14

Use a formula

Question 15

In the Select a Rule Type section of the New Formatting Rule dialog box, you…

Answer 15

(?)
NOT specify the cells that will receive the formatting
NOT enter a formula that will be evaluated to determine which type of formatting to apply

Question 16

At the New Formatting Rule dialog box, begin creating a new rule by choosing which of the following?

Answer 16

the type of condition for Excel to check before formatting

Question 17

Changing the appearance of a cell based on a condition is called…

Answer 17

conditional formatting

Question 18

To filter data using an advanced filter, enter criteria on _____ row(s) when using the And logical operator and enter criteria in _____ row(s).when using the Or logical operator.

Answer 18

the same; separate

Question 19

The _____ Excel feature is used to display only the rows that meet specified criteria.

Answer 19

Custom AutoFilter

Question 20

Excel provides special number formats that are specific to which of the following?

Answer 20

countries and languages

Question 21

Conditional formatting cannot be applied based on which of the following?

Answer 21

errors in data entry

Question 22

If a custom number format is deleted, the custom formatting will be removed from which of the following?

Answer 22

all the cells to which it was applied in that workbook

Question 23

When filtering a range using an advanced filter, start by…

Answer 23

adding five or six rows at the top of the worksheet

Question 24

The Edit the Rule Description section of the New Formatting Rule dialog box varies depending on…

Answer 24

the active option in the Select a Rule Type section

Question 25

You can filter values by more than one criterion at a time by using...

Answer 25

a comparison operator

Question 26

In the Conditional Formatting Rules Manager dialog box, the default setting for the Show formatting rules for option is...

Answer 26

Current Selection

Question 27

If the custom number format code is ###.##, which value will display if the value 23.959 is entered?

Answer 27

23.96

Question 28

Which of these questions would best be answered using conditional formatting?

Answer 28

Which salespeople exceeded their goals last month?

Question 29

Values in rows that do not meet the criteria in the Custom AutoFilter feature are...

Answer 29

temporarily hidden from view

Question 30

Using _____, you can classify data into categories based on threshold values for the selected range.

Answer 30

icon sets

Question 31

The first step in creating a custom number format is to...

Answer 31

select the range

Question 32

Which of the following is not an example of a conditional formatting rule?

Answer 32

Format only values in exponential notation

Question 33

One way to remove conditional formatting is to select the range, click the Conditional Formatting button, point to Clear Rules, and then choose

Answer 33

Clear Rules from Selected Cells

Question 34

How do you open the New Formatting Rule dialog box?

Answer 34

click New Rule

Question 35

You can edit or delete conditional formatting rules in the _____ dialog box.

Answer 35

Conditional Formatting Rules Manager

Question 36

Select the option(s) _____ at the Custom AutoFilter dialog box to specify two criteria by which to filter.

Answer 36

And or Or

Question 37

The custom number format allows you to do each of the following except...

Answer 37

combine arabic numbers and roman numerals

Question 38

When you delete a custom rule at the Conditional Formatting Rules Manager dialog box, what happens to cells already formatted by the rule?

Answer 38

The formatting is removed

Question 39

Which of these custom number format codes will round values to fit the number of digits after the decimal point and fill in leading zeros?

Answer 39

000.00

Question 40

Excel can add text such as GX- before all the values typed in cells if the cells have been formatted using the _____ category.

Answer 40

Custom

Question 41

You can create custom rules based on each of the following except...

Answer 41

cell position

Question 42

As a visual guide to see the variation of values in a range, Excel provides each of the following except...

Answer 42

animated graphics

Question 43

Which of the following is not one of the options on the Formatting tab of the Quick Analysis button?

Answer 43

Highlight Cell Rules

Question 44

If the appropriate formatting has been applied, data can be filtered by all the following except

Answer 44

font type

Question 45

The IF function’s logical test returns _____ result.

Answer 45

only a true or false

Question 46

Which of the following is not an option in the Conditional Formatting button drop-down list?

Answer 46

Font Sets

Question 47

Exponential notation is often used by engineers and scientists for...

Answer 47

very small and very large numbers

Question 48

With conditional formatting, cells that do not meet the criteria specified

Answer 48

remain unchanged

Question 49

You _____ create your own conditional formatting rules.

Answer 49

can

Question 50

Which of the following is not one of the 13 categories of functions mentioned in Chapter 2?

Answer 50

spatial

Question 51

Which of the following represents the proper structure of the arguments for an HLOOKUP function?

Answer 51

(lookup_value, table_array, row_index_num, range_lookup)

Question 52

Which of the following scenarios would be the most likely application of the VLOOKUP function?

Answer 52

looking up a number grade and returning a letter grade

Question 53

The ABS, RAND, SQRT, and SUMIF functions are in the _____ category.

Answer 53

math and trigonometry

Question 54

Which of these functions uses an argument containing multiple criteria?

Answer 54

COUNTIFS

Question 55

The VLOOKUP argument that identifies a range of cells, rather than a value, a single cell, or a column number, is...

Answer 55

table_array

Question 56

The AND and OR functions use _____ logic to construct a condition test in a formula.

Answer 56

Boolean

Question 57

If you divide the interest rate by 12 for a monthly rate with the PMT or PPMT function, you might have to multiply the _____ by 12.

Answer 57

number of payment periods

Question 58

A _____ is a preset formula.

Answer 58

function

Question 59

What is the first step in creating an AVERAGEIF formula?

Answer 59

Make active the cell in which the formula will be inserted

Question 60

You can automatically create range names based on column or row labels by clicking the _____ button in the Defined Names group on the Formulas tab.

Answer 60

Create from Selection

Question 61

The lookup_value argument in the VLOOKUP function specifies which of the following?

Answer 61

the value that Excel searches for in the reference table

Question 62

Excel’s Lookup & Reference functions provide a way to...

Answer 62

look up values in a range

Question 63

Excel stops evaluating an IF formula...

Answer 63

once the logical_test has been answered as true

Question 64

In a PPMT function, the argument fv refers to the...

Answer 64

balance at the end of the loan

Question 65

Which function should be used if you want to count the number of teams that have won more than 10 games and have fewer than 25 people on the roster?

Answer 65

COUNTIFS

Question 66

If a range name used in a formula is deleted, cells that used the name will display this error message.

Answer 66

#NAME?

Question 67

By default, what happens when the VLOOKUP function does not find an exact match in the first column of the VLOOKUP table?

Answer 67

it looks in the first column of the table for the largest value that is less than the lookup_value

Question 68

A function includes two parts: the name of the function and the...

Answer 68

argument

Question 69

The _____ function will take the value –7 as an argument and return the value 7.

Answer 69

ABS

Question 70

The _____ function will find a value in a column of data and use it in a formula.

Answer 70

VLOOKUP

Question 71

The _____ function contains these arguments: (logical_test,value_if_true,value_if_false).

Answer 71

IF

Question 72

In a nested IF function, how does Excel help you keep track of the parentheses that belong to each IF function?

Answer 72

It color-codes them

Question 73

The _____ argument of the PPMT function can contain only a 1 or a 0.

Answer 73

type

Question 74

Which of the following functions will result in a number rounded to the nearest tens value?

Answer 74

=ROUND(145,-1)

Question 75

Use the _____ function to find a value when the table contains comparison data organized in rows.

Answer 75

HLOOKUP

Question 76

To make a formula easier to read, place each logical test on a new line and then...

Answer 76

expand the Formula bar

Question 77

To place an argument or logical test on a new line in the Formula bar, place the insertion point immediately before the logical test and then press which of the following?

Answer 77

ALT + enter

Question 78

Use the _____ function with an IF function to test multiple conditions.

Answer 78

AND

Question 79

A nested IF formula can be used...

Answer 79

when more than two outcomes are possible

Question 80

Excel will add the required syntax automatically when you create a COUNTIF formula...

Answer 80

at the Insert Function dialog box

Question 81

MAX and MIN are statistical functions that return the _____ and the _____ value in the range, respectively.

Answer 81

largest; smallest

Question 82

The syntax of the COUNTIF and COUNTIFS arguments requires that the criteria _____ if it is not a cell reference.

Answer 82

be enclosed in quotation marks

Question 83

The result of an IFS function must test...

Answer 83

true

Question 84

The _____ Excel function calculates the principal portion of a loan payment.

Answer 84

PPMT

Question 85

What is the keyboard shortcut for opening the Name Manager dialog box?

Answer 85

Ctrl + F3

Question 86

The argument (range,criteria,average_range) is required by the _____ function.

Answer 86

AVERAGEIF

Question 87

In Excel, an argument can include any of the following except..

Answer 87

a specified format

Question 88

The _____ function is in the math and trigonometry function category.

Answer 88

SUMIFS

Question 89

Which of the functions discussed in Chapter 2 requires the argument (range1,criteria1,range2,criteria2)?

Answer 89

COUNTIFS

Question 90

Unlike a nested IF function, an IFS function does not require each argument to be enclosed in...

Answer 90

(?) NOT parentheses (( ))

Question 91

The COUNTIF function requires two arguments: range and

Answer 91

criteria

Question 92

The number of right parentheses needed to end a nested IF statement equals the number of times

Answer 92

IF appears in the formula

Question 93

The “range to calculate” argument comes first in all the following functions except the _____ function.

Answer 93

SUMIF

Question 94

What argument follows sum_range in the SUMIFS function?

Answer 94

criteria_range1

Question 95

Range names can be created, edited, and deleted at the Name Manager dialog box, which is opened by clicking the

Answer 95

Name Manager button on the Formulas tab

Question 96

Which of the following formulas uses the correct syntax?

Answer 96

=COUNTIF(Expenses,">700")

Question 97

In the VLOOKUP function, what does the col_index_num argument indicate?

Answer 97

the column number from the lookup table that contains the data to be placed in the formula cell

Question 98

Which of these lookup functions is most commonly used?

Answer 98

VLOOKUP

Question 99

Which of the following is not one of the arguments in the PPMT function?

Answer 99

pmt