Excel Final Exam

(T/F) The IPMT function is used to calculate the total interest on a loan.

False IPMT returns the interest payment for a specified period.

What Excel process allows you to keep rows and/or columns visible onscreen at all times even when you scroll through a large dataset?

Freeze Pane

A predefined formulas is a...

Function

Michael estimates he can afford a monthly car payment of $390.00. He needs to know how much he should pay (Negotiated Sales Price) if he makes a $1,500 down payment. Which of the following Excel tools can help him determine the negotiated sales price for his new car?

Goal Seek

The ________ feature of Excel greatly increases user efficiency by enabling actions such as data entry and formatting on multiple worksheets at one time.

Grouping

The structure and order of a function and its arguments are also known as the function's __________.

Syntax

(T/F) Named cells and named ranges are always absolute references

TRUE!!

(T/F) All formulas and functions must start with the equal sign (=).

True

(T/F) An Excel Chart Sheet can only contain a single chart.

True

(T/F) An Excel Structured Table that is converted back to a normal range retains the table formatting.

True

(T/F) In an Excel structured table, as columns or rows are added to a table formatted with a banded style, the new columns and rows are automatically formatted to match the banded style.

True

(T/F) Numerical data placed into the VALUES area of a PivotTable will be summed by default.

True

(T/F) When Named ranges are used in formulas the named range is an absolute reference.

True

The formula =AND(6>5,NOT(6<5)) returns which of the following results?

True

Your have applied Excel's Structured Table feature to your data. You suspect your table has identical records. What should you do?

Use the Remove Duplicates command button, but use Conditional formatting to verify before using the button.

If your reference data is arranged in a column layout which of the following lookup functions might you use?

VLOOKUP

Data Table

What If Analysis table Created to analyze one or two variables in a spreadsheet model; one of the What If data tools

Text that is too lengthy to fit in one cell can be displayed on multiple lines within the cell using which of the following:

Wrap Text

Which Excel tool allows users to make the cell content appear on multiple lines by adjusting the row height to fit the column width?

Wrap Text

Do you agree with the following statement? Before protecting a worksheet, you must first unlock any cells that you want your users to modify.

Yes

Determining which retrieval function (VLOOKUP or HLOOKUP) to use is based on ____________.

arrangement of the data table (table_array)

Single one-variable data table

enables you to analyze one variable and unlimited results.

AutoFill allows users to __________ using the fill handle.

fill information from one cell, or a series of cells, in to other cell(s)

Dependent cell

requires the preceding value in another cell to calculate its result.

A single two-variable data table enables you to analyze _________ input variable(s) and ________ result(s).

two, one

Which of the following is TRUE about hard-coded values? A. A formula such as =P1*Q2 contains a hard-coded value. B. Hard-coding makes it easier to update data and adapt to change. C. In the formula =J5*12 the 12 is a hard coded value. D. Hard-coding is recommended when you have a value that may change.

C. In the formula =J5*12 the 12 is a hard coded value.

SUMMARY Michael estimates he can afford a monthly car payment of $390.00. How much should he pay (Negotiated Sales Price) if he makes a $1,500 down payment? QUESTION In solving for the answer to the previous question (summary), which cell was the 'set cell'?

C8

The ________ function calculates the amount of interest paid over a specific number of loan periods.

CUMIPMT

Chart Data Table

Chart element A chart element added to a chart to provide categorical details.

You want to add a visual element to a data set that highlights values that are greater than $200.00. What tool might you use?

Conditional Formatting

AND, OR, and NOT are examples of Excel ________ functions.

Conjunction

The spa calculates the total revenue that a spa package generates over the course of a year. This is an example of ________ statistics.

Descriptive

Calculating the range and variance of a data set are all methods used to determine the ________ of a data set.

Dispersion

PivotTables _______ when changes are made to the underlying data in the data source.

Do not update automatically

The Accounting Number format has which of the following characteristics? A. Dollar sign right aligned B. Displays two decimal places C. Comma to separate thousands D. A, B, and C E. B and C

E. B and C B. Displays two decimal places C. Comma to separate thousands

To add emphasis to a specific pie chart slice, ________ or drag a slice.

Explode

The _____ function in Excel is used to return the position of a specific character or substring within a text string. For example, the position of a delimiter like a comma or a space.

FIND

(T/F) A chart includes several elements - the chart area, the plot area, data series, the horizontal and vertical axis, and the legend. These elements cannot be modified or changed.

False

(T/F) After making changes to the original source data, PivotTable data automatically updates the changes in the PivotTable.

False

(T/F) Excel does not have specific tools for working with and managing large data sets.

False

(T/F) Excel's formatting options: Center Across Selection and Merge & Center are the same in Excel?

False

(T/F) Hiding a row or column affects the data and any formulas that reference a hidden cell.

False

(T/F) The use of currency formatting or accounting formatting affect the way that numbers are used in calculations.

False

Formulas cannot reference values or formulas in cells on other worksheets.

False

The formula =OR(8<7,NOT(7<8)) returns which of the following results?

False

A ________ is a user-friendly way to quickly filter data in a PivotTable.

Slicer

A ________ is a window that is used to quickly filter data in an Excel table.

Slicer

Precedent cell

Supplies a value to the cell containing a formula

MIN, MAX, RANK, and QUARTILE.EQ are what type of Excel functions?

Statistical

If you accidentally create a formula that divides a number by zero, which of the following error values will you get?

#DIV!/0

Michael estimates he can afford a monthly car payment of $390.00. How much should he pay (Negotiated Sales Price) if he makes a $1,500 down payment?

$23,150.83

Assume Michael buys a $29,000.00 car and makes a $1,800 down payment (interest and terms remain the same). Use the spreadsheet model to determine how much principal Michael will pay on his fifth car payment.

$424.05

Referencing the spreadsheet model and the values shown, what is Michael's monthly car payment?

$489.96

The Accounting Number format has which of the following characteristics?

-Dollar sign left aligned -Displays two decimal places -Comma to separate thousands

When worksheet source values are changed, pie chart percentages and pie slices are automatically ___________________. 1. Recalculated 2. Upsized 3. Displayed 4. Resized Choose from the following: 1 and 2 1 and 4 2 and 4 3 and 4

1 and 4 Recalculated and Resized

Which of the following is FALSE about PivotTables? 1. Each row in the source data becomes a PivotTable field. 2. Levels of data can be expanded and collapsed. 3. Best suited for summarizing and analyzing large amounts of data. 4. Can be used for totaling and subtotaling data.

1. Each row in the source data becomes a PivotTable field.

When talking about referencing values on another sheet, what type of formula is being asked about?

3D (3D formulas and 3D references are used to reference cells across worksheets. The name '3D' is because a 1) Row, 2) Column, and 3) Worksheet are being referenced.)

Which of the following are the same axis for an Excel chart? 1. category axis and x-axis 2. value axis and x-axis 3. value axis and y-axis 4. category axis and y-axis 5. 1 and 3 6. 2 and 4

5. 1 and 3

Referencing the spreadsheet model, which of the following formulas might Michael have in cell C8?

=PMT(C6/12,C7*12,C5)

Structured Table

A table with a defined structure, style, and organization Created by one of two methods: 1. Home tab > Style group> Format as Table button 2. Insert tab > Tables group > Table button

Each of the following statements regarding multiple worksheets are TRUE EXCEPT: A. Data can be accessed between worksheets using 2-D references. B. Data can be copied and pasted from one worksheet to another. C. Multiple worksheets can be formatted at the same time. D. Data from multiple worksheets can be referenced to generate new data via formulas or functions

A. Data can be accessed between worksheets using 2-D references.

Which of the following statements is FALSE? A. Worksheets contain workbooks. B. Another word for worksheets is spreadsheets. C. Worksheets contain rows and columns. D. The number of worksheets that be contained in a workbook is a function of the amount of available memory.

A. Worksheets contain workbooks.

In Excel, rows are added...

Above the selected row

$B$32 is an example of what type of cell referencing?

Absolute

The Name Box displays the...

Address of the active cell

COUNT, SUM, AVERAGE are examples of _______ functions.

Aggregate

Table

An Excel range with Row/Column labels

Which of the following functions requires all arguments to be TRUE to return a TRUE result?

And

The following formula is entered into cell B4: =OR(5<6,4<7,2<0). The word TRUE appears in cell B4. Why?

At least one argument is true

Excel has structured tables that facilitate data analysis and management. Some advantages of structured tables include: 1. Filters for efficient sorting and filtering. 2. Calculated Total Row that enables the user to aggregate data easily. 3. Calculated columns where formulas are completed automatically. A. 1 and 2 B. 2 and 3 C. 1, 2, and 3

B. 2 and 3 2. Calculated Total Row that enables the user to aggregate data easily. 3. Calculated columns where formulas are completed automatically.

When using an Excel Structured Table, the table can do all of the following EXCEPT: A. Provide both flexibility and scalability B. Automatically sort data in table columns C. Help provide context to the user by organizing data in a meaningful way

B. Automatically sort data in table columns

Which of the following is NOT a common logical function? A. IF B. PMT C. OR D. NOT

B. PMT

Which of the following is an example of an Absolute Cell Reference V. C$4 W. $F$10 X. Interest_Rate (cell C6's 'name') Y. AB43 Z. $2$F Chose from the following answers: A. V and W B. W and X C. X and Y D. Y and Z

B. W and X **Z IS NOT BC THE NUMBER IS BEFORE THE LETTER

Which of the following formulas could be used to calculate shipping costs in the following situation: When parts are shipped by boat, the shipping cost is 15% of product value; when parts are shipped using ground transportation, the shipping cost is 17% of product value; finally, when parts are shipped by air, the shipping cost is 22% of product value.

IF(Ship Method = "Boat", Value *0.15, IF(Ship Method = "Ground", Value * 0.17, Value* 0.22) (OR) The following could also be correct answers: IF(Ship Method = "Boat", Value *0.15, IF(Ship Method = "Air", Value * 0.22, Value* 0.17) IF(Ship Method = "Air", Value *0.22, IF(Ship Method = "Boat", Value * 0.15, Value* 0.17) IF(Ship Method = "Air", Value *0.22, IF(Ship Method = "Ground", Value * 0.17, Value* 0.15) IF(Ship Method = "Ground", Value *0.17, IF(Ship Method = "Boat", Value * 0.15, Value* 0.22) IF(Ship Method = "Ground", Value *0.17, IF(Ship Method = "Air", Value * 0.22, Value* 0.15)

If you see a column with #### instead of results, what action will correct the error?

Increase the column width

What is the purpose of the MATCH function?

It provides the relative position of a value in a list of values that meets a specified criteria.

Which of the following is the argument in the following: =SUM(J2:J4)

J2:J4

Excel stores dates as a serial number. Day 1.0 is...

January 1, 1900

The ________ chart type is the best chart style to illustrate data changes over a period of time

Line

The IF function's first argument is the logical_test and requires which of the following:

Logical operator

A ________ IF function uses IF functions as arguments within another IF function.

Nested

You accidently selected the wrong Conditional Formatting option. You must delete the rule and redo the Conditional Formatting?

No

Data mining techniques such as PivotTables can detect ________ of data.

Patterns (Data mining is the process of analyzing data from different perspectives and summarizing it into useful information.)

In the statement "When a value being referenced in a cell is changed, all the formula results using the cell reference recalculate", the cell that is being referenced in the formula is known as a ____________________ cell.

Precedent

An Excel function is a _______________.

Predefined formula that performs a calculation

If you don't know the interest rate for a loan but you know the present value, payment, and number of payment periods, you can use the ________ function to determine the interest rate.

RATE

When a value being referenced in a cell is changed, all the formula results using the cell reference __________.

Recalculate

CUMPRINC

Returns the cumulative principal paid on a loan between the start and end period