Excel Calculations Self-Test



Excel Calculations Self-Test

Multiple choice. 22 questions.

1. An Excel formula must begin with what symbol?

A. ‘ (single quote mark)

B. / (forward slash)

C. + (plus sign)

D. = (equals sign)

Correct: D.

2. To what can a cell name reference refer?

A. A single cell only.

B. Either a single cell, a range of contiguous cells, or a combination of single cells and ranges.

C. A range of contiguous cells only.

Correct: B.

Comment: Even non-contiguous cell and range selections can be assigned a name.

3. How would you refer to ALL the cells in row 5? How would you refer to ALL the cells in column A?

A. R5 and CA

B. A5:ZZZ5 and A1:A2985

C. 5:5 and A:A

Correct: C.

Comment: A5:IV5 would also reference the entire 5th row. A1:A65536 would also reference the entire column A. However, 5:5 and A:A do the same thing and are easier to type and remember.

4. What is Excel’s R1C1 reference style?

A. Reverses the usual row and column order in references. For example, A5 becomes 5A.

B. Numbers both rows and columns on a worksheet instead of using numbers and characters.

C. Makes all cell references absolute automatically, without the user having to manually apply absolute cell references.

Correct: B. The R1C1 reference style is used most often when writing Excel macros.

5. What Excel operator would you use in a formula to join the words “Duke” and “University” into “Duke University”?

A. The “at” sign. That is =”Duke”@” “@”University”

B. The ampersand. That is =”Duke”&” “&”University”

C. The plus sign. That is =”Duke”+” “+”University”

Correct: B.

Comment: You can also concatenate using the concatenate function. For example, you could write = concatenate(“Duke”, ” “, “University”) to get the same result.

6. Are the colon, space, and comma symbols at the TOP of Excel’s order of calculations or at the bottom? That is, in a calculation are they evaluated first or last?

A. Bottom.

B. Top.

Correct: B.

Comment: See a list of operator symbols and their order by entering “order of operations” in Excel’s online help Answer Wizard.

7. Excel’s calculation is automatic by default. How would you change the calculation method to manual?

A. Hit the F9 key.

B. Open the “Options” dialog (Tools, Options), choose the “General” tab, and choose “Calculate on Demand”.

C. Open the “Options” dialog (Tools, Options), choose the “Calculation” tab, and toggle on “Manual” calculation.

Correct: C.

Comment: The F9 key causes recalculation when manual calculation is on.

8. What is Excel’s “formula palette”?

A. A dialog that displays all of Excel’s built-in functions.

B. The space in Excel’s formula bar where the content of the formula displays, as opposed to the worksheet where the result of the formula displays.

C. An addendum to the formula bar that displays when you click the equals button on the formula bar and shows you the progress of your formula as you build it.

Correct: C.

Comment: The formula palette displays intermediate formula results as you build a formula.

9. How can you view the serial number that underlies an Excel date or the decimal fraction that underlies an Excel time?

A. Choose Tools, Options from the menus, select the “View” tab, and choose “Show date/time”.

B. Change the format of the cells containing the data and time to General number format.

C. Click the F9 key.

Correct: B.

Comment: Microsoft Excel begins serial numbers with 1900. Some other systems begin with 1904. If necessary you can use the menu commands Tools, Options, select the “Calculation” tab, and toggle on “1904 date system” to convert to this alternative scheme.

10. How can you replace formulas in a worksheet with the formulas’ calculated values? That is, the formula =5+5 would be replaced in the cell with the value 10.

A. Copy the formula to the Clipboard, select Edit, Paste Special from Excel’s menus, then from the “Paste Special” dialog choose the “Values” option.

B. Choose Tools, Options from Excel’s menus, select the “View” tab, and choose the “Formulas to values” option.

C. Hit the F9 key.

Correct: A.

Comment: Excel’s “Paste Special” dialog is well-worth exploring. Its 15 options include along with paste options a variety of arithmetic operations and transposition.

11. How can you replace PART of a formula with its value?

A. In the formula bar highlight the part of the formula in question and hit the F9 key. Then hit the enter key.

B. You can’t. It’s all or nothing.

C. Highlight the part of the formula in question and hit the F4 key.

Correct: A.

Comment: The very useful F4 key is used to toggle on and off absolute, relative, and mixed addressing when writing a formula.

12. Which of the below are valid methods for naming a cell or range?

A. Choose the range or cell and enter the name in the formula bar’s “Name Box”.

B. Choose Insert, Name, Define from Excel’s menus and complete the “Define Name” dialog.

C. Choose Insert, Name, Create from Excel’s menus and complete the “Create Names” dialog.

D. All of the above.

Correct: D.

13. When is it important to be mindful of absolute vs. relative vs. mixed addressing?

A. When using Excel’s alternative R1C1 reference method.

B. When copying a formula.

C. When writing Excel macros.

Correct: B.

Comment: If you don’t plan to copy a formula you need not worry about relative vs. absolute addressing. But if you DO plan to copy a formula, be sure you understand the differences.

14. Which of the answers below best describes Excel’s precision in calculations?

A. Excel stores all the digits that are part of a value.

B. Excel stores numbers with up to 25 digits of precision and discards digits beyond 25.

C. Excel stores numbers with up to 10 digits of precision, then converts any digits beyond 10 to zero.

Correct: B.

15. For which operation(s) can you use Excel’s Edit, Fill, Series commands?

A. Auto fill operations.

B. Date operations.

C. Linear operations.

D. A, B. and C.

E. None of the above.

Correct: D.

Comment: Linear, growth, date, and auto fill operations are all handled in the “Series” dialog.

16. How can you quickly highlight ALL the cells in a spreadsheet that contain formulas?

A. Choose Tools, Options from the menu, select the “View” tab, and choose “Formulas”.

B. Click the F5 key to open the “GoTo” dialog, choose “Special”, and in the “Go To Special” dialog, choose “Formulas”.

C. Hit the F9 key.

Correct: B.

Comment: The view formulas option displays all formulas in a worksheet but does not highlight all cells that contain them.

17. What’s the syntax for referencing a range in a different worksheet in the SAME workbook?

A. =WorksheetName!RangeReference

For example, =Marketing!B1:B10

B. =”WorksheetName”+RangeReference

For example, =”Marketing”+B1:B10

C. =WorksheetNumber, RangeReference

For example, =Sheet3, B1:B10

Correct: A. An exclamation mark must go between the worksheet name and the range reference. If the worksheet name contains blanks, surround the name with single quote marks.

18. What’s the syntax for referencing a range in a different WORKBOOK?

A. =[WorkbookName]SheetName!RangeReference

For example, =[Budget.xls]Marketing!B1:B10

B. ={WorkbookName}SheetName!RangeReference

For example, ={Budget.xls}Marketing!B1:B10

C. =SheetName!WorkbookName,RangeReference

For example, =Marketing!Budget.xls,B1:B10

Correct: A.

Comment: Use square brackets to surround the workbook name. Use the same method to reference sheet name and range as before (that is, exclamation mark between the two and single quote marks around the sheet name if the name contains spaces).

19. How can you display Excel’s Auditing toolbar to trace precedents and dependencies in a workbook?

A. Choose the menu commands View, Toolbars to see a list of toolbars and toggle on the Auditing toolbar.

B. Choose the menu commands Tools, Auditing, Show Auditing Toolbar.

C. Use they keyboard shortcut ALT+a (depress the ALT key and tap the “a” key).

Correct: B.

Comment: The Auditing toolbar doesn’t display on the list of toolbars visible using the View, Toolbars commands. However it DOES appear in the list of toolbars on the “Toolbars” tab of the “Customize” dialog.

20. Which of the below display the correct syntax for Excel’s IF function?

A. =IF(Test-condition, CellReference1, CellReference2)

For example, =IF(10 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download