Excel Final Review (Chapter 1-8)

A new Excel 2013 workbook has how many worksheet(s)?

One

The cell D4 refers to the cell at:

The intersection of column D and row 4

Long Date format.

Saturday, January 1, 2000

Formulas begin with which character?

=

Symbol for absolute reference?

$
Example: $G$2

Sum, Average, Minimum, Maximum Functions.

=SUM
=AVERAGE
=MIN
=MAX

AutoSum is located...?

On the Home tab, in the Editing group

The zoom slider is located...?

At the lower right corner of the status bar

keyboard shortcut to begin the spelling checker is:

F7

Can be performed in Backstage.

1. Enable Editing when a workbook is in Protected View.
2. Preview how a workbook will look when printed.
3. Create a new workbook from a template

When you cut and paste a cell containing a formula with relative references, Excel...?

1. Does not update the cell references to the new location. 2. Treats the formulas as if it contained absolute references.

Which paste option pastes formulas and number formatting but not the cell formatting?

Formulas & Number Formatting.

How to split a cell into two cells.

You cannot split a cell into two cells; you can only unmerge merged cells.

How to apply the Double Underline accounting underline format.

In the Font dialog, on the Font tab, expand the Underline list and select Double Accounting.

Clicking the Borders button on the Ribbon applies which border style?

The most recently used border style.

The keyboard shortcut to open the Quick Analysis tool is?

Ctrl + Q.

What conditional formatting option do you use to highlight cells with values in the top 10% of the select range.

Top/Bottom Rules.

How to remove conditional formatting?

Clear Rules from Selected Cells command.

Selecting a cell and pressing Delete clears what?

cell content only.

Where is the Set Print Area command located?

On the Page Layout tab, in the Page Setup group.

Methods you can use to enter a formula using the AVERAGE function.

1. Formula Auto
2. Complete AutoSum
3. Function Arguments dialog

To count the number of cells that contain text or numerical values, but not blanks, use which function?

COUNTA

What function returns the current date and time?

NOW

What is the definition of concatenate?

To link items together.

What cannot be in a named range?

Spaces
Example: Bonus Rate

Identify the Value_if_true argument in this formula: =IF(A1>50,"bonus","no bonus")

"bonus"
Syntax: =IF (logical_test, [value_if_true], [value_if_false])

Identify the Pv argument in this formula:
= - PMT(B2/12,B3,B4)

B4
Syntax: = - PMT(rate, nper, pv, [fv], [type])

In a PMT function, what is the Pv argument?

The present value of the loan.

A formula to display the value of cell B2 on the Sales worksheet looks like this:

=Sales!B2

Dependant

The cell containing a formula that references the value or formula in the selected cell.

T/F: If you delete a worksheet, you can undo the action.

False

To group worksheets that are not next to each other, select the first worksheet and press the ________ key, and then click the other sheet tabs you want included in the group.

Ctrl

Theme

A unified color, font, and effects scheme you apply to a workbook.

To keep column headings as you scroll through a large spreadsheet you should use the ________command.

Freeze First Column

T/F: When you hide a row or column, the data remain in your workbook. They are no longer displayed on-screen but will be part of the printed workbook.

False

To display a worksheet in two or four panes, you should _______ the worksheet.

split

Footer

Text that appears at the bottom of every page just above the margin.

Headings

Numbers at the left of rows and the letters at the top of columns.

T/F: If your worksheet is wide, you may want to print with portrait orientation where the height of the page is greater than the width.

False

Margins

Allow for more or less white space around your worksheet area when printing.

T/F: Data markers are graphic representations of the values of data points in a chart.

True

Pie Chart

Represents data as parts of a whole.

Quick Styles

Apply combinations of fonts, line styles, fills, and shape effects to charts.

Sparklines

Represent each data series as an individual graphic within a single cell.

A Total row can be used to quickly calculate the ______ and ______ of all the values in a column.

Sum and Average

T/F: When you format data as a table, the header row automatically includes filtering and sorting.

True

Goal Seek Command

Lets you enter a designated value for a formula and specify an input cell that can be modified in order to reach that goal.

Sorting

Rearranges the rows in your worksheet by the data in a column or columns.

Data Table Command

Provides a quick what-if analysis of the effects of changing a single variable within a formula.

Pivot Table

A special report view that summarizes data and calculates the intersecting totals.

Identify the result of this formula: =ROUNDUP(4.9876021,2)

4.99

Identify the Sum_range argument in this formula: =SUMIFS(JuneOrdered,JuneItems,B1,JunePOs,B2)

JuneOrdered
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, critera_range2, criteria2)

What function do you use to find the middle value in a list of values.

MEDIAN

To create an array formula, press _______ after entering the formula in the formula bar.

Ctrl + Shift + ↵ Enter

T/F: Identify the result of this formula: =IF(3<4,AND(1<2,5>6),OR(1>2,2<1))

False
Syntax: =IF(logical_test, [value_if_true], [value_if_false])
[value_if_true] =AND(logical1, logical2)
[value_if_false]=OR(logical1, logical2)

Correct formula to calculate how much money you will have in an investment account where $200 is invested every month for two years at 3.5% annual interest.

=FV(3.5%/12,24,-200)
Syntax: =FV(rate, nper, pmt)

What function do you use to calculate the present value of a future investment with variable inflows and outflows of capital?

NPV
Syntax: =NPV(rate, value1, [value2])

Identify the Salvage argument in this function: =SLN(B2,B3,B4)

B3
Syntax: =SLN(cost, salvage, life)

Y/N: When naming a range of cells to be used as the Array argument with the INDEX function, should you include the label row?

No

T/F: The IFERROR function displays TRUE if the formula results in an error and FALSE if it does not.

False

Most common combination for a combo chart?

Column and line

T/F: Use the Chart Styles button that appears at the upper right corner of a selected chart to change the style for a selected data series or single data point.

False

What method allows you to separate a data point in a pie chart?

Enter a value in the Point Explosion box in the Format Data Point task pane.

You can apply a chart template that you've saved from what method?

On the Insert tab, in the Charts group, click the Recommended Charts button, and then click the All Charts tab.

Trendline uses:

1. Analyze the relationship between data points in a chart.
2. Forecast future values on a chart.
3. Estimate previous values on a chart.

Trendline

Used for regression analysis

T/F: To change the Sparkline type, you must first clear the existing Sparklines.

False

T/F: When you insert a picture into a worksheet, it is inserted into a specific cell.

False

T/F: When you add a picture to a worksheet header, it is not visible in Normal view.

True

T/F: You can insert a screenshot of any open window, even if it's minimized.

False

To open the Sort dialog,

1.On the Data tab, in the Sort & Filter group, click the Sort button.
2. On the Home tab, in the Editing group, click the Sort & Filter button, and click Custom Sort.
3. Right-click any cell in the data set, point to Sort, and click Custom Sort.

What option is not available from the Text Filters menu?

Greater Than

T/F: When setting up the criteria range for Advanced Filter, type criteria in separate rows if data must meet any one of the criteria to be included in the filter.

True

Before adding subtotals with the Subtotal command, you must __________ the data.

Sort

The Subtotal command uses which function to create subtotals?

SUBTOTAL

If you want to summarize data without using an outline format or including formulas, use which command?

Consolidate

T/F: When you show a scenario, any changing cells that included formulas originally will be converted to values.

True

To install Solver, open the Excel Options dialog, and go to which section?

Add-Ins

When setting up the Solver parameters, which cells must include a formula?

Objective

T/F: When a PivotTable's source data updates, the PivotTable and PivotChart update automatically.

False