Deck 5: Excell: Working With Excel Tables, Pivottables, and Pivotcharts

Question 1

Records have been filtered in the list shown in the accompanying figure.

Answer 1

True

Question 2

Custom filters enable you to specify various conditions in addition to those that are based on an "equals" criterion.

Answer 2

False

Question 3

You can calculate sort statistics (including sum, average, count, maximum, and minimum) on all the columns in an Excel table.

Answer 3

False

Question 4

Table names cannot include underscores .

Question 5

If you freeze the top row in a worksheet and press Ctrl+Home, you will return to the cell A1.

Question 6

You can create at most one Excel table in a worksheet.

Question 7

When you delete a record from a table, a dialog box displays to confirm the delete operation.

Question 8

When you create a PivotTable , you need to specify where to find the data for the
PivotTable.

Question 9

You can drag one field to the FILTERS area of the PivotTable Fields pane to
change what values are displayed in the PivotTable.

Question 10

Before you complete a sort, you need to decide whether you want to put the list in ascending or descending order.

Question 11

An easy way to sort data when there is only one sort field is to use the Sort A to Z or Sort Z to A buttons.

Question 12

Excel can summarize data in a range of data by inserting subtotals.

Question 13

To create a custom list, in the Sort & Filter group on the HOME tab, click the Sort button. Click the Order arrow, and then click Custom List.

Question 14

Ascending order arranges labels alphabetically from A to Z.

Question 15

You can use a table's sizing handle to add columns or rows to a table.

Question 16

Each column represents a record that describes some attribute or characteristic of a person, place, or thing.

Question 17

If a column consists of day or month labels, you can sort them in their correct chronological order using one of the predefined custom lists.

Question 18

When you create a table, if your data does not contain column headers, Excel adds headers with default names.

Question 19

The first sort field in a sort is called the major sort field.

Question 20

The Subtotal command inserts a subtotal row into the range for each group of data and adds a grand total row below the last row of data.

Question 21

____ order arranges labels in reverse alphabetical order, from A to Z

A) Reverse
B) Major
C) Ascending
D) Descending

Question 22

In Excel, a collection of similar data can be structured in a(n) ____ of rows and columns.

A) index
B) glossary
C) list
D) range

Question 23

____ order arranges labels in reverse alphabetical order, from Z to A.

A) Reverse
B) Major
C) Ascending
D) Descending

Question 24

A data ____ table describes the fields you plan to maintain in a table.

A) structure
B) definition
C) map
D) linked

Question 25

The ____ criteria filter requires the records displayed to have the specified text string anywhere.

A) Has
B) Is
C) Contains
D) Matches

Question 26

Which of the following is a valid table name?

A) %_table
B) $Table2
C) _Newtable
D) 2Table

Question 27

When you designate a range as a(n) ____, Excel provides the same operations as are available for a structured range of data.

A) table
B) catalogue
C) index
D) roster

Question 28

The ____ criteria filter requires the records displayed to start with the specified text string.

A) Origin
B) Begins With
C) Initial
D) Start

Question 29

You can access the Sort buttons in the ____ group on the HOME tab by clicking the Sort & Filter button and clicking a sort option.

A) Filtering
B) Formatting
C) Editing
D) Text

Question 30

When you have more than one sort field, you should use the Sort ____ to specify the sort criteria.

A) menu
B) status bar
C) wizard
D) dialog box

Question 31

To create a table, click the ____ tab on the Ribbon and then, in the Tables group, click the Table button.

A) DESIGN
B) HOME
C) INSERT
D) VIEW

Question 32

Excel assigns the name ____ to the first Excel table created in a workbook.

A) 1
B) 1Table
C) NewTable
D) Table1

Question 33

In Excel, a table is a range of related data that is managed independently from the data in other ____ in the worksheet.

A) rows and headers
B) grids and columns
C) rows and columns
D) footers and grids

Question 34

After you filter a column, the ____ Filter command becomes available so you can remove the filter and redisplay all the records.

A) Remove
B) Clear
C) Delete
D) Archive

Question 35

The ____ condition requires that all of the selected criteria be true for the record to be displayed.

A) OR
B) NAND
C) AND
D) IF

Question 36

The fields you use to order your data are called sort ____.

A) values
B) fields
C) tags
D) tools

Question 37

You can display or hide filter arrows for an Excel table by using the Filter button in the Sort & Filter group on the ____ tab.

A) CATEGORY
B) VIEW
C) DATA
D) HOME

Question 38

A table name cannot include ____.

A) numbers
B) spaces
C) underscores
D) letters

Question 39

To add a sort field in the Sort dialog box, click the ____ button.

A) Add Level
B) Add Field
C) New Sort Field
D) New Level

Question 40

____ rows and columns lets you keep headings on the screen as you work with the data in a large worksheet.

A) Hiding
B) Freezing
C) Showing
D) Sealing

Question 41

With the Last Quarter criteria filter, quarters are defined as, for example, ____.

A) April, May, June
B) March, April, May
C) May, June, July
D) March, April, May, June

Question 42

In the figure above, if you only want to set one criterion for this filter, you will ____.

A) leave the lower boxes empty
B) enter "0" into the lower boxes
C) enter "none" into the lower boxes
D) access a pull-down menu of options

Question 43

On the bottom-right corner of the figure above, there are four squares labeled FILTER, COLUMNS, ROWS, and VALUES. These four areas represent the ____ of the PivotTable.

A) files
B) categories
C) segments
D) layout

Question 44

You can create a PivotTable by clicking the Summarize with PivotTable button in the Tools group on the ____ tab.

A) HOME
B) VIEW
C) TABLE TOOLS DESIGN
D) PIVOTTABLE

Question 45

You can split the worksheet window into ____ separate panes.

A) two or four
B) four or six
C) six or eight
D) between two and eight

Question 46

In addition to displaying subtotals, the Subtotal feature "____" your worksheet so you can control the level of detail that is displayed.

A) headers
B) outlines
C) shadows
D) defines

Question 47

You can click the More Functions command on the Total arrow button list to open the ____ dialog box and select any available function.

A) Display Function
B) Insert Function
C) Add Function
D) New Function

Question 48

To calculate subtotal for a table, the first step is to use the ____ button on the TABLE TOOLS DESIGN tab.

A) Expand for Calculation
B) Convert to Range
C) Summary
D) Function

Question 49

The ____ criteria filter requires the records displayed to have the current date.

A) Current Date
B) This Date
C) This
D) Today

Question 50

On the far right, in the figure above, there is the PivotTable field list. The items listed there, such as Day and Segment are ____ in the table.

A) columns
B) rows
C) chart data labels
D) field values

Question 51

The ____ report layout displays one column for each field and leaves space for column headers.

A) Outline
B) Design
C) Tabular
D) Headers

Question 52

The ____ criteria filter requires the records displayed to be since January 1 of the current year to the current date.

A) Year to Date
B) Today
C) Current
D) none of the above

Question 53

The ____ criteria filter requires the records displayed to be greater than or equal to and less than or equal to the specified numbers.

A) Top 10
B) Between
C) Aggregate
D) Range

Question 54

You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet one of the criteria.

A) Or
B) Both
C) And
D) Either

Question 55

When you click in each cell in the Total row, a(n) ____ appears that you can click to open a list of the most commonly used functions.

A) menu
B) arrow
C) button
D) status bar

Question 56

In the figure above, the 300 indicates the amount. Records with the following values meet the criteria: ____.

A) $298
B) $299
C) $300
D) $301

Question 57

You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet both of two criteria.

A) Or
B) Both
C) And
D) Either

Question 58

To get to the screen shown in the figure above, you would click the INSERT tab on the Ribbon, and then, in the ____ group, click the PivotTable button.

A) Tables
B) PivotTables
C) Tools
D) Data

Question 59

By default, the PivotTable report uses the ____ function for numbers in the Values area.

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

Question 60

You cannot change data directly in the PivotTable. Instead, you must edit the Excel table, and then ____, or update, the PivotTable to reflect the updated data.

A) reset
B) refresh
C) renew
D) change

Question 61

The second sort field in a sort is called the ____________________ field.

Question 62

Case-Based Critical Thinking Questions
Case 5-1
You are a student who is new to the more advanced features of Excel.
You have a lot of familiarity with ranges from an earlier Excel class, but you know that there is an alternative that will give you access to additional features that you do not have with a cell range. What is that option?

A) Convert the range to a table.
B) Convert the range to an index.
C) Convert the range to a PivotChart.
D) You are wrong; the range has as many features as any Excel alternative.

Question 63

Each column in a range represents a(n) ____________________ that describes some attribute or characteristic of a person, place, or thing.

Question 64

Case-Based Critical Thinking Questions
Case 5-2
Cali has just discovered the power of using text filters to control which records display in his worksheet, and he has begun to use them often to find precisely the records he needs. The following questions are based on a camp directory table that includes First Name, Last Name, Date of Birth, Camp, and Phone Number.
If Cali wants to find all of his campers whose last names end in "son" (such as Samson and Colson), what should he do?

A) Create a text filter using the Ends With operator to display all records that have cson as the last characters in the text value.
B) Create a text filter using the Contains operator to display all records that include son in the text value.
C) Create a text filter using the Ends With operator to display all records that have son as the last characters in the text value.
D) Create a text filter using the Contains operator to display all records that have son as the last characters in the text value.

Question 65

When related fields are grouped together in a row, they form a(n) ____________________, a collection of fields that describes a person, place, or thing.

Question 66

Critical Thinking Case-Based Questions
Case 5-3
As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.
Ted wants to be able to analyze date data in his PivotTable by month. What must he do with the date field in his PivotTable?

A) He must aggregate the data by the date field.
B) He must cluster the data by the date field.
C) He must sort the data by the date field.
D) He must group the data by the date field.

Question 67

Critical Thinking Case-Based Questions
Case 5-3
As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.
Ted wants to be able to use his PivotTable to combine items into groups. Which of the following is true?

A) Items that appear as row labels cannot be grouped.
B) Items that appear as column labels can be grouped.
C) If items are dates, they cannot be grouped automatically using the Grouping dialog box.
D) If items are numbers, they cannot be grouped manually using the Ctrl key to select items in a group and then choosing Group from the shortcut menu.

Question 68

When you want to see all the data in a filtered table, you can ____________________, or remove, the filters.

Question 69

Critical Thinking Case-Based Questions
Case 5-3
As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.
Which of the following is a true statement Carter can make to Ted about PivotTables?

A) To remove a field from a PivotTable, you click the field's check box in the PivotTable Field list.
B) You cannot rename a value label by typing the new text directly in any cell where the value label appears in the PivotTable.
C) Removing a field from the PivotTable changes the underlying Excel table.
D) To remove a field from a PivotTable, you click the field's check box in the PivotChart list.

Question 70

Case-Based Critical Thinking Questions
Case 5-2
Cali has just discovered the power of using text filters to control which records display in his worksheet, and he has begun to use them often to find precisely the records he needs. The following questions are based on a camp directory table that includes First Name, Last Name, Date of Birth, Camp, and Phone Number.
If Cali wants to find all of his campers named Cathy, Carla, and Carey, what should she do?

A) Create a text filter using the Begins With operator to display all records that have Ca at the beginning of the text value.
B) Create a text filter using the Contains operator to display all records that have Ca at the beginning of the text value.
C) Create a text filter using the Begins With operator to display all records that have Car at the beginning of the text value.
D) Create a text filter using the Contains operator to display all records that include Ca in the text value.

Question 71

Critical Thinking Case-Based Questions
Case 5-3
As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.
Ted wants to use his PivotTable to show the total value of the motorcycles for a series of motorcycle dealerships but he wants to filter the display to show only those dealers whose total stock of motorcycle is greater than $1 million. How would he accomplish this?

A) He would develop a PivotTable using the aggregate filter.
B) He would develop a PivotTable using the report filter.
C) He would develop a PivotTable using the category filter.
D) He would develop a PivotTable using the value filter.

Question 72

Critical Thinking Case-Based Questions
Case 5-3
As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.
If Ted decides that he wants to work with nonnumeric data in his PivotTable, what function must he use to produce summaries?

A) SUM
B) AGGREGATE
C) TOTAL
D) COUNT

Question 73

The process of hiding certain records and viewing the ones you want is called ____________________ your data.

Question 74

Case-Based Critical Thinking Questions
Case 5-2
Cali has just discovered the power of using text filters to control which records display in his worksheet, and he has begun to use them often to find precisely the records he needs. The following questions are based on a camp directory table that includes First Name, Last Name, Date of Birth, Camp, and Phone Number.
To find anyone whose Camp is either Day Camp , or Overnight Camp , or Nature Camp , what should he do?

A) Create a text filter using the Contains operator to display all records that have Nature anywhere in the text value.
B) Create a text filter using the Ends With operator to display all records that have Day as the last characters in the text value.
C) Create a text filter using the Begins With operator to display all records that have Overnight as the first characters in the text value.
D) Create a text filter using the Contains operator to display all records that have Camp anywhere in the text value.

Question 75

The ____________________ criteria filter requires that the records displaying begin with the specified text string.

Question 76

Every ____ consists of an object that contains a button for each unique value in that field.

A) piece
B) slicer
C) section
D) part

Question 77

To freeze a row, select the cell below the row(s) you want to freeze, click the ____________________ button in the Window group and then click Freeze Top Row.

Question 78

Case-Based Critical Thinking Questions
Case 5-1
You are a student who is new to the more advanced features of Excel.
Which of the following is true about Excel tables (as opposed to structured ranges of data)?

A) You cannot format quickly using a table style.
B) You cannot enter a formula in one table cell that is automatically copied to all other cells in that table column.
C) You cannot add new rows and columns that automatically expand the range.
D) You cannot freeze the top row of the worksheet.

Question 79

A(n) ____________________ list indicates the sequence in which you want data ordered.

Question 80

To delete records that are incorrect, out of date, or no longer needed, select a cell in each record you want to delete, click the Delete button arrow in the Cells group on the ____ tab, and then click Delete Table Rows.

A) HOME
B) INSERT
C) VIEW
D) DATA