ALL OF LEVEL TWO 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…
choose the type of condition to have Excel check before formatting
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
If you type a formula in the first record of a new table column, Excel automatically creates a(n)
calculated column
The _____ guides you through the steps of separating data into columns.
Convert Text to Columns wizard
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
By default, where does Excel display filter arrows in a table?
next to each label in the header row
By inserting a check mark in the First Column or Last Column check box in the Table Style Options group, you can
format that column differently
What is the first step when splitting text into multiple columns?
Insert a blank column(s) next to the source data
The Table Tools Design tab contains options for _____ the table.
formatting
Excel can compare records within a worksheet and automatically delete duplicate rows based on
the columns selected that might contain duplicate values
On the Table Tools Design tab, which of these boxes contains a check mark by default?
Banded Rows
Which of the following is not one of the three tabs at the Data Validation dialog box?
Add Level
To convert a table to a normal range, use the
Convert to Range button in the Tools group
What happens if you type new data in the row immediately below the last row of a table?
The table automatically adds a new row
Add a Total row to a table by clicking the
Total Row check box on the Table Tools Design tab
The Data Tools group on the Data tab does not contain which of the following buttons?
Sort & Filter
Which of the following is not one of the types of error messages that appears when incorrect data is entered in a cell?
Fatal
To select only specific subtotals and/or grand totals in a collapsed outline, click the Find & Select button, click the Go to Special option, click the _____ radio button to insert a bullet, and then click OK.
Visible cells only
_____ is not one of the data validation criteria available in the Allow option box at the Data Validation dialog box.
Fraction
Use the keyboard shortcut Shift + Alt + Right Arrow key to _____ data.
group
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
A PivotChart can be created directly from a PivotTable or from
the data in a worksheet
What are Sparklines?
miniature charts embedded in the background of cells
Which of the following areas does not appear in the layout section of the PivotTable Fields task pane?
Slicers
Slicers allow you to
filter without using a filter arrow
To change a Sparkline chart from a line to a column format, you will use tools in the _____ group.
Type
What will happen to the link to an external reference if you move the source workbook?
The link will not work
How does Chapter 4 suggest that you use Sparklines?
to show high or low values within a range
In a formula, a(n) _____ is used to separate a worksheet reference from a cell reference.
exclamation point (!)
To create a PivotChart in a worksheet without a PivotTable,
select the data range, click the PivotChart button arrow, and then click PivotChart
To permanently remove a linked reference, click the _____ button at the _____ dialog box.
Break Link; Edit Links
Insert a check mark in the Header Row check box in the Table Styles Options group to
show or hide the column headings in the table
When a table automatically expands to include new entries, the _____ displays.
AutoCorrect Options button
Before creating a subtotal for a range of related data, you must first
sort the data by the fields in which the records are to be grouped
The Table button is in the
Tables group on the Insert tab
The Stop Automatically Expanding Tables option appears when you click the _____ button.
AutoCorrect Options
What is the keyboard shortcut for adding a Total row?
Ctrl + Shift + T
What is the first step in creating a PivotTable?
Select the source range
The point-and-click method of creating a link to another workbook creates an _____ reference to the source cell.
absolute
When using a 3-D reference, it is a good idea to set up the data in each worksheet in
identical cells
When you create a PivotTable, you can select to place it in the existing worksheet or in
a new worksheet
By default, non-numeric fields are added to the _____ box of the PivotTable Field task pane, and numeric fields are added to the _____ box.
Rows; Values
Excel’s _____ feature can be used to summarize data from multiple worksheets or another workbook into a master worksheet.
Consolidate
As changes are made to the PivotChart,
the PivotTable associated with it also updates
The _____ function is active by default when you open the Consolidate dialog box.
Sum
The Insert Slicer button is in the _____ group on the PivotTable Tools Analyze tab.
Filter
What becomes visible when you activate any Sparkline cell?
the Sparkline Tools Design tab
When you open a workbook with an external reference, the _____ feature is disabled and a(n) _____ is displayed.
automatic updates; security warning message
Delete the dollar symbols ($) in the cell reference if the formula is to be copied and the source cell needs to be
relative
What is the first step in consolidating data?
Make the starting cell active
What does a Slicer pane contain?
all the unique values for the specified field
A formula that refers to the same cell in a range that includes several worksheets is known as a(n)
3-D reference
Creating a PivotTable allows you to do all the following except
delete data
When a Total row is added to a table, which of the following does not occur?
The table columns are automatically banded
Click the _____ button if you remove duplicate rows by mistake.
undo
The method of formatting even-numbered rows in a table differently from odd-numbered rows is called
banding
Why would you want to convert a table to a normal range?
to use the Subtotal feature
The first row of an Excel table contains
field names
Suppose that you have received a table containing first and last names in one column. What is the easiest way to split the names into separate columns?
Use the Text to Columns feature
Which of the following is not one of the banding options available in Excel?
add random bands to columns
There are no ____ within an Excel table.
(?)
NOT column labels
NOT text data
If a cell within a table is referenced in a formula in another cell outside the table, then the _____ is included in the formula.
table name
