Q&A Hero

Q: A formula can include a reference to another workbook (called an ____ reference), which creates a set of linked workbooks. a. internal b. external c. import d. export

Q: Simon tells Brooke that when he attempts to open a workbook, the Edit Links dialog box appears with the message "This workbook contains one or more links that cannot be updated." Brooke suggests several possible causes of this error message. Which of the following is a possible cause? a. one of the workbook's source files was edited c. one of the workbook's source files was replaced with an updated file of the same name d. the workbook was sent to him without links ANSWER: d

Q: Hyperlinks are usually represented by words with colored letters and underlines or images. _________________________

Q: To delete a hyperlink, right-click the hyperlink cell and then click Clear Value. _________________________

Q: 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.

Q: Some of the task-specific outlines available from the Office.com site include: Monthly Family Budget, Inventory List, and Time sheets. _________________________

Q: Using multiple worksheets makes it more difficult for you to group your data. a. True b. False

Q: Excel, by default, looks for template files in the Templates folder, which is where custom template files are often stored. _________________________

Q: Once you group a collection of worksheets, any changes you make to one worksheet are applied to all sheets in the group. a. True b. False

Q: A worksheet ____ is a collection of two or more selected worksheets. a. index b. roster c. group d. cluster

Q: If a worksheet group includes all the worksheets in a workbook, you can edit only the active worksheet. a. True b. False

Q: Like cell ____, a worksheet group can contain adjacent or nonadjacent sheets. a. controls b. groups c. tabs d. ranges

Q: If you delete a value from one cell in a worksheet group, the content is also deleted from the same cell in all the worksheets in the group. a. True b. False

Q: The ____ caption indicates a worksheet group. a. [Worksheets] b. [Selected Sheets] c. [Group] d. [Workgroup]

Q: Using multiple worksheets with identical layouts enables you to use 3-D references to quickly summarize the data in another worksheet. a. True b. False

Q: To ungroup worksheets, you can click a sheet ____ of a sheet not in the group. a. index b. panel c. tab d. pane

Q: When the source and destination workbooks are stored in the same folder, you still need to include the location information in the workbook reference that links the two files. a. True b. False

Q: To remove a worksheet group, you can right-click the sheet tab of a sheet in the group and then click ____ on the shortcut menu. a. Remove Group b. No Group c. Exit Group d. Ungroup Sheets

Q: To move a worksheet or worksheet group to another location in the same workbook, select the worksheets and then drag and drop them by the selected sheet ____. a. tabs b. indices c. keys d. icons

Q: When you create a new workbook from a template, an unnamed copy of the template opens. a. True b. False

Q: When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference. a. ? b. ! c. " d. ;

Q: A workbook based on a specific template always displays the name of the template followed by the date. a. True b. False

Q: All template files have the .xlts file extension. a. True b. False

Q: In order to reference data located in one workbook in another workbook, you have to create a(n) dialog between the two workbooks. _________________________

Q: When you use the point-and-click method to build formulas with external references, Excel enters all of the required punctuation, including quotation marks. _________________________

Q: A(n) template is a workbook with labels, formats, and formulas already built into it with data removed. _________________________

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

Q: 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.

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

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

Q: Every ____ consists of an object that contains a button for each unique value in that field. a. piece b. slicer c. section d. part

Q: 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.

Q: 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. c. You cannot add new rows and columns that automatically expand the range. d. You cannot freeze the top row of the worksheet. ANSWER: d

Q: 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.

Q: 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.

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

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

Q: 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.

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

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

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

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

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

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

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

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

Q: By default, the PivotTable report uses the ____ function for numbers in the Values area. a. COUNT b. AVERAGE c. MIN d. SUM

Q: The entry 20000 is the ____________________ for the first condition.

Q: The ____ report layout displays one column for each field and leaves space for column headers. a. Outline b. Design c. Tabular d. Headers

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

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

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

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

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

Q: A data ____ table describes the fields you plan to maintain in a table. a. structure b. definition c. map d. linked

Q: The ____ criteria filter requires the records displayed to start with the specified text string. a. Origin b. Begins With c. Initial d. Start

Q: ____ 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

Q: The ____ criteria filter requires the records displayed to have the specified text string anywhere. a. Has b. Is c. Contains d. Matches

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

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

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

Q: The ____ criteria filter requires the records displayed to have the current date. a. Current Date b. This Date c. This d. Today

Q: Excel assigns the name ____ to the first Excel table created in a workbook. a. 1 b. 1Table c. NewTable d. Table1

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

Q: Which of the following is a valid table name? a. %_table b. $Table2 c. _Newtable d. 2Table

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

Q: A table name cannot include ____. a. numbers b. spaces c. underscores d. letters

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

Q: ____ order arranges labels in reverse alphabetical order, from Z to A. a. Reverse b. Major c. Ascending d. Descending

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

Q: ____ order arranges labels in reverse alphabetical order, from A to Z a. Reverse b. Major c. Ascending d. Descending

Q: The fields you use to order your data are called sort ____. a. values b. fields c. tags d. tools

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

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

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

Q: 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. ____________________

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

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

Q: You can use a tables sizing handle to add columns or rows to a table. a. True b. False

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