Deck 5: Working With Excel Tables, Pivot-tables, and Pivot-charts

Question 1

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

Answer 1

True

Question 2

To create a custom list,in the Sort & Filter group on the $\underline { \text { HOME } }$ tab,click the Sort button.Click the Order arrow,and then click Custom List.____________________

Answer 2

False

Question 3

$\underline { \text { Custom } }$ filters enable you to specify various conditions in addition to those that are based on an "equals" criterion.____________________

Answer 3

False

Question 4

Each column represents a $\underline { \text { record } }$ that describes some attribute or characteristic of a person,place,or thing.____________________

Question 5

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

Question 6

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

Question 7

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

Question 8

If a column consists of day or month labels,you can sort them in their correct $\underline { \text { chronological } }$ order using one of the predefined custom lists.____________________

Question 9

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

Question 10

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

Question 11

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

Question 12

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

Question 13

Table names cannot include $\underline {\text { underscores. } }$ .____________________

Question 14

When you create a $\underline {\text { PivotTable } }$ ,you need to specify where to find the data for the
PivotTable.____________________

Question 15

The first sort field in a sort is called the $\underline { \text { major } }$ sort field.____________________

Question 16

$\underline { \text { Ascending } }$ order arranges labels alphabetically from A to Z.____________________

Question 17

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

Question 18

The $\underline { \text { 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 19

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 20

You can calculate $\underline { \text { sort } }$ statistics (including sum,average,count,maximum,and minimum)on all the columns in an Excel table. ____________________

Question 21

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

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

Question 22

____ 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 23

Which of the following is a valid table name?

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

Question 24

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
Indicates the sequence in which you want data ordered

Question 25

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
To keep headings visible as you work with the data in a large worksheet

Question 26

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
Documentation that lists the fields to be maintained for each record

Question 27

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
Used to group and summarize a list into a concise,tabular format

Question 28

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
A column within an Excel range

Question 29

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 30

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 31

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

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

Question 32

A table name cannot include ____.

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

Question 33

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
Enables you to specify various conditions in addition to those that are based on an "equals" criterion

Question 34

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
To temporarily hide all the records that do not meet your criteria

Question 35

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
A collection of related fields that are grouped together

Question 36

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

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

Question 37

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 38

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
To rearrange records in a table or range based on the data in one or more fields

Question 39

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 40

MATCHING
Identify the letter of the choice that best matches the phrase or definition.
a.custom list
f.data definition table
b.field
g.filter
c.record
h.PivotTable
d.freeze
i.field name
e.criteria filter
j.sort
A unique label that describes the contents of data in a column

Question 41

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 42

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

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

Question 43

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

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

Question 44

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 45

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 46

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

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

Question 47

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 48

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 49

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 50

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

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

Question 51

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 52

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 53

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

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

Question 54

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

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 57

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 58

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 59

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 60

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 61

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 62

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 63

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 64

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 65

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 66

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

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

Question 67

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 68

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 69

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 70

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 71

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

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

Question 72

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

Question 73

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 74

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 75

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 76

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 77

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 78

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 79

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 80

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