C2 Unit 1 Review Excel

Pataasin ang iyong marka sa homework at exams ngayon gamit ang Quizwiz!

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


Kaugnay na mga set ng pag-aaral

Introduction to Computer Programming Final Exam

View Set

oth3416: the ultimate exam 2 mega-quizlet

View Set

Inv - Type/Char Cash Equivalents (1)

View Set

Chapter 9: nail structure and growth

View Set

Finance Final, Fk Pirim (Exams1-3)

View Set

JavaScript Objects and Prototypes

View Set