ITSW 1304 Excel Spreadsheet Ch. 4

________ is the process of examining a worksheet for errors in formulas. Tracing Auditing Segmenting Defining

Auditing

Various styles, such as Headings and Titles, can be applied by using ________ in the Styles group. Conditional Formatting Merge Styles Format as Table Cell Styles

Cell Styles

________ improves accuracy because it limits and controls the type of data an individual can enter into a form. Goal Seek Data protection Data validation What-If Analysis

Data validation

Which of the following rules is NOT acceptable for defining names? The first character of the defined name must be a letter, an underscore, or a backslash. A defined name can be no longer than 255 characters. Defined names can be the same as a cell reference. Defined names can contain uppercase and lowercase letters.

Defined names can be the same as a cell reference.

The ________ command checks for common errors that occur in formulas. Hide Error Error Checking Trace Error Audit

Error Checking

The PMT function belongs in the ________ functions category. Statistical Engineering Financial Logical

Financial

When you start typing a formula, such as =SUM(B Excel will display the ________, which contains all of Excel's built in functions starting with the letter B. table array Formula AutoComplete list defined name AutoFill

Formula AutoComplete list

When using the PMT function to calculate payments, which of the following is NOT a required argument? Future value (Fv) principal rate Nper

Future value (Fv

A method to find a specific value for a cell by adjusting the value of one other cell is: validation. protection. Goal Seek. lookup.

Goal Seek.

When you borrow money, the amount charged to you is called: interest. principal. capital. Present value (Pv).

Interest

________ functions look up a value in a defined range of cells located in another part of the workbook to find a corresponding value. Lookup Statistical Financial Logical

Lookup

If you want to copy only the value in a cell and not the formula, use: Paste Special. Paste. Paste Link. Format Painter.

Paste Special.

________ are cells that are referred to by a formula in another cell. Trace cells Dependent cells Related cells Precedent cells

Precedent cells

In the Data Validation dialog box, you can create a ________ that provides the user with instructions about what to do. defined name Screen Tip cell reference table array

Screen Tip

Which of the following is NOT a way to define a name for a cell or group of cells? Select the range, click in the Name box, and then type the name. Select the range, click in the Formula Bar, and then type the name. Select the range, click the Define Name button, and then type the name. Select the range, open Name Manager, click New, and then type the name.

Select the range, click in the Formula Bar, and then type the name.

Which of the following is NOT a rule for defining names? Defined name can be no longer than 255 characters. First letter must be a letter, underscore, or backslash. Spaces are valid. The letters C or R cannot be defined as a name.

Spaces are valid.

In addition to the indicator in the upper left corner of the cell, you can use the ________ command to locate an error in a formula. Trace Error Fix Error Find Error Locate Error

Trace Error

An Excel function that looks up values in a table array arranged as vertical columns is the ________ function. SUM PMT HLOOKUP VLOOKUP

VLOOKUP

If you have defined a two-column range of cells containing names and phone numbers, you would use the ________ function to match a name with a number. VLOOKUP HLOOKUP LOOKUP MATCH

VLOOKUP

Data table are NOT used to: view and compare results. calculate possible values for each argument. calculate multiple versions in one operation. determine the Future value (Fv).

View and compare results

The process of changing the values in cells to see how those changes affect the outcome of formulas on a worksheet is called: Validation. What-If Analysis. Lookup. Goal Seek.

What-If Analysis.

Using various interest rates to determine the amount of loan payments is called: What-If Analysis. Lookup. Validation. Protection.

What-If Analysis.

Tracer arrows show the relationship between ________ cells. active and related active and reticent active and precedent active and dependent

active and related

In the Name Manager dialog box, defined names are listed by: the order in which they were named. alphabetical order. reverse order in which they were named. length of the name with shortest first.

alphabetical order.

If you calculate payments that are too high for your budget, you might consider: borrowing less money. increasing the interest rate. decreasing the time period to repay the loan. borrowing more money.

borrowing less money.

A red triangle in the upper right corner of a cell indicates that there is a(n): value. error. option. comment.

comment.

A(n) ________ is a range of cells that shows how changing certain values in your formula affects the results of the formulas. row array column data table

data table

A defined name provides a logical reference to: date. functions. tables. formulas.

date.

A ________ is a word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value. defined name cell range table array validation list

defined name

If you calculate payments that are high for your budget, you might consider: decreasing the time period to repay the loan. borrowing more money. increasing the interest rate. extending the time period to repay the loan.

extending the time period to repay the loan.

A one-variable data table will allow you to see how different interest rates and different payment periods will affect a monthly payment.

false

A to Z indicates that a list will be sorted in descending order.

false

An error is indicated in a cell by a triangle that appears in the top right corner of the cell when an error is found.

false

An error value is the result of a formula that Excel can evaluate correctly.

false

By using the Watch Window, you will need to repeatedly scroll or go to different parts of your work to inspect, audit, or confirm formula calculations and results in a large worksheet.

false

Goal Seek is a method to find a specific value for a cell by adjusting the value of one other cells.

false

If you click Ignore Error, the error is NOT marked to be ignored for each consecutive check.

false

In the Error Checking rules options, all but the next to last rule are selected by default.

false

New data cannot be inserted into a named range.

false

Resetting ignored errors will not reset all errors in all worksheets in the active workbook.

false

The cell reference M$10 can be used as a defined name.

false

To stay within your budget, an alternative to borrowing less money would be to decrease the number of years of payment.

false

When borrowing money, the interest rate is usually quoted per month.

false

You cannot change a defined name.

false

An advantage to naming a range of cells is that you can use the name in a: table array. data table. formula. lookup table.

formula.

A ________ triangle in the upper left corner of a cell indicates that the formula in the cell is suspect for some reason. red green blue orange

green

The Trace Error button will appear next to a cell where a formula error occurs and is identified by a ________. purple triangle green triangle red triangle blue triangle

green triangle

If you want to see how different interest rates affect a monthly payment, you would use a: two-variable data table. table array. defined name. one-variable data table.

one-variable data table.

Cells that have external references to other workbooks are displayed in the Watch Window toolbar only when the other workbooks are: listed. closed. open. found.

open.

Cells referred to by a formula in another cell are called: dependent cells. Trace dependents. precedent cells. Trace precedents.

precedent cells

The initial amount of a loan also is called the: percentage. principal. rate. interest.

principal

In Excel, the interest percentage on a loan is called the: Future value (Fv). payment period. Present value (Pv). rate.

rate

Within Excel, there are groups of Formula Auditing features that are useful in showing ________ in complex worksheets. precedents tracings dependents relationships

relationships

The ________ refers to the location where the name is recognized without qualification. scope of a name cell range formula table array

scope of a name

In a lookup formula, the defined range is referred to as the: defined name. table array. validation list. named range.

table array

An advantage to naming a range of cells is that you can use the name in a formula in other parts of your workbook.

true

Data tables are used to view and compare the results of different operations.

true

Data tables make it easy to calculate multiple versions in one operation.

true

Data validation improves accuracy.

true

Dependent tracer arrows are red when showing a cell that contains an error.

true

Goal Seek can be used to determine how much you can borrow to stay within your budget.

true

Goal Seek is a data tool used for What-If Analysis.

true

If the copied data contains a formula, this formula overwrites the data in the calculated column.

true

In a Lookup function, the formula refers to the defined range as the table array.

true

In the PMT function, a minus sign is placed before the amount of the principal so that payments are shown as a positive value.

true

Like a spelling checker that checks for errors in data that you enter in cells, you can implement certain rules to check for errors in formulas.

true

Naming cells has no effect on the displayed or underlying values.

true

Payments on a loan are usually made monthly.

true

Present value (Pv) represents the amount of the loan before any payments are made.

true

The AutoFill feature will duplicate a pattern of values that you set in the beginning cells.

true

The Error Checking command checks for common errors that occur in formulas.

true

The PMT function calculates the payment for a loan based on constant payments and at a constant interest rate.

true

The first step in using a lookup function is to define the range of cells that will serve as the table array.

true

When a new row is inserted in a named range, Excel adjusts formulas accordingly.

true

When creating a data table, the PMT function must be entered in the upper left corner of the range of data.true

true

You can only have one watch per cell.

true

To determine a monthly interest rate, you must divide the annual interest rate by: four. twelve. two. six.

twelve

To determine the number of months to repay a loan, you must multiply the number of years by: six. four. two. twelve.

twelve

The defined range for a lookup formula must have at least ________ rows or columns. three two four one

two

When creating a data table, you enter the PMT function in the ________ of your range of data. lower right corner upper right corner lower left corner upper left corner

upper left corner

A ________ is a list of values that are acceptable for a range of cells. data table validation list cell range table array

validation list

Loans are typically made for a period of: days. months. years. quarters.

years

The Future value (Fv) at the end of the time period is usually ________ for loans. zero half the principal twice the principal the same as the principal

zero