Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / Excel Tutorial 5: Working with Excel Tables, PivotTables, and PivotCharts   TRUE/FALSE        1)   If you freeze the top row in a worksheet and press Ctrl+Home, you will return to the cell A1

Excel Tutorial 5: Working with Excel Tables, PivotTables, and PivotCharts   TRUE/FALSE        1)   If you freeze the top row in a worksheet and press Ctrl+Home, you will return to the cell A1

Computer Science

Excel Tutorial 5: Working with Excel Tables, PivotTables, and PivotCharts

 

TRUE/FALSE

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

     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.

 

 

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

 

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

 

   10.   You can drag one field to the FILTERS area of the PivotTable Fields pane to

change what values are displayed in the PivotTable.

 

 

MODIFIED TRUE/FALSE

 

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

 

 

     2.   Table names cannot include underscores. ____________________

 

 

     3.   Ascending order arranges labels alphabetically from A to Z. ____________________

 

 

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

 

 

     5.   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. ____________________

 

 

     6.   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. ____________________

 

 

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

 

 

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

 

 

     9.   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. ____________________

 

 

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

PivotTable. ____________________

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

 

 

     1.   A collection of related fields that are grouped together

 

     2.   To keep headings visible as you work with the data in a large worksheet

 

     3.   Documentation that lists the fields to be maintained for each record

 

     4.   A column within an Excel range

 

     5.   Used to group and summarize a list into a concise, tabular format

 

     6.   To temporarily hide all the records that do not meet your criteria

 

     7.   Indicates the sequence in which you want data ordered

 

     8.   Enables you to specify various conditions in addition to those that are based on an “equals” criterion

 

     9.   A unique label that describes the contents of data in a column

 

   10.   To rearrange records in a table or range based on the data in one or more fields

 

MULTIPLE CHOICE

 

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

a.

index

c.

list

b.

glossary

d.

range

 

 

 

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

a.

structure

c.

map

b.

definition

d.

linked

 

 

 

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

a.

Hiding

c.

Showing

b.

Freezing

d.

Sealing

 

 

 

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

c.

rows and columns

b.

grids and columns

d.

footers and grids

 

 

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

a.

table

c.

index

b.

catalogue

d.

roster

 

 

 

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

a.

DESIGN

c.

INSERT

b.

HOME

d.

VIEW

 

 

 

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

a.

1

c.

NewTable

b.

1Table

d.

Table1

 

 

 

     8.   Which of the following is a valid table name?

a.

%_table

c.

_Newtable

b.

$Table2

d.

2Table

 

 

 

     9.   A table name cannot include ____.

a.

numbers

c.

underscores

b.

spaces

d.

letters

 

 

 

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

a.

Reverse

c.

Ascending

b.

Major

d.

Descending

 

 

 

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

a.

Reverse

c.

Ascending

b.

Major

d.

Descending

 

 

 

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

a.

values

c.

tags

b.

fields

d.

tools

 

 

 

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

c.

Editing

b.

Formatting

d.

Text

 

 

 

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

a.

menu

c.

wizard

b.

status bar

d.

dialog box

 

 

 

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

a.

Add Level

c.

New Sort Field

b.

Add Field

d.

New Level

 

 

 

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

a.

Remove

c.

Delete

b.

Clear

d.

Archive

 

 

 

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

c.

DATA

b.

VIEW

d.

HOME

 

 

 

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

a.

OR

c.

AND

b.

NAND

d.

IF

 

 

 

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

a.

Origin

c.

Initial

b.

Begins With

d.

Start

 

 

 

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

a.

Has

c.

Contains

b.

Is

d.

Matches

 

 

 

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

c.

Aggregate

b.

Between

d.

Range

 

 

 

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

a.

Current Date

c.

This

b.

This Date

d.

Today

 

 

 

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

a.

Year to Date

c.

Current

b.

Today

d.

none of the above

 

 

 

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

a.

April, May, June

c.

May, June, July

b.

March, April, May

d.

March, April, May, June

 

 

 

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

c.

button

b.

arrow

d.

status bar

 

 

 

   26.   You can split the worksheet window into ____ separate panes.

a.

two or four

c.

six or eight

b.

four or six

d.

between two and eight

 

 

 

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

c.

And

b.

Both

d.

Either

 

 

 

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

c.

And

b.

Both

d.

Either

 

 

 

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

a.

leave the lower boxes empty

c.

enter “none” into the lower boxes

b.

enter “0” into the lower boxes

d.

access a pull-down menu of options

 

 

 

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

a.

$298

c.

$300

b.

$299

d.

$301

 

 

 

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

c.

Add Function

b.

Insert Function

d.

New Function

 

 

 

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

a.

Expand for Calculation

c.

Summary

b.

Convert to Range

d.

Function

 

 

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

a.

headers

c.

shadows

b.

outlines

d.

defines

 

 

 

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

a.

HOME

c.

TABLE TOOLS DESIGN

b.

VIEW

d.

PIVOTTABLE

 

 

 

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

c.

chart data labels

b.

rows

d.

field values

 

 

 

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

c.

segments

b.

categories

d.

layout

 

 

 

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

c.

Tools

b.

PivotTables

d.

Data

 

 

 

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

a.

COUNT

c.

MIN

b.

AVERAGE

d.

SUM

 

 

 

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

a.

Outline

c.

Tabular

b.

Design

d.

Headers

 

 

 

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

c.

renew

b.

refresh

d.

change

 

 

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

c.

VIEW

b.

INSERT

d.

DATA

 

 

 

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

a.

piece

c.

section

b.

slicer

d.

part

 

 

 

Case-Based Critical Thinking Questions

 

Case 5-1

You are a student who is new to the more advanced features of Excel.

 

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

 

 

 

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

 

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

 

 

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

 

 

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

 

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

 

 

 

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

 

 

 

   50.   If Ted decides that he wants to work with nonnumeric data in his PivotTable, what function must he use to produce summaries?

a.

SUM

c.

TOTAL

b.

AGGREGATE

d.

COUNT

 

 

 

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

 

 

 

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

 

 

 

COMPLETION

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

     9.   The entry 20000 is the ____________________ for the first condition.

 

 

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

 

 

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

 

 

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

 

 

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

 

 

   14.   The box that is covering columns A, B, and C in the figure above is the PivotTable ____________________ area.

 

 

   15.   A(n) ____________________ is a graphical representation of the data in a PivotTable.

 

 

ESSAY

 

     1.   What are the guidelines for creating an effective structured range of data?

 

 

     2.   What are the steps for sorting data using multiple fields?

 

 

     3.   What is a PivotTable? Describe its use in Excel, including the definition of value fields and category fields.

Option 1

Low Cost Option
Download this past answer in few clicks

5.87 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE

Related Questions