EL2 C1 C2 Flashcards
How will numbers display when the format code is [Red];[Blue]?
Positive numbers will display in red; negative numbers in blue
To create your own number format, select _____ in the Category list box at the Format Cells dialog box with the Number tab selected.
Custom
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
conditional formatting
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?
M*
_____ formatting converts numbers to exponential notation (E + n).
Scientific
Which of these formatting Type options is not available for US English at the Format Cells dialog box with the Number tab selected?
Credit Card Number
Icon sets allow you to…
classify data into three to five categories
Which of these custom number format codes aligns numbers vertically on the decimal point?
????.??
Which of the following numbers is represented by 2.60E + 05?
260,000.00
Which of the following buttons is not available when you click Manage Rules at the Conditional Formatting button drop-down list?
Copy Rule
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?
Alphabetical
When applying conditional formatting using predefined rules, the first step is to…
select a range of cells
How can you tell that a filter has been defined for a column?
A filter arrow appears in the column heading
How can you format a cell based on the value in a different cell?
Use a formula
In the Select a Rule Type section of the New Formatting Rule dialog box, you…
(?)
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
At the New Formatting Rule dialog box, begin creating a new rule by choosing which of the following?
the type of condition for Excel to check before formatting
Changing the appearance of a cell based on a condition is called…
conditional formatting
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.
the same; separate
The _____ Excel feature is used to display only the rows that meet specified criteria.
Custom AutoFilter
Excel provides special number formats that are specific to which of the following?
countries and languages
Conditional formatting cannot be applied based on which of the following?
errors in data entry
If a custom number format is deleted, the custom formatting will be removed from which of the following?
all the cells to which it was applied in that workbook
When filtering a range using an advanced filter, start by…
adding five or six rows at the top of the worksheet
The Edit the Rule Description section of the New Formatting Rule dialog box varies depending on…
the active option in the Select a Rule Type section
You can filter values by more than one criterion at a time by using…
a comparison operator
In the Conditional Formatting Rules Manager dialog box, the default setting for the Show formatting rules for option is…
Current Selection
If the custom number format code is ###.##, which value will display if the value 23.959 is entered?
23.96
Which of these questions would best be answered using conditional formatting?
Which salespeople exceeded their goals last month?
Values in rows that do not meet the criteria in the Custom AutoFilter feature are…
temporarily hidden from view
Using _____, you can classify data into categories based on threshold values for the selected range.
icon sets
The first step in creating a custom number format is to…
select the range
Which of the following is not an example of a conditional formatting rule?
Format only values in exponential notation
One way to remove conditional formatting is to select the range, click the Conditional Formatting button, point to Clear Rules, and then choose
Clear Rules from Selected Cells
How do you open the New Formatting Rule dialog box?
click New Rule
You can edit or delete conditional formatting rules in the _____ dialog box.
Conditional Formatting Rules Manager
Select the option(s) _____ at the Custom AutoFilter dialog box to specify two criteria by which to filter.
And or Or
The custom number format allows you to do each of the following except…
combine arabic numbers and roman numerals
When you delete a custom rule at the Conditional Formatting Rules Manager dialog box, what happens to cells already formatted by the rule?
The formatting is removed
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?
000.00
Excel can add text such as GX- before all the values typed in cells if the cells have been formatted using the _____ category.
Custom
You can create custom rules based on each of the following except…
cell position
As a visual guide to see the variation of values in a range, Excel provides each of the following except…
animated graphics
Which of the following is not one of the options on the Formatting tab of the Quick Analysis button?
Highlight Cell Rules
If the appropriate formatting has been applied, data can be filtered by all the following except
font type
The IF function’s logical test returns _____ result.
only a true or false
Which of the following is not an option in the Conditional Formatting button drop-down list?
Font Sets
Exponential notation is often used by engineers and scientists for…
very small and very large numbers
With conditional formatting, cells that do not meet the criteria specified
remain unchanged
You _____ create your own conditional formatting rules.
can
Which of the following is not one of the 13 categories of functions mentioned in Chapter 2?
spatial
Which of the following represents the proper structure of the arguments for an HLOOKUP function?
(lookup_value, table_array, row_index_num, range_lookup)
Which of the following scenarios would be the most likely application of the VLOOKUP function?
looking up a number grade and returning a letter grade
The ABS, RAND, SQRT, and SUMIF functions are in the _____ category.
math and trigonometry
Which of these functions uses an argument containing multiple criteria?
COUNTIFS
The VLOOKUP argument that identifies a range of cells, rather than a value, a single cell, or a column number, is…
table_array
The AND and OR functions use _____ logic to construct a condition test in a formula.
Boolean
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.
number of payment periods
A _____ is a preset formula.
function
What is the first step in creating an AVERAGEIF formula?
Make active the cell in which the formula will be inserted
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.
Create from Selection
The lookup_value argument in the VLOOKUP function specifies which of the following?
the value that Excel searches for in the reference table
Excel’s Lookup & Reference functions provide a way to…
look up values in a range
Excel stops evaluating an IF formula…
once the logical_test has been answered as true
In a PPMT function, the argument fv refers to the…
balance at the end of the loan
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?
COUNTIFS
If a range name used in a formula is deleted, cells that used the name will display this error message.
NAME?
By default, what happens when the VLOOKUP function does not find an exact match in the first column of the VLOOKUP table?
it looks in the first column of the table for the largest value that is less than the lookup_value
A function includes two parts: the name of the function and the…
argument
The _____ function will take the value –7 as an argument and return the value 7.
ABS
The _____ function will find a value in a column of data and use it in a formula.
VLOOKUP
The _____ function contains these arguments: (logical_test,value_if_true,value_if_false).
IF
In a nested IF function, how does Excel help you keep track of the parentheses that belong to each IF function?
It color-codes them
The _____ argument of the PPMT function can contain only a 1 or a 0.
type
Which of the following functions will result in a number rounded to the nearest tens value?
=ROUND(145,-1)
Use the _____ function to find a value when the table contains comparison data organized in rows.
HLOOKUP
To make a formula easier to read, place each logical test on a new line and then…
expand the Formula bar
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?
ALT + enter
Use the _____ function with an IF function to test multiple conditions.
AND
A nested IF formula can be used…
when more than two outcomes are possible
Excel will add the required syntax automatically when you create a COUNTIF formula…
at the Insert Function dialog box
MAX and MIN are statistical functions that return the _____ and the _____ value in the range, respectively.
largest; smallest
The syntax of the COUNTIF and COUNTIFS arguments requires that the criteria _____ if it is not a cell reference.
be enclosed in quotation marks
The result of an IFS function must test…
true
The _____ Excel function calculates the principal portion of a loan payment.
PPMT
What is the keyboard shortcut for opening the Name Manager dialog box?
Ctrl + F3
The argument (range,criteria,average_range) is required by the _____ function.
AVERAGEIF
In Excel, an argument can include any of the following except..
a specified format
The _____ function is in the math and trigonometry function category.
SUMIFS
Which of the functions discussed in Chapter 2 requires the argument (range1,criteria1,range2,criteria2)?
COUNTIFS
Unlike a nested IF function, an IFS function does not require each argument to be enclosed in…
(?)
NOT parentheses (( ))
The COUNTIF function requires two arguments: range and
criteria
The number of right parentheses needed to end a nested IF statement equals the number of times
IF appears in the formula
The “range to calculate” argument comes first in all the following functions except the _____ function.
SUMIF
What argument follows sum_range in the SUMIFS function?
criteria_range1
Range names can be created, edited, and deleted at the Name Manager dialog box, which is opened by clicking the
Name Manager button on the Formulas tab
Which of the following formulas uses the correct syntax?
=COUNTIF(Expenses,”>700”)
In the VLOOKUP function, what does the col_index_num argument indicate?
the column number from the lookup table that contains the data to be placed in the formula cell
Which of these lookup functions is most commonly used?
VLOOKUP
Which of the following is not one of the arguments in the PPMT function?
pmt
