CS Exam 2

The maximum number of scenarios scenario manager can handle is

32

Where is Solver located if it is already loaded onto Excel

Analysis group on the Data tab

_____ are created to organize and summarize data in PivotTables

Categories

Which of the following will not remove a field name from a PivotTable?

Click a field name in the drag fields between areas below section and then press the delete key

Which of the following will not delete a PivotChart?

Click and drag the PivotChart off the worksheet

In addition to sorting and filter cells by content you can sort and/or filter by

Conditional Formatting

Which of the following is NOT an important element when creating a PivotTable?

Create meaningful record names

The subtotal command is located on the ____ tab

Data

The _____ dialog box contains the row and column input cell boxes

Data Table

A ____ is necessary for creating a PivotTable

Data source

Which of the following is FALSE about PivotTables?

Each row in the source data becomes a PivotTable field

Which of the following commands cannot be used on a constraint in the solver parameters dialog box?

Formatt

Which tools have the ability to change values in the original dataset

Goal Seek & Solver

Which what if analysis tool would be best determining how much you can borrow for a car loan while only paying $250 a month?

Goal seek

What is the main difference between goal seek and variable data tables?

Goal seek uses the original worksheet data to change an input

Which of the following is NOT one of the three parameters in Solver?

Input value

Which of the following is NOT a concern when adding subcategories to a PivotTable?

It improves the readability of the PivotTable

Click the ____ to sort or filter the chart representation based on the values

Legend Field arrows

The Sort by Value dialog box contains the following options EXCEPT:

Manual (you can drag items to rearrange them)

Which of the following is TRUE about scenario summary reports?

New reports appear on a separate worksheets

If you delete a PivotChart, what happens to the PivotTable?

Nothing

A two variable data table returns ____ results for each combination of two variables

One

A one variable data table must have a least ___ blank row and ____ blank column between the dataset and the data table

One; one

Where do you go to begin loading the solver add-in?

Options on the file tab

Data mining techniques such as PivotTable can detect ____ of data

Patterns

To build a PivotTable or PivotChart at the same time, click the PivotTable arrow and then

PivotChart

A ____ allows you to identify relationships between variables in your data

PivotTable

To change the PivotTable name, click in the ____ group and type the name in the PivotTable name text box

PivotTable

After a PivotChart has been created, the PivotTable Settings can still be changed using the:

PivotTable Field List

To view the PivotTable Field List, click Field List in the Show group on the ____ tab

PivotTable Tools Options

Predefined styles to format a PivotTable are available in the

PivotTable style gallery

Which of the following is NOT required for Solver to generate a solution?

Precedent values

To begin a filter based on a particular field, you drag the desired field to which area under drag fields between areas below?

Report Filter

Which of the following is NOT in the What-If Analysis command in the data tools group on the data tab?

Solver

Before using solver, it is recommended you complete which step first?

Specify the goal

Which of the following is NOT true about two-variable data tables?

The data table can produce two results for each combination of two variables

Which cell become the active cell when you press the "Enter"key?

The next cell down

The value you want to achieve in Goal Seek needs to be entered in the _____ box in the Goal Seek dialog box

To value

Which What-If Analysis would be best at comparing the combined effects of various interest rates and down payments?

Two variable data table

To show a "hidden" row one (1) or hidden column A

Type A1 in the name box and then press enter

Filtering the Date column (field) to show only records older than January 2001 is an application of

a Date filter

Filtering the Last_Name column (field) to show only records that begin with the letter S is an application of

a Text filter

A formula

a combination of cell references, operators values and/or functions used to perform calculations

A workbook is defined as

a file containing related worksheets

A sparkline is

a miniature chart contained in a single cell

An output area

a range of cells containing results based upon manipulation of the variables in the input area

An Input Area (as it applies to Excel 2010) is defined as:

a range of cells containing values for variables used in formulas

A worksheet is defined as

a single spreadsheet that often contains formulas, functions, values, text, and visual aids

A spreadsheet PROGRAM is defined as

a software application used to create and modify spreadsheets

The strength of a PivotTable lies in its:

ability to easily and quickly rearrange data

A 3-D chart

adds a third dimension to each data series, creating a distorted perspective of the data

A formula in Excel must begin with

an Equals (=) sign

Sorting text A to Z

arranges data in alphabetical order

The Reports list in the Solver Results dialog box displays the

available report types

Sorting arranges records in a table

by the value in field(s) within a table

The SUBTOTAL function

calculates an aggregate for values in a range or database

The AVERAGE function

calculates the arithmetic mean of values in a range

The PMT function

calculates the periodic payment for a loan with a fixed interest rate and fixed term

The show group includes all EXCEPT:

calculations

To create a calculated field select ____ located on the PivotTable tools options tab

calculations

A stock chart

can show the open, high, low, and close prices for individual stocks over time

After creating a scenario summary report, it is recommended that you do all of the following EXCEPT:

change the colors of the title row

To adjust the column width and/or row height of man cells at once

click and drag across the cells to select them and use any sizing method you use

To add a record (row) to a table

click in a cell and on the home tab, click insert arrow in the cells group

To begin a sort in a PivotTable, you must first:

click in any cell in the column that you wish to sort

To select a range by using the name box

click in the name box and type the range address such as B15:D25 and then press enter

To open a saved solver model you must first

click load/save in the solver parameters dialog box

To reverse the data series of a chart

click switch row/column in the data group

To create a table from an exiting range of data

click the Insert tab and then click Table in the Tables group

To convert a table back into a range,

click within the table, click the Table Tools Design tab, and then select Convert to Range

When data is grouped, the margin area displays the

collapse and expand buttons

PivotCharts look best when they use basic charts such as a:

column chart

A multiple data series chart

compares two or more sets of data in one chart

A bar chart

compares values across categories using horizontal bars

A single data series

compares values for one set of data

Solver can be used for ____

complex equation solving

A chart sheet

contains a single chart and no spreadsheet data

A nested function

contains another function embedded inside one or more of its arguments

A mixed cell reference

contains both an absolute and a relative cell reference

The table style options group

contains check boxes to select format actions such as header row which displays the header row when checked

A lookup table

contains data for the basis of the lookup and the data to be retrieved

The plot area

contains graphical representation of values in data series

A nonadjacent range

contains two or more cells or ranges that are not touching each other

The order of precedence (as it applies to math operations in Excel)

controls the sequence in which Excel performs arithmetic operations

The solver summary report is

created in a new worksheet

In a data table, the ____ number format is used to disguise the formula reference in the first row as label

custom

A print area

defines the range of data to print

After creating a chart you can change the chart type by using

design tab/ change chart type button

When you select a chart, Excel displays a chart tools contextual tab with three specific tabs

design, layout, and format

A total row appears as the ast row of a table to

display summary statistics such as sum

Formula Auto-complete

displays a list of functions and defined names as you enter a function

A column chart

displays data comparisons vertically in columns

Sorting dates oldest to newest

displays data in chronological order

The category axis

displays descriptive group names or labels, such as college names or cities, to identify data

The first section of a solver report

displays information about the report

The formula bar (as it applies to Excel 2010)

displays the content of the active cell

The TODAY function

displays the current date

The NOW function

displays the current date and time

The MIN function

displays the lowest value in a range

A sheet tab (as it applies to Excel 2010)

displays the name of a worksheet within a workbook

A surface chart

displays trends using two dimensions on a continuous curve

If a slicer obstructs the view of the PivotTable, ____ slicer to a new location

drag

After entering the substitution values and the reference to a formula, the next step to complete a two variable data table and view the results is to

drag to select the data table boundaries

An area chart

emphasizes magnitude of change over time by filling in the space between lines with a color

Wrap text

enables data to appear on two or more lines within a cell

Previewing the worksheet in Backstage view

enables you to adjust settings to control how the worksheet will print

A handle, indicated by faint dots on the outside border of a selected chart

enables you to adjust the size of the chart

Auto fill

enables you to copy the contents of a cell or to continue a sequence by dragging the fill handle

To create an effective outline in Excel, you must:

ensure the data has a hierarchical structure

The IF function

evaluates a condition and returns one value if the condition is true and a different value if the condition is false

The logical test

evaluates true or false

A faster way to expand all categories at one time is to click

expand entire field in the active field group

When you see Sum of Total...in the values area box, position the pointer over the _____ in the values area to see a screen top with the full name

field name

Top Bottom Rules

format cells based upon their value in relation to the value of other cells such as the top 10%

For a basic mathematical expression it is best to use

formulas such as =B4+C4

When using a slicer, the available filters appear in blue fill color, white unavailable filters appear in

gray

A clustered column chart

groups or clusters similar data in columns to compare values across categories

Using Conditional Formatting to draw attention to cells containing errors

helps locate errors in cells quickly

Using Conditional Formatting to draw attention to cells that are blank

helps locate where data may be missing

Highlight Cell Rules

highlight cells meeting specified criteria with a specified color, font, or border

Conditional Formatting

highlights or emphasize cells that meet certain conditions

A cell address (as it applies to Excel 2010)

identifies a cell by a column letter and row number

The name box

identifies the address of the current cell

The MAX function

identifies the highest value in a range

One benefit of using range names in formulas is

if you copy the formula, you do not have to make the cell reference absolute

When you paste copied data, Excel displays the paste options button

in the bottom right corner of the pasted data

The data type "text"

includes numbers, letter, and spaces not used in calculations

A relative cell reference

indicates a cell relative location from the cell containing the formula; the reference changes when you copy the formula

An absolute cell reference

indicates a cell's specific location and the reference does not change when you change the formula

A page break

indicates where data starts on a new printed page

To put a long text label on two or more lines within a single cell

insert a line break with the alt+enter key combination

in Excel 2010, dataset

is a collection of structured, related data in columns and rows

A record

is a complete set of data for an entity

A color scale

is a conditional format that displays a particular color based on the relative value of the cell contents to other selected cells

An icon set

is a conditional format that displays an icon representing a value in the top third, quarter, or fifth based on values in the selected range

A data series

is a group of related data points

A data bar

is a horizontal gradient or solid fill indicating the cell's relative value compared to other selected cells

The X-axis

is a horizontal line that boarders the plot area to provide a frame of reference for measurement

A gridline

is a horizontal or vertical line that extends from the horizontal or vertical axis through the plot area

A legend

is a key that identifies that color, gradient, picture, text, or pattern fill assigned to each data series in a chart

A chart title

is a label that describes a chart

An axis title

is a label that describes either the category axis or the value axis

A Border (as it applies to Excel)

is a line that surrounds a cell or a range of cells

A trendline

is a line used to depict trends and forecast future data

A data point

is a numeric value that describes a single value on a chart

The Table Array

is a range containing a lookup table

The lookup value

is a reference to a cell containing a value to look up

Syntax (as it applies to Excel 2010)

is a set of rules that govern the structure and components for a function

The fill handle (as it applies to Excel 2010)

is a small black square at the bottom right corner of a cell that facilities fill operations

A structured reference

is a tag or use of a table element as a reference in a formula

The Y-axis

is a vertical line that borders the plot area to provide a frame of reference for measurement

A chart

is a visual representation of numerical data

A range name

is a word or a string of characters that represent one or more cells

A table

is an area in a worksheet that contains rows and columns of related data formatted to enable data management and analysis

A spreadsheet

is an electronic file that contains a grid of columns and rows for related data

An argument (as it applies to Excel 2010)

is an input such as a cell reference or value needed to complete a function

A category label

is text that describes a collection of data points in a chart

Row Height

is the adjustable vertical measurement of a row

The column index number

is the argument in a VLOOKUP function that identifies from which column to return a value

A column index number

is the argument in a VLOOKUP function that identifies from which columns to return a value

Fill color

is the background color of a cell

A cell

is the intersection of a column and a row

The active cell (as it applies to Excel 2010)

is the location of the insertion point as indicated by a dark border

The break-point

is the lowest value for a specific category or series in a lookup table

Filtering

is the process of displaying only records that meet specific conditions

Semi-selection or pointing

is the process of using the mouse to select cells while building a formula

Print order

is the sequence in which pages print

the NPER

is the total number of payment periods

Freeze Top Row

keeps only the top row visible as you scroll through a worksheet

Freezing rows and/or columns

keeps them visible as you scroll through a worksheet

The VLOOKUP function

looks up a value and returns a related result from the lookup table

The LOOKUP function

looks up a value in a lookup table where the first row contains the values to compare with the lookup value

Filtering the cost column (field) to show only records greater than $10,000 is a

number filter

A PivotTable style controls all EXCEPT

number format

A circular reference

occurs when a formula directly or indirectly refers to the cell containing the formula

A 100% stacked column chart

places (stacks) data in one column per category with each column having the same height of 100%

Excel displays a green arrow in the top left corner of a cell if it detects a

potential error

A function is a

predefined formula that performs a calculation

Excel applies basic formatting to PivotTables such as

primary row labels in bold

To simplify entering ranges in formulas you can use

range names

A "range"

refers to a group of adjacent or contiguous cells

Horizontal Alignment (as it applies to Excel)

refers to the left-right position of contents in a cell

Vertical alignment

refers to the up-down position of contents in a cell

A bubble chart shows

relationships among three values by using bubbles

Each column in a table

represents a Field which can contain an individual piece of data for a record

Each row in an Excel table

represents a record

Auto-complete

searches for and displays any other similar label in the current column as you begin to type

A show iteration results option allows you to

see the values of each trial solution prior to reaching a final solution

The Show Iteration Results option allows you to:

see the values of each trial solution prior to reaching a final solution

To transpose columns and rows

select and copy the original range then click the top left corner of the destination range, click the paste arrow and then click transpose

To remove duplicate records

select any cell in the table, then click remove duplicates from the tools group on the table tools design tab

To ensure a PivotTable is up to date when the workbook opens

select the refresh data when opening the file in the PivotTable options dialog box on the data tab

To modify the PivotTable value settings you must first:

select the value in the appropriate filed in the PivotTable

An exploded pie chart

separates one or more pie slices from the rest of the pie chart

It is wise to create a PivotTable in a new worksheet because

separating the PivotTable from the original dataset will prevent accidental deletion of the original dataset

An X Y (scatter) chart

shows a relationship between two variables

A pie chart

shows each data point in proportion to the whole data series as a slice in a circular pie

Which of the following has to be loaded onto Excel

solver

In a what-if analysis of a car purchase, the original interest rates of 6% and 5%, 5.5%, 6.5% are the ____ values

substitution

To add a PivotTable choose insert PivotTable command from the ____ group

tables

The COUNTBLANK function

tallies the number of blank cells in a range

The COUNTA function

tallies the number of cells in a range that are not empty

The COUNT function

tallies the numbers of cells in a range that contain values

To insert the current date into an active cell use

the ctrl and semicolon key combination

A table style controls

the fill color of the header row, columns, and records in the table

the PV is

the present value of a loan

The PivotChart tools contextual tab includes all the following EXCEPT:

type

A PivotTable Report Filter filters data based:

upon a particular field

A PivotTabe Group Filter filters data based:

upon the row and column label groupings

To change the data source of a chart

use the select data source dialog box from the select data button

To freeze columns and/or rows

use the view tab, window group and click freeze panes

A line chart

uses a line to connect in order to show trends over a period or time

A doughnut chart displays

values as percentages of the whole but may contain more than one data series

When creating a range for the substitution values in a one variable data table, it is best to arrange them in a ____ orientation

vertical

You should use cell references in formulas instead of constant values so

you can change the input values without changing the formulas

"Copy as Picture" can be a useful command when

you need an un-editable "picture" of data to use elsewhere in the workbook or other programs

If you change any of the Solver parameters:

you will need to create another Solver report