Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
1. If you freeze the top row in a worksheet and press Ctrl+Home, you will return to the cell A1.
a. True
b. False
2. You can create at most one Excel table in a worksheet.
a. True
b. False
3. When you create a table, if your data does not contain column headers, Excel adds headers with default names.
a. True
b. False
4. You can use a table’s sizing handle to add columns or rows to a table.
a. True
b. False
5. Before you complete a sort, you need to decide whether you want to put the list in ascending or descending order.
a. True
b. False
6. When you delete a record from a table, a dialog box displays to confirm the delete operation.
a. True
b. False
7. 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.
a. True
b. False
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
8. Excel can summarize data in a range of data by inserting subtotals.
a. True
b. False
9. You can drag one field to the Filters area of the PivotTable Fields pane to change what values are displayed in the
PivotTable.
a. True
b. False
10. Each column represents a record that describes some attribute or characteristic of a person, place, or thing.
____________________
11. Table names cannot include underscores. ____________________
12. Ascending order arranges labels alphabetically from A to Z. ____________________
13. The first sort field in a sort is called the major sort field. ____________________
14. 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. ____________________
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
15. 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. ____________________
16. Custom filters enable you to specify various conditions in addition to those that are based on an “equals” criterion.
____________________
17. You can calculate sort statistics (including sum, average, count, maximum, and minimum) on all the columns in an
Excel table. ____________________
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. ____________________
19. When you create a PivotTable, you need to specify where to find the data for the PivotTable. ____________________
20. 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
21. A data ____ table describes the fields you plan to maintain in a table.
a. structure b. definition
c. map d. linked
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
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
23. 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
24. 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
25. Excel assigns the name ____ to the first Excel table created in a workbook.
a. 1 b. 1Table
c. NewTable d. Table1
26. Which of the following is a valid table name?
a. %_table b. $Table2
c. _Newtable d. 2Table
27. A table name cannot include ____.
a. numbers b. spaces
c. underscores d. letters
28. ____ order arranges labels in reverse alphabetical order, from Z to A.
a. Reverse b. Major
c. Ascending d. Descending
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
29. ____ order arranges labels in reverse alphabetical order, from A to Z
a. Reverse b. Major
c. Ascending d. Descending
30. The fields you use to order your data are called sort ____.
a. values b. fields
c. tags d. tools
31. 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
32. 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
33. 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
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
35. 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
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
c. Data d. Home
36. 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
37. The ____ criteria filter requires the records displayed to start with the specified text string.
a. Origin b. Begins With
c. Initial d. Start
38. The ____ criteria filter requires the records displayed to have the specified text string anywhere.
a. Has b. Is
c. Contains d. Matches
39. 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
40. The ____ criteria filter requires the records displayed to have the current date.
a. Current Date b. This Date
c. This d. Today
41. 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
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
42. 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
43. 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
44. 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
45. 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
46. 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
47. 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
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
48. 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
49. 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
50. By default, the PivotTable report uses the ____ function for numbers in the Values area.
a. COUNT b. AVERAGE
c. MIN d. SUM
51. The ____ report layout displays one column for each field and leaves space for column headers.
a. Outline b. Design
c. Tabular d. Headers
52. 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
53. 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
54. Every ____ consists of an object that contains a button for each unique value in that field.
a. piece b. slicer
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
c. section d. part
Case-Based Critical Thinking Questions
Case 5-1
You are a student who is new to the more advanced features of Excel.
55. 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.
56. 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.
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.
57. 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.
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
58. 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.
59. 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.
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.
60. 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.
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
61. 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
62. 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.
63. 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.
64. When related fields are grouped together in a row, they form a(n) ____________________, a collection of fields that
describes a person, place, or thing.
65. Each column in a range represents a(n) ____________________ that describes some attribute or characteristic of a
person, place, or thing.
66. 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.
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts
67. The second sort field in a sort is called the ____________________ field.
68. A(n) ____________________ list indicates the sequence in which you want data ordered.
69. When you want to see all the data in a filtered table, you can ____________________, or remove, the filters.
70. The ____________________ criteria filter requires that the records displaying begin with the specified text string.
71. The entry 20000 is the ____________________ for the first condition.
72. The ____________________ command automatically inserts a subtotal line into the range for each group of data.
73. 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.
74. In PivotTable terminology, the fields that you summarize are known as ____________________ fields.
75. 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.
Name:
Class:
Date:
Excel Module 05: Working with Excel Tables, PivotTables, and PivotCharts