Question 1

Which type of cell reference preserves the exact cell address in a formula?

Accepted Answer

Absolute cell reference uses "$" symbol in front of the column and row reference to lock the cell address, so that it preserves the exact cell address in a formula. Mixed cell reference also locks either the column or row reference using $ symbol, but not both. Relative cell reference changes according to the position of the formula when it is copied to other cells, hence it does not preserve the exact cell address. Therefore, the answer is A, absolute cell reference.

Question 2

When cells or rows are inserted or deleted in an Excel worksheet, how are cell references affected by the insertion or deletion?

Accepted Answer

Cell references are automatically adjusted when cells or rows are inserted or deleted in an Excel worksheet. This ensures that formulas and functions referencing those cells continue to work correctly.

Question 3

Which method of entering a function requires the user to know the name and initial characters of a function?

Accepted Answer

Manually typing the function requires the user to know the exact name and initial characters of the function. The other options may provide a list of functions for the user to choose from, but they still require the user to know what function they need to use.

Question 4

In the formula =(G15-G14)*$M$3/4, which mathematical operation is performed first?

Accepted Answer

In the formula, (G15-G14) is the first operation performed as it is enclosed within the parentheses. This is followed by multiplication with $M$3, and then division by 4. Therefore, choice C, which represents the first operation of subtracting G14 from G15, is the best choice.

Question 5

All functions begin with the formula prefix ____.

Accepted Answer

All formulas in Excel begin with an equals sign (=), so choice B is the correct answer. The other choices are not formula prefixes in Excel.

Question 6

Functions can be entered for formulas using all of the following methods except ____.

Accepted Answer

Typing the function argument alone is not a method to enter a function; it must be part of a complete function entry.

Question 7

In Excel, MAX and AVERAGE are examples of ____.

Accepted Answer

MAX and AVERAGE are both functions in Excel, which are predefined formulas that perform a specific calculation or task. They are used to find the highest value and the average value of a range of numbers, respectively. Arguments are inputs that are passed into a function, and lists are collections of data. Buttons are clickable tools that perform specific actions, but they are not related to the MAX and AVERAGE functions in Excel.

Question 8

When cells are copied using Excel's copy function, what happens to the data in the copied cells?

Accepted Answer

When cells are copied using Excel's copy function, the data remains in its original location. The copied cells are simply duplicated in another location.

Question 9

You can create an absolute cell reference by placing a ____ in front of both the column letter and the row number of the cell address.

Accepted Answer

The dollar sign ($) is used to create an absolute cell reference by placing it in front of both the column letter and the row number of the cell address.

Question 10

The COUNTA function is used to count the number of cells in a range that contain which type of data?

Accepted Answer

COUNTA function counts all non-blank cells, regardless of the type of data they contain. This includes numbers, dates, and formulas.

Question 11

Which function key on the keyboard allows the Excel user to cycle through all possible combinations of relative, mixed, and absolute cell references for a formula?

Accepted Answer

The function key [F4] in Microsoft Excel allows the user to cycle through all possible combinations of relative, mixed, and absolute cell references for a formula. This is useful for adjusting cell references without having to retype the entire formula.

Question 12

Once the Office Clipboard reaches its maximum of 24 items, how does it handle new items that are copied or cut?

Accepted Answer

Once the Office Clipboard reaches its maximum of 24 items, it automatically deletes the oldest item on the clipboard to make room for the new item. This ensures that the clipboard is always functioning and able to hold the most recent items that have been copied or cut.

Question 13

Which of the following is a complex formula?

Accepted Answer

The formula in B involves multiple mathematical operations, including division and multiplication, making it more complex than the other options which involve only one operation each.

Question 14

An Excel user should use a relative cell reference when it is important to ____.

Accepted Answer

Relative cell references change their position relative to the location of the formula when copied to other cells. If preserving the relationship to the formula location is important, then a relative cell reference should be used.

Question 15

After you use the fill handle to copy cell contents, the Quick Copy Options button appears, which can be used to fill the cells with only specific elements of the copied cell, if you wish. _________________________

Accepted Answer

The correct term is "Auto Fill Options" button, not "Quick Copy Options" button.

Question 16

To insert cells, click the Insert list arrow in the Cells group on the ____ tab, then click Insert Cells.

Accepted Answer

The Insert Cells option is found under the Cells group on the HOME tab in Excel.

Question 17

Which formula contains a mixed cell reference?

Accepted Answer

In formula D, $A6 is a mixed cell reference because the row number (6) is not fixed with a dollar sign, but the column letter (A) is fixed with a dollar sign. This means that the formula will always refer to column A, but will adjust to the row number of the cell where the formula is copied or dragged to.

Question 18

How can the order of precedence in this formula, =C12+C13*F4, be changed so that cells C12 and C13 are added together as the first operation that occurs?

Accepted Answer

The answer of How can the order of precedence in...

Question 19

In the formula =D1+C1/F3, which calculation will be performed first?

Accepted Answer

The answer of In the formula =D1+C1/F3, which calculation will...

Question 20

Which tab and group will allow the insertion of rows and columns in a worksheet?

Accepted Answer

The Home tab in Excel is generally used for basic editing and formatting tasks, while the Page Layout tab is used for adjusting the appearance of the sheet for printing. The Styles and Page Format groups do not contain options for inserting rows and columns. Therefore, the answer is option C, the Home tab's Cells group, which contains the Insert command for adding rows and columns to the worksheet.

Quiz 7: Excel 2: Working With Formulas and Functions