Excel - Chapter 5

Static Consolidation

- places a non-changing result in the consolidation sheet - If source data is edited, the consolidation sheet is not updated.

Dynamic Consolidation

-Use when source data might be edited -Choose option to create links to source data -Places formulas on the consolidated sheet in an outline -When data on a source worksheet is edited, the formula recalculates

False **A static consolidation summarizes data and displays a result on the consolidated sheet that does not update automatically.

A static consolidation summarizes data and displays a result on the consolidated sheet that updates automatically. True or False?

Dependent workbook

A workbook that contains data cells from another workbook

true

An external reference formula refers to cells in another workbook and establishes a link to the source workbook.

True

An identifier is a character such as an exclamation point that marks or signifies a component of an external reference. True or False?

What are procedures to prepare a consolidation worksheet?

Arrange worksheet data in the same rows and columns on each sheet. Copy one of the source worksheets to create a consolidation worksheet. Delete the contents of the cells to be consolidated.

Hyperlink

Click on this text string or object and your pointer will jump to another location

False

Create the consolidation worksheet after you start the Consolidate command. True or False?

source worksheet

Existing worksheet containing a range included in a consolidation worksheet

In the Cloud

For workbooks to be linked, they must all be accessible over a network, on the same computer, or

as an outline

How does Excel display a sheet that is being used in a dynamic consolidation?

True

If values are edited in any of the source worksheets used in a static consolidation, run the Consolidate command again to update results.

True

If values are edited in any of the source worksheets used in a static consolidation, run the Consolidate command again to update results. True or False?

consolidate command

If values are edited in source worksheets used in a static consolidation, which of the following commands will update the results?

False

If you save a workbook while sheets are grouped, the workbook will open with ungrouped sheets the next time you open the file. True or False?

Consolidation by Category

In Excel, this refers to a consolidation based on row or column labels

False

Passwords in Excel are not case sensitive. True or False

Selection Handles

Points on the frame of a graphic used to resize it

False

Press Shift+F6 to switch to the next open workbook. True or False?

True

The Consolidate command calculates totals, averages, or other functions for data from multiple worksheets. True or False?

False

The header or footer is one command that requires all grouped sheets to be identical. True or False?

True

The outline in a consolidation worksheet uses 3D reference formulas. True or False?

false

The procedure to build a dynamic consolidation sheet is different from building a static consolidation sheet. true or false?

True

To capture one part of a computer screen, use the Screen Clipping command. True or False?

The Arrange All Button

To view open workbooks side by side or horizontally tiled, use

False

Use the Page Layout tab to insert a picture in an Excel worksheet. True or False?

Ctrl+F6

What do you press to switch to the next open workbook?

Count, Product, Sum

What functions are listed in the Consolidate dialog box?

A ScreenTip

What identifies the file and provides basic instructions for a hyperlink?

Picture Tools Format tab

When a screenshot graphic object is selected, which of the following context-sensitive tabs display?

The cell reference and defined names groups cannot be expanded **The Cell Reference and Defined Names groups have expand and collapse buttons.

When creating a cell hyperlink, which of the following is not true when selecting a Place in this Document option?

delete the contents

When creating a consolidation worksheet, what must you do to the range of cells to be consolidated?

Create the consolidation worksheet before you start the Consolidate command.

When do you create the consolidation worksheet?

a link to the source workbook

When linking workbooks using a formula, what type of link is created?

False

When worksheets are grouped, the title bar displays [Document1] after the file name. True or False?

[Group]

When worksheets are grouped, the title bar displays what after the file name?

true

When you group sheets, most editing and formatting commands that you perform affect every sheet in the group. True or False?

a picture object.

When you place a screenshot in a worksheet, what type of object is it?

Insert tab, Illustrations group.

Where is the Take a Screenshot command located?

Consolidate

Which command links workbooks?

Edit Links dialog box

Which dialog box is used to update links in a workbook?

Apply a fill color, Change fonts, Change alignment

Which of the following can be performed when worksheets are grouped?

expand each outline item

Which of the following can you do immediately after you create a dynamic consolidation?

Insert a graphic

Which of the following cannot be performed when worksheets are grouped?

the header or footer command.

Which of the following commands can you apply to grouped sheets that are not identical?

Mark as Final command

Which of the following commands sets a read-only file property?

The bounding frame is a rectangle that surrounds the graphic

Which of the following describes a SmartArt graphic bounding frame?

Worksheets are automatically grouped.

Which of the following does not occur after a dynamic consolidation is created?

Right click any sheet tab and choose Select All Sheets

Which of the following groups all sheets in a workbook?

Source workbook

Which of the following includes data that is referenced in a dependent workbook?

Screenshot

Which of the following is a picture of a full or partial computer screen inserted as an object in a worksheet?

A consolidation worksheet

Which of the following is a separate summary sheet in a workbook?

All workbooks must be open when you use the Consolidate command. Link workbooks using the Consolidate command. Link workbooks building formulas that refer to cells in other workbooks.

Which of the following is a true statement?

='[WorkbookName]WorksheetName'!CellRange

Which of the following is an example of an external reference with all of its identifiers?

external reference formula

Which of the following is not a component of a SmartArt graphic?

PMT

Which of the following is not a function listed in the Consolidate dialog box?

Delete formulas that will be used on the consolidated worksheet. **DO NOT delete formulas that will be used on the consolidated worksheet.

Which of the following is not a procedure to prepare a consolidation worksheet?

When you use the point method to create an external reference, it is built with mixed references.

Which of the following is not a true statement?

text effects

Which of the following is not an example of SmartArt objects?

As you type an entry, the text and its shape are sized to fit the content.

Which of the following is not true AFTER inserting a SmartArt graphic?

Ctrl+K

Which of the following is the keyboard shortcut for inserting a hyperlink?

Linking

Which of the following is the process of referring to data in another workbook?

It rests on an invisible, transparent layer in the worksheet

Which of the following is true of a SmartArt graphic's position in a worksheet?

Ctrl

Which of the following is used to select nonadjacent sheets?

Arrange open windows side by side

Which of the following makes it easier to take screenshots?

Hyperlink

Which of the following moves the pointer to another workbook or web page?

All of the Above: The data is consolidated in outline format. Each item is collapsed. The consolidation sheet displays a 3-D reference in each cell above the result cell when the outline is expanded.

Which of the following occurs after a dynamic consolidation is created?

Consolidation by category can be either dynamic or static

Which of the following statements is true?

Right-click any sheet tab and choose Ungroup Sheets

Which of the following ungroups all sheets in a workbook?

Labels match

You can build a data consolidation by category as long as the

SmartArt graphic (Info)

You can customize the text in a SmartArt graphic. A SmartArt graphic includes a text pane for entering text. As you type in a SmartArt graphic, the text and its shape are sized to fit the contents. A SmartArt graphic is NOT linked to worksheet data.

true

You can dynamically consolidate data by using 3D references in your own formulas. true or false

SmartArt objects

matrices, pyramids, bulleted lists, and other similar diagrams are examples of what?

Hyperlink

there is no underlined text.

False

A static consolidation places formulas on the consolidated sheet in an outline format. True or False?

3D reference formula

A formula containing a reference that identifies the sheet name and the cell location.

External reference formula

A formula that links a dependent workbook to a source workbook

false

A Snapshot is a picture of a full or partial computer screen inserted as an object in a worksheet. t/f?

True

A consolidated worksheet summarizes data from multiple worksheets using a mathematical or statistical function. True or false?

Screen Clippings

A feature of the Screenshot button that allows you to select a portion of a window

False

LivePreview identifies the file and provides basic instructions for a hyperlink. True or False?

True

Not all commands work for grouped worksheets. True or False?