CSI-1200 - Intro Cmptr and Prgmng w/Excel - CH5,4,7
The _____________ wildcard symbol is often used in filters and can represent any number of characters.
, asterisk, star
If you use the Color Scale conditional formatting, you are limited to how many colors (and their gradations?)
2 or 3
By how many levels will Excel allow you to sort?
64
What is the match_type argument value for an exact match in a MATCH function?
0, zero
If you have 3 possible outcomes in a nested IF statement, then you need _________ logical tests.
2, two
The criteria range must contain _________ at least rows in Advanced Filtering.
2, two
The YEAR function would return __________ on the value December 27, 2025.
2025
IF you use the function, =MATCH(66, A1:A4,0), on the table below, what would be the result?
4
If there are 5 possible outcomes, how many logical tests are required?
4
If 9/3/2021 is the contents of B3, what would be the result of =MONTH(B3)?
9
A PivotTable Calculated field starts with __________
=, equal sign, equal
If you need to find the day of the month on which events occurred from a list of dates in the named range of Start_Date, which formula would provide the correct result?
=DAY(Start_Date)
Which of the following is true about creating a blank PivotTable?
A PivotTable may be created on the same worksheet as the source data.
How does Excel indicate that a column has been selected in a structured reference?
A colored border appears around that column.
What is a nested function?
A function embedded within an argument of another function
Which is the Array argument in =INDEX(A2:B5,MATCH(MAX(B2:B5),B2:B5,0),1)?
A2:B5
You created a slicer on the field State. What items would you be most likely to see in the slicer?
AL, CA, GA, KY, MS, NC, OR, and VA
Which is not true about Slicers?
Aggregate values do not respond to slicers.
When using advanced filtering, leaving a cell empty in the criteria range will return which of the following?
All records
What is not true about using the AND function in a custom conditional formatting rule?
All the tests must be of the same data type, for example: all numbers or all text.
Which of the following is not a way to bring up the Go To dialog box?
Alt+F7
Match each of the following terms to its description: I. <> II. > III. < IV. <= V. >= A. Greater than or equal to B. Not equal C. Greater than D. Less than or equal to E. Less than
B, C, E, D, A
Match each of the following Data Modeling terms to its most accurate description: I. Data model II. Table III. Related field IV. Relationship V. VLOOKUP A. The link between the fields with matching data in two tables B. Collection of related tables C. The field with matching data in two tables used to identify a correlation D. Where data is stored in a data model E. Function similar to how a data model matches related fields
B, D, C, A, E
Match each of the following subtotal functions to its best description: I. SUM II. AVERAGE III. MAXI V. MIN V. COUNT A. The greatest value B. Adds the values in the field together C. The least value D. The number of values E. Adds the values in the field together and then divides by the number of values
B, E, A, C, D
Match each of the following terms to its best description: I. Array II. Lookup value III. Row_num IV. Column_num V. Position A. Return of the Match function B. The range of values to be used in an index function's first argument C. The second argument in the INDEX function D. The last argument in the INDEX function E. The first argument in the match function, the value to be found
B, E, C, D, A
Where does Excel display the Total row in a table?
Below the last row in the table
Which one of the following filter options is not found on the number filter submenu?
Bottom 10...
In a structured reference what do you use to indicate a column heading or field name?
Brackets []
Using the data bars conditional formatting, how is the highest value represented?
By the longest bar
Match each of the following PivotTable summary statistic functions to its mathematical operator or explanation: I. Sum II. Max III. Min IV. Product V. Count A. * or x B. (1, 2, 3, 4...) C. + D. < E. >`
C E, D, A, B
Associate the following functions with its corresponding purpose: I. INDEX II. OR III. MATCH IV. IF V. RATE A. Returns the position of a particular value B. Decision structure C. Returns the value of the intersection of a specified row and column D. Returns the interest rate of a loan or investment E. Returns true if any criteria are true
C, E, A, B, D
Which of the following is not a characteristic of a table?
Can contain multiple ranges
You created a PivotTable and later made changes to values in the underlying data. What happens to the PivotTable?
Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.
________ apply different formatting and color schemes to a PivotChart.
Chart Styles
if you wanted every cell that has a value over $1,251 to have a purple fill, you would use what feature in Excel?
Conditional formatting
What cannot be found on the Manage Rules dialog box?
Copy Rule
To what does the @ symbol refer in a structured reference?
Current row
If you need to perform complex filters on your data you will want to use the ____________ AutoFilter dialog box.
Custom
Match each of the following functions to its return: I. DAYS II. DAY III. YEAR IV. YEARFRAC V. MONTH A. Returns the year for a date value B. Returns the day of the month for a date value C. Returns the percentage of a year past between two dates D. Calculates the number of days between two dates E. Returns the month number of a date value
D, B, A, C, E
Match each of the following functions its best description of mathematical operation: I. DMIN II. DSUM III. DSTDEV IV. DAVERAGE V. DMAX A. > B. + C. Calculates the difference from the median D. < E. Mean
D, B, C, E, A
Match each of the following nested functions to its correct return based on this table: I. in D2 =IF(OR(B2="Blue",B2="Red"),A2,"else") II. in D4 =IF(OR(B4="Blue",B4="Red"),A4,"else") III. in D3 =IF(AND(B2="Blue",(NOT(B3="Green"),A2,"else") IV. in D3=IF(AND(B3="Blue",B3="Red"),A3,A4) V. in D4=IF(NOT(B4="Blue"),A3,A4) A. An error B. Phone C. Else D. Car E. Flower
D, C, A, E, B
Match each of the following tools to its best description of its function: I. PivotTable Styles II. PivotTable Layout III. Pivot Chart Styles IV. Quick Layout V. Change Colors A. Change the colors and backgrounds of PivotCharts B. Change the color scheme of a PivotChart C. Determine which totals and subtotals show D. Change the appearance of a PivotTable's color, font effects and shading E. Change the layout of the elements on a PivotChart
D, C, A, E, B
Match each of the following terms to its description: I. ROWS area II. COLUMNS area III. FILTERS area IV. Slicer V. VALUES area A. The first field in this area creates a filter that appears in cell B1. B. Fields added to this area have a calculation of some sort applied. C. A small window containing one button for each unique item in a field D. The field(s) used to organize a PivotTable along the left side of the table E. The field(s) used to organize PivotTable along the top of the table
D, E, A, C, B
If you need to find out how many days between two dates, which function would you use?
DAYS
The _______ function returns the difference between 2 dates.
DAYS
If your database field contains empty cells, you should use the ________ function to count it.
DCOUNTA
The __________ function identifies the highest value in a database column that matches specified conditions in a criteria range.
DMAX
Which database function returns the lowest value from the dataset based on your criteria?
DMIN
The _____ database function will add the values on a specified criteria range.
DSUM, thirty two
You would find the Group and Ungroup buttons in the Outline group on the ______ tab
Data
Match each of the following terms with its best conceptual description: I. Nested Function II. AND III. OR IV. NOT V. IF A. False = true B. At least one condition must be met for a true return C. All conditions must be met for a true return D. Tests conditions to process decisions E. Use the results of one function as an argument in another
E, C, B, A, D
Match the following functions with their categories of function: I. DCOUNT II. IPMT III. INDEX IV. YEARFRAC V. IF A. Logical B. Lookup & Reference C. Date & Time D. Financial E. Database
E, D, B, C, A
The formula = ________ (10/15/2018,2) would return 12/15/2018.
EDATE
Why would you put a negative sign in many financial functions?
Excel interprets these calculations as a negative cash flow (money leaving your account).
"John Smith" is a good example of a field.
F
A MATCH function returns the value of a given position.
F
A PivotTable has a live update feature which refreshes the data in real time as the dataset changes.
F
A PivotTable is limited to a single table of data.
F
A calculated field must be built in the original data set.
F
Advanced filtering will not copy results to a new location.
F
An INDEX function returns the position of a value.
F
An OR function evaluates to TRUE only if all of the conditions are true.
F
An empty cell in the criteria in an Advanced Filter will display no records.
F
Before you freeze panes make sure the active cell is one row above and one column to the left of the rows and columns you want to freeze.
F
Database functions do not require a Criteria Range.
F
Even if panes are frozen pressing Ctrl+Home will still make A1 the active cell.
F
Excel uses dashed blue lines to indicate manual page breaks.
F
Excel uses solid blue lines to indicate automatic page breaks.
F
Filter arrows are always displayed in data tables.
F
In Page Break Preview, the area to be printed has a light blue background.
F
It is impossible to create a data table from random, unorganized data.
F
Locking is the process of making sure that certain rows or columns are visible at all times.
F
Records are individual pieces of information.
F
Selecting a range automatically makes it a data table.
F
The DCOUNTA function returns an error if it encounters a blank record.
F
The MONTH function would return 25 when used on a cell containing August 25, 2018.
F
The YEARFRAC function returns the number of the month in a year.
F
To print row labels on the left side of each page you would select the row(s) that contain the labels in the rows to repeat at left box.
F
When the underlying data changes, a PivotTable automatically updates.
F
You can manually adjust a page break by pointing to the page break line and waiting for the four-headed arrow to appear, then drag the page break to your desired location.
F
You cannot create custom calculations in a PivotTable.
F
You need the same number of logical tests as outcomes in a nested IF function.
F
The four areas for organizing fields in a PivotTable are ROWS, COLUMNS, ___________ and VALUES.
FILTERS
In a PivotTable Fields task pane, the __________ filters the data to display results based on particular conditions you set.
FILTERS area
Which of the following is not an argument in a Database function?
Filter
Which one of the following filter options is not found on the date filter submenu?
First Quarter
Use the Sort by __________ Color if you want to sort records by the color of the text.
Font
What is not true about creating a data table?
For efficiency, make sure your data table does not exceed 256 rows.
What feature in Excel automatically displays a list of field headings when you begin to create a structured reference?
Formula AutoComplete
Which of the following is not a freeze option?
Freeze Section
The Table Styles _______ shows you all the table styles which are available.
Gallery
Which table style option shows the field names when it is checked?
Header Row
Which conditional formatting option would you choose if you wanted to apply formatting to cells whose value falls between two numbers?
Highlight Cells Rules
To go to the first unfrozen cell you would press Ctrl+_______ at the same time.
Home
Which function returns a value at the intersection of a specified row and column?
INDEX
Which one of the following filter options is not found on the text filter submenu?
Includes
The calculation option which displays the values of a specific field from greatest to least is Rank _______________
Largest to Smallest
What is the first argument of an IF function?
Logical_test
The _______ function returns the position of a value.
MATCH
Which function returns the relative position of a value?
MATCH
Which of these functions does not support multiple conditions?
Match
If you want to turn off grand totals for columns on a PivotTable, select this option in the Grand Totals options.
On for Rows Only
The ________ function calculates the principal payment for a specified period on a loan or investment.
PPMT
What function returns the total present value of an investment with a fixed rate, specified number of payment periods and a series of identical future payments?
PV
On which tab will you find options if you have to print a large dataset?
Page Layout
Using the Print _______ can help you save paper.
Preview
Which of these is not a capability of the Advanced Filter tool?
Provide pop up filters
Which wildcard character only replaces one character with one other character?
Question Mark ?
Where would you access the Manage Relationships dialog box to create a data model in Excel?
Relationships on the Data tab
What is not true about tables in Excel?
Row headings stay on the screen automatically.
The ________ function is the default summary statistic for PivotTables.
SUM
What would you do to format all values as Currency in the Sum of Extended Price field no matter how you pivot the data?
Select Number Format and choose Currency in the list of options and then click OK.
You have a PivotTable with 2 fields in the ROWS area and you want to see only the top level of categories. How do you hide the entire second level without removing it from the ROWS area?
Select a field in the row headers and click Collapse Field in the Active Field Group on the Analyze tab.
The option to display a value as a percentage of the whole rather than a raw calculation is found on the __________ tab of the Value Field Settings dialog box.
Show Values, Show Values as
A worksheet of faculty members at your university contains these columns in this sequence: Rank, Last Name, First Name, Department, and Salary. What is the first step to subtotal departmental salaries?
Sort by Department
Data _______ is the method used to organize data points in a dataset.
Structure
____________ references help to clarify what type of data is being used in any calculations.
Structured
An _______ quickly formats a PivotTable with bold, fonts, shading, headers.
Style, Pivot Table Style
Which is not true of filters in a PivotTable?
Summary statistics do not change to reflect the values selected in the slicer.
A NOT function reverses the truth of a logical test.
T
A PivotTable can display no subtotals and no grand totals.
T
A Recommended PivotTable creates a PivotTable on a new worksheet.
T
A Truth Table is a matrix that provides true or false for every possible combination for an OR, NOT, or AND criteria combination.
T
After a table has been named, that name can be used in functions instead of cell references.
T
An OR condition in an advanced filter requires at least two rows in the criteria range.
T
Auto Outline creates an outline based on the data structure.
T
Creating a data model in Excel is very similar to creating relationships between tables in Access.
T
DSTDEV calculates the standard deviation of a sample population in a database range.
T
In a Subtotaled Worksheet, this is a correct example of a subtotal function in cell B90 which sums B80:B89 : =SUBTOTAL(9,B80:B89).
T
In a data table calculated columns will copy down the table automatically.
T
Rows and/or columns which have been deleted in a data table can be brought back with the Undo command.
T
The Arguments of an INDEX function are array, row_num, and column_num.
T
The EOMONTH function returns the last day of the month of the specified number of months from a given date.
T
The database argument of a database function must include the entire range of the database.
T
The real functionality of MATCH and INDEX functions comes from nesting them.
T
This could be a valid formula in Excel: =[@Cost]-[@Pre_Paid_Taxes].
T
When you use the Freeze Panes option you freezing both columns and rows.
T
You can add a row to a data table as long as you put in the row immediately below the table.
T
You can apply filters on dates to display summary statistics for data occurring during a particular time period.
T
You can clear all fields from a PivotTable.
T
You can freeze as many columns and rows at the same time as you desire.
T
You can nest AND, OR and NOT functions in an IF function.
T
You can select multiple print areas.
T
Which is not an argument in an INDEX function?
Table
By default, what is the first table on a worksheet named?
Table 1
Which of the following is not a conditional formatting option?
Table format
The filter "Begins With..." option is found on which type of filter?
Text filter
In the formula =SUBTOTAL(102,[PAID]), what does the 102 represent?
The COUNT function
In conditional formatting what happens if the cell value does not pass the logical test?
The cell is not formatted.
What data does Excel print by default?
The entire dataset on the active sheet
When you have frozen rows and columns, what happens when you press the Ctrl+Home combination?
The first unfrozen cell becomes the active cell.
What best describes a criteria range?
The group of adjacent cells that specifies the conditions used to control the results of a filter
What type of match would the following function return when the lookup array is sorted in descending order? =MATCH(A3,B7:B53,-1)
The match would be the smallest value in the list without going over the value of A3.
In the formula =SUBTOTAL(102,[PAID]), what does the word PAID represent?
The range to be included in the calculation
If you use the Icon sets conditional formatting, you are limited to how many categories?
Three, four, or five
What is the purpose of the Row_num argument in the following INDEX function? =INDEX(A2:B5,MATCH(MAX(B2:B5),B2:B5,0),1)
To return the position of the highest value in the range B2:B5
Why would you nest MATCH in an INDEX function?
To use the position returned in the MATCH function as an argument in the INDEX function
A slicer can be formatted so the ___________ display in one or many columns.
buttons
Moving one of two fields from the ROWS area to the COLUMNS area of a PivotTable would ________.
consolidate data and remove redundant labels
If you wanted to sort the days of the week in their natural order, not alphabetically, you would set up a(n) __________ sort.
custom
If you have related data stored in multiple tables, create a(n) ________ to create a PivotTable on the combined data
data model
The first argument of any database function is _________
database
A PivotTable time line can filter dates by all of these, except ________.
decade.
In a data table each column is considered a(n) _____
field
A(n) __________ hides some values from a field in a PivotTable.
filter, slicer
When you set up various conditions with the purpose of only showing a subset of data, that process is called _____________
filtering
When you include table names in a structure reference it is called a(n) ____________ structured reference.
fully qualified
Grouping is ________.
joining rows or columns of related data into a single entity for analysis
A(n)________ is a schedule that calculates the interest per payment period, principal, repayment, and remaining balance.
loan amortization table
An ______ is a hierarchical structure used to group and summarize related data.
outline
A primary benefit of creating a PivotTable is that you can rearrange or ___________ fields to view and summarize data from different perspectives.
pivot, turn
A collection of fields about one person would be considered a(n) ____________
record
In a data table each row is considered a(n) _______
record
In database functions, <>, >, =, <, >= and <= are called __________
relational operators
An ________ associates matching fields between 2 tables.
relationship
A PivotChart ________.
represents the consolidated data of a PivotTable visually
The advanced filter tool can ________.
search for unique records, only
You can see how many records meet the conditions of the filter by looking at the ____________ bar.
status
In a data table you can use ________ references in place of cell references inside the formulas.
structured
Table ______ add color and borders to a table.
styles, style
Which of the following is not controlled by a PivotTable style?
subtotal calculations
An ______ is a range of organized, related data.
table
The YEARFRAC calculates ________.
the fraction of a year in days between two dates
A(n) ________is a matrix that provides the results (TRUE or FALSE) for every possibility of an AND, OR, or NOT criteria combination.
truth table
In a structured reference, when you use field headings without row references it is called a(n) _________ reference.
unqualified
Excel indicates where page breaks will occur by placing temporary ____________ (such as Page 1, Page 2) on your display.
watermarks
Match the following Conditional Formatting Options in the Quick Analysis Gallery with their description: I. Text Contains II. Duplicate Values III. Unique Values IV. Equal To V. Clear Format A. Formats cells that have exactly the same value B. Formats any cell that has content that matches the first content in the first selected cell C. Removes any conditional formatting D. Formats cells that have no matching valuesE. Formats cells that have the value
B, A, D, E, C
If you wanted several cells to be filled with a certain color where the width of the fill color in the cell represents that cell's value compared to the values in the other cells, you would use the Data ____________ conditional formatting option.
Bars
What would be the result if you sorted the following dates in ascending order: December 25, 2000; January 1, 2015; January 16, 1957; December 26, 1959?
January 16, 1957; December 26, 1959; December 25, 2000; January 1, 2015
Which table style option is useful to display aggregated data when it is checked?
Last Column
What is not true about creating a data table?
Make sure to leave one blank row below the data labels.
__________ page breaks can be inserted if the automatic page breaks would occur at undesirable places.
Manual
What is the default data table style?
Medium 2
By default Excel prints page in what order?
Top-left section, bottom-left section, top-right section, and bottom-right section.
Which conditional formatting option is not found in the Quick Analysis Gallery?
Undo Format
If you create a Recommended PivotTable on a group of data about book sales with theses columns: Discipline (i.e. Business), Area (i.e. Finance and Marketing), Units Sold, Wholesale Price, what column might Excel use as values?
Units Sold
When would you not need to use a fully qualified structured reference?
When referring to a cell within the table
Which of the following would be an effective calculated field labeled Retail Price to calculate a retail price that is 300% of wholesale price (for example, the wholesale price is $3 and retail price is $9) from a Wholesale Price field?
Wholesale Price * 3 with a Name of Retail Price in the Insert Calculated Field dialog box
What is true about adding a new field to the right side of an existing data table?
You can add it to the right of the data table and Excel will extend the table to include the new field.
A subtotal row must contain at least one ________ function.
aggregate
The print __________ is the range of cells that will be printed.
area
