BUS 311 Excel Chapter 5 TESTBANK QUIZ
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
Grand total
The full summary of all items in a group
MAX
The greatest value
MIN
The least value
Relationship
The link between the fields with matching data in two tables
COUNT
The number of values
VALUES
VALUES
Table
Where data is stored in a data model
Slicer
A small window containing one button for each unique item in a field.
Group
A tool to organize items in an outline
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
You would like to see both average sales and total sales for a category in a pivot table. Which of these would best accomplish both a Total and an Average column on the field Sales?
Add sales to the VALUES area and change its Value Field Settings to Average in the Summary value field by and then repeat, allowing it to default to SUM.
Which function is not a Subtotal function?
Aggregate
Which is not true about slicers?
Aggregate values do not respond to slicers.
The slicer ________ is text that displays in the header at the top of the slicer window to identify the data in the field.
Caption
What field is in the ROWS area of the PivotTable that built this PivotChart?
Category
PivotTable Styles
Change the appearance of a PivotTable's color, Font, effects and shading
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.
__________is the process of analyzing large amounts of data to identify trends and patterns in the data
Data Mining
A(n) ________________ is a collection of related tables forming a database.
Data model
A PivotTable time line can filter dates by all of these, except ________.
Decade
PivotTable Layout
Determine which totals and subtotals show
Given a list of candy bars in a field called Item and their prices in a field called Price on an empty PivotTable, how would you find out how many of each type of candy bar you have?
Drag Item to both the VALUES and the ROWS area.
A PivotTable Calculated field starts with a(n) _____________________.
Equal
Data mining
Evaluating a large dataset looking for trends.
Which of the following is not true about the PivotTable Fields task pane?
Excel automatically assigns the table the name Table1.
A PivotChart has no filters available.
False
A PivotTable can only display one summary statistic at a time.
False
A PivotTable has a live update feature which refreshes the data in real time as the dataset changes.
False
A PivotTable is limited to a single table of data.
False
A calculated field must be built in the original data set.
False
If you are interested in finding what products people buy when they buy coffee, you would employ data warehousing techniques on supermarket sales data.
False
When the underlying data changes, a PivotTable automatically updates.
False
Displaying subtotals at the top or bottom of a section is controlled on the PivotTable styles gallery.
Fasle
Filters and slicers are the same.
Fasle
Which of the following functions would you use to obtain the summary data that is visible in a PivotTable?
GETPIVOTDATA
Grouping is ________.
Joining rows or columns of related data into a single entity for analysis
The calculation option which displays the values of a specific field from greatest to least is Rank _______________________
Largest to Smallest
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
A(n) _____________ is a hierarchical structure used to group and summarize related data.
Outline
The row level above the row you are working with in a PivotTable is the__________row.
Parent
A primary benefit of creating a PivotTable is that you can rearrange or ____________ fields to view and summarize data from different perspectives.
Pivot
A PivotChart is similar to a regular chart, except it is based on the categories and structure of a(n)_____________________
Pivot table
A(n) ________ is an interactive table which uses calculations to consolidate and summarize information.
PivotTable
A(n) __________________ quickly formats a PivotTable with bold, fonts, shading, headers.
PivotTable style
____________________ is an add-in that enables you to relate tables and external data from multiple sources.
Power pivot
_________________Analysis is the built-in tool which automatically builds a recommended pivot table based on your dataset.
Quick
A(n) _______________ associates matching fields between 2 tables.
Relationship
Where would you access the Manage Relationships dialog box to create a data model in Excel?
Relationships on the Data tab
The ________ function is the default summary statistic for PivotTables.
SUM
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.
Which of the following is not a valid method to remove a field from a PivotTable?
Select the field to be removed in the PivotTable and press DELETE.
What does applying a banded row format do to a Pivot Table?
Shades odd and even rows differently
A(n) _____________ is the tool which appears in a separate window and displays all available options to show or hide data for a field.
Slicer
The first step in using the subtotal feature in Excel is to ____________ the data.
Sort
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
_______________________display the total values for each particular row or column in a PivotTable.
Subtotals
Dataset
The underlying data of a pivot table
Which of the following is not true about grouping data?
This happens automatically when Excel creates an outline.
A PivotChart has live updates as you make changes to the PivotTable.
True
A PivotTable can display no subtotals and no grand totals.
True
A Recommended PivotTable creates a PivotTable on a new worksheet.
True
A relationship is an association between two tables where the tables contain a common field of data.
True
A slicer has a window with a button for each item in a field.
True
Auto Outline creates an outline based on the data structure.
True
Creating a data model in Excel is very similar to creating relationships between tables in Access.
True
Excel is able to group data both by rows and by columns in a subtotal.
True
In a Subtotaled Worksheet, this is a correct example of a subtotal function in cell B90 which sums B80:B89 : =SUBTOTAL(9,B80:B89).
True
Sum is the default action of a numerical VALUES field in a PivotTable.
True
The % Period Expense Column on this PivotTable uses a % of Parent Row Total calculation option.
True
You can apply filters on dates to display summary statistics for data occurring during a particular time period.
True
You can change the label of a value.
True
You can clear all fields from a PivotTable.
True
You can create custom calculations in a PivotTable.
True
If you create a Recommended PivotTable on a group of data about book sales with these columns: Discipline (i.e. Business), Area (i.e. Finance and Marketing), Units Sold, Wholesale Price, what column might Excel use as values?
Units Sold
What does a + button indicate to the left of a row heading in an outline?
You can expand the details of that category
Using the % of Grand Total Calculation Option will display the value as ________.
a percentage of the grand total
A subtotal row must contain at least one ________ function.
aggregate
PivotTables ___________ or summarize large amounts of data for analysis.
aggregate
A slicer can be formatted so the ___________ display in one or many columns.
buttons
If the underlying data for a PivotTable changes, you must ____________ the PivotTable to show the changes.
refresh
Data model
related tables
Which of the following is not controlled by a PivotTable style?
subtotal calculations
ROWS area
the field used to organize a pivot table along the left side of the table
COLUMNS area
the field used to organize a pivot table along the top of the table
FILTERS area
the first field in this area creates a filter that appears in cell B1
What dictates the order of hierarchy of fields in the rows of a PivotTable?
the order of the fields in the ROWS area.
Pivot Chart Styles
Change the colors and backgrounds of pivot charts
Quick Layout
Change the layout of the elements on a Pivot Chart
___________ apply different formatting and color schemes to a PivotChart.
Chart Styles
Related field
The field with matching data in two tables used to identify a correlation
Select PivotTable styles ________.
from thumbnails in a style gallery
Which of the following is true about creating a blank PivotTable?
A PivotTable may be created on the same worksheet as the source data.
SUM
Adds the values in the field together
AVERAGE
Adds the values in the field together and then divides by the number of values
Change Colors
Change the color scheme of a pivotChart
LEGEND (SERIES)
COLUMNS
Summary statistics
Calculate like totals or averages
Which is an effective way to change a SUM of Pricefield in the VALUES area to an AVERAGE function?
Click the field's arrow in the VALUES area and select Value Field Settings.
Which option would clear a PivotTable filter on a State field?
Click the funnel icon to the right of the State filter and then select All and then click OK.
Matching: Data model
Collection of related tables
No Calculation
Default value shows the raw summary of the value.
If you would like to show a subtotal of the wholesale price of a number of items of clothing on a PivotTable of data of both men and women. You have the following fields: Gender, Item and Price. Sum of Price is in the VALUES area. Gender is in the ROWS area. How do you get a subtotal of shirts and pants to display for each gender?
Drag the Item field beneath Gender in ROWS.
% of Column Total
Each value displays as a percentage of the column.
% of Parent Row Total
Each value displays as a percentage of the hierarchical level above it.
% of Grand Total
Each value displays as a percentage of the whole and adds up to 100%.
% of Row Total
Each value shows as a percentage of the row.
FILTERS
FILTERS
Date (date of the purchase)
FILTERS or not used, at all
Category (Regular Meal, Snacks, etc.)
First in ROWS
Quantity (quantity of item sold)
First in VALUES
VLOOKUP
Function similar to how a data model matches related fields
__________________display the total values for each particular row or column in a PivotTable.
Subtotals
Which is not true of filters in a PivotTable?
Summary statistics do not change to reflect the values selected in the slicer.
Plot Area
PivotTable
AXIS (CATEGORY)
Rows
Item (Pizza Slice, Cookies, Water, etc.)
Second in ROWS
Extended Price (unit price * quantity of item sold)
Second in VALUES
subtotal
Show intermediate calculated values for a group of items
Collapse
Show less detail
A(n) ________ is a user-defined field that derives its value by performing calculations on other fields in a PivotTable.
calculated field
A(n) ____________ hides some values from a field in a PivotTable.
collapse
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 have related data stored in multiple tables, create a(n) ________ to create a PivotTable on the combined data
data model
How would you add a date filter to a PivotTable with these fields: Date, Category, Item, Unit Price, Number Sold, and Extended Price?
drag the Date field to the FILTERS area.
VALUES area
fields added to this area have a calculation of some sort applied
In a PivotTable Fields task pane, the ____________ area filters the data to display results based on particular conditions you set.
filters
The four areas for organizing fields in a PivotTable are ROWS, COLUMNS, _______________, and VALUES.
filters
Outline
hierarchical structure
A(n) ________ is the association between tables where both tables contain a common field.
relationship
A PivotChart ________.
represents the consolidated data of a PivotTable visually
The ______________ area is replaced with the AXIS (CATEGORY) in PivotChart task pane.
rows
expand
show more detail
