Excel Chapter 5
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
Using the % of Grand Total Calculation Option will display the value as ________.
a percentage of the grand total
A(n) ________ is a user-defined field that derives its value by performing calculations on other fields in a PivotTable
calculated field
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
A PivotTable time line can filter dates by all of these, except ________.
decade
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.
Select PivotTable styles ________.
from thumbnails in a style gallery
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
Which of the following is not controlled by a PivotTable style?
subtotal calculations
What dictates the order of hierarchy of fields in the rows of a PivotTable?
the order of the fields in the ROWS area.
Which of the following is true about creating a blank PivotTable?
A PivotTable may be created on the same worksheet as the source data.
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.
A subtotal row must contain at least one ________ function.
Aggregate
Which function is not a Subtotal function?
Aggregate
Which is not true about Slicers?
Aggregate values do not respond to slicers.
What field is in the ROWS area of the PivotTable that built this PivotChart?
Category
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.
Which is an effective way to change a SUM of Price field 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.
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
What does a + button indicate to the left of a row heading in an outline?
You can expand the details of that category
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.
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.
Grouping is ________.
Joining rows or columns of related data into a single entity for analysis
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 an interactive table which uses calculations to consolidate and summarize information.
PivotTable
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 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
Which is not true of filters in a PivotTable?
Summary statistics do not change to reflect the values selected in the slicer.