Deck 5: Subtotals Pivot-tables and Pivot-charts

Question 1

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

Answer 1

B

Question 2

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

Answer 2

C

Question 3

Which of the following calculations cannot be completed with subtotal?

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

Answer 3

A

Question 4

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 5

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 6

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 7

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

Question 8

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 9

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

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

Question 10

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 11

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 12

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 13

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

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

Question 14

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

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

Question 15

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 16

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 17

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)

Question 18

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 19

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 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 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 22

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 23

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

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

Question 24

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 25

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 26

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 27

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 28

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 29

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 30

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 31

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 32

Where is the Subtotal command located on the Ribbon?

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

Question 33

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 34

Where are PowerPivot tools located on the Ribbon?

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

Question 35

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 36

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 37

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 38

Where are PivotTable formatting tools located?

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

Question 39

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 40

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 41

Multiple subtotals can be applied to one data set.

Question 42

Multiple filters can be applied to a PivotTable.

Question 43

Custom slicers can be imported into other workbooks.

Question 44

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

Question 45

Connections are automatically created in PowerPivot.

Question 46

Once applied,subtotals cannot be removed.

Question 47

Slicer captions can be used to provide instructions to users.

Question 48

By default,PivotTables display aggregated data for each category.

Question 49

PowerPivot in an Excel add-in.

Question 50

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

Question 51

A PivotTable can be created from the Quick Analysis gallery.

Question 52

The default numerical field calculation for PivotTables is SUM.

Question 53

PivotTables automatically refresh data.

Question 54

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

Question 55

Once grouped,columns cannot be ungrouped.

Question 56

Custom slicers can be created in Excel.

Question 57

Once slicers are created additional Slicer Styles can be applied.

Question 58

You must sort in order to properly execute a subtotal.

Question 59

Once applied,slicers cannot be removed.

Question 60

Once created,a PivotTable cannot be edited.

Question 61

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

Question 62

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 63

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

Question 64

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

Question 65

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

Question 66

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

Question 67

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

Question 68

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

Question 69

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

Question 70

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 71

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

Question 72

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

Question 73

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

Question 74

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

Question 75

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

Question 76

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

Question 77

PivotTables can be created from the ________ gallery.

Question 78

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

Question 79

Once completed,fields in PivotTables can be rearranged.

Question 80

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