EL2 C3 C4 Flashcards
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
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
How are subtotals displayed when they have been created for a range of data at the Subtotal dialog box?
using the Outline feature
At the Data Validation dialog box, choose the type of data to be validated in the _____ option box on the Settings tab.
Allow
When writing a formula, how should you enter the workbook name reference?
enclosed in square brackets: [ ]
To begin building a PivotTable, click the PivotTable button in the Tables group on the _____ tab.
Insert
_____ Timeline pane(s) can be open at a time, but/and data can be filtered using _____.
More than one; one Timeline at a time
Which of the following include the worksheet reference by default?
a range name
What happens after you click the Insert Timeline button?
The Insert Timelines dialog box opens
When must you include the sheet name in a formula?
when it references a cell in a different worksheet
When validating or restricting data entry, _____ the added characters if a custom number format adds punctuation or text that appears in a cell.
ignore
Before removing duplicate records from a table, how should you first check to see what records will be deleted?
Click Duplicate Values in the Highlight Cells Rules at the Conditional Formatting drop-down list
The Use labels in section of the Consolidate dialog box contains these two options.
Top row and Left column
To begin formatting a PivotTable, make active a cell inside it and then click the _____ tab.
PivotTable Tools Design
How can you restore a broken link to an external resource?
Recreate the linked formula
To move a PivotChart to a new sheet, use the _____ button in the Actions group.
Move Chart
A table in Excel is similar in structure to a
database
In Excel, a range that can be managed separately from other rows and columns in the worksheet is called a
table
Which of these steps should be performed first when creating a table?
Select the range
A PivotTable is a(n) _____ table that organizes and summarizes data based on fields and records.
interactive
The workbook that contains the data that is linked to the destination workbook is called the _____ workbook.
source
Which of these examples shows the correct syntax for a formula that links to a cell in the budget.xlsx workbook?
=[budget.xlsx]January!A3
Create a PivotChart with the active cell anywhere in a PivotTable by clicking the PivotChart button in the _____ group.
(?)
NOT PivotTable
When a PivotTable is filtered using a Timeline, which of the following time periods is not an option?
weeks
To create a Sparkline, begin by
selecting the empty cell range in which to insert the Sparklines
You can change the summary function in a PivotTable by _____ any numeric value in the PivotTable, pointing to Summarize Values By at the shortcut menu, and then clicking a function name.
right-clicking
Click the _____ button to change the summary function in a PivotTable.
(?)
NOT Number Format
Field Settings
Using buttons in the Group group on the Sparkline Tools Design tab, you can do each of the following except _____ Sparklines.
copy
Before creating subtotals for a range of data, be sure to remove any _____ within the range to be grouped and subtotaled.
blank rows
Which of the following is not one of the options in the Table Style Options group on the Table Tools Design tab?
(?)
NOT Filter Button
To delete duplicate records from a table, click the Remove Duplicates button in the _____ group on the _____ tab.
Data Tools; Data
When data in a worksheet is entered as a table, each row is a _____ and each column is a _____.
record; field
What two options are presented at the Group dialog box?
Rows and Columns
Which of the following is an example of a structured reference formula?
=[@[Billable Hours]]*[@Rate]
Which two buttons are common to all three error alert message boxes?
Cancel and Help
In a _____, Excel copies the formula from the first cell to the remaining cells in the column immediately after the formula is entered.
calculated column
Use this keyboard shortcut to create a table.
CTRL + T
What does the Text to Columns feature do?
It takes data from a single column and separates it into multiple columns
To change an external reference to link to a different workbook, begin by clicking the _____ button at the _____ dialog box.
Change Source; Edit Links
To select more than one item in a Slicer pane,
click the Multi-Select button
To filter a PivotTable,
(?)
NOT right-click in the report
Linking to a cell in another workbook incorporates _____ references and requires adding a workbook name reference to the formula.
external
