Spreadsheet Final

How many buttons display above the search text box in the Excel Help window? Selected Answer: Answers: 7 3 1 5

5

Which of the following formulas shows an example of a mixed cell reference? Answers: =$A$3 =$A$3+B$3 =A3+B3 =$A$3+B3

=$A$3+B$3

The argument (range,criteria,average_range) is required by the _____ function. Answers: A. AVERAGEIF B. SUMIF C. AVERAGEIFS D. SUMIFS

A. AVERAGEIF

Which of the following is not one of the three tabs in the Data Validation dialog box? Answers: A. Add Level B. Settings C. Input Message D. Error Alert

A. Add Level

You can remove a workbook from one folder and insert it into another folder by using the _____ option(s) at the Open dialog box. Answers: A. Cut and Paste B. Send to C. Delete D. Create Shortcut

A. Cut and Paste

One method of moving to a range of cells is by using options at this dialog box. Answers: A. Go To B. Range C. Moving D. Search

A. Go To

To create a hyperlink to an email address, click the E-mail Address button in the _____ section. Answers: A. Link to B. Hyperlink C. Look in D. Network Paths

A. Link to

How are numbers displayed when the format code is [Red];[Blue]? Selected Answer: A. Positive numbers are displayed in red; negative numbers in blue. B. Positive numbers are displayed in blue; negative numbers in red. C. Numbers to the left of the decimal point are displayed in red; numbers to the right in blue. D. Numbers to the left of the decimal point are displayed in blue; numbers to the right in red.

A. Positive numbers are displayed in red; negative numbers in blue.

Which of the following boxes does not appear in the layout section of the PivotTable Fields task pane? Answers: A. SLICER B. FILTERS C. COLUMNS D. ROWS

A. SLICER

Create a PivotChart with the active cell anywhere in a PivotTable by clicking the PivotChart button in the Answers: A. Tools group on the PIVOTTABLE TOOLS ANALYZE tab. B. PivotTable group on the PIVOTTABLE TOOLS ANALYZE tab. C. Layout group on the PIVOTTABLE TOOLS DESIGN tab. D. Charts group on the DATA tab.

A. Tools group on the PIVOTTABLE TOOLS ANALYZE tab.

Add a Total row to a table by clicking the Answers: A. Total Row check box on the TABLE TOOLS DESIGN tab. B. Total Row check box in the Table Styles group. C. Totals button on the TABLE TOOLS TABLE STYLES tab. D. Sum Rows check box on the FORMULAS tab.

A. Total Row check box on the TABLE TOOLS DESIGN tab.

How can you format a cell based on the value in a different cell? Answers: A. Use a formula. B. Apply two conditional formatting rules. C. Apply conditional formatting rules to both cells. D. You cannot format a cell in this way.

A. Use a formula.

If you delete a custom number format, which of the cells with that custom formatting will have it removed? Answers: A. all cells in that workbook B. only the cells in the active worksheet C. only the cells currently selected D. all cells in all workbook

A. all cells in that workbook

The syntax of the COUNTIF and COUNTIFS arguments requires that the criteria . Answers: A. be enclosed in quotation marks. B. be enclosed in square brackets. C. contain only numerical values. D. contain only text.

A. be enclosed in quotation marks.

Slicers allow you to Answers: A. filter a PivotTable or PivotChart without opening the FILTERS list box. B. sort a named range with one click. C. filter data from several workbooks at once. D. insert a graph into a single cell of the worksheet.

A. filter a PivotTable or PivotChart without opening the FILTERS list box.

The styles you define in a workbook are available Answers: A. only in that workbook. B. in any workbook. C. in any workbook in the same folder as the workbook where they were created. D. only in the worksheet that was active in the workbook where they were created.

A. only in that workbook.

The number of brackets needed to end a nested IF statement equals Answers: A. the number of times IF appears in the formula. B. the number of times IF appears in the formula plus one. C. the number of times IF appears in the formula minus one. D. the number of equals signs preceding the formula.

A. the number of times IF appears in the formula.

How are the subtotals displayed when you subtotal a range of data with the Subtotal dialog box? Answers: A. using the Outline feature B. in Print Preview mode C. in comments inserted in cells D. as new rows at the end of the data list

A. using the Outline feature

Which number category in the Format Cells dialog box lines up the currency symbol and decimal points in a column? Answers: Number Accounting Currency Percentage

Accounting

What does the error code #N/A mean? Answers: An argument parameter has been left out of a function. A function name is not entered correctly. A reference cell no longer exists within a worksheet. A formula is attempting to divide a number by zero.

An argument parameter has been left out of a function.

Which of these custom number format codes will round values to fit the number of decimals and fill in leading zeros? Answers: A. ###.## B. 000.00 C. ???.?? D. "Number"###

B. 000.00

Which formula counts the number of cells that are not empty in a range containing both text and numbers? Answers: A. =COUNT(B5:B15) B. =COUNTA(B5:B15) C. =COUNTIF(B5:B15) D. =EMPTYCELL(B5:B15)

B. =COUNTA(B5:B15)

Which of the following formulas shows the proper use of a valid range name? Answers: A. =SUM(A1:A31) B. =SUM(JanSales) C. =SUM(JanSales(A1:A31)) D. =SUM(1stQuarterSales)

B. =SUM(JanSales)

The Stop Automatically Expanding Tables option appears when you click the _____ button. Answers: A. Table B. AutoCorrect Options C. Format as Table D. PivotTable

B. AutoCorrect Options

Which of these table design boxes is checked by default? Answers: A. Banded Columns B. Banded Rows C. First Column & Last Column D. Last Column

B. Banded Rows

Which step opens the New Formatting Rule dialog box? Answers: A. Click the Conditional Formatting button. B. Click New Rule. C. Click the HOME tab. D. Click Manage Rules.

B. Click New Rule.

When you click the Online Pictures button, this displays. Answers: A. Insert Picture dialog box B. Insert Pictures window C. Online Picture dialog box D. Online Pictures window

B. Insert Pictures window

Which of these examples would you use if you wanted to set up a filter to display all U.S. states beginning with the letter M? Answers: A. M> B. M* C. [M] D. M/

B. M*

Which of the following features is new to Excel 2013? Answers: A. Sparklines B. Recommended PivotTables C. Consolidate D. absolute references

B. Recommended PivotTables

What button can you use to return chart formatting back to the original layout? Answers: A. SmartArt B. Reset to Match Style C. Change Chart Type D. Format Selection

B. Reset to Match Style

Which of these is not permitted as the first character in a range name? Answers: A. a letter B. a number C. an underscore (_) D. a backslash (\)

B. a number

Excel allows you to link data Answers: A. between Excel and other spreadsheet applications. B. between worksheets in the same or different workbooks. C. only between worksheets in the same workbook. D. between your worksheet and any other Microsoft Office application.

B. between worksheets in the same or different workbooks.

A table in Excel is similar in structure to a Answers: A. chart. B. database. C. named range. D. workbook.

B. database.

Change the formatting of a SmartArt graphic with options Answers: A. on the SMARTART TOOLS DESIGN tab. B. on the SMARTART TOOLS FORMAT tab. C. at the Shapes button. D. at the SmartArt button.

B. on the SMARTART TOOLS FORMAT tab.

You can use the _____ that appears with transformed WordArt text to change its slant. Answers: A. four-headed arrow B. purple square C. white sizing handle D. hand

B. purple square

When you apply a transform shape to WordArt text, the WordArt border displays with a small, Answers: A. green, triangle shape. B. purple, square shape. C. blue square. D. red circle.

B. purple, square shape.

Which of the following include worksheet references by default? Answers: A. single sheet formulas B. range names C. 3-D formulas D. COUNTIF, SUMIF, and AVERAGEIF formulas

B. range names

When data in a worksheet is entered as a table, each row is a _____ and each column is a _____. Answers: A. field; record B. record; field C. worksheet; workbook D. table; database

B. record; field

When filtering a PivotTable using Timelines, which of the following time levels is not available? Answers: A. days B. weeks C. months D. quarters

B. weeks

If a range name used in a formula is deleted, cells that used the name will display this error message. Answers: A. #ERROR! B. #REF! C. #NAME? D. #NA

C. #NAME?

Which of these format codes aligns numbers vertically on their decimal points? Answers: A. ####.## B. 0000.000 C. ????.?? D. [Red]##.##

C. ????.??

When a table is automatically expanded, the _____ is displayed. Answers: A. Format Cells dialog box B. Insert Function dialog box C. AutoCorrect Options button D. Format as Table gallery

C. AutoCorrect Options button

By default, non-numeric fields are added to the _____ box of the PivotTable Field task pane, and numeric fields are added to the _____ box. Answers: A. Column Labels; Values B. Values; Column Labels C. Rows; Values D. Report Filter; Row Labels

C. Rows; Values

In which of these Office applications can you create a watermark? Answers: A. Excel B. Outlook C. Word D. Access

C. Word

A table should not contain Answers: A. column labels. B. text data. C. blank rows. D. more than five columns.

C. blank rows.

If you type a formula in the first row of a new table column, Excel will create a(n) Answers: A. preformatted column. B. error message. C. calculated column. D. calculated row.

C. calculated column.

In a formula, a(n) _____ is used to separate a worksheet reference from a cell reference. Answers: A. period (.) B. semicolon (;) C. exclamation point (!) D. backslash (\)

C. exclamation point (!)

Using _____, you can classify data into categories based on threshold values for the selected range. Answers: A. format styles B. threshold sets C. icon sets D. filtering

C. icon sets

The Arrange Windows dialog box is used to arrange the display of Answers: A. multiple applications, such as Excel, Word, and Access. B. split windows. C. multiple workbooks that are open at the same time. D. frozen windows.

C. multiple workbooks that are open at the same time.

How is clearing cell contents different from deleting cells? Answers: Clearing cells only removes entered data; it does not remove formulas. Clearing cells must be done on an entire row or column, not on individual cells. Clearing cell contents gives you the option of removing both contents and formatting from a cell. Clearing cells is only temporary; the cell contents and formatting are restored when the workbook is saved.

Clearing cell contents gives you the option of removing both contents and formatting from a cell.

f you do not want a series to increment while using AutoFill, hold down the _____ key while dragging the fill handle. Answers: Tab Alt Ctrl Shift

Ctrl

Which formula uses the correct syntax? Answers: A. =COUNTIF(Expenses>700) B. =COUNTIFS(Expenses,>700) C. =COUNTIF("Expenses",>700) D. =COUNTIF(Expenses,">700")

D. =COUNTIF(Expenses,">700")

Which of these formatting Type options is not available for U.S. English? Answers: A. Zip code B. Zip code + 4 C. Social Security Number D. Credit Card Number

D. Credit Card Number

Which function contains these arguments: (logical_test,value_if_true,value_if_false)? Answers: A. SUMIF B. AVERAGEIFS C. VLOOKUP D. IF

D. IF

What happens if you type new data in the row immediately below the last row of a table? Answers: A. An #NA error message is generated. B. The user is prompted to expand the table size. C. The table automatically adds a new row and column. D. The table automatically adds a new row.

D. The table automatically adds a new row.

Which of these Excel features would be best for creating company logos and headlines? Answers: A. SmartArt B. Charts C. Shapes D. WordArt

D. WordArt

Can you insert a picture watermark into an Excel worksheet? If so, how? Answers: A. Yes; click the Picture button in the Illustrations group. B. Yes; click the Watermark button in the Illustrations group. C. No; watermarks are only available in Word. D. Yes; click the Picture button in the Header & Footer Elements group.

D. Yes; click the Picture button in the Header & Footer Elements group.

To apply a style, select the cell(s) to which the style will be applied, S Answers: A. click the Cell Styles button in the Styles group, and then click the New Cell Style option. B. click the Cell Styles button in the Styles group, and then click the Merge Styles option. C. click the Format button in the Cells group on the HOME tab. D. click the Cell Styles button in the Styles group and then pick a style from the drop-down gallery.

D. click the Cell Styles button in the Styles group and then pick a style from the drop-down gallery.

If the appropriate formatting has been applied, data can be filtered by all of the following except Answers: A. cell color. B. cell icon. C. font color. D. font type.

D. font type.

In Excel, a range that can be managed separately from other rows and columns in the worksheet is called a Answers: A. named range. B. PivotChart. C. chart. D. table.

D. table.

Filter options are available in the Answers: Data Tools group on the Data tab. Text group on the Insert tab. Sheet Options group on the Page Layout tab. Editing group on the Home tab.

Editing group on the Home tab

You can insert a new row in a worksheet by clicking the Insert button arrow, clicking Insert Cells, and then clicking this at the Insert dialog box. Answers: Shift cells down Shift cell right Entire row Entire column

Entire row

When you print column titles in your worksheet pages, how does Excel know what cells to use as the column titles? Answers: Excel automatically uses the first row in the worksheet. You specify them in the Rows to repeat at top text box in the Page Setup dialog box. You specify them in the Columns to repeat at left text box in the Page Setup dialog box. Excel automatically uses the left-most column in the worksheet.

Excel automatically uses the left-most column in the worksheet.

Using the _____ dialog box to sort data in a column maintains the relationship among the data. Answers: Sort Custom Filter Editing

Filter

Clicking the down-pointing arrow at the right side of the AutoSum button and then clicking More Functions will display this dialog box. Answers: More Functions Insert Function Function Arguments Function Builder

Insert Function

In Excel's order of operations, which type of operation happens first? Answers: percents negations multiplications exponentiations

Negations

At the Function Arguments dialog box, enter into this text box the range of cells you want to include in the formula. \ Answers: Range1 Number1 Function1 Results1

Number1

What happens if you select and merge a block of cells that contain data? Answers: Only the data in the upper left cell of the block will be retained. Data in all cells of the block will be deleted. Data in all cells of the block will be retained. An error message will be displayed.

Only the data in the upper left cell of the block will be retained.

This function returns the current date in a date format. Answers: NOW TODAY DATE DAYS

TODAY

If you want to perform a find and replace operation on the entire workbook, you should use this option in the Find and Replace dialog box. Answers: Look in Search Find what Within

Within

In terms of an Excel formula, what is a constant? Answers: a value entered directly into a formula an absolute cell reference entered into a formula a relative cell reference entered into a formula a formula that is not updated when it is copied to a new cell

a value entered directly into a formula

You can use your mouse to change the height of multiple rows at the same time if the rows are Answers: adjacent to one another. all the same height to begin with. not adjacent to one another. empty.

adjacent to one another.

The Page Break Preview allows you to view Answers: and adjust your worksheet page breaks and margins. and adjust your worksheet page breaks, margins, and headers/footers. but not edit your worksheet page breaks. and adjust your worksheet page breaks.

and adjust your worksheet page breaks.

Where can you preview your headers and footers before printing? Answers: in Normal view in Page Break view at the Info backstage area at the Print backstage area

at the Print backstage area

Which component of a theme consists of a set of line and fill effects? Answers: font theme borders theme color theme effects theme

effects theme

The Background button in the Page Setup group on the Page Layout tab allows you to Answers: add sound to your worksheet that will play in the background when the data is displayed. insert into the worksheet a background picture that displays behind the data and can also be printed. insert a watermark that prints behind your worksheet cells. insert into the worksheet a background picture that displays behind the data.

insert into the worksheet a background picture that displays behind the data.

One of the features Excel offers to help with data entry is the AutoComplete feature, which automatically Answers: inserts data in a cell that begins the same as a previous entry. adds dollar signs to currency data. inserts word, numbers, or formulas in a series. corrects many typographical errors.

inserts data in a cell that begins the same as a previous entry.

Using the Delete key to delete cells Answers: deletes the cell data and formatting. only deletes the cell contents. only deletes the cell formatting. deletes the entire cell from the worksheet.

only deletes the cell contents.

When a formula is written by _____, the range of cells to be included in the formula can be selected. Answers: crediting entering editing pointing

pointing

When a row height is being changed, the row height number that displays represents a measurement in Answers: inches. millimeters. points. picas.

points

When you have finished editing the data in a cell, you must get out of Edit mode. Which of the following will not accomplish this? Answers: pressing Shift + Tab pressing Tab pressing Enter pressing Home

pressing Home

The process of calculating a formula and inserting the value in a cell is referred to as Answers: nesting. returning the result. a what-if scenario. layering.

returning the result.

By default, numbers typed in a cell are formatted to Answers: left-align, with no decimals or commas unless they are typed in the cell. right-align, with two decimal places and no commas. left-align, with one decimal place and commas for the thousand separator. right-align, with no decimals or commas unless they are typed in the cell.

right-align, with no decimals or commas unless they are typed in the cell.

The Search option in the expanded Find and Replace dialog box allows you to search by _____ or _____. Answers: uppercase; lowercase numbers; text worksheets; workbooks rows; columns

rows; columns

Clicking the Quick Print button will Answers: display the Print backstage area. send the workbook directly to the printer. print only the cells containing data. print the current selection.

send the workbook directly to the printer.

The Trace Error button is a type of Answers: Wizard. ScreenTip. smart tag. tool tip.

smart tag

Clicking the filter arrow in a heading will display a list of options to do all of the following except Answers: select an entry from the column. create a custom filter. sort all records. filter all records.

sort all records.

Which of the following characters is permitted in a workbook file name? Answers: forward slash (/) space (spacebar) colon (:) question mark (?)

space

Excel's spell checker checks the worksheet from _____ to _____. Answers: the active cell; the last cell in the worksheet that contains data cell A1; the last cell in the worksheet that contains data cell A1; the last cell in the last worksheet of the workbook the active cell; cell Z6000

the active cell; the last cell in the worksheet that contains data

The column width number in the Column Width dialog box represents Answers: the width of the column measured in millimeters. the average number of characters in the standard font that will fit in a cell. the width of the column measured in the typesetting unit called picas. the width of the column measured in inches.

the average number of characters in the standard font that will fit in a cell.

When using the Sort & Filter button, by which column will the data be sorted? Answers: the first column containing numbers the last column on the worksheet the column containing the active cell the first selected column

the first selected column

In the Insert Function dialog box, what displays in the Select a function list box? Answers: all functions an alphabetical list of functions functions that Excel thinks might be useful for the current worksheet the most recently used functions

the most recently used functions

When would you need to use the Then by option in the Sort dialog box? Answers: when sorting numeric data when sorting by more than one column when sorting text data when sorting a group of columns

when sorting by more than one column