chapter 5 Working with Multiple Worksheets and Workbooks

PAGE LAYOUT

Display the ___ tab to specify page setup options, such as setting the location of page breaks or centering a printout horizontally and vertically.

all of the above

You can drill a ___ down through a workbook. (format, function, formula)

SHIFT

To select adjacent sheets, select the first sheet by clicking its tab and then hold down the ___ key and click the sheet tab at the other end of the list of adjacent sheet tabs.

linear

1,2,3,4 etc. is an example of a ____ fill series?

ENTER

If you complete a paste operation using the ___ key, then the marquee disappears and the office Clipboard no longer contains the copied data following the action.

CTRL

If you are summing numbers on noncontiguous sheets, hold down the ___ key when selecting the sheets.

Style includes

A style affects the format of a cell or range of cells only if the corresponding check box is selected in the ___ area in the style dialog box.

dotted lines

Excel displays ___ on the worksheet to show the boundaries of each page.

3-D

A range that spans two or more worksheets in a workbook is called a(n) ___ range.

CTRL+R

Use the shortcut key ___ to copy the format of the source area but not the contents.

=sum(a : a)

Use which of the following to sum all the values in column A?

CTRL+K

Use the shortcut key ___ to copy the format of the contents, format and styles of the source area.

Nonlinear

which of the following is NOT a series type that you can create in Excel (Auto fill, Growth, Date)

are blank

By default, headers and footers ___.

floating

A(n) ___ dollar sign always appears immediately to the left of the first significant digit in a cell.

four

A format code can have up to ___ sections.

page setup

The ___ defines the appearance and format of a printed worksheet.

header

A ___ appears at the top of every printed page.

linking

Consolidating data from other workbooks can be referred to as ___.

Custom format codes

___ allow you to specify how cell entry assigned a format will appear.

circular

A ___ reference is a formula that depends on its own value.

Normal

The ___ style is the format style that Excel initially assigns to all cells in a workbook.

footer

A ___ appears at the bottom of every printed page.

style

A(n) ___ is a group of format specifications that are assigned a style name.

source

Linked workbooks are called the ___ workbooks.

CTRL+D

To copy the contents of a cell to the cell directly below it, tap or click in the target cell and press ___.

fixed

A(n) ___ dollar sign always appears in the same position in a cell (regardless of the number of significant digits).

CTRL+F

Use the shortcut key ___ to copy the formulas from the source area but not the contents or format.

based on the amount of memory in your computer

Excel limits the number of worksheets you can have in a workbook ___.

NOW()

Which of the following functions will display the system date?

fill handle

You use the ___ to drag through cells to create a fill series

number signs

A date might be displayed as a series of ___ if the date, as initially formatted by Excel, does not fit in the width of the cell.

single quotation marks

The worksheet reference must be included in ___ when the worksheet name contains a space character.

margins

You can change the ___ to increase or decrease the white space surrounding a printed worksheet or chart.

1

If you multiply 15.7 times 232.999, how many decimal places of accuracy will you have?

ROUND

It is recommended that you use the ___ function on formulas that potentially can result in more decimal places than the applied format display in a given cell.

CTRL

To select nonadjacent sheets, select the first sheet by clicking its tab and then hold down the ___ key and click the sheet tabs of the remaining sheets you want to select.

10

Excel rotates a chart ___ degrees in a clockwise direction each time you tap or click the X Rotation up arrow.

link

A ___ is a reference to a cell or range of cells in another workbook.

CTRL+P

Use the shortcut key ___ to copy the contents and format of the source area.

drilling an entry

Entering a number once and then copying it through worksheets so that it is in the same cell on all selected worksheets is called ___.

page breaks

Excel inserts ___ that show the boundaries of what will print on each page.

Jan, Feb, Mar, etc..

which of the following is an example of a date series?

consolidation

the process of summarizing data gathered from multiple worksheets on one worksheet is called ___.

*

You can select any range of cells with entries surrounded by blank cells by tapping or clicking a cell in the range and pressing CTRL+SHIFT+___.

growth

A ___ series multiplies values by a constant factor.

1 , 1, 1, 1, 1, 1, etc.

which of the following is an example of an auto fill series?

consolidation

The process of summarizing data included on multiple worksheets on one worksheet is called _____.

Custom

_____ format codes allow you to specify how a cell entry assigned a format will appear.

comments

You document a workbook by adding _____ to cells that contain complex formulas.

workspace

Excel allows you to work with separate workbooks in a _____ and then link the workbooks to provide a consolidated view of the data in the workbooks.

Placeholders

_____ often are used when creating an initial consolidated worksheet to guide users of the worksheet regarding what type of data to enter in cells.

numbers

In consolidated worksheets with more complex formulas, you may want to use _____ that test the extreme boundaries of valid data.

CTRL+SHIFT+ASTERISK (*)

Which of the following keyboard shortcuts allows you to select a range of cells with entries surrounded by blank cells?

=NOW()+1

You can enter _____ in a cell to display tomorrow’s date.

DATE

You can use the _____ function to change a year, month, and day to a serial number.

test data

You should create good _____ to ensure your workbooks are free of errors.

date

The following sequence, Jan, Feb, Mar, etc., is an example of a _____ series.

fill

The following sequence, 1, 1, 1, etc., is an example of a _____ series.

linear

The following sequence, 2, 4, 6, etc., is an example of a _____ series.

linear

The following sequence, 1, 2, 3, etc., is an example of a _____ series.

(Home tab | Editing group)

Which of the following is the path to the Fill button?

CTRL

To fill a linear series using the fill handle, hold down the _____ key as you drag through the range.

ROUND

It is recommended that you use the _____ function on formulas that potentially can result in more decimal places than the applied format displays.

(Home tab | Editing group)

The path to the Sum button is _____.

=

You can change from Enter mode or Edit mode to Point mode by typing the _____ sign or symbol followed by clicking a cell.

CTRL+D

To copy the contents of a cell to the cell directly below it, click the target cell and press _____.

CTRL

You should hold down the _____ key to select the nonadjacent cells, as shown in the accompanying figure.

(Home tab | Number group)

The path to the Accounting Number Format button is _____.

.(period)

Which of the following format codes insures that Excel will display a decimal point in the number?

" " (quotation marks)

Which of the following format symbols displays text along with numbers entered in a cell?

Custom

You should click _____ in the Category list on the Number tab in the Format Cells dialog box to select or create a format code.

0.00%;[Blue](0.00%)

Which of the following format codes shows percent values with two decimal places and shows negative percent values in blue with parentheses?

Startup

You can open a workbook each time you start your computer by copying the workbook file to the _____ folder located in the All Programs list.

one

A workbook contains _____ worksheets by default.

(Home tab | Cells group)

Which of the following is the path to the Insert Cells button?

1. Format
2. Function
3. Formula

Which of the following can you drill through a workbook? (All of the Above)

3-D

A range that spans two or more sheets in a workbook is called a _____ range.

CTRL

To select noncontiguous sheets, hold down the _____ key as you select the sheets.

circular

A(n) _____ reference is a formula that depends on its own value.

page setup

Headers, footers, and margins are all _____ options.

CTRL+U

Pressing the _____ keyboard shortcut keys pastes an image of the source area as a picture.

on the status bar

You can switch to Page Layout view by clicking the Page Layout button _____.

page

When you print a worksheet or use the Page Setup dialog box, Excel inserts_____ breaks that show the boundaries of what will print on each page.

(Page Layout tab | Page Setup group)

Which of the following is the path to the Breaks button?

link

A _____ is a reference to a cell or range of cells in another workbook.

(View tab | Window group)

Which of the following is the path to the Switch Windows button?

a. positive numbers
b. negative numbers
c. zeros
d. text

You can customize a format code in a cell entry to specify how _____ are displayed in a cell.(a, b, c, d)

a. Row heights
b. Margins
c. Column widths
d. Headers and footers

When designing a consolidation worksheet, which of the following should be set to sizes large enough to accommodate future needs? (a, c)

a. fill color
b. unit numbers
c. currency amounts
d. style

All numeric cell entry placeholders should be formatted properly for _____.(b, c)

a. Jan, Feb, March, etc.
b. 1, 1, 1, etc.
c. 2, 4, 6, etc.
d. 1, 2, 3, etc.

Which of the following fill actions can you perform by dragging the fill handle? (a, b, c, d)

a. If the number of digits argument is greater than 0 (zero), then the number is rounded to the specified number of digits to the right of the decimal point.
b. If the number of digits argument is equal to 0 (zero), then the number is rounded to the nearest integer.
c. If the number of digits argument is not included, then the number is rounded to 10 times the current value.
d. If the number of digits argument is less than 0 (zero), then the number is rounded to the specified number of digits to the left of the decimal point.

Which of the following are true about the ROUND function? (a, b, d)

True

You can enter formulas that reference cells on other worksheets in the workbook.

False

The less time you spend documenting and designing a workbook, the easier it will be for users to understand its contents.

True

Some organizations require that certain types of data be formatted in a specific manner.

True

The first step in creating a consolidation workbook is to create the consolidated worksheet, shown in the example in the accompanying figure.

False

The DATE function is most useful in formulas where the year, month, and day are constants.

True

As you develop more sophisticated workbooks, it will become increasingly important that you create good test data to ensure your workbooks are free of errors.

False

You must enter the first three numbers in a series so that Excel can determine the increment amount when using the fill handle to increment a series by two.

True

The result of an arithmetic operation, such as multiplication or division, is accurate to the factor with the least number of decimal places.

False

The forward slash (/) is only used to format dates.

True

You can reference an entire column or an entire row in a function argument by listing only the column or only the row.

True

The standard accounting format for a table or numbers contains floating dollar signs in the first row and the total row.

False

You can add a fixed dollar sign to a cell value by clicking Currency on the Number tab in the Format Cells dialog box.

True

Excel assigns a format code to every format style listed in the Category list on the Number sheet in the Format Cells dialog box.

True

The Normal style that Excel initially assigns to all cells in a workbook.

False

A code is a group of format specifications that are assigned a name.

True

You can display the Cell Styles gallery by clicking the Cell Styles button (Home tab | Styles group).

True

You can create and then assign a style to a cell, a range of cells, a worksheet, or a workbook.

False

When you consider how to organize your data in a workbook, use multiple worksheets when the data is tightly related.

True

When you insert a new worksheet, Excel places the new sheet tab to the left of the active tab.

True

You can use the ENTER key to complete a paste operation.

False

Excel’s capability of drilling data through worksheets is an inefficient way to enter data that is common among worksheets.

False

To reference cells in other sheets in a workbook when creating a formula, include the sheet reference rather than the cell reference.

True

If added, a footer appears at the bottom of every printed worksheet page.

False

You can change headers or footers to increase or decrease the white space surrounding the printed worksheet or chart.

False

A worksheet file saves display information about open workbooks.

sheet
worksheet

You can click the____________tabs at the bottom of the Excel window to move from worksheet to worksheet in a workbook.

designs

You create easy to understand workbooks by reviewing alternative ____________prior to creating the workbook.

headers
footers header
and footers

When working with multiple worksheets, using properly formatted page _____________ is important.

Formatting

____________ that can be modified for each worksheet in a consolidated workbook should be applied to titles and subtitles to provide cues to uses of the worksheets.

test
sample

Always take the time to select ___________ data that tests the limits of the formulas.

status bar

You know you are in Point mode when the word Point appears on the left side of the ____________.

Colors

You can click the ______________ button (Page Layout | Themes group) to display a gallery of color schemes.

format code

A(n) __________ is a series of format symbols that defines how a cell entry assigned a format will appear.

symbol

Each format _____________ within the format code has special meaning.

CTRL+1

Pressing the _____________ keyboard shortcut keys opens the Format Cells dialog box.

[color]

The _________ format code displays the characters in the cell in the designated color.

Insert

You can right-click a sheet tab and then click _________ on the shortcut menu to insert a new worksheet.

default

An alternative to adding worksheets to a workbook is to change the __________ number of worksheets before you open a new workbook.

Excel Options

To start a new workbook with more than the default number of worksheets, open the ___________ dialog box and change the ‘Include this many sheets’ option in the ‘When creating new workbooks’ section of the General category.

selected
grouped

The [Group] indicator following the workbook name on the title bar indicates that multiple worksheets are _________________, as shown in the accompanying figure.

drilling an entry

Entering a number once and copying it through worksheets so that it is entered in the same cell on all the selected worksheets is called _____________.

absolute

A sheet reference in a formula, such as ‘Public Works,’ always is a(n) _______________ reference.

3-D

A reference to a range that spans two or more sheets in a workbook is called a(n) ______________ reference.

Point

You can enter a sheet reference in cell by typing it or by clicking the appropriate sheet tab while in __________ mode.

page setup

Before printing a workbook, consider the _____________, which defines the appearance and format of a printed worksheet.

source workbooks

Linked workbooks.

drilling an entry

Enter a number once and copy it through worksheets.

CTRL+P

Paste contents and format of source area.

consolidation

The process of summarizing data included on multiple worksheets on one worksheet.

drag page break

Reposition page break in Page Break Preview.

sample data

Used in place of actual data to verify formulas.

workspace file

Saves information about all the workbooks that are open.

circular reference

A formula that depends on its own value.

Number of Pages button

(Header & Footer Tools Design tab | Header & Footer Elements group)

dependent workbook

A workbook that automatically reads data from linked workbooks

a. apply the built-in format or style that he likes the best; it is impossible to create or modify number formats
b. create and assign a custom format code using the Format Code category in the Number tab in the Create Code dialog box
c. create and assign a custom format code using the Custom category in the Number tab in the Format Cells dialog box
d. apply a different theme to the worksheet

Louie is designing a complex worksheet and needs to format cells containing numbers and calculated values in a special way. Simply applying bold or italic formatting will not meet his needs. He gives you a call to ask for formatting tips. You suggest to Louie that he ____________. (c)

a. a style is a group of format specifications that are assigned a name; he can view styles in the Cell Styles gallery and then click a style to apply it to the selected cell or range
b. styles are effective when used to format a word-processing document and cannot be used to format a worksheet
c. a style is just another name for a set of formatting codes; a style must be customized to be useful
d. using a style will allow him to apply multiple formats at one time to an active cell if it contains text; unfortunately, styles cannot be used to format numbers or calculated values

2. Louie calls back with another formatting question. Another coworker suggested that he use styles to format cells in his worksheet, but he is unfamiliar with styles. You tell Louie that _____. (a)

a. she should copy the data from each worksheet in the three workbooks and paste the data into a single worksheet in a new workbook; then create simple formulas to add all the data
b. she can create a new dependent workbook containing a consolidation worksheet; then create 3-D linking formulas to the three source workbooks to summarize the data in the consolidation worksheet
c. she should try to export the data in the three workbooks into a database program; then create a summary report using the database report tool
d. unfortunately, she will have to manually prepare the summary from data in the three workbooks

Judy, an administrative assistant to the sales manager, sends you an e-mail requesting some help on a current project. The sales manager has created three separate workbooks in which she stores sales data. Judy needs to create a new workbook with worksheets that summarize the data from the three workbooks; she asks if there is any way to summarize the data in the three workbooks without spending hours to do it manually. You tell Judy that ___________. (b)

a. open and arrange the group of workbooks and then click the Save Workspace button (View tab | Window group) and create a workspace file; the next time she needs to work with the group of workbooks, just open the workspace file
b. open and arrange the group of workbooks and then click the Create Work Area button (Home tab | Window group) and create a work area file; she can then open the work area file when she wants to work in the workbooks
c. use the Grouping tool to group all of the workbooks into a single workbook file; then open the new workbook file each
time you need to work with the group of workbooks
d. simply open each workbook file one at a time from the Open dialog box; there is no shortcut to opening groups of
workbooks

4. Judy sends you another e-mail thanking you for your tip and asks if there is any quick way that she can open a group of workbooks each time she needs to work with them. You tell Judy to _____. (a)

F11

Once you have selected a range to chart, you can press_____ to draw a chart on a worksheet.

False

If multiple sheets are selected and you want to select the active sheet, if you are using a mouse, hold down the CTRL key and then click the sheet tab of the active sheet.