BUS 1305 Excel

What error value will be displayed if the VLOOKUP function cannot find the lookup value?

#N/A

What are examples of Statistical functions?

(*AVERAGE*)

What are examples of conditional formats in Excel?

(ANSWER ON THE TEST IS *DATA BARS*)

What is the difference between extrapolating and interpolating a series?

--- (LOOKING FOR "STEP" ON THE TEST)

What is the syntax for the DB function used to calculate the declining balance depreciation?

= DB (cost, Salvage, life, period [month])

What would the formula look like that is used to multiply cell A1 by cell C1?

=A1*C1

What is an adjacent range?

A collection of cells

In function "SUM(number1 [, number2, number3, ...])", the number 1, number 2, and number 3 may be numbers or other type of value?

A number or cell reference

What is a constant is a value?

A value that does not change (T/F)

If you merge cells A1, B1, C1, D1, and E1, what will be the correct reference for the merged cell?

A1

Which logical condition requires that all of the selected criteria be true for the record to be displayed?

AND

What keys do you need to use to create a new line within a cell?

Alt+Enter

By default, numbers appear in what format.

Always appear in the general format by default

What are arguments, expressions and values?

Arguments - number text or cell reference used by the function to return a value

What are the characters +,-, and * examples of?

Arithmetic Operators

What is the reverse alphabetical order, from Z to A considered? What is Ascending and Descending order?

Ascending - A to Z Descending - Z to A

What process makes it easier to enter repetitive text in cells?

Auto Complete

How do Sparklines differ from data bars?

Bars are places in cells containing the value they represent, and each cell represents only a single bar from the bar chart. Sparklines can be inserted anywhere within the workbook and can represent data from several rows/columns

Which logical criteria filter requires the records displayed to start with the specified text string?

Begins with

What type of reference cells can you use within an Excel table?

Cells and range addresses

As a general rule, what are the common alignments for titles, text and numbers?

Center - titles Left - text Right - numbers

How can your perform a what-if-analysis?

Changing one or more of the input values to see how they affect the calculated results

What is an exploded pie chart?

Chart that moves a slice away from the pie chart

What is the command that becomes available so you can remove a filter and redisplay all the records?

Clear Command

How do you change a chart type?

Click "change chart type" from the type group in the chart tools design tab

After you select a cell with an error indicator, what do you need to select to display additional information about the possible error?

Click the error checking button to display additional information about the possible error and determine if further action is needed

How does you create a PivotTable?

Click the summarize with pivot table button in the tools group on the table tools design tab

A range reference is separated by what character?

Colon (:)

What are "AZ" and B2 examples of?

Column headings

What will the fill handle copy?

Copies content and format

Which function is sometimes referred to as a conditional count?

Count If Function

Which keys should you press to make A1 the active cell?

Ctrl Home

What do you call a table that describes the fields you plan to maintain in an Excel table?

Data Definition Table

What do you call descriptive text for the individual data markers, such as pie slices?

Data labels

Define data source, data series and legend?

Data source - contains the actual values that are plotted on the chart Data series - a row or columns of numbers that are plotted in a chart Legend - Designed to help readers understand the charted data

Which function calculates the difference between two dates and shows the result in months, days, or years?

Datedif

If an optional argument is not included, what value will Excel use?

Default option

What is the process of allocating the original cost of an investment over the years known as?

Depreciation

What are the contextual tabs that appear on the Ribbon when you select a chart?

Design and format tabs

When you have more than one sort field, what Sort option do you use to specify the sort criteria?

Dialogue Box

What is a chart sheet?

Displays only the chart and no worksheet cells

What is the formula bar?

Displays the values or formulas of an active cell (T/F)

When you copy a formula that contains an absolute reference to a new location, what happens to the reference?

Does not change

Absolute references are marked with what character?

Dollar sign ($)

What are steps when using the Format Painter to paste the same format multiple times?

Double click on the format painter button to paste the same format multiple times then click on the format painter to turn it off

What do you need to do to switch into the edit mode?

Double click the cell

What do you call a combination chart that has data series with vastly different values?

Dual Access Chart

Header and footer elements such as worksheet name, current date, and time are what type of elements.

Dynamic elements

How many separate panes can split the worksheet window into?

Either two or four

What does an Excel formula always begin with?

Equal sign (=)

What are the matching options for constructing Lookup tables?

Exact or approximate match (ANSWER IS TRUE ON TEST)

In pie charts, is it best to make the slice colors as similar as possible?

FALSE (T/F)

Are line charts best suited for representing data that follows a non sequential order?

FALSE/NO (T/F)

In Excel when you delete a record from a table will a dialog box appear to confirm the delete operation?

FALSE/NO (T/F)

When using the PMT function to calculate monthly loan payments, should the interest rate and the number of payments be based on the interest rate per year and the total months to pay off the loan or the interest rate per?

FALSE/NO months (T/F)

What command is used to search through the current worksheet or workbook for the content or formatting you want to locate?

Find Command

Where are category values usually located in data source?

First row or the second column

What process do you use to enter text-based patterns it finds in the data?

Flash Fill

What are italic, bold, bold italic, and underline examples of?

Font Styles

What is the term that describes keeping headings on the screen as you work with the data in a large worksheet?

Freezing

What does the general syntax of a function?

Function(argument1, argument2,....) *if it has an equal sign choose it*

The NPV function represents the _______ value of all the cash inflows and outflows in the investment adjusted by the time value of money?

Future

When you nest functions, what should you consider with regard to the parentheses?

Have the same number of left and right parenthesis

What tab contains the Cells group used to delete records that are incorrect, out of date, or no longer needed?

Home tab

Which function is a logical function that evaluates a condition and then returns one value if the condition is true and another value if the condition is false?

IF function

When would you use a nest IF function?

If you have three or more outcomes

The Insert Function button is on which Tab and in which group?

In the function library (Formulas Tab)

What do you need to ensure when working with nested functions?

Include all the parenthesis (T/F)

How is a worksheet organized?

Individual Cells

To create a table, which tab on the Ribbon will you find the Tables group?

Insert Tab

By default how does Excel insert a new chart?

Inserts it as an embedded chart (T/F)

If you extended a series of the initial entry Jan using AutoFill, what will the series look like?

Jan, Feb, Mar, .....

What do you call the space between the page content and the edges of the page?

Margins

What are the common merge options?

Merge across

When you click in each cell in the Total row, what does the arrow reveal?

Most commonly used functions

What must you do to calculate the annual rate when you know value returned by the RATE function and the number of payments per year?

Multiply the value return by the rate function by the number of payments per year

What are the rules for valid table names?

Must start with a letter or an underscore (but can use any combination of letters, numbers, and underscores for the rest of the name) Cannot include spaces (EXAMPLE: __Newtable)

Do formatting changes affect the data itself?

NO (only the appearance of the data) (T/F)

=ROUND(AVERAGE(A1:A100),2) is an example of what type of function?

Nested function

To calculate the number of monthly payments required to pay off a loan or meet an investment goal, which function would you use?

Nper function

Which financial function calculates the number of payment periods in an investment or loan?

Nper function

How many charts can a chart sheet contain?

Only one chart

What do you need to set to print only data in cells A1:L1?

Page break

What characters do you need to use to change the order of operations in a formula?

Parenthesis

When you borrow money, the money you receive represents what type of cash flow?

Positive

What do you set that allows for information to be printed on each page?

Print Title

If you are creating a calculated column or formula within an Excel table, what type of reference would you use in the formula?

Qualified

When you want to reference an entire column of data in a table, what do you need to create?

Qualifier

Which financial function calculates the interest rate charged or received during each payment period?

RATE function

When creating an Excel document, what color combination of text and background should your avoid for the most common type of color blindness.

Red and Green

If all the logical conditions listed in an AND function are true what value will the AND function to return?

Return a "true" (T/F)

If all of the logical conditions are true, what logical value will the AND function return?

Return a "true" value

What are the steps to add fill color to a sheet tab?

Right click on the sheet tab, point to the tab color on the shortcut menu, and click on the color (ANSWER ON THE TEST IS LOOKING FOR *FILL COLOR*)

What are the sections that can used in the header and footer?

Right, Left, Center

Does the column width or the row heigh increase when text wraps within a cell?

Row height increases (T/F)

What option do you use to edit existing conditional formatting rules?

Rules from the conditional formatting in rules manager

What is the default, function used in a PivotTable report?

SUM

The range of values of an axis is based on the values in the data source is known as what?

Scale

What do you call the options that allows you change the width or the height of the printout so that all of the columns or all of the rows fit on a single page?

Scale

How does the VLOOKUP function search the lookup table for the value you entered?

Searches vertically down the first column

How do you resize an embedded chart?

Select the chart and drag the corner sizing handle of the selection box

How do you set a page break in Excel?

Select the first cell below the row where you want to insert the page break

What keys do you press to move the active cell up one row?

Shift Enter

Which type of reference makes it easier to create formulas that uses a portion or all of an Excel table because the names of headers are usually simpler to identify than cell addresses?

Structured reference

In which group on the Home Tab would you find the option to change the background fill color of duplicate values?

Styles

What are the two display options for the Subtotal feature?

Subtotal and an outline of your worksheet

What do you call a chart that compares three set of values in a three-dimensional chart?

Surface chart

Can you have a qualified or unqualified structured reference?

TRUE (ANSWER ON TEST IS TRUE)

If a formula is in a calculated table column and you need to make a change in only one cell, will that change be automatically be copied to all the cells in the column?

TRUE (T/F)

With a chart can you formatted, added to the chart, or removed elements from the chart?

TRUE (T/F)

By default, does AutoFill copy only the content not the formatting of the original range to the selected range?

TRUE/YES (T/F)

Can you create more than one Excel tables in a worksheet?

TRUE/YES (T/F)

Does the tracer arrow provides a visual clue to the source of the error?

TRUE/YES (T/F)

If a column consists of day or month labels, can you sort them in their correct chronological order using one of the predefined custom lists?

TRUE/YES (T/F)

If your data does not contain column headers will Excel create those?

TRUE/YES (T/F)

Is cash flow concerned with the direction of money as it moves in and out of a organization?

TRUE/YES (T/F)

Will the Subtotal command insert a subtotal row into the range for each group of data and adds a grand total row below the last row of data?

TRUE/YES (T/F)

Which financial function calculates the amount paid into an investment or loan during each payment period?

The PMT Function

What does the step value represents in an extrapolated series?

The amount each value is increased is an extrapolation

What is principal of a loan?

The amount of money being loaned

What are the advantages of using an electronic spreadsheet?

The content can easily be edited and updated to reflect changing financial conditions

In the function =CUMIPMT(rate, nper, pv, start, end, type), which argument defines whether the payments are made at the beginning or end of each period?

The end argument

In the following formula: =IF(A1="YES", "DONE", "RESTART"), what happens if A1= "NO"?

The formula will return the text "restart"

What does NPER stand for?

The total number of periods

What is the major difference between HLOOKUP and VLOOKUP functions?

The way it is organized

Fonts, colors, and cell styles are organized in what type of categories?

Theme and Non Themes

What are the values in a logical function considered to be?

True or False

How do you locate the Manage Rules option?

Using the conditional formatting button

Which function would you use to return a value from a table that is based on an approximate match lookup?

VLOOKUP

Does the direction of the cash flow matter in for most Excel financial functions return the correct value?

Yes direction matters (LOOK FOR DIRECTION ON THE TEST)

One of the great advantages of relative references?

You can quickly generate row and columns without having to worry about the revision of the formula as you copy them to a new location

Which Lookup function searches vertically down the first column of the lookup table?

the VLOOKUP