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

Question 1

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

Answer 1

  True

Question 2

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.

Answer 2

  True

Question 3

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

Answer 3

  False

Question 4

Ascending order arranges labels alphabetically from A to Z.____________________

Question 5

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

Question 6

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

Question 7

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

Question 8

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

Question 9

Table names cannot include underscores.____________________

Question 10

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

Question 11

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

Question 12

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

Question 13

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 14

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 15

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

Question 16

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 17

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

Question 18

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 19

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

Question 20

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

Question 21

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 22

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 23

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

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

Question 24

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 25

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

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

Question 26

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

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

Question 27

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

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

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

Question 29

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 30

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

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

Question 31

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 32

A table name cannot include ____.

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

Question 33

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

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

Question 34

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

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

Question 35

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 36

Which of the following is a valid table name? 

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

Question 37

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 38

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 39

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 40

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

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

Question 41

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 42

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 43

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 44

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

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 46

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

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

Question 47

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 48

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 49

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

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

Question 50

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 51

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 52

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 53

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 54

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 55

Case 5-1
You are a student who is new to the more advanced features of Excel.
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 56

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 57

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 58

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 59

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 60

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 61

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 62

In PivotTable terminology,the fields that you summarize are known as ____________________ fields.

Question 63

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

A(n)____________________ is an interactive table that enables you to group and summarize an Excel list into a concise,tabular format for easier reporting and analysis.

Question 65

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

Question 66

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

Question 67

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 68

The entry 20000 is the ____________________ for the first condition.

Question 69

In addition to data fields,a PivotTable uses ____________________ fields to group values such as department,model,year,and vehicle type in an automobile-related sheet,for example.

Question 70

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

Question 71

The ____________________ command automatically inserts a subtotal line into the range for each group of data.

Question 72

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

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

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

Question 75

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