Excel Module II: Chapters 4- 6

Up to how many logicalN statements can there be?

255 statements.

A ________________ is a formula containing a reference that identifies the sheet name and the cell location.

3D reference formula

Syntax for MATCH function is

=MATCH(lookup_value, lookup_array, [match_type])

Screen Clipping

A feature of the Screenshot button that allows you to select a portion of a window

___________________ functions are nested within IF to check for multiple conditions by using an AND or OR function as the logical_test argument.

AND or OR

The Microsoft Office relational database application that works with Excel to import and export data is called ________________.

Access.

Like many financial functions, the PV function uses the concept of a _____________________.

Annuity.

Database functions all follow the same syntax and have three ___________.

Arguments.

To view open workbooks side by side or horizontally tiled, use the ________________ button.

Arrange All.

A range of cells that you select in which to apply a function is called an _____________________.

Array.

The rectangle that surrounds a SmartArt graphic is called a ________________________.

Bounding frame.

_______________________ are applications and processes for assessing types of results or changes to data

Business Intelligence (BI)

_______________________________ is the Excel feature that highlights information in a worksheet by setting different fill colors or font styles for selected cells,

Conditional formatting

The ________________ command summarizes data from multiple worksheets

Consolidate

If the data on your source worksheets might be edited after you have created your consolidation, you should ___________________.

Create a dynamic consolidation.

To use an Advanced Filter in Excel, you first need to create a _____________.

Criteria range.

The keyboard shortcut to insert a hyperlink is _____________________.

Ctrl+K.

The database function that counts the cells in the field that are not empty and that meet the criteria is the _____________________ function.

DCOUNTA.

The __________ function displays the smallest value in the field that meets the criteria.;

DMIN

A ___________________ function totals the values in a field that meet the criteria.;

DSUM

_____________________________ is the term for specified Excel data that is indicated with a fill color, a horizontal bar, or an icon within the cell.

Data visualization.

_______________ is a basic Excel command that uses relational or comparison operators to determine if the value or label in a cell should be formatted.

DataFind.

The Date & Time function category includes functions that convert text into dates, determine workdays in a range of dates, or display the day of the week for a date.;

Date & Time

When you import a document into Excel that uses special characters, such as commas to separate columns, you are importing a _____________________.

Delimited file.

A _____________________ sort sorts numerical data from the largest to the smallest value.

Descending

In a calculation, the difference between actual values and predicted values is called _____________________.

Deviation.

To edit an Excel worksheet that is marked as final and therefore should not be edited, click

Edit Anyway.

_____________________ is data from another program or format

External data

The ___________________ function calculates the future value of regular payments or deposits invested at a constant interest rate for a specified period of time.

FV

True or False: Functions in the Database category perform a calculation regardless of whether data meets the criteria.

False-only when data meets the criteria.

Many functions in the ___________________ category include rate and nper arguments and use the concept of an annuity, a series of equal payments.

Financial

___________________ functions analyze money transactions.

Financial

Use the _____________________ when you want to make the same change to many functions or formulas in a worksheet.

Find and Replace

An Excel command that recognizes a pattern and duplicates it is _______________

Flash Fill.

What is the correct syntax for a Database function?

FunctionName (database, field, criteria).

If you wish to make formatting changes to identical worksheets in a workbook, simply do this before applying the changes.

Group the worksheets.

1. Type descriptive labels in the first row and begin each label with a letter, not a number. 2. Assign each header a unique label; do not repeat any of the descriptive labels. 3. Keep the same type of data within each column (e.g. text or values), 4. Do not leave blank rows within the data. 5. Keep the table separate from other data on the worksheet.

Guidelines for optimizing tables of data using excel commands

An ___________________ function can determine if a cell contains an error value and then display the message you choose rather than the default error value.

IFERROR

The IFERROR function is written as ____.

IFERROR(value,value_if_error)

The ___________________ function uses multiple logical_test arguments with a corresponding value_if_true argument for each test.

IFS

An ___________________ function displays the contents of the cell at the intersection of a specified column and row.

INDEX

The _____________________ function displays the contents of the cell at the intersection of an identified column and row.

INDEX

An ____________ is a character such as an exclamation point that marks or signifies a component of an external reference.

Identifier

The Hyperlink button is located on the _________________ tab.

Insert

You can build a data consolidation by category as long as the _______________.

Labels match.

To create a consolidated worksheet by position, both documents must have the same ______________.

Layout.

Web site data can be imported into Excel if the data is of______________.

List-type.

A _____________ function displays TRUE or FALSE after determining if one or all conditions are met.

Logical

The ___________________ function returns the relative location of data in a list.

MATCH

Nested _____________ and _______________ formulas are well-suited to datasets with millions of rows.

MATCH and INDEX

________________ is a Network product that allow collaboration and simultaneous work by a group

Microsoft SharePoint

The ___________________ function calculates the net present value of an investment using a discount rate and a series of cash flows which can be receipts or payments.

NPV

A function within a function is described as _____________________.

Nested.

The _____________________ function enables you to observe the effects of edits to formulas without having to scroll through the worksheets in a workbook.;

Nper

_____________________ is an argument in a formula that represents the first value or range.;

Number 1

In a financial function, nper stands for _____________________.

Number of payments.

When you use Excel's subtotal command your list data is formatted as an ________________.

Outline.

The ___________________ function calculates the current value of regular payments at a set interest rate for a specified period of time.

PV

________________ is a summary report in a separate worksheet based on list-type data

PivotTable

The Math & Trig function that totals cells only if they meet specified criteria, including cells from more than one column, is _____________________.

SUMIFS.

_____________________ are circular points on the frame of a graphic used to resize it

Selection Handles

Organization charts, matrices, pyramids, bulleted lists, and other similar diagrams available within Office applications are called___________.

SmartArt.

________________ is the process of arranging rows of data in an identified order.

Sorting.

A ________________ is the existing worksheet containing a range included in a consolidation worksheet

Source worksheet

The Standard Deviation functions are found in the ____________________ category

Statistical.

________________ is a column name along with its table name.

Structured reference

________________ command inserts summary rows for a sorted list and formats the data as an outline

Subtotal

The ___________________ function displays columnar data in rows and vice versa.

TRANSPOSE

What results do AND and OR functions return?

TRUE or FALSE

________________ is a tab character separates the fields

Tab delimited

Which type of function would you use to format a value as a label?

Text

True or False: The criteria range can use AND conditions in the same row or OR criteria in separate rows.

True

In an OR function, if any one of the arguments is true, the result is ____________.

True.

______________________ clears an Auto Outline

Ungroup button

MATCH and INDEX functions nested to accomplish the same tasks as a ___________________ formula but at a faster speed.

VLOOKUP

What is the criteria argument represent?

a range of cells with labels in one row and criteria below the label row on the same or another sheet.

A static consolidation shows calculated values that do not _________________________.

automatically update.

NPV function

calculates the net present value of an investment using a discount rate and a sense of future payments.;

For workbooks to be linked, they must all be accessible over a network, on the same computer, or in the ___________.

cloud

The Mark as Final command results in a simple _____________________.

courtesy message.

A ___________ is a column of data.

field

A _________________ is the label in the header row that helps identify the field.

field name

A ___________________ is the deviation or difference between actual values and predicted or forecasted values.

forecast error

Use Excel functions to build common statistical ___________________ calculations.

forecasting.

A ___________________ results in a default error message in the cell such as #N/A or #VALUE!

formula syntax error

The ____________________ is the first row of a table with descriptive titles or labels

header row

What does an AND function test and what results are shown when all conditions are met?

it tests multiple conditions and shows a result of TRUE

Database functions are used with what types of data?

list or table-type data.

The only argument of AND and OR functions is called

logicalN.

The ___________________ a statistical measure of how spread out a group of values is from the average.;

mean absolute deviation (MAD)

A ___________________ is a function within another function.

nested function

Once you place a screenshot in a worksheet, it becomes a _____________________.

picture object.

A ____ is a row of data.

record

Date arithmetic is possible because Excel uses a ___________________ system for dates.;

serial numbering

What does an OR function tests multiple conditions and

shows TRUE if one condition is met and shows FALSE only when all statements are false.

A ___________________ measures how broadly values vary from the mean (average) value, a common statistical calculation;

standard deviation

An Excel __________ is a of related pieces of information that is formatted with a title row followed by rows of data.

table

When data is in ___________________, you can organize, sort, filter, and calculate data easily and quickly, much like a database.

table format

A ____________ is a predesigned set of built-in format options such as borders, fill colors, and effects for an Excel table, you use:.

table style

A ________________ is a type of importable document that includes raw data, no formatting, and uses commas or other characters to separate the data into columns and rows.

text file