C2 Unit 1 Review Excel
To add data near a table without having the table expand,
. leave a blank column or row between the table and the new data.
To edit or delete an existing rule, begin by opening the _____ dialog box.
Conditional Formatting Rules Manager
The Consolidate button is in the _____ group on the Data tab.
Data Tools
One of the methods to further collapse and expand subgroups of records at various levels is to use the _____ and _____ buttons in the Outline group on the Data tab.
Group; Ungroup
Click the _____ check box on the Table Tools Design tab to show or hide the column headings in a table.
Header Row
The _____ logical function returns FALSE when all the conditions test false.
OR
The Group dialog box contains two options:
Rows and Columns.
At the Format Cells dialog box with the Number tab selected, if Special is selected in the Category list box and English (United States.) is selected in the Locale option box, the Type list box will contain each of the following options except
Student ID.
Which of the following is not one of the ways to create a PivotTable?
Use the Recommended PivotTables button in the Tables group of the Quick Analysis button.
Since most lookup tables are arranged with comparison data in columns, the _____ function is the more commonly used lookup function.
VLOOKUP
The function IPMT is used to calculate the _____ portion of a loan payment.
interest
At the Consolidate dialog box, clicking the Create links to source data option will cause Excel to
update the data automatically when the source ranges change.
The error alert icon that displays as a yellow triangle with an exclamation point inside is used for a(n) _____ error alert.
warning
You can create, edit, or delete range names at the Name Manager dialog box, which can be opened from the _____ tab.
Formulas
The Number group on the Home tab contains three buttons for popular formats. Which of the following is not one of them?
Fraction Style
Excel provides several table styles categorized by _____ color themes.
Light, Medium, and Dark
A PivotChart displays the data from a(n) _____ in chart form.
PivotTable
A _____ is an interactive table that organizes and summarizes data based on fields and records.
PivotTable
You can build a table by dragging fields between areas at the PivotTable Fields task pane. Which of the following is not one of the areas included?
Titles
The three types of Sparkline charts are Line, Column, and
Win/Loss.
In a custom number format, the # format code represents
a digit.
In the Table Style Options group on the Table Tools Design tab, the First Column and Last Column options are used to _____ these columns.
add emphasis to
By default, which columns are selected when you open the Remove Duplicates dialog box?
all the columns
If you remove duplicate rows by mistake,
click Undo.
Before deleting records with the Remove Duplicates dialog box, you should consider
conditionally formatting duplicate values.
The COUNTA function is used when the cells in the range
contain text or a combination of text and numbers.
At the Name Manager dialog box, you can do all the following except _____ a range name.
copy
L2 C1: Conditional formats can be based on date or text entries or on
duplicated values.
After data is selected, the Quick Analysis button appears near the _____ at the bottom right corner of the selected cells.
fill handle
Conditional formatting would be useful for which of the following?
finding values that are below average
When you use an IF statement in a formula to apply conditional formatting, the formatting will be applied to all the cells
for which the conditional test returns a true result.
In the VLOOKUP function, the col_index_num argument refers to the column number
from the lookup table that contains the data to be returned.
What does a Timeline do?
group and filter a PivotTable based on a specific timeframe
The nper argument in the PPMT function refers to the
number of payment periods for a loan.
By default, when a subtotal or grand total is selected in a collapsed outline, the underlying data is
selected.
When you create a formula that references a cell in the same worksheet, you do not need to include the _____ in the reference.
sheet name
A _____ in Excel is similar in structure to a database.
table
The Top/Bottom Rules in the Conditional Formatting button drop-down list allow you to highlight cells based on a top _____ or bottom _____ value or percent.
ten; ten
The Data Validation dialog box provides the option of adding a(n) _____ message and a(n) _____ message.
input; error alert
The COUNTIFS function is used to count cells that
meet multiple criteria.
A range of data with a column that has _____ can be grouped, and subtotals can be created for each group automatically.
multiple rows with the same field value
L2 C2: Assigning a _____ to a range of cells allows you to reference the source by a descriptive label rather than the range address.
name
A(n) _____ function is one function enclosed within another function.
nested
When external references have been created, moving the source workbook or changing its name will cause the link to
no longer work.
The PPMT function returns the _____ of a loan payment.
principal portion
At the Advanced Filter dialog box, use options in the _____ section to filter the list in its current location or copy the filtered list to another location.
Action
Use options available at the _____ button to quickly apply conditional formatting, create charts, add totals, create tables, and add Sparklines.
Quick Analysis
You can use _____ to filter a PivotTable report or PivotChart without using a filter arrow.
Slicers
Chapter 4 suggests using _____ to help readers quickly determine visually if a trend or pattern exists within a data set.
Sparklines
_____ are miniature charts embedded into the background of a cell.
Sparklines
To change the line and/or marker appearance of a Sparkline, use options in the _____ group on the Sparkline Tools Design tab.To change the line and/or marker appearance of a Sparkline, use options in the _____ group on the Sparkline Tools Design tab.
Style
The first row of an Excel table contains column headings and is called the field names row or the _____ row.
header
A formula that references the same cell in a range that extends over two or more worksheets is often called a(n) _____ reference.
3-D
Use the _____ function to return the absolute value of a number (that is, the number without its sign).
ABS
The _____ logical function returns FALSE if any of the conditions tests false.
AND
The _____ function returns the arithmetic mean of a range of numbers.
AVERAGE
The _____ function is used to find the arithmetic mean of the cells within a specified range that meet a single criterion.
AVERAGEIF
The _____ function requires these arguments: (average_range,criteria_range1,criteria1,criteria_range2,criteria2...).
AVERAGEIFS
The Clear Rules option at the Conditional Formatting button drop-down list contains two options for removing rules: Clear Rules from Selected Cells and
Clear Rules from Entire Sheet.
Excel creates a link formula using a(n) _____ reference to the source cell.
absolute
When a PivotChart has been created from an existing PivotTable, changes made to the data by filtering in the PivotChart will
be reflected in the PivotTable.
While creating a PivotTable, make sure that the source data contains no
blank rows or columns.
If you build a PivotChart from scratch, Excel will
build a PivotTable in the background.
Typing a formula in the first record of a new table column creates a(n) _____ column.
calculated
Use the Text to Columns feature to
split columns in which more than one field has been entered.
In a formula with an external reference, the name of the external workbook is enclosed in
square brackets ([]).
The RAND function returns a random number between
0 and 1.
Once the correct reference is inserted in the Reference text box at the Consolidate dialog box, click the _____ button before setting up the next reference.
Add
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
What happens when you open a workbook with a linked external reference?
Automatic updates are disabled and Excel displays an alert message from which the content can be enabled.
The _____ feature can be used to summarize data from multiple worksheets into a master worksheet.
Consolidate
The Text to Columns feature is in the _____ group on the Data tab.
Data Tools
Which of the following is not one of the buttons at the Edit Links dialog box?
Import Source
To add a subtotal to a data range that already has one or more subtotals, select a cell in the range and then click the Subtotal button in the _____ group on the _____ tab.
Outline; Data
Use the _____ function to modify the actual number of characters by rounding the value.
ROUND
_____ formatting converts a number to exponential notation.
Scientific
What is the first step in sorting by color?
Select the range.
Use the keyboard shortcut _____ to ungroup data.
Shift + Alt + Left Arrow key
Convert a table to a normal range to use the _____ feature or when the data no longer needs to be treated as a table.
Subtotal
By default, Excel uses the _____ function to summarize the numeric value added to a PivotTable.
Sum
When the Consolidate dialog box opens, the _____ function is selected by default in the Function option box.
Sum
The COUNTIF function counts cells within a range that meet
a single criterion.
Unlike a nested IF function, an IFS function does not require each _____ to be enclosed in parentheses.
argument
When subtotals are added to a range of data, Excel displays the subtotals with _____ along the left of the worksheet area to show or hide the details for each group using the Outline feature.
buttons
L2 C4: A 3-D reference can be created
by typing directly in the cell.
Which of the following is not one of the arguments required by the VLOOKUP function?
criteria
Excel's _____ feature allows you to control the type of data accepted for entry in a cell.
data validation
By default, Excel bands the rows within a table when a theme is applied, which means that
even-numbered rows are formatted differently from odd-numbered rows.
Summarizing data in one workbook by linking to a cell or range in another workbook incorporates _____ references.
external
The formula =[Invoices.xlsx]January!B6 is an example of a(n) _____ reference.
external
In an Excel table, columns are called
fields.
When using a two-color scale in conditional formatting, you specify the shade of color that represents a _____ value within the range.
higher or lower
In the HLOOKUP function, the letter H designates a _____ lookup.
horizontal
In the SUMIF function, the range argument
is where to look for the data.
By default, an Excel table displays a filter arrow next to each _____ in the _____.
label; table header row
AND, IF, and OR are _____ functions.
logical
The custom number format [Green];[Red] will display _____ in green and _____ in red.
positive numbers; negative numbers
In a PPMT function, the _____ argument is the amount of money borrowed.
pv
In addition to the asterisk, which wildcard character can be used in a custom filter?
question mark (?)
To create a table in Excel, enter data in the worksheet and then define the _____ as a table.
range
Chapter 4 suggests using _____ to simplify formulas that summarize data in multiple worksheets.
range names
What three arguments are required by the SUMIF function?
range, criteria, and sum_range
The data entered into rows in an Excel table are referred to as
records.
For the range_lookup argument in the VLOOKUP function, a value of FALSE will instruct Excel to
return only exact matches to the lookup value.
Once an Excel table has been defined, typing new data into the _____ will cause the table to expand automatically.
row immediately below the last row of the table or the column immediately right of the last column in the table
L2 C3: Adding a Total row to a table causes Excel to add the word Total in _____ of a new row at the bottom of the table.
the leftmost cell
In a calculated column, Excel copies the formula from the first cell to _____ as soon as you enter the formula.
the remaining cells in the column
The HLOOKUP function uses _____ argument parameters as the VLOOKUP function.
the same
To apply an advanced filter using the And logical operator, enter criteria in _____ row(s).
the same
Custom number formats are stored in
the workbook in which they are created.
You can format a range of values using an icon set to classify data into _____ categories.
three to five
