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