Deck 22: Excel: Subtotals, Pivot-tables, and Pivot-charts

Question 1

Which is the best tool for managing multiple filters within a PivotTable?

A)Slicers
B)Refreshing the data
C)PivotChart
D)Sorting

Answer 1

A

Question 2

When setting custom calculation options in a PivotTable,what does % of Parent Row Total calculate?

A)Displays each value as a percentage of the grand total
B)Displays each value as a percentage of the respective column
Total.The values in each column total 100%
C)Displays each value as a percentage of the respective row
Total.The values in each row total 100%
D)Displays values as: (value for the item)/ (value for the parent
Item on rows)

Answer 2

D

Question 3

Which of the following is not true about PivotTables?

A)PivotTables are dynamic
B)PivotTables can arrange data in columns or rows
C)PivotTables can summarize data
D)PivotTables can Auto Refresh data

Answer 3

D

Question 4

When editing a PivotTable where are values field settings edited?

A)PivotTable Design tab
B)Data tab
C)PivotTable Fields task pane
D)Formulas tab

Question 5

What is the best PowerPivot tool when attempting to link to tables with a common related field?

A)Pivot
B)Relationships
C)Links
D)External references

Question 6

Where is the command to insert a PivotTable located in the Ribbon?

A)Data tab
B)Insert tab
C)Formulas tab
D)Developer tab

Question 7

Which of the following tools is best suited for maximum control when creating an outline?

A)Subtotals
B)PivotTable
C)Grouping
D)Slicers

Question 8

What does the Filters Area of a PivotTable do?

A)Subdivides data into one or more additional categories
B)Filters the data to display results based on particular conditions you set
C)Organizes and groups data into categories on the left side.Each group
Name occupies a single row
D)Displays summary statistics,such as totals or averages

Question 9

Which of the following options is the best tool when using a PivotTable that contains more data than you currently need?

A)Sorting
B)Removing a value field
C)Filtering
D)Refreshing the data

Question 10

What is the best option for adding a calculation to a PivotTable that does not already exist?

A)Custom function
B)Calculated field
C)Function
D)PivotTool Calculation

Question 11

Which of the following calculations cannot be completed with subtotal?

A)Average
B)Standard Deviation
C)Sum
D)Max

Question 12

Which of the following tools is best suited for data mining and summarizing data?

A)Subtotal
B)PivotTable
C)Column chart
D)Data table

Question 13

What does a slicer caption do?

A)Provides feedback on slicers that are engaged
B)Provides access to slicer options
C)Displays text in the header at the top of the slicer window
D)Creates caption meta data

Question 14

From which of the following locations can you create a PivotTable?

A)Data tab
B)Quick Analysis gallery
C)Backstage
D)Formulas tab

Question 15

Which of the following is not a benefit of PowerPivot?

A)Import data from multiple sources
B)Create relationships
C)Create web queries
D)Maintain connections

Question 16

Which of the following is not a calculation that can be completed with the SUBTOTAL function?

A)Average
B)Count
C)Min
D)Index

Question 17

When setting custom calculation options in a PivotTable,what does Running Total calculate?

A)Displays each value as a percentage of the grand total
B)Displays each value as a percentage of the respective column
Total.The values in each column total 100%
C)Displays values as running totals
D)Displays values as: (value for the item)/ (value for the parent
Item on rows)

Question 18

What does the Values Area of a PivotTable do?

A)Subdivides data into one or more additional categories
B)Filters the data to display results based on particular conditions you set
C)Organizes and groups data into categories on the left side.Each group
Name occupies a single row
D)Displays summary statistics,such as totals or averages

Question 19

When using subtotal,what does the 4th button of the outline levels do?

A)Collapses the outline to display the grand total only
B)Displays subtotals by the main subtotal category and the grand total
C)Displays the entire list
D)Displays subtotals by the main subtotal category,the secondary
Subtotal category,and the grand total

Question 20

Which of the following tools is best suited for complex cross referenced subtotals?

A)PivotTable
B)Subtotal
C)Data table
D)Outline

Question 21

Which of the following appears in the PivotTable Fields task pane?

A)Choose fields to add to report
B)Filter area
C)Plot area
D)Rows area

Question 22

Which of the following is not true regarding data mining?

A)Analyzes large amounts of data
B)Uses advanced statistical techniques
C)Identifies trends
D)Exports data

Question 23

Which PivotTable calculation option is best suited for comparing the percentage of sales completed by one sales rep against the entire sales force?

A)% of Column Total
B)% of Row Total
C)Running Total
D)% of Grand Total

Question 24

What is the default summary statistic for numerical fields in a PivotTable?

A)Average
B)COUNT
C)SUM
D)STDEV

Question 25

Which tool is best suited for use with PivotTables when the data is based on multiple tables?

A)PivotCharts
B)Slicers
C)Sorting
D)Filters

Question 26

Which of the following is not a PivotTable Subtotals option?

A)Do Not Show Subtotals
B)Show All Subtotals at Bottom of Group
C)Do Not Include Filtered Items in Totals
D)Include Filtered Items in Totals

Question 27

Which of the following tools is best suited to analyze the most productive students in a school district over a period of time?

A)PivotTable
B)Subtotal
C)Pie Chart
D)Slicer

Question 28

Which of the following is not true about a PivotChart?

A)They function independently from the PivotTable
B)They can be formatted similar to a regular chart
C)They update as the PivotTable updates
D)They can be placed on a separate worksheet

Question 29

Which of the following is not an area of the PivotTable Fields task pane?

A)Filters Area
B)Columns Area
C)Data Area
D)Rows Area

Question 30

Where are PowerPivot tools located on the Ribbon?

A)Insert tab
B)Formulas tab
C)Data tab
D)Review tab

Question 31

Which of the following is not one of the rules regarding good table design?

A)Use meaningful column labels
B)Ensure data accuracy
C)Avoid blank rows
D)Insert rows to separate data

Question 32

What tool is best suited for incorporating manufacturing information from one table with shipping information from a related table within one PivotTable?

A)Slicers
B)PowerPivot
C)Pivot Charts
D)Grouping

Question 33

What is the name of the tab that appears in the Ribbon when a PivotTable is selected?

A)PivotTable Options
B)PivotTable
C)PivotTable Tools
D)Analysis

Question 34

Which of the following is the best tool for analyzing employee performance over a period of time?

A)Data table
B)PivotTable
C)Subtotals
D)Outline

Question 35

Once a slicer is inserted into a worksheet which of the following actions cannot be performed?

A)Move the slicer
B)Append the slicer
C)Filter data
D)Remove a filter

Question 36

Where are PivotTable formatting tools located?

A)PivotTable Tools Analysis tab
B)Data tab
C)PivotTable Tools Design tab
D)Insert tab

Question 37

Where is the Subtotal command located on the Ribbon?

A)Insert tab
B)Data tab
C)View tab
D)Backstage

Question 38

Where are the Expand Field and Collapse Field buttons located within the PivotTable Tools tab?

A)Filter group
B)Calculations group
C)Data group
D)Active Field group

Question 39

How are grouped columns ungrouped?

A)Select the entire worksheet and click Ungroup in the Subtotal group
B)Select one cell in the grouped columns and click Ungroup in the Outline group
C)Select all grouped columns and click Ungroup in the Outline group
D)Right-click and select Ungroup

Question 40

What tool is best suited for organizing a spreadsheet of rental cars by category in order to be collapsed or expanded?

A)Grouping
B)PivotTables
C)Slicers
D)Outlines

Question 41

Once created,a PivotTable cannot be edited.

Question 42

Custom calculated fields will not update if the data source changes.

Question 43

The default numerical field calculation for PivotTables is SUM.

Question 44

PowerPivot in an Excel add-in.

Question 45

PivotTables automatically refresh data.

Question 46

Once applied,slicers cannot be removed.

Question 47

Once applied,subtotals cannot be removed.

Question 48

Once grouped,columns cannot be ungrouped.

Question 49

Multiple filters can be applied to a PivotTable.

Question 50

Once slicers are created additional Slicer Styles can be applied.

Question 51

A PivotTable can be created from the Quick Analysis gallery.

Question 52

By default,PivotTables display aggregated data for each category.

Question 53

Multiple subtotals can be applied to one data set.

Question 54

Slicer captions can be used to provide instructions to users.

Question 55

Custom slicers can be imported into other workbooks.

Question 56

Custom slicers can be created in Excel.

Question 57

Connections are automatically created in PowerPivot.

Question 58

You must sort in order to properly execute a subtotal.

Question 59

Slicers can only be removed from the Analyze tab in the Ribbon.

Question 60

After basic creation,various formatting styles can be applied to a PivotTable.

Question 61

________ is the process of joining rows or columns of related data together into a single entity.

Question 62

PivotTables can be created from the ________ gallery.

Question 63

When modifying a PivotTable,the ________ appears on the right side of the window.

Question 64

A(n)________ should be performed before subtotaling data.

Question 65

Once completed,fields in PivotTables can be rearranged.

Question 66

PivotTables can be created from the ________ tab in the Ribbon.

Question 67

In a PivotTable Fields task pane,the ________ subdivides data into one or more additional categories.

Question 68

________ is the process of analyzing large volumes of data,using advanced statistical techniques,and
identifying trends and patterns in the data.

Question 69

A(n)________ is a powerful,interactive data-mining feature that enables you to summarize and analyze data,especially helpful when working with large datasets.

Question 70

More than one function can be applied per field in a PivotTable.

Question 71

The Subtotal feature creates a(n)________,a hierarchical structure of data.

Question 72

Label filters such as "Does Not Equal" can be applied to a PivotTable.

Question 73

The Quick Analysis gallery is the only method to create a PivotTable.

Question 74

When subtotals are applied,outline button number ________ displays the entire list.

Question 75

Adding ________ to a preexisting data set can help you identify which disciplines and which areas contribute the highest revenue for a company and which disciplines and areas produce the lowest revenue.

Question 76

Filters can only be applied to PivotTables one at a time.

Question 77

A(n)________ could be used to determine the most productive employee over an extended period of time.

Question 78

When subtotals are applied the + that appears above the worksheet is the ________ button.

Question 79

When formatting the data to compile a PivotTable,meaningful ________ should be used.

Question 80

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