ITM Exam 1

Which of the following symbols display when a column is too narrow? - @@@@@@ - ###### - $$$$$$ - %%%%%%

######

Which of the following codes is inserted in a header or footer to include the date on each page? - &Date - &[Date] - &{Date} - &(Date)

&[Date]

Which of the following is a valid 3D cell reference? - Sheet2B2 - Sheet2B2! - Inventory#$B2 - 'Inventory#'!B2

'Inventory#'!B2

How many items can the Office Clipboard hold? - 12 - 16 - 24 - unlimited

24

Which of the following does not apply to adding and removing chart elements? - You can select a chart element from the Chart Elements drop-down list. - Show or hide chart elements using the Chart Elements button in the top-right corner of a chart. - All charts have the same elements - Screen Tips identify a chart element

All charts have the same elements.

Which of the following methods inserts a manual line break in a cell? - Ctrl+Enter - Ctrl+F4 - Alt+Enter - Shift+Enter

Alt+Enter

Which of the following options is the correct format for an external reference? - An external reference includes the name of the workbook in parentheses (). - An external reference includes the name of the workbook in square brackets []. - An external reference includes the name of the workbook in braces {}. - An external reference includes an exclamation point (!) in the name of the workbooks.

An external reference includes the name of the workbook in square brackets [].

Which of the following options enables you to assign a name to existing formulas that refer to the cells identified in range names? - Apply Names command - Formula AutoComplete - Name Manager - Create from Selection

Apply Names command

Which of the following features displays a suggested label in a column when the first character or two that you type matches a label already in the column? - Autofill - Autocomplete - AutoFit - AutoCorrect

AutoComplete

Which of the following is not a basic rule for naming a cell range? - Do not use spaces or special characters in a range name. - Do not name a range with a cell reference. - Begin a range name with a number. - Use a short, descriptive name.

Begin a range name with a number.

When you open a blank workbook, which of the following names is automatically assigned to the workbook? - Book1 - Sheet1 - Workbook1 - Worksheet1

Book1

By default, how does Excel align labels in a cell? - top left of a cell - top right of a cell - bottom left of a cell - bottom right of a cell

Bottom left of a cell.

Which of the following functions counts cells that meet the criteria arguments within a range? - COUNTA - COUNTBLANK - COUNTIF - DCOUNT

COUNTIF

Which of the following displays the result of a formula? - cell - range - Formula bar - Name box

Cell

Which of the following commands horizontally centers multiple rows across multiple columns? - Wrap Text - Merge and Center - Center Across Selection - Merge Cells

Center Across Selection

What key do you press to select non-adjacent worksheets? - Ctrl - Alt - Shift - Tab

Ctrl

Which of the following keyboard shortcuts opens the Format Cells dialog box? - Ctrl+1 - Ctrl+2 - Ctrl+3 - Ctrl+4

Ctrl+1

Which of the following keyboard shortcuts opens a new blank workbook? - Ctrl+A - Ctrl+N - Ctrl+O - Ctrl+W

Ctrl+N

Which of the following options does not appear in the Trace Error drop-down list? - inconsistent Formula - Help on this error - Ignore Error - Delete Error

Delete Error

Which of the following is not true for inserting dates? - Excel treats each date as a serial number. - Excel sets January 1, 2000, as number 1. - You can see the serial number for any date by applying the General format to the cell. - The TODAY function inserts the current date in the cell and updates each time the workbook opens.

Excel sets January 1, 2000, as number 1.

Which of the following opens the Paste Name dialog box? - F1 - F3 - F5 - F7

F3

Once you have selected a cell address, which of the following keys inserts dollar signs quickly? - F2 - F4 - F6 - F8

F4

T/F: A circular reference occurs when a formula references the wrong cell address.

False

T/F: A dependent is a cell that contributes to the formula results.

False

T/F: An external reference formula refers to a cell in another worksheet.

False

T/F: Cell F6 is the intersection of row F and column 6.

False

T/F: Column and row references do not change when you copy a relative cell address.

False

T/F: Excel calculates addition operations before operations within parentheses are performed.

False

T/F: Excel treats an empty cell in an array in a SUMPRODUCT formula as if it has a value of 1.

False

T/F: Formula AutoComplete highlights and color codes each cell as you type its address.

False

T/F: In Excel, you can average up to 100 numbers (or ranges).

False

T/F: The Arrange All command changes or cycles through open workbooks.

False

T/F: The Fill Handle is a useful tool for formatting worksheet cells.

False

T/F: The VLOOKUP function has a row_index_num argument.

False

T/F: The default top and bottom margins for an Excel worksheet are 1.5.

False

T/F: The feature that resizes column width or row height to accommodate the width or height of the longest or tallest entry is called the AutoWiden feature.

False

T/F: The thickness of an outline is measured in inches.

False

T/F: To remove a selected cell from a selected range, press Shift and click the cell to be deselected.

False

T/F: You can insert up to 21 worksheets in a workbook.

False

Which of the following paste options copies all data and formulas from the source cell(s), but not the formatting? - Paste - Formulas - Formulas & Number Formatting - Keep Source Formatting

Formulas

Hide all of the dependency tracer arrows at once.

Formulas tab > in the Formula Auditing group, click the Remove Arrows button

In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11).

Formulas tab, Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Click and drag to select cells G2:G11. Click ok.

Modify the number format so no decimal places are visible after the decimal point.

Home Tab, Number Group, you clicked the Decrease Decimal button, clicked the Decreased Decimal button.

The August Purchase Orders data point has been selected for you. Explode the data point by exactly 45% to move it slightly away from the rest of the pie chart.

In the Format Ribbon Tab in the Current Selection Ribbon Group, you clicked the Format Selection button. Inside the Format Data Point dialog from the Trendline options list, you clicked the Series Options list item, typed 45 in the Point Explosion input, and pressed the Enter key.

The August Purchase Orders data point in the Items Received data series has been selected. Change the outline width to 6 pt.

In the Format Ribbon Tab in the Shape Styles Ribbon Group, you clicked the Shape Outline button arrow. In the Outline menu in the Weight menu, you selected the 6 pt option.

Use the Create from Selection command to create named ranges for the data table B8:E11 using the labels in row 7 as the basis for the names.

In the Formulas Ribbon Tab in the Defined Names Ribbon Group, you clicked the Create from Selection button. Inside the Create from Selection dialog, you clicked the OK button.

Show the tracer arrows from the precedent cells to cell B5.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Trace Precedents button.

In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11).

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the AutoSum button arrow. In the AutoSum menu, you clicked the Max menu item. You pressed Enter.

*Insert the current date and time in the selected cell.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the NOW menu item. Inside the Function Arguments dialog, you clicked the OK button.

Insert the current date in the selected cell. Do not include the current time.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the TODAY menu item. Inside the Function Arguments dialog, you clicked the OK button.

Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the PMT menu item. Inside the Function Arguments dialog, you typed B5/12 in the Rate input, typed B6 in the Nper input, typed -B4 in the Pv input, and clicked the OK button.

Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the VLOOKUP menu item. Inside the Function Arguments dialog, you typed B1 in the Lookup_value input, typed DailySales in the Table_array input, typed 5 in the Col_index_num input, typed False in the Range_lookup input, and clicked the OK button.

Enter a formula in cell E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMIF menu item. Inside the Function Arguments dialog, you typed Company in the Range input, typed "ColorFab" in the Criteria input, typed InStock in the Sum_range input, and clicked the OK button.

Enter a formula in cell C3 using the SUMPRODUCT function to calculate the total income by multiplying the daily special price by the number of daily sales. Use the range names SpecialPrice and MembershipsSold.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMPRODUCT menu item. Inside the Function Arguments dialog, you typed SpecialPrice in the Array1 input, typed MembershipsSold in the Array2 input, and clicked the OK button.

In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11).

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the COUNT menu item. Inside the Function Arguments dialog, you typed F2:F11 in the Value1 input, clicked the OK button.

Add an element to the center section of the header to display the current date.

In the Insert Ribbon Tab in the Text Ribbon Group, you clicked the Header & Footer button. In the Design Ribbon Tab in the Header & Footer Elements Ribbon Group, you clicked the Current Date button.

Split the worksheet into four panes at the selected cell.

In the View Ribbon Tab in the Window Ribbon Group, you clicked the Split button.

Which of the following paste options copies all data and formatting? - Merge Conditional Formatting - Match Destination Formatting - Formulas & Number Formatting - Keep Source Formatting

Keep Source Formatting

Set row 1 to print on every page.

On the Page Layout tab, in the Page Setup group, click the Print Titles button. In the Page Setup dialog, on the Sheet tab, click the Rows to repeat at the top box. Click the row selector for row 1. click OK in the dialog.

Freeze the top row of the worksheet.

On the View tab, in the Window group, click the Freeze Panes button. Click Freeze Top Row.

Which of the following views opens the header and footer areas, indicates margin areas, and shows rulers? - Page Layout View -

Page Layout View?

Change the scaling option so all columns will print on one page.

Page layout tab, scale to fit group, click the width arrow, click page 1.

Which of the following activates edit mode? - Press F2 - Press F4 - Press F12 - Press Enter

Press F2

Copy cell F6, and paste to cell F7.

Press the Ctrl+C keyboard shortcut, clicked cell F2, and pressed the Ctrl+V keyboard shortcut.?

Which of the following functions multiplies the cells identified in its array arguments and then total those individual products? - SUMIF - SUMPRODUCT - ROUND - AUTOSUM

SUMPRODUCT

Which of the following terms determines if a range applies to a particular sheet or to the entire workbook? - Name Manager - Scope - Define Name - Range Finder

Scope

What key do you press to select adjacent worksheets? - Enter - Alt - Shift - Tab

Shift

Which of the following is not descriptive of Shapes? - Text can be added to all shapes. - Use shapes to highlight or emphasize a point in a chart. - Shapes can be drawn anywhere on the chart but are usually placed on the background or the plot area. - To insert a shape, select the shape, click, and drag to draw the shape.

Text can be added to all shapes.

Which of the following statements about arrays is false? - An array is a range of cells in a row or a column. - An array has a dimension, which is the number of columns or rows. - The arrays A1:A5 and C1:C5 have the same dimension. - The maximum number of arrays in a formula is two.

The maximum number of arrays in a formula is two.

Which of the following statements is not true when setting page setup options? - Right-click a sheet tab and choose Select All Sheets to group all sheets in the workbook for formatting. - The top and bottom margin settings must be less than the header and footer margins or the worksheet data prints over the header and footer text. - The default orientation is portrait. - The default header and footer margins are 0.3".

The top and bottom margin settings must be less than the header and footer margins or the worksheet data prints over the header and footer text.

Which of the following is not true when inserting and deleting columns? - When you insert a row, functions or formulas are automatically updated to include inserted rows or columns. - To insert multiple columns or rows, each column or row is inserted individually. - -

To insert multiple columns or rows, each column or row is inserted individually.

Which of the following Formula Auditing tools displays lines with arrows to all cells that use the active cell directly or indirectly in a formula? - Trace Precedents - Trace Dependents - Error Checking - Evaluate Formula

Trace Dependents

T/F: A dependent is a cell that is affected by the active cell.

True

T/F: An IF function can test if monthly revenue is above or below average and display "Yes" or "No" in the cell.

True

T/F: If your formula includes a syntax error, Excel displays an error message that starts with the number sign (#) and ends with an exclamation point (!).

True

T/F: It is possible to preview a worksheet from the Page Setup dialog box.

True

T/F: Pressing Shift+F3 opens the Insert Function dialog box.

True

T/F: Sparklines are miniature charts displayed in a cell or cell range.

True

T/F: The Activate dialog box displays a list of existing worksheet names.

True

T/F: The Create from Selection button on the Formulas tab automatically assigns range names based on labels in the top row, the left column, or both.

True

T/F: The PMT function assumes the borrower makes regular payments and the loan has a constant interest rate.

True

T/F: The SUMIF function combines the SUM function with the logical criteria component of the IF function.

True

T/F: The cell address $G4 is a relative cell reference.

True

T/F: To activate edit mode, double-click the cell or press F2.

True

T/F: To insert a new worksheet to the left of the active sheet, press Shift+F11.

True

T/F: When a value is rounded, the rounded value is used in calculations, not the full unrounded number.

True

On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet.

Type =, then click the ByMonth sheet tab, click cell B3. Press Enter

Before using the Merge & Center command, what cell should contain the data? - Bottom-left cell of the selection - Bottom-right cell of the selection - Upper-left cell of the selection - Upper-right cell of the selection

Upper-left cell of the selection

Which of the following methods is not used to edit a formula? - Double-click the cell. - Use the Formula AutoComplete. - Click the Formula bar while the cell is active. - Press F2 to start edit mode.

Use the Formula AutoComplete.

Arrange the Excel windows in a row next to each other.

View tab, Windows group, Click arrange all, select vertical option, click ok

Which of the following statements about hidden worksheets is not true? - Hidden worksheets do not appear in a printout. - A hidden worksheet tab can be referenced in calculations. - You can hide a worksheet by right-clicking a sheet tab. - You can hide a worksheet if it is the only sheet in the workbook.

You can hide a worksheet if it is the only sheet in the workbook.

Which of the following is not a true statement when moving and copying worksheets? - When you copy a worksheet, the copy is named with the same name as the original followed by a number in parentheses. - When moving sheets within a workbook, simply drag the tab to the desired location. - You can only move or copy one sheet at a time. - To copy a worksheet by dragging, press Ctrl and drag the tab to the desired location for the copy.

You can only move or copy one sheet at a time.

Enter a formula in cell C2 to return a value of yes if the value in cell E8 is greater than or equal to the value in B2 or no if it is not.

You clicked the Logical button. In the Logical menu, you clicked the IF menu item. Inside the Function Arguments dialog, you typed E8>=B2 in the Logical_test input, typed yes in the Value_if_true input, typed no in the Value_if_false input, and clicked the OK button.

Edit the formula in cell B9 so the references to cell E2 will update when the formula is copied, and the reference to cell B8 will remain constant. Use AutoFill to copy the formula to cells B10:B12.

You clicked the formula bar, clicked the formula bar, typed "=E2*$B8" in the formula bar, clicked the formula bar, typed "=E2*$B$8" in the formula bar, pressed Enter, clicked cell B9, clicked and dragged the cell B9 fill handle, and released the mouse button on cell B12.

Cut cell F9 and paste it to cell B12.

You right clicked cell F9. In the Right-Click menu, you clicked the Cut menu item. You right clicked cell B12. In the Cell right click menu, you clicked the Paste button.

Which of the following chart types displays the magnitude of change over time and shows the rate of change? - area - XY - stock - radar

area

If the formula in cell D10 is =D8+D9+D10. Which of the following terms describes the error? - value error - numeric error - circular reference error - reference error

circular reference error

*Which of the following criteria sums sales for all items that begin with the letter P? - *p - p* - *?p - ?*p

p*