CIS 30 Excel - Final Exam prep
A name with ________ scope can be referenced anywhere within the workbook
global
A worksheet ___________ is a collection of two or more selected worksheets.
group
A ______________ is an organization of fields that start with the most general going down to the most specific.
hierarchy
When you click a(n) _________ , the computer switches to the file or portion of the file that it references.
hyperlink
With Goal Seek, Excel changes the _________ values to produce the result values you are looking for.
input
A(n) _________ procedure starts with an initial solution and uses it as a basis to calculate a series of solutions.
iterative
The ____________ identifies which data markers are associated with each data series.
legend
A _______ chart plots a data series against a set of category values that are assumed to be arranged in sequential order.
line
If you need to use data located in one workbook in the other workbook, you have to create a(n) _____________ between two workbooks.
link
What trendline should you add to a chart for data that increases or decreases quickly and then levels out?
logarithmic trendline
PivotTable ____________ is a calculation that summarizes data from a Data Model table.
measure
Using Solver Report ________ constraints means that these constraints are not limiting factors in arriving at the solution.
non-binding
In addition to displaying subtotals, the Subtotal feature "__________" your worksheet so you can control the level of detail that is displayed.
outlines
To change the order of operations, enclose part of the formula in ______________.
parentheses
A chart that compares relative values from different categories to the whole is called a(n) _____________ chart.
pie
The __________ is a part of the chart that contains the graphical representation of all the data series in the chart.
plot area
In a worksheet you can select the cells you want to print and then define them as a ________ area.
You can repeat information, such as company name, by specifying which rows or columns in the worksheet act as ____________ , information that prints on each page.
print titles
A database in which tables are joined in table relationships through the use of common fields are know as ________ database.
relational
In Excel, dates are _________-aligned in the cell by default, regardless of date format.
right
_________ is a program that searches for the optimal solution of a problem involving several variables.
solver
Before you add Subtotals to a data range, you must first __________ the data range by order the field for which you want to subtotals calculated.
sort
A _________ PivotTable is based on an Excel table or data range and not the Power Pivot Data model.
standard
The following is an example of a delimiter.
tab
In a database a collection of data that is stored in rows and columns is called:
table
A _____________ is a workbook that you can open with the text, formats, and formulas already built into it.
template
In Excel a ___________ data table is a data table with two input values and a single result.
two-variable
The three items that you must specify when using Solver are ____.
variable cell, objective cell, and constraints
A cell containing a number displays #####, why does this occur?
The number of digits could not fit within the width of the cell.
Changing the number of decimal places displayed in a cell does not change the value stored in the cell.
True
Excel automatically groups the date fields that are not added to a Pivotable into quarters, months, and years if the dates span more than one year.
True
Index one of PivotTable calculations, calculates the relative importance of the cell with the PivotTable.
True
Number data is any number that can be used in a mathematical calculation.
True
The data is usually organized in columns separated by character known as a delimiter.
True
A _______ is a single value that describes a person, place , or thing.
field
_________ functions find values in tables of data and insert them in another location in the worksheet such as cells or formulas.
LOOKUP
In a database a column of a table that stores information about a specific characteristic for a particular person, place, or thing is called:
field
The process of displaying a subset of rows in the table that meets the criteria you specify is called:
filtering
Italic, bold, bold italic, and underline are examples of ________________.
font styles
Cell B2 contains the formula, =SUME(A1:A100), with the name of the SUM function misspelled as SUME. What error value will appear in the cell?
#NAME?
What is the 3-D reference to add the values in cell E22 in the worksheets: Quarter1, Quater2, Quarter3, Quarter4, all included in a workbook
'Quarter1:Quarter4'!E22
The condition _________ requires that all of the selected criteria be true for the record to be displayed.
AND
The first three selected values in a series are 3, 6, and 9. What are the next three values that will be inserted using AtuoFill?
12,15,18
A ________ reference refers specifies not only the rows and columns of a cell range, but also the names of the worksheets on which the cells appear.
3-D
What is the result of the expression =50+20/10*5-2?
58
The formula to divide the value in cell C9 by the value in cell B9 is ___________.
=C9/B9
What formula would you use to display the text string "Yes" if the value in cell A1 is greater than the value in cell B1 and "No" if otherwise?
=IF(A1>B1, "Yes", "No")
Write a conditional formula that displays text Outstanding if the amount owed in cell K5 is above 0 and the transaction date in cell D5 is before 10/25/2019, but otherwise leaves the cell blank.
=IF(AND(K5>0, D5<10/25/2019), "Outstanding", "")
What is the function to return the index number of the cell within the range E1:E50 that is equal to "Carter", using exact match?
=MATCH("Carter", E1:E50, 0)
What formula with functions you can enter to calculate the ratio of the maximum values in the range C20: C52 to the minimum value?
=MAX(C20:C52)/MIN(C20:C52)
What function would you enter to add the values in cells B4, B5, and B6?
=SUM(B4:B6)
Cell E11 contains the formula = SUM(E1:E10). How is the formula adjusted when a new row inserted above row 5?
=SUM(E1:E11)
Write a formula to retrieve the value from the third field in the Sales_Result table that exactly matches the lookup value in cell B10.
=VLOOKUP(B10, Sales_Result, 3, FALSE)
Provide the formula to perform an exact match lookup with the lookup value from cell G5 using lookup table located in range A1:F5. Return the value from the third column of the table: _______________.
=VLOOKUP(G5,A1:F5,3,FALSE)
The range A1:H1 is merged into a single cell. What is the cell reference of this merged cell?
A1
What is the range reference for the block of cells from A5 through F8 _________.
A5:F8
The _________ function is a logical function that returns a TRUE value if all of the conditions are true.
AND
________ makes it easier to enter repetitive text in cells.
AutoComplete
The __________feature automatically adjusts a column width or row height to accommodate its widest or tallest entry.
AutoFit
To count cells containing non-numeric data such as text strings, you need to use the ___________ function.
COUNTA
You can calculate the number of cells in a range that match criteria you specify using the _____________ function.
COUNTIF
_______ analysis is an important business decision-making tool because it predicts the effect of cutting overhead or raising prices on net income.
CVP
The three PivotTable layout options are:
Compact, Outline, Tabular
________ provide descriptive text for the individual data markers, such as pie slices.
Data Labels
logarithmic trendline
Data Model
Slicer can be used with _____________.
Excel tables
Is Report-Data a valid named range?
False
When you copy a formula that contains an absolute reference to a new location, the reference changes.
False
You can create a PivotChart without a PivotTable.
False
___________ is the process of changing a workbook's appearance by defining the fonts, styles, colors, and decorative features.
Formatting
____________ rows and columns lets you keep headings on the screen as you work with the data in a large worksheet.
Freezing
A(n) _____________ is a conditional format in which different icons are displayed in a cell based on the cell's value.
Icon set
_____________ filters, which select data based on the labels of the items in the field.
Label
The ____ language is the language used by the Query Editor in constructing ata queries.
M
_______ references are seldom use other than when creating tables of calculated values such as a multiplication table in which the values of the formula or funciton can be found in the initial rows and columns of the table.
Mixed
One way to ensure that you are using consistent formats is to copy and paste your formats using Format __________.
Painter
__________ is a graphical representation of PivotTable.
PivotChart
A ____________ is an interactive table that groups and summarizes data in a concise tabular format
PivotTable
___________ is a 3-D visualization tool for charting data against a virtual globe.
Power Map
_______________ display the output values of interest in the different scenarios.
Result cells
What default statistics is used for numeric data in the Values area on the PivotTable?
SUM
A chart that displays entirely within a worksheet cell is called ____________.
Sparkline
You can use a __________ function to return a value from a table that is based on an approximate match lookup.
VLOOKUP
What is the function to retrieve the letter grade stored in the second column of the GradeScale table using a lookup value of 83 in the approximate match lookup?
VLOOKUP(83, GradeScale, 2)
What is the syntax to reference cell B21, in sheet Summary, in workbook Sales.xlsx
[Sales.xlsx]Summary!B21
The conditional formula: =IF(G22="x",SUM(H22:J22), "") will display ________ if G22 is not equal "x".
a blank cell
A(n) _________ is a customized program that adds command and features to MS Office programs such as Excel:
add-in
A formula can include a reference to another workbook (called an ________ reference), which creates a set of linked workbooks.
external
To apply calculations with PivotTable, you identify a _____________ which is a field used as the basis for comparison.
base field
The point where revenue equals expenses is called:
break-even-point
The information used to create a PivotTable is stored within a data structure called the PivotTable _________.
cache
You can place a chart in a _________ so that the entire sheet contains only the chart and no worksheet cells.
chart sheet
A _________ format applies formatting only when a cell's value meets a specified condition.
conditional
Fill handle copies ________.
content and formats
A ___________ indicates the sequence in which you want data ordered.
custom list
A _______________ is a conditional format in which a horizontal bar is added to a cell background with the length of the bar proportional to the cell's value.
data bar
A _________ contains the actual values that are plotted on the chart.
data series
If you sort an Excel table data from the most recent purchase date to the oldest purchase date, in what order have you sorted the table:
descending
To manually sort the items within a PivotTable field, ________ the field items in the order you prefer.
drag
A(n) ________ chart is a chart that is placed in a worksheet next to its data source.
embedded
