Deck 5: Subtotals, Pivottables, and Pivotcharts

Question 1

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?

A)Discipline
B)Area
C)Units Sold
D)Both Discipline and Area

Answer 1

C

Question 2

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?

A)Drag Item to both the VALUES and the ROWS area.
B)Drag Price to the ROWS and Item to the VALUES area.
C)Drag Item to the COLUMNS area.
D)Drag Item to the ROWS areas.

Answer 2

A

Question 3

The ________ function is the default summary statistic for PivotTables.

A)SUM
B)AVERAGE
C)MAX
D)MIN

Answer 3

A

Question 4

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?

A)Drag the Item field to VALUES.
B)Drag the Item field above Gender in ROWS.
C)Drag the Gender field off the ROWS.
D)Drag the Item field beneath Gender in ROWS.

Question 5

What would you do to format all values as Currency in the Sum of Extended Price field no matter how you pivot the data?

A)Select Product.
B)Select Number Format and choose Currency in the list of options and then click OK.
C)Click the Show Values As tab and select Currency in this list and then click OK.
D)Click cancel and select the cells and then format as Currency on the Home tab.

Question 6

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?

A)Sort by Last Name
B)Sort by Salary
C)Sort by Rank
D)Sort by Department

Question 7

You created a PivotTable and later made changes to values in the underlying data.What happens to the PivotTable?

A)The PivotTable updates automatically when you make changes to the dataset.
B)Excel gives an error message that data has changed and the PivotTable may no longer be accurate.
C)Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.
D)Excel deletes the PivotTable automatically so you can recreate it.

Question 8

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?

A)Add sales to the VALUES area once and select both AVERAGE and SUM functions.
B)Add sales to the COLUMNS area.
C)Add sales to the ROWS area and then choose Average.
D)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.

Question 9

Moving one of two fields from the ROWS area to the COLUMNS area of a PivotTable would ________.

A)divide the values by the number of columns
B)consolidate data and remove redundant labels
C)have no impact on the outcome
D)make the table twice as long

Question 10

Which of the following is true about creating a blank PivotTable?

A)A PivotTable may only be created from a table.
B)A PivotTable may not be created from a range.
C)A PivotTable may not be created on a connection to an external dataset.
D)A PivotTable may be created on the same worksheet as the source data.

Question 11

What does a + button indicate to the left of a row heading in an outline?

A)You can expand the details of that category.
B)The row has been modified.
C)You can collapse the details of that category.
D)There is a calculation in that cell.

Question 12

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?

A)Click Expand Field in the Active Field Group on the Analyze tab.
B)Select a field in the row headers and click Collapse Field in the Active Field Group on the Analyze tab.
C)Click the - button to the left of the first category.
D)You must move the second level out of the ROWS area.

Question 13

A(n)________ is an interactive table which uses calculations to consolidate and summarize information.

A)list
B)PivotTable
C)Table
D)range

Question 14

Which is an effective way to change a SUM of Price field in the VALUES area to an AVERAGE function?

A)Select the appropriate cell in the PivotTable and type =AVERAGE(Price).
B)Click the AVERAGE button on the Analyze tab.
C)Right click the appropriate cell and select AVERAGE from the mini-tool bar.
D)Click the field's arrow in the VALUES area and select Value Field Settings.

Question 15

A subtotal row must contain at least one ________ function.

A)financial
B)sort
C)aggregate
D)sum

Question 16

You are analyzing sales data and eating habits of students in a school cafeteria, you created a PivotTable with Date as a ROW and Extended Price as a summed VALUE.Which of the following Pivot Tables would demonstrate best what would happen if you added Category to the COLUMNS area?

A)
B)
C)
D)

Question 17

Which function is not a Subtotal function?

A)MAX
B)SUM
C)AGGREGATE
D)AVERAGE

Question 18

Which of the following is not a valid method to remove a field from a PivotTable?

A)Click the field name in the Drag fields between areas below section and select Remove Field.
B)Click the check box next to the field name to deselect it in the Choose fields to add to report section.
C)Select the field to be removed in the PivotTable and press DELETE.
D)Drag a field name in the Drag fields between areas below section outside the PivotTable Fields List.

Question 19

What dictates the order of hierarchy of fields in the rows of a PivotTable?

A)alphabetical order
B)the order of the fields in the ROWS area.
C)the order of the fields in the COLUMNS area
D)the rank in the sort order dialog box.

Question 20

Grouping is ________.

A)using a VLOOKUP function to categorize data
B)joining rows or columns of related data into a single entity for analysis
C)the top level in an outline
D)joining unrelated data for analysis

Question 21

Using the % of Grand Total Calculation Option will display the value as ________.

A)a percentage of the grand total
B)a percentage of the column total
C)a percentage of the row total
D)a percentage of the parent level row

Question 22

A(n)________ is the association between tables where both tables contain a common field.

A)link
B)data model
C)relationship
D)share point

Question 23

What is the best option for a field to build a relationship between the tables Sales Personnel and Orders with the following fields?

A)Commission Rate
B)First Name
C)Sales Person ID
D)Price

Question 24

What does applying a banded row format do to a Pivot Table?

A)Puts a band around subtotals
B)Shades odd and even rows differently
C)Keeps totals together
D)Puts a line between each row

Question 25

Which is not true about Slicers?

A)Aggregate values do not respond to slicers.
B)You can filter data by a single value.
C)You can move a slicer so it does not cover a PivotTable.
D)You can filter data by multiple values.

Question 26

Where would you access the Manage Relationships dialog box to create a data model in Excel?

A)Relationships on the Data tab
B)Data Models on the Analyze tab
C)Layout on the Design tab
D)Joins on the Data tab

Question 27

A PivotTable time line can filter dates by all of these, except ________.

A)decade.
B)day.
C)quarter.
D)year.

Question 28

What field is in the ROWS area of the PivotTable that built this PivotChart?

A)Expenses
B)Category
C)Regular Meal
D)Lunch Expenses

Question 29

Which is not true of filters in a PivotTable?

A)Filters show a subset of the dataset.
B)Summary statistics do not change to reflect the values selected in the slicer.
C)You may apply filters on multiple fields.
D)An icon with a picture of a funnel appears when a PivotTable has a filter applied.

Question 30

A PivotChart ________.

A)is not altered by filters and slicers
B)represents the consolidated data of a PivotTable visually
C)does not use a PivotTable's COLUMNS area fields
D)displays fields from the VALUES area on the legend

Question 31

How would you add a date filter to a PivotTable with these fields: Date, Category, Item, Unit Price, Number Sold, and Extended Price?

A)remove Date from any PivotTable area
B)drag Date to the ROWS area and group it.
C)drag the Date field to the FILTERS area.
D)You must use a slicer with dates.

Question 32

If you have related data stored in multiple tables, create a(n)________ to create a PivotTable on the combined data

A)Access link
B)data collection
C)Excel table
D)data model

Question 33

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?

A)* Wholesale Price * 3 and accept default Name in the Insert Calculated Field dialog box
B)= Wholesale Price * 300 with a Name of Retail Price in the Insert Calculated Field dialog box
C)= Wholesale Price * 3 with a Name of Retail Price in the Insert Calculated Field dialog box
D)Wholesale Price *300 with a Name of Retail Price in the Insert Calculated Field dialog box

Question 34

Select PivotTable styles ________.

A)from the PivotTable wizard
B)from options in the minitoolbar
C)from thumbnails in a style gallery
D)from the Font group

Question 35

Which option would clear a PivotTable filter on a State field?

A)Select the filter arrow on the Rows header and select clear filter.
B)Select the filter arrow on the Columns header and select clear filter.
C)Drag the State field off the FILTERS area.
D)Click the funnel icon to the right of the State filter and then select All and then click OK.

Question 36

You created a slicer on the field State.What items would you be most likely to see in the slicer?

A)State 1, State 2
B)AL, CA, GA, KY, MS, NC, OR, and VA
C)Sales totals for each state
D)A map of the US with the states represented as active.

Question 37

Which of the following is not controlled by a PivotTable style?

A)row and column shading
B)borders
C)bold fonts
D)subtotal calculations

Question 38

A(n)________ is a user-defined field that derives its value by performing calculations on other fields in a PivotTable

A)value function
B)dataset variable
C)user field
D)calculated field

Question 39

If you want to turn off grand totals for columns on a PivotTable, select this option in the Grand Totals options.

A)On for Rows Only
B)On for Columns Only
C)Off for Rows and Columns
D)On for Rows and Columns

Question 40

Which category or categories display in the PivotTable, if you see this slicer?

A)A La Carte, Drinks, and Snacks
B)Regular Meal
C)All categories
D)No categories

Question 41

A(n)________ is a collection of related tables forming a database.

Question 42

A PivotTable Calculated field starts with ________.

Question 43

________ is the process of analyzing large amounts of data to identify trends and patterns in the data.

Question 44

A primary benefit of creating a PivotTable is that you can rearrange or ________ fields to view and summarize data from different perspectives.

Question 45

A slicer can be formatted so the ________ display in one or many columns.

Question 46

A(n)________ is the tool which appears in a separate window and displays all available options to show or hide data for a field.

Question 47

________ Analysis is the built-in tool which automatically builds a recommended pivot table based on your dataset.

Question 48

A(n)________ hides some values from a field in a PivotTable.

Question 49

The first step in using the subtotal feature in Excel is to ________ the data.

Question 50

A(n)________ quickly formats a PivotTable with bold, fonts, shading, headers.

Question 51

The four areas for organizing fields in a PivotTable are ROWS, COLUMNS, ________ and VALUES.

Question 52

A(n)________ is a hierarchical structure used to group and summarize related data.

Question 53

PivotTables ________ or summarize large amounts of data for analysis

Question 54

If the underlying data for a PivotTable changes, you must ________ the PivotTable to show the changes.

Question 55

The calculation option which displays the values of a specific field from greatest to least is Rank ________.

Question 56

The row level above the row you are working with in a PivotTable is the ________ row.

Question 57

You would find the Group and Ungroup buttons in the Outline group on the ________ tab.

Question 58

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.

Question 59

A(n)________ associates matching fields between 2 tables.

Question 60

________ display the total values for each particular row or column in a PivotTable.

Question 61

Filters and Slicers are the same.

Question 62

If you are interested in finding what products people buy when they buy coffee, you would employ data warehousing techniques on supermarket sales data.

Question 63

In a Subtotaled Worksheet, this is a correct example of a subtotal function in cell B90 which sums B80:B89 : =SUBTOTAL(9,B80:B89).

Question 64

A PivotTable has a live update feature which refreshes the data in real time as the dataset changes.

Question 65

A slicer has a window with a button for each item in a field.

Question 66

You can clear all fields from a PivotTable.

Question 67

A Recommended PivotTable creates a PivotTable on a new worksheet.

Question 68

________ is an add-in that enables you to relate tables and external data from multiple sources.

Question 69

A calculated field must be built in the original data set.

Question 70

Excel is able to group data both by rows and by columns in a subtotal.

Question 71

When the underlying data changes, a PivotTable automatically updates.

Question 72

You cannot change the label of a value.

Question 73

Sum is the default action of a numerical VALUES field in a PivotTable.

Question 74

A PivotChart is similar to a regular chart, except it is based on the categories and structure of a(n)________.

Question 75

In a PivotTable Fields task pane, the ________ filters the data to display results based on particular conditions you set.

Question 76

The ________ area is replaced with the AXIS (CATEGORY)in PivotChart task pane.

Question 77

Auto Outline creates an outline based on the data structure.

Question 78

A PivotTable can only display one summary statistic at a time.

Question 79

You can apply filters on dates to display summary statistics for data occurring during a particular time period.

Question 80

________ apply different formatting and color schemes to a PivotChart.