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