Module 3 Workshop 6 Analyze Data Using Tables

Which of the following statements regarding PivotTables is NOT TRUE? A) When there are a lot of groups within a field, it is better to put those fields along the row side of the PivotTable. B) There are three context-sensitive tabs associated with PivotTables. C) Typically the contents of a PivotTable should go down more than across. D) Fields for PivotTable grouping could be all in the Rows area, all in the Columns area, or a mixture.

B) There are three context-sensitive tabs associated with PivotTables.

Which of the following should NOT be performed in an effort to protect the integrity of data? A) Keep a backup of original data. B) Whenever possible, keep data and the analysis of that data on the same worksheet. C) Check data for completeness and accuracy. D) Organize data within a workbook in a meaningful manner.

B) Whenever possible, keep data and the analysis of that data on the same worksheet.

Which of the following statements about filtering is NOT TRUE? A) Filtering makes it possible to view specific data. B) The filtering function can be applied to only one data set on a worksheet. C) Filters can only be established on an Excel table. D) Filtering enables a user to examine and analyze a subset of records.

C) Filters can only be established on an Excel table.

All of the following statements are TRUE regarding PivotTables EXCEPT: A) Multiple PivotTables are allowed on one worksheet for comparison and analysis purposes. B) Prior to creating a PivotTable, any desired calculated fields should be added. C) PivotTables can be created on a new worksheet or an existing worksheet. D) PivotTables automatically expand and contract on a worksheet as variables are added, removed, or rearranged.

A) Multiple PivotTables are allowed on one worksheet for comparison and analysis purposes.

All of the following statements are TRUE regarding PivotCharts EXCEPT: A) Once a PivotChart is created, all formatting elements from a regular chart are no longer available. B) When the PivotTable data are rearranged, they are automatically updated in the PivotChart. C) When making changes to the PivotChart the corresponding changes are seen in the PivotTable. D) PivotCharts provide a visual representation of the data in a PivotTable.

A) Once a PivotChart is created, all formatting elements from a regular chart are no longer available.

All of the following statements are TRUE regarding slicers EXCEPT: A) Slicers were introduced in Excel 2007 as an interactive way to filter PivotTable data. B) In Excel 2013, slicers can now also be used to filter data in Excel tables, query tables, and other data tables. C) One of the benefits to using slicers is that they are easy to generate and use. D) Slicers indicate the current filter so you will know exactly what data you are viewing.

A) Slicers were introduced in Excel 2007 as an interactive way to filter PivotTable data.

All of the following statements are TRUE regarding PivotTable and PivotChart best practices EXCEPT: A) Format the PivotTable or PivotChart so it is easy to read and your audience understands what it is you are trying to communicate. B) Once you have selected your data, ensure the source data are arranged in an area with column headings representing each field and the rows representing each record. C) Distinguish fields as either grouping variables or slicer variables. D) Know who your audience is prior to developing your PivotTable or PivotChart. It could make a difference in how much detail you display.

All of the following statements are TRUE regarding PivotTable and PivotChart best practices EXCEPT: C) Distinguish fields as either grouping variables or slicer variables.

All of the following statements are TRUE regarding raw data EXCEPT: A) Raw data may be numeric or text. B) Raw data without context is meaningless to users. C) Information is another name for data. D) Raw data is unorganized.

C) Information is another name for data.

All of the following statements are TRUE regarding grouping in PivotTables EXCEPT: A) Grouping variables are placed along the top or the left side of the PivotTable. B) The summary variables are positioned in the VALUES area below the PivotTable Fields list. C) The grouping variables are positioned in the COLUMNS or ROWS areas at the top of the PivotTable Fields pane. D) Dates are great for grouping into months, quarters, and years to explore trends over time.

C) The grouping variables are positioned in the COLUMNS or ROWS areas at the top of the PivotTable Fields pane.

When using an Excel table, a table can do all of the following EXCEPT ________. A) automatically adjust table formatting and formula references B) provide both flexibility and scalability C) automatically sort data in table columns D) help provide context to the user by organizing data in a meaningful way

C) automatically sort data in table columns

To change a table back to a range of data, click ________ located in the Tools group on the Design tab.

Convert to Range

Pressing ________ allows you to select multiple criteria on a slicer.

Ctrl

All of the following statements are TRUE regarding the function_num argument of the SUBTOTAL function EXCEPT: A) This argument informs Excel which function to use on the subset of records. B) One set of function_num values will include both filtered and hidden data. C) One set of function_num values will include only filtered data. D) One set of function_num values will include only hidden data.

D) One set of function_num values will include only hidden data.

Which of the following is NOT a benefit of using PivotTables? A) PivotTables are useful when working with a huge table of data. B) PivotTables are useful when looking for trends. C) PivotTables are useful for summarizing data. D) PivotTables are useful for to consolidate multi-worksheet data.

D) PivotTables are useful for to consolidate multi-worksheet data.

The PivotTable ________ tab focuses on formatting and the appearance aspect of designing the PivotTable.

Design

To open the Create PivotTable dialog box, click on the ________ tab, and then click Summarize with PivotTable.

Design

The context-sensitive tabs available to aid in the control of the PivotTable include ________.

Design and Analyze

________ allows a user to select an individual piece of data in a PivotTable and then create a copy of individual records that were used to get that summary data on another worksheet.

Drilling down

________ variables are fields within a data set that can be used to categorize or group for comparison.

Grouping

It is NOT recommended to use the standard formatting techniques found on the ________ tab with PivotTables.

Home

Which of the following functions is NOT available as an option when selecting the SUBTOTAL function's first argument?

IF

21) Which of the following options is NOT listed in the Create PivotTable dialog box? A) Select a table or range B) New Workbook C) Use an external data source D) New Worksheet

New Workbook

________ are interactive tables that extract, organize, and summarize data.

PivotTables

________ allows a user to import up to one million rows of data from multiple sources, such as large corporate databases, public data feeds, spreadsheets, and text files on your computer.

PowerPivot

Use the ________ button on the Options tab to update PivotTable data changes made to source data.

Refresh

The ________ function will run calculations on data that is in a subset when a filter is applied.

SUBTOTAL

The ________ feature located on the PivotTable Analyze tab offers a list of options for summarizing its data values.

Summarize Values By

________ variables are data that can be aggregated by summing, counting, or averaging.

Summary

PivotTable data formatted through the ________ dialog box applies to all other cells within the same field.

Value Field Settings

What should NOT be performed in an effort to protect the integrity of data?

Whenever possible, keep data and the analysis of that data on the same worksheet.

When using the ________ Filter feature in Excel, filtering criteria will be listed on the same worksheet.

advanced

Click ________ in the Sort & Filter group on the Data tab to removed any existing filters that have been applied to a data set on the Excel worksheet.

clear

________ is the name used for a collection of related sets of information that are composed of separate elements.

data sets

By default, PivotTables show a ________ for both columns and rows.

grand total

Gender and dates are two examples of PivotTable ________ variables.

grouping

________ is data that has context, meaning, and relevance.

information

A table can be created by selecting a range on an Excel worksheet, clicking the ________ tab, clicking the Format as Table button, and selecting a table style.

insert

Can you automatically sort data in table columns in an EXCEL table?

no

A ________ is a visual mechanism for quickly filtering data in a PivotTable.

slicer

The ________ filter displays the values in the field that can be toggled on and off through the use of check boxes.

standard

Exam scores, quantity, and price are three examples of PivotTable ________ variables.

summary