MISY 5325 MS Excel Midterm Exam Study Guide
1.Add-in 2.Binding constraint 3.Constraint 4.Changing variable cell 5.Solver
1.A program that can be added to Excel to provide enhanced function 2.A constraint that Solver enforces to reach the target value 3.A limitation that imposes restrictions on Solver 4.A cell containing a variable whose value changes until Solver optimizes the value in the objective cell 5. An add-in application that manipulates variables based on constraints to find the optimal solution to a problem.
1.Slicer 2.Filter 3.Calculated Field 4.PivotChart 5.Slicer caption
1.A small window containing one button for each unique item in a field so that you can filter the PivotTable quickly 2.Excludes particular categories or values 3.A user-defined field that does not exist int he original dataset 4.An interactive graphical representation of the data in a PivotTable 5.Text that displays in the header at the top of the slicer window
You can apply text, value, and date filters to fields based on the data in a particular field.
true
What is the best option for adding a calculation to a PivotTable that does not already exist?
Calculated field
compares groups set side by side relationship of individual data points to the whole
Clustered column chart Stacked column chart
Goal Seek appears in which tab group within Excel?
Data Tools
Where is the Subtotal command located on the Ribbon?
Data tab
Which of the following is Not a number format available in the Number group on the Home tab?
Date Format
What does the Values Area of a PivotTable do?
Displays summary statistics, such as totals or averages.
Which of the following is not a method supported by Solver?
Duplex
Function key __ accesses to Go To command in Excel.
F5
A range name contain up to 450 characters and it must begin with a number.
False
An argument is an operator such as an equal sign or a plus sign needed to complete a function.
False
Because the Sum function is a rarely used function it has no button on the Ribbon.
False
Connections are automatically created in PowerPivot.
False
Excel 2013 automatically assigns a different pattern to each column of a clustered column chart.
False
Formatting data makes it difficult to read and tends to obscure meaningful details.
False
In Excel 2013, it is only possible to add a new worksheet when the workbook is created.
False
The Date number format only allows Long Date, for example, march 14, 2016.
False
The IF function only evaluates a condition of the condition is true.
False
To begin a formula in a cell, first type the # symbol.
False
Which of the following is False about a function in Excel
Functions are organized into two categories in Excel.
The ___ command is helpful for navigating to a cell that is not visible onscreen
Go to
Click Format in the Cells group o the __ tab to change the color of a worksheet tab.
Home
To begin creating sparklines on a worksheet, click the __ tab.
Insert
The __ function finds the midpoint value of a list.
Median
The ___ view displays print options and displays a worksheet in print preview
Office Backstage
A(n) ___ is a structured range that contains different values for one variable to compare how these values affect one or more calculated results.
One-variable data table
Which of the following is the best tool for analyzing employee performance over a period of time?
PivotTable
Which of the following is not true about PivotTables?
PivotTables cna Auto Refresh data
PivotTables can be created from the __ gallery.
Quick Analysis
Which of the following is a new feature in Excel 2013?
Quick Analysis
Which tool is best suited for use with PivotTables when the data is based on multiple tables?
Slicers
Which is the best tool for determining the optimal blend of products to produce in order to maximize profit?
Solver
____ has the ability to generate Answer Reports.
Solver
___is the best what-if analysis tool to solve complex linear and nonlinear problems
Solver
the analysis tool has the ability to handle multiple adjustable cells while minimizing, maximizing, or meeting goals.
Solver
Which of the following calculations cannot be completed with subtotal?
Standard Deviation
A Special number format allows characters such as hyphens.
True
Axes, chart tittles, and data tables are examples of chart elements.
True
Comma, Date, Time, and Fraction are all number formats available in Excel 2013.
True
Excel automatically determines the starting, incremental, and stopping values on a chart based on the data selected.
True
Excel data can be copied and pasted into a Word or PowerPoint document.
True
In Excel 2013, the function name describes the purpose of the function.
True
Label filters such as "Does Not Equal" can be applied to a PivotTable.
True
Multiple filters can be applied to a PivotTable.
True
Text in a cell can by angle clockwise
True
When you hide a column or row, the data is not deleted, it is just hidden.
True
Which of the following is best suited for comparing the effects of two variables on a formula?
Two-variable data table
active cell
What is the part called that has the BLACK BORDER BOX
columns
What part of Excel Window is labeled with the ALPHABET
rows
What part of Excel Windows is labeled with NUMBERS
Which of the following is NOT one of most commonly used chart types?
X Y chart
A chart is:
a visual depiction of numerical data
A(n) ___ chart is a derivative of a scatter chart in which both axes are value axes.
bubble
A(n) __ is a user-defined field that does not exist in the original dataset.
calculated field
In addition to sorting and filtering cells by content, you can sort and/or filter by __ formatting
conditional
In addition to sorting and filtering cells by content you can sort and/or filter by __.
conditional formatting
___ specify the restrictions or limitations imposed on a spreadsheet mode as Solver determines the optimum value of the objective cell.
constraints
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.
To ___ a table into a range, click within the table, click the Table Tools Design tab, and from the Tools group, select Convert to Range.
convert
PivotTables automatically refresh data.
false
To print only a chart, select the chart, click the __ tab, and click Print.
file
Ctrl P
the shortcut key to PRINT a WORKBOOK
Ctrl S & F12
the shortcut key to SAVE a WORKBOOK
Ctrl O
the shortcut key to open WORKBOOK
Ctrl N
the shortcut key to open a NEW WORKBOOK
The IF function in Excel 2013 has __ arguments.
three
1.Scenario Manager 2.Scenario Summary Report 3.Scenario 4.Goal seek 5.Optimization model
1.Enables you to define and manage scenarios to compare how they affect results 2.A worksheet that contains scenario results 3.Detailed sets of values that represent different possible situations 4.A tool that identifies the necessary input value to obtain a desired goal 5.Finds that highest, lowest, or exact value for one particular result by adjusting values for selected variables
1.Filtering 2.Top 10 option 3.Custom Filter 4.Duplicate Values 5.Sorting
1.displays only records that meet conditions 2.specify a number or percentage of records to display 3.complex filtering requirements 4.conditional formatting that displays doubled cells 5.arranges records in a table by the value
What is the maximum number of scenarios supported by Scenario Manager?
32
Excel enables you to sort data on __ different levels.
64
Match the argument types to their function: (Number1) () (logical_test) (....,table_array,...) (rate,..,...)
=SUM =TODAY =IF =VLOOKUP =PMT
Formula ___ displays a list of functions and defined name that match the letters that you start typing when creating a formula.
AutoComplete
A(n) __ is a grid that contains the data source values and labels.
data table
The IF function:
evaluates a condition and returns one value if the condition is true, a different if false.
Freeze Panes only locks columns above the active cell as you scroll through a worksheet.
false
Table style controls Table Style Options group Sorting Text A to Z Sorting Dates oldest to newest Sorting Values smallest to largest
fill color of the header row, columns and records in a table check boxes to select format actions arranges data in alphabetical order displays data in chronological order arranges numbers in sequential order
A chart__ controls which data series and categories are visible in a chart.
filter
Top/Bottom Rules:
format cells based upon their value in relation to the value of other cells.
The ___ Table Style option displays the field names of a table when checked.
header row
Highlight Cells Rules Top Bottom Rules Data bar Color scale Icon set
highlights cells meeting criteria with a color, font, or border formats cells based on the value in relation to the other cells horizontal gradient of solid fill indicating a cell's relative value conditional format that displays a particular color based on the relative value of a cell conditional format that displays a small image representing a value
Gridlines on a chart in Excel 2013 are:
horizontal and vertical line that span across the chart
An absolute cell reference:
indicates a cell's specific location and the reference does not change when you copy the formula.
The lookup value:
is a reference to a cell containing a value to look up.
A range name:
is a word or string of characters tat represent one or more cells.
Print order:
is the sequence in which pages print.
A stacked column chart:
places stacks of data in segments on top of each other in one column, with each category in the data series represented by a different color.
A(n) __ defines the range of data from a worksheet to print.
print area
A(n) ___ is an organized formatted structured report that appears int he form of a worksheet outline.
scenario summary report
A pie chart
shows each data point in proportion to the whole data series as a slice in a circular pie.
A(n) __ is a small window containing one button for each unique item in a field so that you can filter the PivotTable quickly.
slicer
A range name cannot include special characters or __
spaces
Ctrl W
the shortcut key to CLOSE A WORKBOOK
