TEST BANK for New Perspective on Microsoft Excel 2013, Comprehensive 1st Edition. By June Parsons Da

New Perspectives on Microsoft Excel 2013 Comprehensive 1e June Parsons Dan Oja Roy Ageloff Patrick Carol (Test Bank All Chapters, 100% Original Verified, A+ Grade) Excel Tutorial 1: Getting Started with Excel TRUE/FALSE 1. The name of the active workbook appears in the status bar of the Excel window.

ANS: F

PTS: 1

REF: EX 2

2. The formula bar displays the value or formula of the active cell.

ANS: T

PTS: 1

REF: EX 2

3. The sheets in a workbook are identified in the sheet tabs at the top of the workbook window.

ANS: F

PTS: 1

REF: EX 8

4. The AutoComplete feature automatically completes an entry based on previous entries in a column.

ANS: T

PTS: 1

REF: EX 20

5. A ScreenTip is a box with descriptive text about a command that appears when you point to a button on the ribbon.

ANS: T

PTS: 1

REF: EX 6

6. Speed Fill enters text based on patterns it finds in the data.

ANS: F

PTS: 1

REF: EX 48

7. The worksheet is organized into individual cells.

ANS: T

PTS: 1

REF: EX 9

8. An adjacent range is a collection of separate ranges.

ANS: F

PTS: 1

REF: EX 12

9. Numeric data is any number that can be used in a mathematical calculation.

ANS: T

PTS: 1

REF: EX 17

10. AutoFit changes the column width or row height to display the longest or tallest entry within the column or row.

ANS: T

PTS: 1

REF: EX 27

MODIFIED TRUE/FALSE 1. Column width is expressed in terms of the number of characters a column can contain or the size of the column in points. _________________________ ANS: F, pixels

PTS: 1

REF: EX 25

2. A keyboard shortcut is a key or combination of keys that you press to access a feature or perform a command. _________________________ ANS: T

PTS: 1

REF: EX 6

3. A common business practice is to include a worksheet named Documentation that contains a description of the workbook, the name of the person who prepared the workbook, and the date it was created. _________________________ ANS: T

PTS: 1

REF: EX 16

4. When text wraps within a cell, the column width increases so that all of the text within the cell is displayed. _________________________ ANS: F, row height PTS: 1

REF: EX 28

5. A formula is written using operands that combine different values, resulting in a single value that is then displayed in the cell. _________________________ ANS: F, operators PTS: 1

REF: EX 32

6. Every function follows a set of rules, or syntax, which specifies how the function should be written. _________________________ ANS: T

PTS: 1

REF: EX 36

7. Cutting moves the selected content, whereas copying duplicates the selected content. _________________________ ANS: T

PTS: 1

REF: EX 41

8. When you insert a new row, the existing rows are shifted down and the new row has the same width as the row above it. _________________________ ANS: F, height PTS: 1

REF: EX 43

9. In the formula, =2+6*2, addition would be calculated after multiplication. _________________________ ANS: T

PTS: 1

REF: EX 33

10. Normal view shows how the worksheet will appear when printed. _________________________ ANS: F, Page Layout

PTS: 1

REF: EX 51

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. function f. sheet tabs b. Name box g. Ctrl c. worksheet h. Normal view d. range i. formula view e. order of operations j. template 1. Contains a grid of rows and columns into which you can enter text, numbers, dates, and formulas, and display charts.

2. Multiplication before addition 3. When you copy a range of cells, you must press this key while you drag the selection to its new location 4. A named operation that replaces the arithmetic expression in a formula 5. A group of worksheet cells 6. Press Ctrl + ` to switch to this view 7. Displays the cell reference of the active cell 8. Shows the contents of the worksheet 9. Displays the name of the sheets in the workbook 10. A preformatted workbook with many design features and some content already filled in.

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: C ANS: E ANS: G ANS: A ANS: D ANS: I ANS: B ANS: H ANS: F ANS: J

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 7 EX 32 EX 40 EX 36 EX 11 EX 56 EX 2 EX 51 EX 8 EX 4

MULTIPLE CHOICE 1. The advantage of an electronic ____ is that the content can be easily edited and updated to reflect changing financial conditions. a. database c. expression b. spreadsheet d. formula ANS: B

PTS: 1

REF: EX 4

2. Keyboard ____ can help you work faster and more efficiently because you can keep your hands on the keyboard. a. KeyTips c. ShortTips b. ScreenTips d. shortcuts ANS: D

PTS: 1

REF: EX 6

3. In ____, the ribbon increases in height, the buttons are bigger, and more space appears around each button so you can more easily use your finger or a stylus to tap the button you need. a. Touch Mode c. Tap Mode b. Click Mode d. Normal Mode ANS: A

PTS: 1

4. “AZ” is an example of a ____. a. column heading b. row heading ANS: A

PTS: 1

REF: EX 6

c. cell reference d. Name box REF: EX 9

5. To go to column A of the current row, press ____. a. Ctrl + Home c. Shift + Tab b. Home d. Tab + Enter ANS: B

PTS: 1

REF: EX 10

6. An Excel worksheet can have a maximum of ____ columns in a worksheet. a. 156 c. 16,384 b. 256 d. 17 million ANS: C

PTS: 1

REF: EX 9

7. To make A1 the active cell, you should press which of the following keys? a. Page Up c. Home b. Page Down d. Ctrl + Home ANS: D

PTS: 1

REF: EX 10

8. You can move to the previous or next sheet by pressing the ____ keys. a. Alt + Page Up or Alt + Page Down c. Tab + Page Up or Tab + Page Down b. Ctrl + Page Up or Ctrl + Page Down d. F4 + Page Up or F4 + Page Down ANS: B

PTS: 1

REF: EX 10

9. Each range is identified with a range reference that includes the cell reference of the upper-left cell of the rectangular block and the cell reference of the lower-right cell separated by a ____. a. semi-colon c. colon b. period d. comma ANS: C

PTS: 1

REF: EX 11

10. The characters +, -, *, and / are examples of ____. a. formulas c. arithmetic operators b. values d. calculations ANS: C

PTS: 1

REF: EX 32

11. An Excel formula always begins with a(n) ____. a. parentheses c. plus sign b. equals sign d. colon ANS: B

PTS: 1

REF: EX 32

12. The formula used to multiply cell A1 by cell C1 is ____.

a. =A1*C1 b. C1*A1 ANS: A

c. =A1/C1 d. A1*C1 PTS: 1

REF: EX 32

13. A(n) ____ is a group of cells in a rectangular block. a. cluster c. selection b. adjunct d. range ANS: D

PTS: 1

REF: EX 11

14. In the figure above, Box 1 points to the formula =F13*G13 in the ____. a. address box c. formula box b. address bar d. formula bar ANS: D

PTS: 1

REF: EX 34

15. In the figure above Box 3, points to color coded ____ references. a. cell c. row b. formula d. function ANS: A

PTS: 1

REF: EX 34

16. In the figure above, Box 2 points to a formula in ____ multiplying the values in cells F13 and G13. a. cell F13 c. cell G13 b. cell H13 d. cell H13 ANS: B

PTS: 1

REF: EX 34

17. When you insert a new column, the existing columns are shifted to the ____ and the new column has the same width as the column directly to its left a. right c. down b. up d. left ANS: A

PTS: 1

REF: EX 43

18. If you want to create a new line within a cell, press the ____ keys to move the insertion point to the next line within the cell. a. Ctrl + Enter c. Alt + E b. Alt + Enter d. Ctrl + E ANS: B

PTS: 1

REF: EX 28

19. What is the result of the expression =50+20/10*5? a. 60 c. 25 b. 1 d. 50 ANS: A

PTS: 1

REF: EX 33

20. To change the order of operations, enclose part of the formula in ____. a. brackets c. parentheses b. italics d. quotation marks ANS: C

PTS: 1

REF: EX 33

21. Excel makes reading formulas simpler by ____ each cell reference in the formula and its corresponding cell in the worksheet. a. color coding c. highlighting b. bolding d. italicizing ANS: A

PTS: 1

REF: EX 33

22. To switch to ____ mode, you double-click the cell. a. Insert c. Edit b. Copy d. Paste ANS: C

PTS: 1

REF: EX 19

23. To change a column width, you can click ____ on the Column submenu to make the column(s) as wide as the longest entry of the cells in the column. a. QuickFit c. Full Column b. AutoFit d. Longest ANS: B

PTS: 1

REF: EX 27

24. ____ makes it easier to enter repetitive text in cells. a. Editing mode c. AutoComplete b. AutoFit d. AutoFill ANS: C

PTS: 1

REF: EX 20

25. Which of the following takes precedence over multiplication? a. division c. addition b. exponentiation d. subtraction ANS: B

PTS: 1

REF: EX 32

26. ____ enters text based on patterns it finds in the data. a. AutoComplete c. AutoFit b. Flash Fill d. Fill ANS: B

PTS: 1

REF: EX 48

27. In ____ orientation, a page is taller than it is wide. a. natural c. portrait b. landscape d. basic ANS: C

PTS: 1

REF: EX 53

28. ____ displays the location of the different page breaks within the worksheet. a. Normal c. Page Break Preview b. Page Preview d. Page Break Layout ANS: C

PTS: 1

REF: EX 51

29. You can ____ the width or the height of the printout so that all of the columns or all of the rows fit on a single page. a. shrink c. scale b. size d. scroll ANS: C

PTS: 1

REF: EX 54

30. According to the order of operation in Excel: a. Excel performs exponentiation, then multiplication and division, then addition, and subtraction. b. Excel performs multiplication, then exponentiation, then division, then addition, and then subtraction. c. Excel performs exponentiation, then multiplication, then division, then subtraction, and then addition. d. Excel performs multiplication, then division, then exponentiation, then addition, and then subtraction.

ANS: A

PTS: 1

REF: EX 32

31. To move the active cell up one row, press ____. a. Shift + Enter c. Shift + Tab b. Ctrl + Tab d. Ctrl + Enter ANS: A

PTS: 1

REF: EX 10

32. Which of the following will not change the location of the active cell? a. drag and drop c. clicking another cell b. scrolling d. clicking a column heading ANS: B

PTS: 1

REF: EX 8

33. When you copy a selection of cells using drag and drop, which appears before you release the mouse button? a. an outline of the new location of the selected range b. the range c. a cell reference of the old location d. shortcut tip ANS: A

PTS: 1

REF: EX 41

34. What is the syntax for a SUM formula adding the values of cell F6 to F9? a. /SUM(F9:F6)

c. =END(F6:F9)

b. =SUM(F6/F9) ANS: D

d. =SUM(F6:F9) PTS: 1

REF: EX 36 | EX 37

35. Ctrl + G is used to access the ____. a. Go to Next worksheet command b. G column c. Go to a location in the worksheet command d. formula view ANS: C

PTS: 1

36. mm/dd/yyyy is known as ____. a. a text string b. text data ANS: C

PTS: 1

REF: EX 6

c. a date format d. number data REF: EX 23

37. Whenever you click the Save button on the Quick Access Toolbar or press the ____ keys, the workbook file is updated to reflect the latest content. a. Ctrl + V c. Ctrl + C b. Ctrl + G d. Ctrl + S ANS: D

PTS: 1

REF: EX 57

38. To move or copy a range of cells, select the correct order: 1. Move the pointer over the border of the selection until the pointer changes shape.

2. Select the cell or range you want to move or copy.

3. To move the range, click the border and drag the selection to a new location, or to copy the range, hold down the Ctrl key and drag the selection to a new location. a. 1,2,3 c. 1,3,2 b. 3,2,1 d. 2,1,3 ANS: D

PTS: 1

REF: EX 41

39. In the above figure, which box displays the formula in the formula bar? a. 1 c. 5

b. 2 ANS: B

d. 6 PTS: 1

REF: EX 37

40. In the above figure, which box displays the ScreenTip displaying the syntax of the SUM function? a. 1 c. 5 b. 4 d. 6 ANS: C

PTS: 1

REF: EX 37

41. In the figure above, which box displays the range references? a. 2 c. 5 b. 4 d. 6 ANS: D

PTS: 1

REF: EX 37

42. In the figure above, which box displays the highlighted cells used in the function? a. 1 c. 5 b. 3 d. 6 ANS: B

PTS: 1

REF: EX 37

Case-Based Critical Thinking Questions Case 1-1 Tyler has created a spreadsheet to keep track of his customers for his house-sitting business. In the spreadsheet, he includes the owners’ names, addresses, phone number, and number of days his services are needed.

43. Column B in Tyler’s spreadsheet contains the addresses of his clients. Some of the addresses are truncated so Roger ____. a. uses the AutoComplete command to allow for complete entries b. uses the AutoFit command to remove unwanted space c. resizes the column width until all addresses are visible d. switches to Edit mode ANS: C

PTS: 1

REF: EX 26

TOP: Critical Thinking

44. One of the entries Tyler enters into the spreadsheet automatically right-aligns in the cell. Which entry does this? a. customer phone number b. customer name ANS: C

PTS: 1

c. number of days d. customer address REF: EX 17

TOP: Critical Thinking

45. Tyler wants to include the years that each client has been with him. He wishes to insert a new column called “Year” between Column C (Phone Number) and Column D (Number of Days ). To do this, Tyler selects ____. a. Column C, then clicks the Insert button in the Cells group b. Columns C and D, then clicks the Insert button in the Cells group c. Column D, then clicks the Insert button in the Cells group d. Columns C and D, then clicks the Insert Column Between button in the Cells group ANS: C

PTS: 1

REF: EX 44

TOP: Critical Thinking

46. Tyler inserts a new column between columns C and D. The newly inserted column has the ____. a. same width as the original column C b. same width as the original column D c. default width which is applied to all new columns d. average width of all columns in the worksheet ANS: A

PTS: 1

REF: EX 43

TOP: Critical Thinking

47. Tyler notices that the Address column is too wide. He could use that extra space for other columns so he ____. a. autofits the column by double-clicking the cell with the longest entry b. autofits the column by double-clicking its border c. autofits the column by selecting it, then clicking the AutoFit button in the Cells group d. places the mouse pointer on the column border and drags to the right ANS: B

PTS: 1

REF: EX 27

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 1-2 Samantha is a distributor of jewelry. She uses Excel 2013 to record her inventory and order forms.

Samantha’s worksheet for her company’s monthly orders needs to display summary information including the total number of items ordered and the amount of revenue generated from those orders.

48. Samantha will add the number of orders. She chooses to use the ____ function from the AutoSum feature. a. MAX c. SUM b. ADD d. TOTAL ANS: C

PTS: 1

REF: EX 36

TOP: Critical Thinking

49. To activate the AutoSum feature, Samantha clicks in the ____. a. cell below the group of cells she wants to add b. cell to the right of the last cell in the group of cells she wants to add c. formula bar d. active cell ANS: A

PTS: 1

REF: EX 37

TOP: Critical Thinking

50. Samantha decides to rename her worksheet “Samantha’s Jewelry Business Worksheet” but she is unable to complete the word “Worksheet” because Excel ____. a. only allows for three words in a sheet name b. only allows for 31 characters in a sheet name c. only allows one word in a sheet name d. does not allow for symbols such as the apostrophe in Sam’s in sheet names ANS: B

PTS: 1

REF: EX 16

TOP: Critical Thinking

51. Samantha wants to find out how much money she will make on her largest order, which is currently in row E. Cell E4 contains the number of items ordered and cell E5 contains the price per item. An appropriate formula would be ____. a. =E4+E5 c. =E4*E5 b. =E5/E4 d. =E4^E5 ANS: C

PTS: 1

REF: EX 32

TOP: Critical Thinking

52. Samantha has entered several formulas in her worksheet. All of a sudden, she realizes that the cells that once displayed the formula results are now displaying the formulas and all of the columns are wider. She must have switched to ____. a. editing mode by double-clicking the results cell b. formula view by pressing Ctrl + Alt c. editing mode by pressing Ctrl + Alt d. formula view by pressing Ctrl + ` ANS: D

PTS: 1

REF: EX 56

TOP: Critical Thinking

COMPLETION 1. A(n) ____________________ is a grouping of text and numbers in a rectangular grid or table.

ANS: spreadsheet PTS: 1

REF: EX 4

2. Keyboard ____________________ can help you work faster and more efficiently because you can keep your hands on the keyboard.

ANS: shortcuts PTS: 1

REF: EX 6

3. The active cell is highlighted with a thick green border, its cell reference appears in the ____________________, and the corresponding column and row headings are highlighted.

ANS: Name Box PTS: 1

REF: EX 9

4. The range reference for a(n) ____________________ range includes the range reference to each range, separated by a semicolon.

ANS: nonadjacent PTS: 1

REF: EX 12

5. When you ____________________ a workbook, a dialog box might open, asking whether you want to save the workbook.

ANS: close PTS: 1

REF: EX 13

6. A(n) ____________________ is a named operation that replaces the arithmetic expression in a formula.

ANS: function PTS: 1

REF: EX 36

7. A(n) ____________________ of the original range is placed in the new location without removing the original range from the worksheet.

ANS: copy PTS: 1

REF: EX 40

8. The ____________________ function will not include any cell in the range containing a non-numeric value in the final tally.

ANS: COUNT PTS: 1

REF: EX 42

9. A(n) ____________________ includes a series of questions that help you think about the purpose of the workbook and how to achieve your desired results.

ANS: planning analysis sheet PTS: 1

REF: EX 13

10. ____________________ removes the data from the row or column as well as removes the row or column itself.

ANS: Deleting PTS: 1

REF: EX 45

11. ____________________ enters text based on patterns it finds in the data.

ANS: Flash Fill PTS: 1

REF: EX 48

12. Two common ____________________ changes are adding borders to cells and changing the font size of text.

ANS: formatting PTS: 1

REF: EX 49

13. The ____________________ font size for worksheets is 11 points.

ANS: default PTS: 1

REF: EX 51

14. ____________________ view shows how the worksheet will appear when printed.

ANS: Page Layout PTS: 1

REF: EX 51

15. Changing the page orientation affects only the ____________________ sheet.

ANS: active PTS: 1

REF: EX 53

ESSAY 1. Explain the following Excel 2013 terms: spreadsheet, cell, worksheet, and active cell.

ANS: A spreadsheet is a grouping of text and numbers in a rectangular grid or table.

Each cell is identified by a cell reference, which is based on the cell ’s column and row location.

A worksheet contains a grid of rows and columns into which you can enter text, numbers, dates, and formulas, and display charts.

The cell that is currently selected in the worksheet is referred to as the active cell.

PTS: 1

REF: EX 4 | EX 7 | EX 9

TOP: Critical Thinking

2. The order of operations for arithmetic operators is exponentiation, multiplication, division, addition, and then subtraction. In your opinion, what is the best order of precedence for the following Excel tasks and why: 1) work in editing mode, 2) switch to formula view, 3) rename sheet tabs, 4) resize columns and rows, 5) create a planning analysis sheet, and 6) enter data?

ANS: Instructors: There is some flexibility in this answer - the main thing to look for is that students understand that the planning analysis sheet should be the first task performed. Entering data should be next. Renaming sheet tabs, resizing columns and rows, working in editing mode, and switching to formula view can be ordered as the student wishes, as long as they can support their answer with good reasons. Here is an example answer: I think the best order of precedence for the Excel tasks ( 1) work in editing mode, 2) switch to formula view, 3) rename sheet tabs, 4) resize columns and rows, 5) create a planning analysis sheet, and 6) enter data) should be: 1. Create a planning analysis sheet 2. Enter data 3. Resize columns and rows 4. Rename sheet tabs 5. Work in editing mode 6. Switch to formula view Planning should be the first step in any project. Creating a planning analysis sheet before creating an Excel workbook allows the user to figure out what the purpose of the workbook should be and how to achieve the desired results. Entering data should be next so that the workbook can be created and relationships between numbers can be established. Once the data is entered, it might be necessary to resize columns and rows in order to see all of the data. Also, once the data is entered, it makes sense to label sheet tabs if the data is placed on more than one sheet. This step is important for organization, especially considering the last two steps of working in editing mode and switching to formula view.

The order of the last two steps depends on the nature of the worksheet. If there are many text entries or number entries that need to be edited, having the worksheet as organized as possible will make working in editing mode that much easier. If the worksheet is full of formulas, the same is true.

PTS: 1

REF: EX 13 - EX 14

TOP:

Critical Thinking

3. You have three college bound children and you would like to keep track of each of the costs related to tuition. Do you decide to create three Excel workbooks or one Excel workbook with three worksheets and why?

ANS: Instructors: There is no right or wrong answer to this question, however the answer should show some critical thinking to support the students’ decisions. Here is an example: I would create three separate workbooks for my three children I would record the various types of costs associated with each college in separate sheets within each child’s workbook. I think it would be much more efficient for each child to have his or her own customized workbook made up of as many individual worksheets as needed.

PTS: 1

REF: EX 7 | EX 9

TOP: Critical Thinking

Excel Tutorial 2: Formatting Workbook Text and Data TRUE/FALSE 1. Formatting changes only the appearance of data—it does not affect the data itself.

ANS: T

PTS: 1

REF: EX 70

2. Changing the number of decimal places displayed in a cell does not change the value stored in the cell.

ANS: T

PTS: 1

REF: EX 84

3. After you merge a range into a single cell, you cannot realign its content.

ANS: F

PTS: 1

REF: EX 90

4. The Format Painter does not copy formatting applied to selected text within a cell, and it does not copy data.

ANS: T

PTS: 1

REF: EX 101

5. The appearance of non-theme fonts, colors, and effects remains unchanged no matter which theme is applied to the workbook.

ANS: T

PTS: 1

REF: EX 106

6. Conditional formats are dynamic, so a cell’s appearance will change to reflect its current value.

ANS: T

PTS: 1

REF: EX 108

7. A conditional format can make negative numbers red and positive numbers black.

ANS: T

PTS: 1

REF: EX 108

8. Page Break Preview identifies manual page breaks with a dotted blue line and automatic page breaks with a solid blue line.

ANS: F

PTS: 1

REF: EX 116

9. Each header and footer has three sections—a left section, a center section, and a right section.

ANS: T

PTS: 1

REF: EX 119

10. By default, Excel sets the page margins to 0.5 on the left and right margins and 0.75 on the top and bottom margin.

ANS: F

PTS: 1

REF: EX 121

MODIFIED TRUE/FALSE 1. Excel organizes fonts into theme and non-theme fonts. _________________________

ANS: T

PTS: 1

REF: EX 71

2. The cell reference for the merged cell is the upper-right cell reference. _________________________ ANS: F, upper-left PTS: 1

REF: EX 90

3. By default, Excel displays text in a(n) black font color. _________________________ ANS: T

PTS: 1

REF: EX 73

4. The background image does not affect any cell’s format or content. _________________________ ANS: T

PTS: 1

REF: EX 77

5. Calculated values too large to fit into the cell are displayed in scientific notation. _________________________ ANS: T

PTS: 1

REF: EX 82

6. Text and numbers are displayed vertically within cells. _________________________ ANS: F, horizontally PTS: 1

REF: EX 91

7. A(n) theme is a collection of formatting options that include a specified font, font size, font styles, font color, fill color, and borders. _________________________ ANS: F, style PTS: 1

REF: EX 99

8. A(n) conditional format applies formatting to a cell when its value meets a specified condition. _________________________ ANS: T

PTS: 1

REF: EX 108

9. A(n) footer appears at the top of each printed page. _________________________ ANS: F, header PTS: 1

REF: EX 119

10. If you want to print only part of a worksheet, you can set a(n) print area, which is the region of the worksheet that is sent to the printer. _________________________ ANS: T

PTS: 1

REF: EX 115

MATCHING Match each of the following icons with the description that most closely matches its function.

a. b.

f. g.

c.

h.

d.

i.

e.

j.

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Aligns the cell content with the cell’s bottom edge Increases the size of the indentation used in the cell Decreases the size of the indentation used in the cell Vertically centers the cell content within the cell Aligns the cell content with the cell’s top edge Decreases the decimal place Merges the selected cells into a single cell Horizontally centers the cell content within the cell Forces the cell text to wrap within the cell borders Rotates the cell content to any angle within the cell

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: C ANS: F ANS: G ANS: B ANS: A ANS: D ANS: J ANS: E ANS: I ANS: H

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 87 EX 87 EX 87 EX 87 EX 87 EX 84 EX 87 EX 87 EX 87 EX 87

MULTIPLE CHOICE 1. One of the benefits of Live Preview is that it _____. a. lets you see and evaluate different formats as you develop your workbook b. shows you what a document will look like when printed c. applies thematic styles to your document d. allows you to track changes in your document ANS: A

PTS: 1

REF: EX 70

2. When you don’t want to associate a font with a particular design, you use a _____ font. a. theme c. sans serif b. serif d. non-theme ANS: D

PTS: 1

REF: EX 71

3. Italic, bold, bold italic, and underline are examples of _____. a. font themes c. graphics b. font styles d. character settings ANS: B

PTS: 1

REF: EX 71

4. In the accompanying figure, an example of a theme font is _____. a. Algerian c. Arial b. Calibri d. Batang ANS: B

PTS: 1

REF: EX 72

5. In the accompanying figure, the font in Live Preview is _____. a. Algerian c. Arial b. Calibri d. Calibri Light ANS: A

PTS: 1

REF: EX 72

6. In the accompanying figure, the _____ indicates the font is 11pt. a. Font arrow c. Font color b. Font size box d. Font graphic ANS: B

PTS: 1

REF: EX 72

7. In the accompanying figure, it shows that _____ is the theme font used for headings. a. Algerian c. Cambria b. Batang d. Calibri Light ANS: D

PTS: 1

REF: EX 72

8. In a workbook’s theme, _____ colors are designated for text and background, _____ colors are used for accents and highlights, and _____ colors are used for hyperlinks. a. Four, two, six c. Four, six, two b. Two, four, six d. Six, four, two

ANS: C

PTS: 1

REF: EX 73

9. To add a _____ to a sheet tab, right-click a sheet tab, point to Tab Color on the shortcut menu, and then click a color. a. fill color c. format color b. tab setting d. formatting style ANS: A

PTS: 1

REF: EX 76

10. When creating an Excel document, you should avoid using _____ text and background together, since this is the most common form of color blindness. a. red and green c. black and white b. yellow and blue d. red and yellow ANS: A

PTS: 1

REF: EX 76

11. By default, numbers appear in the _____ and generally display values exactly as you type them. a. Home tab c. General number toolbar b. General format d. currency format ANS: B

PTS: 1

REF: EX 82

12. Because Excel stores dates and times as _____, you can apply different formats without affecting the date and time value. a. numbers c. universal numbers b. text d. Greenwich Mean Time ANS: A

PTS: 1

REF: EX 86

13. In Excel, dates are _____-aligned in the cell by default, regardless of date format. a. right c. center b. left d. top ANS: A

PTS: 1

REF: EX 87

14. As a general rule, you should _____. a. center column titles, left-align text, and right-align numbers b. center column titles, right-align text, and left-align numbers c. right-align column titles, left-align text, and right-align numbers d. left-align column titles, left-align text, and right-align numbers ANS: A

PTS: 1

REF: EX 87

15. _____ merges each of the rows in the selected range across the columns in the range. a. Merge build c. Merge Across b. Merge and Center d. Merge Cells ANS: C

PTS: 1

REF: EX 90

16. In Excel, to merge cells A1, B1, C1, D1, and E1, _____ is the correct reference for the merged cell. a. A1!E1! c. E1 b. A1 d. A1-E1 ANS: B

PTS: 1

REF: EX 90

17. Options to rotate cells in Excel are available using the _____ button in the Alignment group on the Home tab. a. Format c. Sorter b. Orientation d. Alignment ANS: B

PTS: 1

REF: EX 91

18. In the accompanying figure, the number 4 displays _____. a. alignment buttons b. indented text c. borders added above and below cell content d. centered text ANS: C

PTS: 1

REF: EX 89

19. In the accompanying figure, the number 3 displays _____. a. alignment buttons c. indented text b. centered text d. left-aligned text ANS: C

PTS: 1

REF: EX 89

20. In the accompanying figure, the number 1 displays _____. a. alignment buttons c. indented text b. centered text d. borders above and below cell content ANS: A

PTS: 1

REF: EX 89

21. The Format Cells dialog box has six tabs, including _____. a. Decimals c. Border b. Security d. Shading ANS: C

PTS: 1

REF: EX 93

22. In the Format Cells dialog box, the _____ tab allows you to format currency by choosing decimal places, comma separators, and regular number displays. a. Number c. Alignment b. Font d. Fill ANS: A

PTS: 1

REF: EX 92

23. You can open the Format Cells dialog box by right-clicking a cell or selected range, and then clicking _____ on the shortcut menu. a. Format c. Format Cells b. Cells d. Cells Style ANS: C

PTS: 1

REF: EX 93

24. When using the Format Painter, double-click the Format Painter button to paste the same format multiple times and click the Format Painter again to _____. a. turn it on c. copy formatting b. turn it off d. paste formatting ANS: B

PTS: 1

REF: EX 101

25. Put the following five steps in the order in which you would perform them to use the Paste Special function.

1. Select and copy a range.

2. Click the Paste button arrow in the Clipboard group on the HOME Tab. 3. Specify exactly what you want to paste.

4. Click Paste Special to open the dialog box.

5. Select the range where you want to paste the Clipboard contents. a. 1, 2, 4, 3, 5 c. 1, 5, 2, 4, 3 b. 1, 2, 4, 5, 3 d. 1, 5, 2, 3, 4 ANS: C

PTS: 1

REF: EX 104

26. _____ are an example of conditional formats in Excel. a. Data bars c. Font sets b. Quick Analysis tools d. Percentages ANS: A

PTS: 1

REF: EX 109

27. A _____ is the space between the page content and the edges of the page. a. margin c. frame b. border d. ruler ANS: A

PTS: 1

REF: EX 121

28. One way to ensure that you are using consistent formats is to copy and paste your formats using the Format _____. a. Master c. Palette b. Gallery d. Painter ANS: D

PTS: 1

REF: EX 101

29. In conditional formatting, to highlight only cells that contain a specific date, select the rule _____. a. A Date Occurring c. Text That Contains b. Duplicate Values d. Equal To

ANS: A

PTS: 1

REF: EX 109

30. Fonts, colors, and cell styles are organized in _____ categories. a. theme and non-theme c. color and black and white b. numbers and text d. form and function ANS: A

PTS: 1

REF: EX 106

31. Setting the _____ to A1:L10 will print only data in those cells. a. Page Format c. Page Break b. Print Area d. Print Preview ANS: B

PTS: 1

REF: EX 115

32. To avoid splitting a worksheet in awkward places, you can insert a _____ to specify a specific breaking point. a. page insert c. print area b. manual page break d. selection ANS: B

PTS: 1

REF: EX 116

33. To set a page break in Excel, select the _____. a. first cell below the row where you want to insert a page break b. first cell above the row where you want to insert a page break c. row above where you want to insert a page break d. row below where you want to insert a page break ANS: A

PTS: 1

34. A _____ indicates a manual page break. a. dashed blue line b. solid blue line ANS: B

PTS: 1

REF: EX 116

c. dashed red line d. solid red line REF: EX 116

35. Information that prints on each page, like a company name or logo, may be set as a _____. a. print setup c. text header b. copied text d. print title ANS: D

PTS: 1

REF: EX 117

36. The code _____ will display the filename of the current workbook. a. &name c. +name b. &[File] d. +[File] ANS: B

PTS: 1

REF: EX 120

37. The header and footer sections include a _____. a. left and right section c. top and bottom section b. center section only d. left, right, and center section ANS: D

PTS: 1

REF: EX 119

38. Header and footer elements such as worksheet name, current date, and time are _____ elements. a. descriptive c. impact b. dynamic d. required

ANS: B

PTS: 1

REF: EX 119

39. The Format Cells dialog box tab that provides options for locking or hiding cells to prevent other users from modifying their contents is _____. a. Alignment c. Protection b. Font d. Security ANS: C

PTS: 1

REF: EX 93

40. The _____ command searches through the current worksheet or workbook for the content or formatting you want to locate. a. Seek c. Locate b. Find d. Search ANS: B

PTS: 1

REF: EX 104

Case Based Critical-Thinking Questions Case 2-1 Harold has been working as a real estate agent. He has a lot of housing data to report and needs help organizing his data sheet for an effective presentation.

41. Harold has chosen green as the background color for his workbook. He should not use _____ for any of the font or highlight colors. a. red c. black b. white d. gray ANS: A

PTS: 1

REF: EX 76

TOP: Critical Thinking

42. In one of Harold’s columns there are too many numbers after the decimal place. Harold does not need this level of detail. It is distracting from the data and he really only needs two numbers after the decimal. How could Harold make the data appear cleaner in the most efficient way? a. reenter all numbers and only enter two decimal places b. decrease the column width c. decrease the font size d. use the Number group to decrease number of digits displayed ANS: D

PTS: 1

REF: EX 85

TOP: Critical Thinking

43. Harold cannot increase the width of his worksheet because, if he does, he will not be able to print it on one sheet. However, his columns are truncating his content. What solution would be the best for Harold to use to make all his text visible without losing data? a. abbreviate all text c. choose Wrap text for the columns b. change the font style d. delete one column ANS: C

PTS: 1

REF: EX 87

TOP: Critical Thinking

44. Harold would like to center a title across the top of his data. His data runs across five columns, from column A to column E. One way he could accomplish this is to _____. a. type the data in A1 and Merge cells A1:A5 b. type the data in A1 and Merge cells A1:E1 c. type the data in C1 d. type the data in any column in row 1 and then choose Center from the Format menu ANS: B

PTS: 1

REF: EX 90

TOP: Critical Thinking

45. Harold needs to format several cells with 11pt Calibri font, two decimal places, right-aligned, and a blue font color. The most efficient way to accomplish this would be to _____. a. use Format Painter b. format each element using the Toolbar c. select all cells and use the Formatting dialog box d. apply a table style ANS: A

PTS: 1

REF: EX 101

TOP: Critical Thinking

46. Harold wants to print ranges E6:F12 and E16:F22 of his worksheet. These are referred to as _____ cell ranges. a. selective c. consecutive b. nonadjacent d. specific ANS: B

PTS: 1

REF: EX 115

TOP: Critical Thinking

Case Based Critical Thinking Questions Case 2-2 Antoinette is part of a college department creating a number of student files with Microsoft Excel. She is working on a student retention project and wants to produce a worksheet that can alert faculty of student progress and current grade status.

47. Antoinette wants all her worksheets to have the same formatting for text, colors, and graphical effects applied to create a specific look and feel. Antoinette needs to apply a _____. a. theme c. style b. template d. font ANS: A

PTS: 1

REF: EX 70

TOP: Critical Thinking

48. Each worksheet will contain a specific class with the students’ grades for the entire term. In order to determine the current student grade, Antoinette would have to use the _____ function. a. SUM c. COUNT b. AVERAGE d. MEDIAN ANS: B

PTS: 1

REF: EX 98

TOP: Critical Thinking

49. Antoinette’s workbook has all the data entered and students’ current grades displayed. She would like the current grade cells to display the value in red if it is less than 60. Antoinette needs to add _____ to the cell. a. cell formatting c. highlighting b. conditional formatting d. fill color ANS: B

PTS: 1

REF: EX 108

TOP: Critical Thinking

50. When Antoinette shares her worksheet with other colleagues, she wants to ensure that everyone knows why certain cells are highlighted. To accomplish this, she will create a _____. a. journal c. legend b. documentation sheet d. formula sheet ANS: C

PTS: 1

REF: EX 112

TOP: Critical Thinking

51. Antoinette wants to be sure that her worksheet does not contain any duplicates. She will use the highlight cells rule _____ to check for this. a. Equal to c. Duplicate Values b. Between d. A Date Occurring

ANS: C

PTS: 1

REF: EX 109

TOP: Critical Thinking

52. Because Antoinette is familiar with conditional formatting, she can use the _____, which provides access to the most common tools for data analysis and formatting. a. QuickTool Bar c. Quick Formatting Bar b. Quick Analysis Tool d. Conditional Analysis Tool ANS: B

PTS: 1

REF: EX 110

TOP: Critical Thinking

COMPLETION 1. ____________________ is the process of changing a workbook’s appearance by defining the fonts, styles, colors, and decorative features.

ANS: Formatting PTS: 1

REF: EX 70

2. A(n) ____________________ is a collection of formatting that specifies the fonts, colors, and graphical effects used throughout the workbook.

ANS: theme PTS: 1

REF: EX 70

3. Ten ____________________ colors—dark red, red, orange, yellow, light green, green, light blue, blue, dark blue, and purple—are always available regardless of the workbook’s theme.

ANS: standard PTS: 1

REF: EX 73

4. Use the ____________________ button in the Alignment group on the Home tab to increase the size of the indention used in a cell.

ANS: Increase Indent PTS: 1

REF: EX 87

5. One way to align text over several columns or rows is to ____________________, or combine, several cells into one cell.

ANS: merge PTS: 1

REF: EX 90

6. The ____________________ tab in the Format Cells dialog box in Excel provides options for creating and applying background colors and patterns to cells.

ANS: Fill PTS: 1

REF: EX 93

7. The ____________________ tab in the Format Cells dialog box in Excel provides options for how data is aligned within a cell.

ANS: Alignment PTS: 1

REF: EX 92

8. The ____________________ copies the formatting from one cell or range to another cell or range without duplicating any of the data.

ANS: Format Painter PTS: 1

REF: EX 101

9. A(n) ____________________ is a selection of formatting options using a specific font and color from the current theme.

ANS: style PTS: 1

REF: EX 99

10. One of the benefits of conditional ____________________ is that it helps you analyze data by highlighting significant trends in data.

ANS: formatting PTS: 1

REF: EX 109

11. To remove conditional formatting in Excel, click ____________________ after clicking the Conditional Formatting button.

ANS: Clear Rules PTS: 1

REF: EX 112

12. A(n) ____________________ is a key that shows each color used in the worksheet and what it means.

ANS: legend PTS: 1

REF: EX 112

13. The region that is sent to the printer from the active sheet is known as the ____________________.

ANS: print area PTS: 1

REF: EX 115

14. A(n) ____________________ is text printed in the top margin of each page.

ANS: header PTS: 1

REF: EX 119

15. When you specify a print area for a nonadjacent range, you are also inserting a(n) ____________________ page break.

ANS: manual PTS: 1

REF: EX 116

ESSAY 1. Explain why it is important to use proper formatting, citing at least three benefits of a well-formatted workbook.

ANS: Proper formatting is a balance between too little and too much formatting, both of which can make data hard to understand. A well-formatted workbook helps accentuate important trends and relationships in data. A well-formatted workbook can (1) be easier to read, (2) establish a sense of professionalism, (3) help draw attention to the points you want to make, and (4) provide continuity between worksheets.

PTS: 1

REF: EX 70

TOP: Critical Thinking

2. Color allows you to effectively enhance a workbook, but could you ever use too much or “bad” color?

Explain how or when color might detract from a workbook. Give at least four tips for effective color use.

ANS: When used wisely, color can enhance any workbook. However, when used improperly, color can distract the user, making the workbook more difficult to read. As you format a workbook, keep in mind the following tips: Students should note at least four of the following tips: • Use colors from the same theme to maintain a consistent look and feel across the worksheets. If the built-in themes do not fit your needs, you can create a custom theme.

• Use colors to differentiate types of cell content and to direct users where to enter data. For example, format a worksheet so that formula results appear in cells without a fill color and users enter data in cells with a light gray fill color.

• Avoid color combinations that are difficult to read.

• Print the workbook on both color and black-and-white printers to ensure that the printed copy is readable in both versions.

• Understand your printer’s limitations and features. Colors that look good on your monitor might not look as good when printed.

• Be sensitive to your audience. About 8 percent of all men and 0.5 percent of all women have some type of color blindness and might not be able to see the text when certain color combinations are used.

Red-green color blindness is the most common, so avoid using red text on a green background or green text on a red background.

PTS: 1

REF: EX 76

TOP: Critical Thinking

3. What is conditional formatting and why would you apply it? Give two examples of conditional formats, and explain a method for applying conditional formatting.

ANS:

Conditional formatting applies formatting only when a cell’s value meets a specified condition. A conditional format could make negative numbers red and positive numbers black.

Excel has four conditional formats—data bars, highlighting, color scales, and icon sets.

Students should explain one of the following two methods for applying conditional formatting.

• Select the range or ranges to which you want to add data bars.

• In the Styles group on the Home tab, click the Conditional Formatting button, point to Data Bars, and then click a data bar color. or • Select the range in which you want to highlight cells that match a specified rule.

• In the Styles group, click the Conditional Formatting button, point to Highlight Cells Rules or Top/Bottom Rules, and then click the appropriate rule.

• Select the appropriate options in the dialog box, and then click the OK button.

PTS: 1

REF: EX 108

TOP: Critical Thinking

Excel Tutorial 3: Calculating Data with Formulas and Functions TRUE/FALSE 1. A constant is a value in a formula that doesn’t change.

ANS: T

PTS: 1

REF: EX 135

2. Optional arguments are always placed first in the argument list.

ANS: F

PTS: 1

REF: EX 141

3. One challenge of nesting functions is to make sure that you include all of the parentheses.

ANS: T

PTS: 1

REF: EX 142

4. Functions are organized in the Function Library group on the FORMULAS tab.

ANS: F

PTS: 1

REF: EX 145

5. A mixed reference “locks” one part of the cell reference while the other part can change.

ANS: T

PTS: 1

REF: EX 154 | EX 155

6. An error value begins with a pound sign (#) followed by an error name that indicates the type of error.

ANS: T

PTS: 1

REF: EX 150

7. By default, AutoFill copies only the content not the formatting of the original range to the selected range.

ANS: F

PTS: 1

REF: EX 163

8. To display the current date and time, which is updated each time the workbook is reopened, use the TODAY function.

ANS: F

PTS: 1

REF: EX 167

9. The IF function is a logical function that returns one value if a condition is true, and returns a different value if that condition is false.

ANS: T

PTS: 1

REF: EX 172

10. Lookup tables can be constructed for exact match or approximate match lookups.

ANS: T

PTS: 1

REF: EX 176

MODIFIED TRUE/FALSE 1. An argument can be any type of value including text, numbers, cell references, or even other formulas or functions. _______________

ANS: T

PTS: 1

REF: EX 141

2. An error value indicates that some part of a function is preventing Excel from returning a calculated value. ________________ ANS: F, formula PTS: 1

REF: EX 150

3. When a formula includes a cell reference, Excel interprets that cell reference as being located relative to the position of the current cell. _________________ ANS: T

PTS: 1

REF: EX 152

4. The following mixed reference in the formula, $A5, has an absolute column reference and a relative row reference. _________________________ ANS: T

PTS: 1

REF: EX 155

5. The Fill Formatting Only option, which is the default, copies both the content and the formatting. ______________ ANS: F, Copy Cells PTS: 1

REF: EX 163

6. The statement “A1 >= B1” tests whether the value in cell A1 is lesser than or equal to the value in cell B1. _________________________ ANS: F, greater PTS: 1

REF: EX 172

7. The WORKDAY function displays the date of the weekday a specific number of weekdays past a starting date.

ANS: T

PTS: 1

REF: EX 161

8. A lookup table organizes numbers or text into categories. _____________ ANS: T

PTS: 1

REF: EX 176

9. A trial-and-error method lets you explore the impact that changing input values has on the calculated values in the workbook. _________________________ ANS: F, what-if analysis PTS: 1

REF: EX 179

10. Goal Seek automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal. _________________________ ANS: T

PTS: 1

REF: EX 180

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. NOW f. Mon, Tues, Wed, Thurs, Fri b. error value g. F4 c. COUNTA h. absolute d. < i. nested e. MEDIAN j. mixed 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

A Statistical function that calculates the middle value of a collection of numbers A date and time function Used to cycle through reference types Function counts the number of nonblank cells in the specified range References that are fixed A comparison operator AutoFill pattern Indicate that some part of formula is preventing Excel from returning a calculated value.

A function inside another function Contains both relative and absolute references.

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: E ANS: A ANS: G ANS: C ANS: H ANS: D ANS: F ANS: B ANS: I ANS: J

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 141 EX 167 EX 156 EX 170 EX 154 EX 172 EX 165 EX 150 EX 142 EX 154

MULTIPLE CHOICE 1. The ____ category of Excel functions retrieves data from multidimensional databases involving online analytical processing. a. OLAP c. Logical b. Cube d. Text ANS: B

PTS: 1

REF: EX 140

2. The ____ category of Excel functions returns (true-false) values a. Information c. Logical b. Statistical d. Math & Trig ANS: C

PTS: 1

REF: EX 140

3. Which of the following is a Statistical function? a. INT c. ROUND b. RAND d. AVERAGE ANS: D

PTS: 1

REF: EX 141

4. Which of the following is a Math & Trig function? ____ a. INT c. MIN b. COUNT d. AVERAGE ANS: A

PTS: 1

REF: EX 141

5. =ROUND(AVERAGE(A1:A100),2) is an example of a(n) ____ function. a. complex c. absolute b. logical d. nested ANS: D

PTS: 1

REF: EX 142

6. After you describe a function in the Search for a function box in the Insert Function dialog box, click ____. a. Go c. Insert b. Select d. Next ANS: A

PTS: 1

REF: EX 145

7. The numbers, text, or cell references used by the function to return a value are called ____. a. expressions c. data b. arguments d. values ANS: B

PTS: 1

REF: EX 133

8. Referring to the figure above, the last entry shown in Column K is 644.7953813. There is a black “plus sign” on the lower right edge of the cell that is the ____. a. fill handle c. formula finder b. Auto Fill Options button d. function finder ANS: A

PTS: 1

REF: EX 163

9. Referring to the figure above, the last entry shown in Column K is 644.7953813. There is an icon in the cell underneath the 644.7953813 which is the ____. a. fill handle c. formula finder b. Auto Fill Options button d. function finder ANS: B

PTS: 1

REF: EX 163

10. Referring to the figure above, which number represents the COUNT function? a. 1 c. 3 b. 2 d. 4 ANS: B

PTS: 1

REF: EX 171

11. Referring to the figure above, which number refers difference between the scheduled and attended workouts. a. 1 c. 3 b. 2 d. 4 ANS: D

PTS: 1

REF: EX 171

12. Which of the following is not a comparison operator? ____ a. = c. << b. <= d. <> ANS: C

PTS: 1

REF: EX 172

13. In the following formula: =IF(A1="YES", "DONE", "RESTART"), what happens if A1= “NO”? a. The formula returns the text “RESTART”. b. The formula returns the text “DONE”. c. The formula returns the text “NO”. d. The formula returns the text “NO, RESTART”.

ANS: A

PTS: 1

REF: EX 173

14. Which of the following is not a date function? ____ a. YEAR c. AND b. NOW d. TODAY ANS: C

PTS: 1

REF: EX 167

15. The ____ function returns the maximum value in the range. a. TOP c. MAX b. FIRST d. MOST ANS: C

PTS: 1

REF: EX 173

16. One of the great advantages of ____ references is that you can quickly generate row and column totals without having to worry about revising the formulas as you copy them to new locations. a. cell c. absolute b. relative d. formula ANS: B

PTS: 1

REF: EX 152

17. Absolute references are marked with a ____. a. < b. : ANS: C

PTS: 1

c. $ d. !

REF: EX 154

18. When you copy a formula that contains an absolute reference to a new location, the reference ____. a. is updated automatically c. becomes bold b. does not change d. has a dotted outline in its cell ANS: B

PTS: 1

REF: EX 154

19. Use ____ references when you want different formulas to refer to the same cell. a. mixed c. absolute b. relative d. fixed ANS: C

PTS: 1

REF: EX 154

20. Another name for the middle value is the ____ value. a. mode c. median b. center d. relative ANS: C 21.

PTS: 1

REF: EX 147

____ arguments may provide more control over the returned value. a. Relative c. Optional b. Required d. Absolute ANS: C

PTS: 1

REF: EX 141

22. FUNCTION(argument1, argument2, ...) is ____. a. the syntax of writing optional arguments b. an example of a formula c. the general syntax of all functions d. the general syntax of all formulas ANS: C

PTS: 1

REF: EX 141

23. If an optional argument is not included, Excel assumes a(n) ____ value for it. a. default c. static b. generic d. absolute ANS: A

PTS: 1

REF: EX 141

24. Excel functions are organized into ____ categories. a. 8 c. 11 b. 10 d. 12 ANS: D

PTS: 1

REF: EX 140

25. Which of the following is not an Excel function category? ____ a. Math & Trig c. Statistical b. Algebra d. Text ANS: B

PTS: 1

REF: EX 140

26. Which function counts how many cells in a range contain numbers, where value1, value2, and so forth are text, numbers, or cell references? a. SUM c. COUNTA b. COUNT d. AUTOSUM ANS: B

PTS: 1

REF: EX 141

27. In “SUM(number1 [, number2, number3, ...])”, number 1, number 2, and number 3 may be numbers or ____. a. letters c. symbols b. cell references d. operators ANS: B

PTS: 1

REF: EX 141

28. Which category of Excel functions returns information about the format, location, or contents of worksheet cells? ____ a. Location c. Reference b. Information d. Description ANS: B

PTS: 1

REF: EX 140

29. In the formula =INT(AVERAGE(A1:A100)), which would occur first? ____ a. The average of the values in the range A1:A100 is added to the integer. b. The average of the integer value is calculated. c. The average of the values in the range A1:A100 is calculated. d. The integer portion of the value is extracted.

ANS: C

PTS: 1

REF: EX 142

30. When you nest functions, you should have ____. a. the equal sign b. the same number of left and right parentheses c. at least two functions d. the correct number of brackets ANS: B

PTS: 1

REF: EX 142

31. The Insert Function button is in the ____ group. a. Calculation c. Sort & Filter b. Function Library d. Home ANS: B

PTS: 1

REF: EX 132

32. The Insert Function dialog box has a(n) ____ option. a. search c. find and replace b. formatting d. expand/collapse ANS: A

PTS: 1

33. The fill handle copies ____.

REF: EX 145

a. content only b. formats only ANS: C

c. content and formats d. formats and cell references PTS: 1

REF: EX 162

34. Using the AutoFill Options button, the Fill Without Formatting option copies the ____ but not the formatting. a. functions c. cell references b. content d. left-aligns ANS: B

PTS: 1

REF: EX 163

35. Using AutoFill, what would be the extended series of the initial entry Jan? ___ a. uary c. Kan, Lan, Man, Nan b. Feb, Mar, Apr d. Jan1, Jan2, Jan3, ANS: B

PTS: 1

REF: EX 165

36. A logical function is a function that works with values that are either ____. a. right or wrong c. positive or negative b. absolute or relative d. true or false ANS: D

PTS: 1

REF: EX 172

37. In the formula =IF(A1=B1, C1, C2), the result will be C2 if ____. a. A is equal to B c. A1+B1 is equal to C1 b. A does not equal B d. A1>B1 is equal to C2 ANS: B

PTS: 1

REF: EX 173

38. ____ references are seldom used other than when creating tables of calculated values such as a multiplication table in which the values of the formula or function can be found in the initial rows and columns of the table. a. Absolute c. Mixed b. Relative d. Matched ANS: C

PTS: 1

REF: EX 159

39. =IF(G22= ”x ”, SUM(H22:J22), “ ”) with display ______ if G22 is not equal to “x”. a. a blank cell c. dashes b. the result of the SUM d. False ANS: A

PTS: 1

REF: EX 173

40. ______ functions find values in tables of data and insert them in another location in the worksheet such as cells or in formulas. a. Sum c. Lookup b. Average d. Find ANS: C

PTS: 1

REF: EX 176

41. If the VLOOKUP function cannot find the lookup value, the _________ error value is displayed in the cell. a. #VALUE c. #ERROR b. #N/A d. #NULL ANS: B

PTS: 1

REF: EX 177

42. One way to perform a _________ is by changing one or more of the input values to see how they affect the calculated results. a. goal seek c. search b. gunction d. what-if analysis ANS: D

PTS: 1

REF: EX 179

Case-Based Critical Thinking Questions Case 3-1 Ellen is a recent college graduate. She has decided to open a T-Shirt business and would like to create budget worksheet using Microsoft Excel 2013. She need to keep track of her expenses and income for the year.

43. Ellen opens Excel 2013 and enters her monthly income for one year beginning with January. To create a series of months, she can ____. a. Type = Jan-Dec in cell A1 and then press Enter. b. Type January in cell A1 and then drag the filll handle. c. Type =YEAR in cell A1 and then press Enter. d. Type =DATE(Jan, Dec) in cell A1 and then press Enter.

ANS: B

PTS: 1

REF: EX 165

TOP: Critical Thinking

44. Ellen has entered all of her monthly expenses. She needs to create a formula that will deduct her monthly expenses from her monthly income. Since her monthly income is the same amount each month, she uses a(n)____ to identify the cell containing the income value. a. relative reference c. mixed reference b. absolute reference d. fixed reference ANS: B

PTS: 1

REF: EX 154

TOP: Critical Thinking

45. Ellen’s spending varies from month to month. She wants to calculate the total expenses for each month. She wants to see all of the arguments associated with the SUM function so she clicks the ____ then clicks SUM to open the Function Arguments dialog box. a. Math & Trig button in the Formula group b. Math & Trig button in the Function Library group c. Financial button in the Formual group d. Financial button in the Function Library group ANS: B

PTS: 1

REF: EX 141

TOP: Critical Thinking

46. Ellen wants to find out what her median monthly spending is. The monthly totals for her spending appear in the range A10:L10. Which of the following is NOT a way to enter the MEDIAN function into her database? a. Type =MEDIAN(A10:L10) into an empty cell. b. Select the range A10:L10, click the Quick Analysis button, click TOTALS, and then click Median. c. Click the More Functions button in the Function Library group on the FORMULAS tab, point to Statistical, and then click MEDIAN. d. Click the Insert Function button in the Function Library group on the FORMULAS tab, scroll the Select a function list, then click MEDIAN. ANS: B

PTS: 1

REF: EX 145

TOP: Critical Thinking

47. Ellen is expecting to get a 3% raise next year. She enters .03 in cell D2 so that she can predict her monthly income for the next year. What function key will make D2 an absolute reference? a. F2 c. F6 b. F4 d. F1 ANS: B

PTS: 1

REF: EX 154

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 3-2 Susan is in charge of the inventory reports for her company. She will need to find specific inventory items as well as project increases in inventory costs. To get ready she will create a worksheet and use the Microsoft Excel 2013 Analysis tools.

48. Susan designs a worksheet with the inventory number, description, quantity, unit cost, reorder point and value. To find values in tables of data, and insert them in another location in the worksheet such as cells or in formulas, she will use ________. a. Max function c. CountA function b. Lookup function d. Sum function ANS: B

PTS: 1

REF: EX 176

TOP: Critical Thinking

49. Susan needs to use a lookup function to find an exact match of an inventory item, so she will set the range_lookup value to _______ . a. EXACT b. 1 c. TRUE d. FALSE ANS: D

PTS: 1

REF: EX 177

TOP: Critical Thinking

50. Susan uses VLOOKUP to retrieve the return value from a vertical lookup table. When she enters the VLOOKUP function, specifying the lookup value and other criteria, the lookup value cannot be found.

It displays ______ error value in the cell. a. ##### c. #N/A b. #Value d. Value not found ANS: C

PTS: 1

REF: EX 177

TOP: Critical Thinking

51. Susan wants to make some changes to the worksheet to explore the impact that changing input values has on the calculated values in the worksheet. She can use ______. a. Lookup Analysis c. IF conditions b. What-If Analysis d. nested OR conditions ANS: B

PTS: 1

REF: EX 179

TOP: Critical Thinking

52. Susan wants to automate the trial-and-error process by allowing her to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal. She can use ______. a. Data Validation c. Goal Set b. Goal Seek d. If Conditional ANS: B COMPLETION

PTS: 1

REF: EX 180

TOP: Critical Thinking

1. Some cell references are called ____________________ references because they are always interpreted in relation to the location of the cell containing the formula.

ANS: relative PTS: 1

REF: EX 133

2. ____________________ is a set of rules which specifies how a function should be written.

ANS: Syntax PTS: 1

REF: EX 141

3. The ____________________ organizes all of the functions by category and allows you to search for functions that perform particular calculations.

ANS: Insert Function dialog box PTS: 1

REF: EX 145

4. A(n) ____________________ function is a function that works with statements that are either true or false.

ANS: logical PTS: 1

REF: EX 161

5. The ____________________ is the middle value from a sample.

ANS: median PTS: 1

REF: EX 132

6. TODAY is an example of a(n) ____________________ function.

ANS: date PTS: 1

REF: EX 167

7. A(n) ____________________ reference contains both relative and absolute references.

ANS: mixed PTS: 1

REF: EX 154

8. Not all functions have arguments, and some functions have ____________________ arguments, which are not required for the function to return a value, but can be included to provide more control over the returned value.

ANS: optional PTS: 1

REF: EX 133

9. ____________________ is an Excel function category that returns information about the format, location, or contents of worksheet cells.

ANS: Information PTS: 1

REF: EX 140

10. Functions can also be ____________________ or placed inside another function.

ANS: nested PTS: 1

REF: EX 142

11. The small black plus sign in the lower-right corner of a selected cell or range is called the ____________________.

ANS: fill handle PTS: 1

REF: EX 162

12. A(n) ____________________ operator is a symbol that indicates the relationship between two values.

ANS: comparison PTS: 1

REF: EX 172

13. ____________________ automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal.

ANS: Goal Seek PTS: 1

REF: EX 180

14. The ____________________ function is a logical function that returns one value if the statement is true and returns a different value if the statement is false.

ANS: IF PTS: 1

REF: EX 172

15. An ___________________ occurs when the lookup value falls within a range of numbers in the first column of the lookup table.

ANS: approximate match lookup PTS: 1

REF: EX 176

ESSAY 1. Explain what a logical function is and give an example of when you would use one in Excel 2013.

ANS:

A logical function is a function that works with values that are either true or false. In Excel you can use logical functions to compare the contents of two cells or comparison operators to determine if the value in a cell is greater, equal to, or lesser than another value. The IF function is a logical function.

The IF function returns one value if the statement is true and returns a different value if the statement is false. One use of a logical function in Excel would be if you wanted to figure out if your employees should receive a yearly bonus. In an Excel spreadsheet you would have columns for each employees monthly sales figures and a cell containing their total sales figure for the year. You could create an IF function that determined if their annual sales exceeded a specific value or not. If “yes”, the employee receives a bonus, if “no”, the employee does not receive a bonus. (This example with vary.)

PTS: 1

REF: EX 172

TOP: Critical Thinking

2. Why is it that the TODAY and NOW date functions do not have any arguments?

ANS: The TODAY and NOW date functions do not have arguments because they both represent the current date and current time. There can be no other values to replace them. The values returned by the TODAY and NOW functions are updated automatically whenever you reopen the workbook or enter a new calculation. If you don’t want the date and time to change, you must enter the date and time value directly in the cell.

PTS: 1

REF: EX 167

TOP: Critical Thinking

3. What is the relationship between the Function Arguments dialog box and the Insert Function dialog box?

ANS: The relationship between the Function Arguments dialog box and the Insert Function dialog box is in how and why the two dialog boxes are used. The Insert Function dialog box is used when you wish to look through categories of functions to choose one for your formula and when you are unsure of which one to use at all. Select the cell in which you wish to enter a formula, then click the Insert Function button in the Function Library group. In this dialog box, you can search for a particular function by typing in some related keywords, then clicking Go, or choose from a category list. Once you select a function, you are then brought to the Function Arguments dialog box. This dialog box lists all of the arguments associated with a specific function. You can also access the Function Arguments dialog box by selecting a specific function from any category in the Function Library group on the Formulas tab.

Typically, you will bypass the Insert Function dialog box when you already know which function you want to use in your formula.

PTS: 1

REF: EX 145 | EX 146

TOP: Critical Thinking

Excel Tutorial 4: Analyzing and Charting Financial Data TRUE/FALSE 1. Each slice in a pie chart represents a single value from a data series.

ANS: T

PTS: 1

REF: EX 200

2. Excel charts are either placed in their own chart sheets or embedded in a worksheet.

ANS: T

PTS: 1

REF: EX 202

3. Every chart contains elements that can be formatted, added to the chart, or removed from the chart.

ANS: T

PTS: 1

REF: EX 203

4. In pie charts, it’s best to make the slice colors as similar as possible.

ANS: F

PTS: 1

REF: EX 208

5. A column chart turned on its side is called a bar chart, with the length of the bar determined by the data value.

ANS: T

PTS: 1

REF: EX 211

6. You can move a chart from one worksheet to another, or you can place the chart in its own chart sheet.

ANS: T

PTS: 1

REF: EX 214

7. Sparklines differ from data bars in that the sparklines are always placed in the cells containing the value they represent.

ANS: F

PTS: 1

REF: EX 242

8. Line charts are best suited for representing data that follows some nonsequential order.

ANS: F

PTS: 1

REF: EX 220

9. Interest is the amount of money being loaned.

ANS: F

PTS: 1

REF: EX 192

10. The PMT function calculates the payment required in each period to pay back the loan.

ANS: T

PTS: 1

REF: EX 193

MODIFIED TRUE/FALSE 1. Interest is the amount added to the principal by the lender. _________________________ ANS: T

PTS: 1

REF: EX 192

2. A color bar is a conditional format that adds a horizontal bar to the background of a cell containing a numeric value. _________________________ ANS: F, data PTS: 1

REF: EX 242

3. Pie charts are most effective with ten or fewer slices. _________________________ ANS: F 6 six PTS: 1

REF: EX 200

4. By default, Excel inserts a chart as an embedded chart. _________________________ ANS: T

PTS: 1

REF: EX 202

5. You can quickly change the appearance of the entire chart by selecting a different chart style in the Chart Design gallery. _________________________ ANS: F, Styles PTS: 1

REF: EX 204

6. When you create sparklines, clicking any cell in the data range selects all of the sparklines in the group. _________________________ ANS: F, location PTS: 1

REF: EX 240

7. The cell with the largest value contains a data marker that extends across the entire width of the cell, and the lengths of the other bars in the selected range are determined relative to that bar. _________________________ ANS: F, data bar PTS: 1

REF: EX 243

8. In a line chart, the data markers are the points connected by the line. _________________________ ANS: T

PTS: 1

REF: EX 225

9. A watermark can be inserted into the header or footer of a worksheet. _________________________ ANS: T

PTS: 1

REF: EX 245

10. Combination charts enable you to show three sets of data using the chart type that is best for each data set. _________________________ ANS: F, two

PTS: 1

REF: EX 230

MATCHING Identify the letter of the chart type that best matches the phrase or definition. a. Scatter f. Pie b. Column g. Stock c. XY h. Bar d. Line i. Surface e. Radar j. Area 1. Compares values from different categories. A value is indicated by the height of each ____.

2. Often used to show trends and changes over time 3. Compares relative values of different categories to the whole. A value is indicated by the area of a(n) ____ 4. Compares values from different categories. A value is indicated by the length of the ____ 5. Compares values from different categories. Areas under the lines contain a fill color 6. Similar to a Scatter chart 7. Compares a collection of values from several different data sets.

8. Compares three sets of values in a three-dimensional chart 9. Displays stock market data 10. Shows patterns or relationship between two or more sets of values. Often used in scientific studies and statistical analyses.

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: B ANS: D ANS: F ANS: H ANS: J ANS: C ANS: E ANS: I ANS: G ANS: A

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 199 EX 199 EX 199 EX 199 EX 199 EX 199 EX 199 EX 199 EX 199 EX 199

MULTIPLE CHOICE 1. A ________ contains the actual values that are plotted on the chart a. data source b. data series c. category values d. legend ANS: B

PTS: 1

REF: EX 198

2. Category values are usually located in the ____ of the data source. a. first row c. either a or b b. first column d. neither a nor b ANS: C

PTS: 1

REF: EX 198

3. Referring to the figure above, if you were to create a Wine Production Chart pie chart using the information in B28:C34, the data source for that chart would be ____. a. B28:C34 c. B28:B34 b. B29:C33 d. B29:C34 ANS: D

PTS: 1

REF: EX 201

4. In the figure above, the biggest slice is the cases of _______. a. Riesling c. Pinot Grigio b. Chardonnay d. Ruby Cabernet ANS: B

PTS: 1

REF: EX 201

5. In the lower right corner, below C34, the _____ tool displays. a. Screen Tip c. What-If b. Quick Analysis d. Quick Chart ANS: B

PTS: 1

REF: EX 200

6. The ____ is the only one of the following charts to compare relative values from different categories to the whole. a. pie c. line b. column d. area ANS: A

PTS: 1

REF: EX 199

7. To add and remove chart elements, you can use the Add Chart Element button in the Charts Layout group on the ____ tab. a. CHART TOOLS LAYOUT c. INSERT

b. CHART TOOLS DESIGN ANS: B

PTS: 1

d. HOME REF: EX 203

8. The ____ CHART TOOLS contextual tabs appear on the Ribbon when you select a chart. a. INSERT and DESIGN c. INSERT and FORMAT b. DESIGN and FORMAT d. FORMAT and PRINT ANS: B

PTS: 1

REF: EX 202

9. The ____ dialog box provides options for moving charts between worksheets and chart sheets. a. Insert New c. Move Chart b. Tab d. Change Chart ANS: C

PTS: 1

REF: EX 214

10. Placing a chart in a ____ displays only the chart and no worksheet cells. a. new worksheet c. graph display b. tabbed view d. chart sheet ANS: D

PTS: 1

REF: EX 214

11. Excel supports all of the following types of sparklines EXCEPT ____. a. A win/loss sparkline for highlighting positive and negative values b. A column sparkline used for column charts c. A line sparkline for highlighting trends d. a true/false sparkline for help making decisions ANS: D

PTS: 1

REF: EX 238

12. To resize an embedded chart, ____. a. select the chart and drag the corner sizing handle of the selection box b. right click the chart and then click Resize on the shortcut menu c. select the chart and press Tab d. both a and b ANS: A

PTS: 1

REF: EX 202

13. The columns and pie slices in the charts above are ____. a. data markers c. major tick marks b. chart areas d. minor tick marks

ANS: A

PTS: 1

REF: EX 225

14. Referring to the figure above, the rectangular area to the right of the pie chart is the ____. a. perspective c. legend b. plot area d. data marker ANS: C

PTS: 1

REF: EX 204

15. Referring to the figure above, in the column chart, the values along the left side ($200,000, $400,000, $600,000, etc.) are ____. a. plot markers c. data markers b. plot marks d. axis labels ANS: D

PTS: 1

REF: EX 222

16. Referring to the figure above, the entry 31.5%, shown next to the largest pie slice, is a ____. a. data label c. legend b. chart key d. series value ANS: A

PTS: 1

REF: EX 226

17. Referring to the figure above, the chart title for the pie chart is ____. a. Estimated Production c. Total Revenue b. Projected Revenue and Expenses d. Production ANS: A

PTS: 1

REF: EX 215

18. To reposition the legend to the left-hand side of a chart, click the _________ button, point to Legend, click the right arrow icon, and click Left. a. Legend c. Formatting b. Chart Elements d. Layout ANS: B

PTS: 1

REF: EX 205

19. ____provide descriptive text for the individual data markers, such as pie slices a. Asset labels c. Data labels b. Legend values d. Leader lines ANS: C

PTS: 1

REF: EX 203

20. A pie slice is a data marker that represents ____ from a data series. a. only one value c. no more than three values b. at least one value d. no more than five values ANS: A

PTS: 1

REF: EX 208

21. To move one slice of a pie chart away from the pie creates a(n) ____. a. spontaneous pie chart c. exploded pie chart b. perfect pie chart d. exponential pie chart ANS: C

PTS: 1

REF: EX 209

22. You already created a line chart, but now decide that you want to change this chart to a pie chart. To do this, you click ____ from the Type group in the Chart Tools Design tab. a. Change Chart Type c. Alter Existing Chart b. New Chart d. Modify Chart

ANS: A

PTS: 1

REF: EX 214

23. A chart that compares three set of values in a three-dimensional chart is _____. a. Stock c. Surface b. Radar d. Scatter ANS: C

PTS: 1

REF: EX 199

24. Which of the following shows the correct order of steps for adding a data series to a chart? a. Select the chart, Click the Add button, Click the Select Data button, Select the range b. Select the chart, Click the Select Data button, Click the Add button, Select the range c. Select the range, Click the Select Data button, Click the Add button, Select the chart d. Select the range, Select the chart, Click the Select Data button, Click the Add button ANS: B

PTS: 1

REF: EX 235

25. Edward Tufte was concerned with what he termed as “chart ____,” in which a proliferation of chart elements—chosen because they look “nice”—confuse and distract the reader. a. espionage c. confusion b. clutter d. junk ANS: D

PTS: 1

REF: EX 245

26. Edward Tufte said that ____ ink is any part of the chart that does not convey information about the data. a. data c. information b. non-data d. non-information ANS: B

PTS: 1

27. A chart sheet can contain ____. a. only one chart b. one or two charts ANS: A

PTS: 1

REF: EX 245

c. one to four charts d. one to six charts REF: EX 214

28. Which of the following represents the pointer when it is over the chart area? a. c. b.

ANS: C

d.

PTS: 1

REF: EX 202

29. You can choose Center, Inside End, Outside End, or Best Fit for the data label positions in the in the ____ pane. a. Format Data Labels c. Format Chart Area b. Format Legend d. Format Axis ANS: A

PTS: 1

REF: EX 207 - EX 208

30. The range of values or _______, of an axis is based on the values in the data source. a. scale b. defined range c. series d. length

ANS: A

PTS: 1

REF: EX 222

31. To format an individual sparkline, select the sparkline you want to format, and then click the Ungroup button in the Group group on the SPARKLINE TOOLS ____ tab. a. CREATE c. LAYOUT b. VIEW d. DESIGN ANS: D

PTS: 1

REF: EX 241

32. When combination charts have data series with vastly different values, you can create ____ charts. a. dual axis b. secondary c. multivalued d. multiple axis ANS: A

PTS: 1

REF: EX 230

33. The ____ identifies which data markers are associated with each data series. a. x-axis c. chart area b. y-axis d. legend ANS: D

PTS: 1

REF: EX 191

34. ____ is the amount added to the prinicpal of a loan by the lender. a. Rate c. Interest b. Simple d. Maximum ANS: C

PTS: 1

35. The FV function stands for ____. a. function value b. future value ANS: B

PTS: 1

REF: EX 192

c. financial value d. formulated value REF: EX 192

36. The amount of money being loaned is known as the ____. a. mortgage c. loan amount b. principal d. proposed principal ANS: B

PTS: 1

REF: EX 192

PMT(rate, nper, pv [, fv=0] [, type=0]) 37. In the figure above, nper stands for the ____. a. total number of payment periods b. total number of payments that have already been made c. number of payments per year d. number of interest payments per year ANS: A

PTS: 1

38. In the figure above, pv stands for the ____. a. payments verified b. principal value of the loan ANS: C

PTS: 1

REF: EX 193

c. present value of the loan d. primary interest rate of the loan REF: EX 193

39. In the figure above, fv and type are ____. a. financial functions b. required arguments ANS: D

PTS: 1

c. comparison operators d. optional arguments REF: EX 193

40. The possible values for the type argument in the function above are. a. 0 or null c. 0, 1, or 2 b. 0 or 1 d. 0, 1, 2, or 3 ANS: B

PTS: 1

REF: EX 193

41. The _______ displays the data series in separate columns side-by-side so that you can compare the relative heights of the columns in the three series. a. clustered column chart c. 100% stacked column chart b. stacked column chart d. column chart ANS: A

PTS: 1

REF: EX 212

42. Generally, _____ are given a “washed-out” appearance and are placed behind text or charts on the sheet so that they do not obscure any of the other content on the sheet. a. charts c. graphics b. watermarks d. pictures ANS: B

PTS: 1

REF: EX 245

Critical-Thinking Case Based Questions Case 4-1 Simon has just started a job with the police department. His director has given him the last six months of test results from four local precincts. Simon has organized the results as four data series and needs to display the trends in a graphic format.

43. Simon’s boss wants him to compare the values in this chart with several different data sets. Simon should prepare a(n) ____ for his boss. a. Radar chart c. Pie chart b. Line graph d. XY scatter chart ANS: A

PTS: 1

REF: EX 199

TOP: Critical Thinking

44. It’s difficult to read the exact scale on some of Simon’s data. Adding ____ would make it easier to read. a. a legend c. colors b. major tick marks d. more numbers ANS: B

PTS: 1

REF: EX 222

TOP: Critical Thinking

45. Simon has finished his chart but needs to save some space. What’s the most effective way for Andy to reduce space without losing important data? a. Delete parts of the data c. Delete the title b. Delete the legend d. Overlay the legend in areas without data ANS: D

PTS: 1

REF: EX 229

TOP: Critical Thinking

46. Simon wants to change the chart’s border. To do this, he should select the CHART TOOLS ____ tab.

a. FORMAT b. EDIT ANS: A

c. DESIGN d. LAYOUT PTS: 1

REF: EX 202

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 4-2 Mya has just surveyed 100 students to get their reaction to a new textbook she is reviewing. She has one data series that she wants to represent graphically, and she needs to display her data in a way that lets her visually compate the data values and see how much contributes to the whole.

47. Since it’s important for Mya to keep percentages of her whole reviewer base in mind, she wants to represent data as parts of a whole. What chart type would you suggest to Mya? a. bar c. scatter b. column d. pie ANS: D

PTS: 1

REF: EX 199

TOP: Critical Thinking

48. Since Mya is still reviewing her data, she wants to make sure that her chart reflects any changes she makes in her data source. She should ____. a. paste her chart in as a picture b. do nothing since charts in Excel remain linked to data sources c. manually adjust data in a chart d. create a new chart each time she modifies her data ANS: B

PTS: 1

REF: EX 210

TOP: Critical Thinking

49. Mya has decided she needs to move her chart so that it appears below her data source. Before she can move or resize her chart she must ______. a. select the chart b. hold down the SHIFT key c. adjust the sizing handles d. move the mouse pointer to the center of the chart.

ANS: A

PTS: 1

REF: EX 202

TOP: Critical Thinking

50. Sometimes Mya might want to see information on only specific slices. Rather than creating a new chart that includes only those selections, she can _____ an existing chart. a. search c. find b. filter d. examine ANS: B

PTS: 1

REF: EX 210

TOP: Critical Thinking

51. Mya has asked for your advice. A colleague told her to remove the legend from her pie chart because it takes up too much space. What would be a better piece of advice to give Mya? a. Resize the legend to be as tiny as possible. b. Remove data from the legend. c. Overlay the legend on a part of the chart that’s empty. d. You agree with the colleague; Mya should remove the legend.

ANS: C

PTS: 1

REF: EX 229

TOP: Critical Thinking

52. Mya needs to call attention to one category above all others. For this purpose, she should use a(n) ____. a. line chart c. scatter chart

b. exploded pie chart ANS: B

PTS: 1

d. banded chart REF: EX 209

TOP: Critical Thinking

COMPLETION 1. Excel supports ____________________ types of charts into 10 categories..

ANS: 53 fifty three fifty-three PTS: 1

REF: EX 199

2. In cases where a label placed outside a pie might appear farther from its slice than is easily readable, a(n) ____________________ might be added to connect the data to its corresponding slices.

ANS: leader line PTS: 1

REF: EX 207

3. The range of values, or ___________________, of an axis is based on the values in the data source.

ANS: scale PTS: 1

REF: EX 222

4. You can add ___________________ to identify smaller intervals between the major tick marks.

ANS: minor tick marks PTS: 1

REF: EX 222

5. To compare values from several categories with a sequential order, such as dates and times occurring at evenly spaced intervals, you would use a(n) ____________________ chart.

ANS: line PTS: 1

REF: EX 220

6. A chart that combines two or more chart types into a single graph is called a(n) ____________________ chart.

ANS: combination PTS: 1

REF: EX 219

7. Since scientific data is often plotting two numeric values against one another, it is oftentimes best to represent this data type with a(n) ____________________ chart.

ANS: XY scatter

X-Y scatter X Y scatter PTS: 1

REF: EX 199

8. Each data source is a collection of ____________________, which is a range of values plotted as a single unit on a chart.

ANS: data series PTS: 1

REF: EX 198

9. A(n) ____________________ chart shows patterns or relationships between two or more sets of values, much like an XY chart, except the size of the data marker is determined by a third value.

ANS: Bubble PTS: 1

REF: EX 199

10. A ____________ displays data values as columns with the height of each column based on the data value.

ANS: column chart PTS: 1

REF: EX 211

11. ______ are horizontal and vertical lines that help you compare data and category values.

ANS: Gridlines PTS: 1

REF: EX 219

12. Each value from a data series is represented by a ________.

ANS: data marker PTS: 1

REF: EX 225

13. The _______ covers the entire background of the chart, whereas the plot area includes only that portion of the chart in which the data markers, such as the columns in a column chart, have been placed or plotted.

ANS: chart area PTS: 1

REF: EX 227

14. An _______ chart takes up less space than a chart sheet.

ANS: embedded PTS: 1

REF: EX 229

15. The goal of a ______ is to convey the maximum amount of information within a very small space.

ANS: sparkline PTS: 1

REF: EX 238

ESSAY 1. Consider the impact of a poorly designed chart. Explain why it is important to have a well-designed chart, and name at least four tips for creating a good chart.

ANS: Answers will vary. Students should cover some of the following major points.

A well-designed chart can illuminate facts that might be hidden by viewing only numbers. However, a poorly designed chart can mislead readers, misrepresent data, and make it more difficult to interpret data.

To create effective and useful charts, keep in mind the following tips as you design charts: • Keep it simple. Do not clutter a chart with too many graphical elements. Focus attention on the data rather than on decorative elements that do not inform.

• Focus on the message. Design the chart to highlight the points you want to convey to readers.

• Limit the number of data series. Most charts should display no more than four or five data series. Pie charts should have no more than six slices.

• Choose colors carefully. Display different data series in contrasting colors to make it easier to distinguish one series from another. Modify the default colors as needed to make them distinct on the screen and in the printed copy.

• Limit your chart to a few text styles. Use a maximum of two or three different text styles in the same chart. Having too many text styles in one chart can distract attention from the data.

The goal of written communication is always to inform the reader in the simplest, most accurate, and most direct way possible. When creating worksheets and charts, everything in the workbook should be directed toward that end PTS: 1

REF: EX 217

TOP: Critical Thinking

2. When selecting a chart, it’s important to choose the right type of chart to represent your data. If choosing between a pie chart and a line chart, what factors would you evaluate in choosing which chart to use? How would each chart differ in representing your data?

ANS: Answers will vary. Students should evidence critical thinking by including some of the following points.

Pie charts should generally be used only when the number of categories is small and the relative sizes of the different slices can be easily determined. Pie charts compare relative value from different categories to the whole. Values are indicated by the size of the pie slice.

Line charts are best for categories that follow a sequential order. However, line charts will distort data that occurs at irregular time intervals, making data appear to have occurred regularly. Line charts also show trends over time. Values are indicated by the height of the line.

Pie charts and line charts assume that numbers are plotted against categories.

PTS: 1

REF: EX 200 | EX 220

TOP: Critical Thinking

3. Describe the some of the concepts and definitions associated with loans before using a PMT function.

ANS: The cost of a loan to the borrower is largely based on three factors —the principal, the interest, and the time required to repay the loan. Principal is the amount of money being loaned. Interest is the amount added to the principal by the lender. You can think of interest as a kind of “user fee ” because the borrower is paying for the right to use the lender ’s money for an interval of time. Generally, interest is expressed at an annual percentage rate, or APR. PTS: 1

REF: EX 192

TOP: Critical Thinking

Excel Tutorial 5: Working with Excel Tables, PivotTables, and PivotCharts TRUE/FALSE 1. If you freeze the top row in a worksheet and press Ctrl+Home, you will return to the cell A1.

ANS: T

PTS: 1

REF: EX 263

2. You can create at most one Excel table in a worksheet.

ANS: F

PTS: 1

REF: EX 264

3. When you create a table, if your data does not contain column headers, Excel adds headers with default names.

ANS: T

PTS: 1

REF: EX 264

4. You can use a table’s sizing handle to add columns or rows to a table.

ANS: T

PTS: 1

REF: EX 268

5. Before you complete a sort, you need to decide whether you want to put the list in ascending or descending order.

ANS: T

PTS: 1

REF: EX 271

6. When you delete a record from a table, a dialog box displays to confirm the delete operation.

ANS: F

PTS: 1

REF: EX 270

7. An easy way to sort data when there is only one sort field is to use the Sort A to Z or Sort Z to A buttons.

ANS: T

PTS: 1

REF: EX 271

8. Records have been filtered in the list shown in the accompanying figure.

ANS: T

PTS: 1

REF: EX 279

9. Excel can summarize data in a range of data by inserting subtotals.

ANS: T

PTS: 1

REF: EX 292

10. You can drag one field to the FILTERS area of the PivotTable Fields pane to change what values are displayed in the PivotTable.

ANS: F

PTS: 1

REF: EX 299

MODIFIED TRUE/FALSE 1. Each column represents a record that describes some attribute or characteristic of a person, place, or thing. ____________________ ANS: F, field PTS: 1

REF: EX 259

2. Table names cannot include underscores. ____________________ ANS: F, spaces PTS: 1

REF: EX 266

3. Ascending order arranges labels alphabetically from A to Z. ____________________

ANS: T

PTS: 1

REF: EX 271

4. The first sort field in a sort is called the major sort field. ____________________ ANS: F, primary PTS: 1

REF: EX 272

5. To create a custom list, in the Sort & Filter group on the HOME tab, click the Sort button. Click the Order arrow, and then click Custom List. ____________________ ANS: F, DATA PTS: 1

REF: EX 274

6. If a column consists of day or month labels, you can sort them in their correct chronological order using one of the predefined custom lists. ____________________ ANS: T

PTS: 1

REF: EX 274

7. Custom filters enable you to specify various conditions in addition to those that are based on an “equals” criterion. ____________________ ANS: F, Criteria PTS: 1

REF: EX 282

8. You can calculate sort statistics (including sum, average, count, maximum, and minimum) on all the columns in an Excel table. ____________________ ANS: F, summary PTS: 1

REF: EX 287

9. The Subtotal command inserts a subtotal row into the range for each group of data and adds a grand total row below the last row of data. ____________________ ANS: T

PTS: 1

REF: EX 292

10. When you create a PivotTable, you need to specify where to find the data for the PivotTable. ____________________ ANS: T

PTS: 1

REF: EX 301

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. custom list f. data definition table b. field g. filter c. record h. PivotTable d. freeze i. field name e. criteria filter j. sort

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

A collection of related fields that are grouped together To keep headings visible as you work with the data in a large worksheet Documentation that lists the fields to be maintained for each record A column within an Excel range Used to group and summarize a list into a concise, tabular format To temporarily hide all the records that do not meet your criteria Indicates the sequence in which you want data ordered Enables you to specify various conditions in addition to those that are based on an “equals” criterion A unique label that describes the contents of data in a column To rearrange records in a table or range based on the data in one or more fields

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: C ANS: D ANS: F ANS: B ANS: H ANS: G ANS: A ANS: E ANS: I ANS: J

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 260 EX 262 EX 261 EX 260 EX 298 EX 278 EX 274 EX 282 EX 259 EX 258

MULTIPLE CHOICE 1. In Excel, a collection of similar data can be structured in a(n) ____ of rows and columns. a. index c. list b. glossary d. range ANS: D

PTS: 1

REF: EX 260

2. A data ____ table describes the fields you plan to maintain in a table. a. structure c. map b. definition d. linked ANS: B

PTS: 1

REF: EX 261

3. ____ rows and columns lets you keep headings on the screen as you work with the data in a large worksheet. a. Hiding c. Showing b. Freezing d. Sealing ANS: B

PTS: 1

REF: EX 262

4. In Excel, a table is a range of related data that is managed independently from the data in other ____ in the worksheet. a. rows and headers c. rows and columns b. grids and columns d. footers and grids ANS: C

PTS: 1

REF: EX 259

5. When you designate a range as a(n) ____, Excel provides the same operations as are available for a structured range of data.

a. table b. catalogue ANS: A

c. index d. roster PTS: 1

REF: EX 264

6. To create a table, click the ____ tab on the Ribbon and then, in the Tables group, click the Table button. a. DESIGN c. INSERT b. HOME d. VIEW ANS: C

PTS: 1

REF: EX 264

7. Excel assigns the name ____ to the first Excel table created in a workbook. a. 1 c. NewTable b. 1Table d. Table1 ANS: D

PTS: 1

REF: EX 266

8. Which of the following is a valid table name? a. %_table c. _Newtable b. $Table2 d. 2Table ANS: C

PTS: 1

9. A table name cannot include ____. a. numbers b. spaces ANS: B

PTS: 1

REF: EX 266

c. underscores d. letters REF: EX 266

10. ____ order arranges labels in reverse alphabetical order, from Z to A. a. Reverse c. Ascending b. Major d. Descending ANS: D

PTS: 1

REF: EX 271

11. ____ order arranges labels in reverse alphabetical order, from A to Z a. Reverse c. Ascending b. Major d. Descending ANS: C

PTS: 1

REF: EX 271

12. The fields you use to order your data are called sort ____. a. values c. tags b. fields d. tools ANS: B

PTS: 1

REF: EX 272

13. You can access the Sort buttons in the ____ group on the HOME tab by clicking the Sort & Filter button and clicking a sort option. a. Filtering c. Editing b. Formatting d. Text ANS: C

PTS: 1

REF: EX 271

14. When you have more than one sort field, you should use the Sort ____ to specify the sort criteria. a. menu c. wizard

b. status bar ANS: D

d. dialog box PTS: 1

REF: EX 272

15. To add a sort field in the Sort dialog box, click the ____ button. a. Add Level c. New Sort Field b. Add Field d. New Level ANS: A

PTS: 1

REF: EX 273

16. After you filter a column, the ____ Filter command becomes available so you can remove the filter and redisplay all the records. a. Remove c. Delete b. Clear d. Archive ANS: B

PTS: 1

REF: EX 281

17. You can display or hide filter arrows for an Excel table by using the Filter button in the Sort & Filter group on the ____ tab. a. CATEGORY c. DATA b. VIEW d. HOME ANS: C

PTS: 1

REF: EX 278

18. The ____ condition requires that all of the selected criteria be true for the record to be displayed. a. OR c. AND b. NAND d. IF ANS: C

PTS: 1

REF: EX 281

19. The ____ criteria filter requires the records displayed to start with the specified text string. a. Origin c. Initial b. Begins With d. Start ANS: B

PTS: 1

REF: EX 282

20. The ____ criteria filter requires the records displayed to have the specified text string anywhere. a. Has c. Contains b. Is d. Matches ANS: C

PTS: 1

REF: EX 282

21. The ____ criteria filter requires the records displayed to be greater than or equal to and less than or equal to the specified numbers. a. Top 10 c. Aggregate b. Between d. Range ANS: B

PTS: 1

REF: EX 282

22. The ____ criteria filter requires the records displayed to have the current date. a. Current Date c. This b. This Date d. Today ANS: D

PTS: 1

REF: EX 282

23. The ____ criteria filter requires the records displayed to be since January 1 of the current year to the current date.

a. Year to Date b. Today ANS: A

c. Current d. none of the above PTS: 1

REF: EX 282

24. With the Last Quarter criteria filter, quarters are defined as, for example, ____. a. April, May, June c. May, June, July b. March, April, May d. March, April, May, June ANS: A

PTS: 1

REF: EX 282

25. When you click in each cell in the Total row, a(n) ____ appears that you can click to open a list of the most commonly used functions. a. menu c. button b. arrow d. status bar ANS: B

PTS: 1

REF: EX 287

26. You can split the worksheet window into ____ separate panes. a. two or four c. six or eight b. four or six d. between two and eight ANS: A

PTS: 1

REF: EX 290

27. You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet both of two criteria. a. Or c. And b. Both d. Either ANS: C

PTS: 1

REF: EX 283

28. You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet one of the criteria. a. Or c. And b. Both d. Either ANS: A

PTS: 1

REF: EX 283

29. In the figure above, if you only want to set one criterion for this filter, you will ____. a. leave the lower boxes empty c. enter “none” into the lower boxes b. enter “0” into the lower boxes d. access a pull-down menu of options ANS: A

PTS: 1

REF: EX 284

30. In the figure above, the 300 indicates the amount. Records with the following values meet the criteria: ____. a. $298 c. $300 b. $299 d. $301 ANS: D

PTS: 1

REF: EX 284

31. You can click the More Functions command on the Total arrow button list to open the ____ dialog box and select any available function. a. Display Function c. Add Function b. Insert Function d. New Function ANS: B

PTS: 1

REF: EX 289

32. To calculate subtotal for a table, the first step is to use the ____ button on the TABLE TOOLS DESIGN tab. a. Expand for Calculation c. Summary b. Convert to Range d. Function ANS: B

PTS: 1

REF: EX 293

33. In addition to displaying subtotals, the Subtotal feature "____" your worksheet so you can control the level of detail that is displayed. a. headers c. shadows b. outlines d. defines ANS: B

PTS: 1

REF: EX 295

34. You can create a PivotTable by clicking the Summarize with PivotTable button in the Tools group on the ____ tab. a. HOME c. TABLE TOOLS DESIGN b. VIEW d. PIVOTTABLE ANS: C

PTS: 1

REF: EX 301

35. On the far right, in the figure above, there is the PivotTable field list. The items listed there, such as “Day” and “Segment” are ____ in the table. a. columns c. chart data labels b. rows d. field values ANS: A

PTS: 1

REF: EX 302

36. On the bottom-right corner of the figure above, there are four squares labeled FILTER, COLUMNS, ROWS, and VALUES. These four areas represent the ____ of the PivotTable. a. files c. segments b. categories d. layout ANS: D

PTS: 1

REF: EX 302

37. To get to the screen shown in the figure above, you would click the INSERT tab on the Ribbon, and then, in the ____ group, click the PivotTable button. a. Tables c. Tools b. PivotTables d. Data ANS: A

PTS: 1

REF: EX 301

38. By default, the PivotTable report uses the ____ function for numbers in the Values area. a. COUNT c. MIN b. AVERAGE d. SUM ANS: D

PTS: 1

REF: EX 303

39. The ____ report layout displays one column for each field and leaves space for column headers. a. Outline c. Tabular b. Design d. Headers ANS: C

PTS: 1

REF: EX 306

40. You cannot change data directly in the PivotTable. Instead, you must edit the Excel table, and then ____, or update, the PivotTable to reflect the updated data. a. reset c. renew b. refresh d. change ANS: B

PTS: 1

REF: EX 312

41. To delete records that are incorrect, out of date, or no longer needed, select a cell in each record you want to delete, click the Delete button arrow in the Cells group on the ____ tab, and then click Delete Table Rows. a. HOME c. VIEW b. INSERT d. DATA ANS: A

PTS: 1

REF: EX 270

42. Every ____ consists of an object that contains a button for each unique value in that field. a. piece c. section b. slicer d. part ANS: B

PTS: 1

Case-Based Critical Thinking Questions

REF: EX 285

Case 5-1 You are a student who is new to the more advanced features of Excel.

43. You have a lot of familiarity with ranges from an earlier Excel class, but you know that there is an alternative that will give you access to additional features that you do not have with a cell range. What is that option? a. Convert the range to a table. b. Convert the range to an index. c. Convert the range to a PivotChart. d. You are wrong; the range has as many features as any Excel alternative.

ANS: A

PTS: 1

REF: EX 264

TOP: Critical Thinking

44. Which of the following is true about Excel tables (as opposed to structured ranges of data)? a. You cannot format quickly using a table style. b. You cannot enter a formula in one table cell that is automatically copied to all other cells in that table column. c. You cannot add new rows and columns that automatically expand the range. d. You cannot freeze the top row of the worksheet.

ANS: D

PTS: 1

REF: EX 264

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 5-2 Cali has just discovered the power of using text filters to control which records display in his worksheet, and he has begun to use them often to find precisely the records he needs. The following questions are based on a camp directory table that includes First Name, Last Name, Date of Birth, Camp, and Phone Number.

45. If Cali wants to find all of his campers named Cathy, Carla, and Carey, what should she do? a. Create a text filter using the Begins With operator to display all records that have Ca at the beginning of the text value. b. Create a text filter using the Contains operator to display all records that have Ca at the beginning of the text value. c. Create a text filter using the Begins With operator to display all records that have Car at the beginning of the text value. d. Create a text filter using the Contains operator to display all records that include Ca in the text value.

ANS: A

PTS: 1

REF: EX 280

TOP: Critical Thinking

46. If Cali wants to find all of his campers whose last names end in “son” (such as Samson and Colson), what should he do? a. Create a text filter using the Ends With operator to display all records that have cson as the last characters in the text value. b. Create a text filter using the Contains operator to display all records that include son in the text value. c. Create a text filter using the Ends With operator to display all records that have son as the last characters in the text value. d. Create a text filter using the Contains operator to display all records that have son as the last characters in the text value.

ANS: C

PTS: 1

REF: EX 280

TOP: Critical Thinking

47. To find anyone whose Camp is either Day Camp, or Overnight Camp, or Nature Camp, what should he do? a. Create a text filter using the Contains operator to display all records that have Nature anywhere in the text value. b. Create a text filter using the Ends With operator to display all records that have Day as the last characters in the text value. c. Create a text filter using the Begins With operator to display all records that have Overnight as the first characters in the text value. d. Create a text filter using the Contains operator to display all records that have Camp anywhere in the text value.

ANS: D

PTS: 1

REF: EX 280

TOP: Critical Thinking

Critical Thinking Case-Based Questions Case 5-3 As he prepares his assignment for his Marketing class, Ted goes to his friend Carter for help with PivotTables because Carter has had a lot of experience with them in his job.

48. Which of the following is a true statement Carter can make to Ted about PivotTables? a. To remove a field from a PivotTable, you click the field’s check box in the PivotTable Field list. b. You cannot rename a value label by typing the new text directly in any cell where the value label appears in the PivotTable. c. Removing a field from the PivotTable changes the underlying Excel table. d. To remove a field from a PivotTable, you click the field’s check box in the PivotChart list.

ANS: A

PTS: 1

REF: EX 301

TOP: Critical Thinking

49. Ted wants to be able to analyze date data in his PivotTable by month. What must he do with the date field in his PivotTable? a. He must aggregate the data by the date field. b. He must cluster the data by the date field. c. He must sort the data by the date field. d. He must group the data by the date field.

ANS: D

PTS: 1

REF: EX 305

TOP: Critical Thinking

50. If Ted decides that he wants to work with nonnumeric data in his PivotTable, what function must he use to produce summaries? a. SUM c. TOTAL b. AGGREGATE d. COUNT ANS: D

PTS: 1

REF: EX 300

TOP: Critical Thinking

51. Ted wants to be able to use his PivotTable to combine items into groups. Which of the following is true? a. Items that appear as row labels cannot be grouped. b. Items that appear as column labels can be grouped. c. If items are dates, they cannot be grouped automatically using the Grouping dialog box. d. If items are numbers, they cannot be grouped manually using the Ctrl key to select items in a group and then choosing Group from the shortcut menu.

ANS: B

PTS: 1

REF: EX 303

TOP: Critical Thinking

52. Ted wants to use his PivotTable to show the total value of the motorcycles for a series of motorcycle dealerships but he wants to filter the display to show only those dealers whose total stock of motorcycle is greater than $1 million. How would he accomplish this? a. He would develop a PivotTable using the aggregate filter. b. He would develop a PivotTable using the report filter. c. He would develop a PivotTable using the category filter. d. He would develop a PivotTable using the value filter.

ANS: D

PTS: 1

REF: EX 309

TOP: Critical Thinking

COMPLETION 1. When related fields are grouped together in a row, they form a(n) ____________________, a collection of fields that describes a person, place, or thing.

ANS: record PTS: 1

REF: EX 260

2. Each column in a range represents a(n) ____________________ that describes some attribute or characteristic of a person, place, or thing.

ANS: field PTS: 1

REF: EX 260

3. To freeze a row, select the cell below the row(s) you want to freeze, click the ____________________ button in the Window group and then click Freeze Top Row. ANS: Freeze Panes PTS: 1

REF: EX 263

4. The second sort field in a sort is called the ____________________ field.

ANS: secondary sort PTS: 1

REF: EX 272

5. A(n) ____________________ list indicates the sequence in which you want data ordered.

ANS: custom PTS: 1

REF: EX 274

6. The process of hiding certain records and viewing the ones you want is called ____________________ your data.

ANS: filtering PTS: 1

REF: EX 278

7. When you want to see all the data in a filtered table, you can ____________________, or remove, the filters.

ANS: clear PTS: 1

REF: EX 281

8. The ____________________ criteria filter requires that the records displaying begin with the specified text string.

ANS: Begins With PTS: 1

REF: EX 280

9. The entry 20000 is the ____________________ for the first condition.

ANS: criterion PTS: 1

REF: EX 282

10. The ____________________ command automatically inserts a subtotal line into the range for each group of data.

ANS: Subtotals PTS: 1

REF: EX 292

11. A(n) ____________________ is an interactive table that enables you to group and summarize an Excel list into a concise, tabular format for easier reporting and analysis.

ANS: PivotTable PTS: 1

REF: EX 298

12. In PivotTable terminology, the fields that you summarize are known as ____________________ fields.

ANS: Values PTS: 1

REF: EX 303

13. In addition to data fields, a PivotTable uses ____________________ fields to group values such as department, model, year, and vehicle type in an automobile-related sheet, for example.

ANS: category PTS: 1

REF: EX 303

14. The box that is covering columns A, B, and C in the figure above is the PivotTable ____________________ area.

ANS: report PTS: 1

REF: EX 302

15. A(n) ____________________ is a graphical representation of the data in a PivotTable.

ANS: PivotChart PTS: 1

REF: EX 316

ESSAY 1. What are the guidelines for creating an effective structured range of data?

ANS: * Enter field names in the top row of the range. A field name (column header) is a unique label that describes the contents of the data in that column. The row of field names is called the header row.

Although the header row often is row 1, it can be any row. * Use short, descriptive field names. Shorter field names are easier to remember and enable more fields to appear in the workbook window at once. * Format field names to distinguish the header row from the data. For example, apply bold, color, and a different font size. * Enter the same kind of data for a field in each record. * Separate the data from other information in the worksheet by at least one blank row and one blank column. The blank row and column enable Excel to accurately determine the range of the data.

PTS: 1

REF: EX 262

TOP: Critical Thinking

2. What are the steps for sorting data using multiple fields?

ANS:

• Select any cell in a table or range.

• On the DATA tab, in the Sort & Filter group, click the Sort button.

• If necessary, click the Add Level button to insert the Sort by row.

• Click the Sort by arrow, select the column heading for the primary sort field, click the Sort On arrow to select the type of data, and then click the Order arrow to select the sort order.

• For each additional column to sort, click the Add Level button, click the Then by arrow, select the column heading for the secondary sort field, click the Sort On arrow to select the type of data, and then click the Order arrow to select the sort order.

• Click the OK button.

PTS: 1

REF: EX 272

TOP: Critical Thinking

3. What is a PivotTable? Describe its use in Excel, including the definition of value fields and category fields.

ANS: A PivotTable is an interactive table that enables you to group and summarize an Excel list into a concise, tabular format for easier reporting and analysis. A PivotTable summarizes data in different categories, using functions such as COUNT, SUM, AVERAGE, MAX, and MIN. To generate a PivotTable report, you need to specify which fields you want to summarize; in PivotTable terminology, these fields are known as data fields. In addition to value fields, a PivotTable uses category fields to group values. Category fields appear in PivotTables as row labels, column labels, and report filters, which allows you to focus on a subset of the PivotTable by displaying one, several, or all items.

PTS: 1

REF: EX 298 | EX 300 | EX 303

TOP: Critical Thinking

Excel Tutorial 6: Managing Multiple Worksheets and Workbooks TRUE/FALSE 1. Using multiple worksheets makes it more difficult for you to group your data.

ANS: F

REF: EX 330

2. Once you group a collection of worksheets, any changes you make to one worksheet are applied to all sheets in the group.

ANS: T

REF: EX 331

3. If a worksheet group includes all the worksheets in a workbook, you can edit only the active worksheet.

ANS: T

REF: EX 332

4. If you delete a value from one cell in a worksheet group, the content is also deleted from the same cell in all the worksheets in the group.

ANS: T

REF: EX 334

5. Using multiple worksheets with identical layouts enables you to use 3-D references to quickly summarize the data in another worksheet.

ANS: T

REF: EX 342

6. When the source and destination workbooks are stored in the same folder, you still need to include the location information in the workbook reference that links the two files.

ANS: F

REF: EX 352

7. If the destination file is closed when you make a change in the source file, you choose whether to update the link to display the current values when you open the destination file or continue to display the older values from the destination file.

ANS: T

REF: EX 361

8. When you create a new workbook from a template, an unnamed copy of the template opens.

ANS: T

REF: EX 372

9. A workbook based on a specific template always displays the name of the template followed by the date.

ANS: F

REF: EX 376

10. All template files have the .xlts file extension.

ANS: F

REF: EX 378

MODIFIED TRUE/FALSE 1. To select nonadjacent worksheets, you click a sheet tab of one of the worksheets, and then press and hold down the Ctrl key as you click the sheet tabs of other worksheets you want included in the group. _________________________ ANS: T

REF: EX 331

2. In order to reference data located in one workbook in another workbook, you have to create a(n) dialog between the two workbooks. _________________________ ANS: F, link REF: EX 352 3. When you use the point-and-click method to build formulas with external references, Excel enters all of the required punctuation, including quotation marks. _________________________ ANS: T

REF: EX 352

4. In the figure above, cell A8 contains a hyperlink. _________________________ ANS: T

REF: EX 371

5. A(n) asterisk separates the worksheet reference from the cell or range reference. _________________________ ANS: F, exclamation mark (!)

REF: EX 339 6. A(n) template is a workbook with labels, formats, and formulas already built into it with data removed. _________________________

ANS: T

REF: EX 368

7. Hyperlinks are usually represented by words with colored letters and underlines or images. _________________________ ANS: T

REF: EX 370

8. To delete a hyperlink, right-click the hyperlink cell and then click Clear Value. _________________________ ANS: F, Contents REF: EX 372 9. Some of the task-specific outlines available from the Office.com site include: Monthly Family Budget, Inventory List, and Time sheets. _________________________ ANS: F, templates REF: EX 373 10. Excel, by default, looks for template files in the Templates folder, which is where custom template files are often stored. _________________________ ANS: T

REF: EX 377 | EX 378

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. 3-D f. link b. template g. M c. A h. worksheet group d. source i. hyperlink e. destination j. Tiled 1. Type of cell reference that refers to the same cell or range of cells on multiple worksheets in the same workbook 2. Usually represented by words with colored letters and underlines or images 3. Divides the open workbooks evenly on the screen 4. Indicates that a link has to be updated manually by the user 5. Indicates a link that is updated automatically 6. Receives the information when two files are linked 7. Contains text, formats, and formulas already built into it for your use 8. Contains the information when two files are linked 9. A connection between two files allowing information to be transferred from one file to the other 10. A collection of two or more selected worksheets 1.

2.

3.

4.

5.

ANS: A ANS: I ANS: J ANS: G ANS: C

REF: REF: REF: REF: REF:

EX 329 EX 370 EX 354 EX 366 EX 366

6.

7.

8.

9.

10.

ANS: E ANS: B ANS: D ANS: F ANS: H

REF: REF: REF: REF: REF:

EX 352 EX 373 EX 352 EX 352 EX 328

MULTIPLE CHOICE 1. A worksheet ____ is a collection of two or more selected worksheets. a. index c. group b. roster d. cluster ANS: C

REF: EX 328

2. Like cell ____, a worksheet group can contain adjacent or nonadjacent sheets. a. controls c. tabs b. groups d. ranges ANS: D

REF: EX 331

3. To select adjacent worksheets, you use the ____ key. a. Shift c. Ctrl b. Alt d. F1 ANS: A

REF: EX 331

4. The ____ caption indicates a worksheet group. a. [Worksheets] c. [Group] b. [Selected Sheets] d. [Workgroup] ANS: C

REF: EX 331 | EX 332

5. To ungroup worksheets, you can click a sheet ____ of a sheet not in the group. a. index c. tab b. panel d. pane ANS: C

REF: EX 335

6. When a worksheet group is selected, the ____ includes text to indicate this. a. title bar c. status bar b. worksheet bar d. formula bar ANS: A

REF: EX 331

7. To remove a worksheet group, you can right-click the sheet tab of a sheet in the group and then click ____ on the shortcut menu. a. Remove Group c. Exit Group b. No Group d. Ungroup Sheets ANS: D

REF: EX 331

8. To move a worksheet or worksheet group to another location in the same workbook, select the worksheets and then drag and drop them by the selected sheet ____. a. tabs c. keys b. indices d. icons

ANS: A

REF: EX 337

9. When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference. a. ? c. " b. ! d. ; ANS: B

REF: EX 339

10. When you reference cells and ranges in other worksheets, if the worksheet name contains spaces, you must enclose the sheet name in ____. a. braces c. parentheses b. brackets d. single quotation marks ANS: D

REF: EX 339

11. Which of the following formulas is correct, given a sheet named Sales Data? a. (Sales Data)!D10 c. 'Sales Data'!D10 b. [Sales Data]!D10 d. {Sales Data}!D10 ANS: C

REF: EX 339

12. The general format of a 3-D cell reference is ____. a. CellRange!WorksheetRange c. WorkbookRange!CellRange b. WorksheetRange!CellRange d. CellRange!WorkbookRange ANS: B

REF: EX 342

13. The worksheet range ____ references the worksheets, "Sheet1," "Sheet2," "Sheet3," and "Sheet4." a. Sheet1_Sheet4 c. Sheet1:Sheet4 b. Sheet1-Sheet4 d. Sheet1/Sheet4 ANS: C

REF: EX 342

14. A(n) ____ cell reference specifies not only the rows and columns of a cell range, but also the names of the worksheets on which the cells appear. a. indexed c. absolute b. relational d. 3-D ANS: D

REF: EX 342

15. A link is a connection between two ____ allowing information to be transferred from one to the other. a. cells c. columns b. ranges d. files ANS: D

REF: EX 352

16. If you need to use data located in one workbook in the other workbook, you have to create a(n) ____ between the two workbooks. a. anchor c. link b. source d. index ANS: C

REF: EX 352

17. The general form of an external reference is ____, where Path is the path of the workbook file on the computer or network, Workbook Name is the filename of the workbook, and Sheet Range and Cell Range are worksheets and cells in the workbook, respectively. a. (WorkbookName)WorksheetName!CellRange b. [WorkbookName]WorksheetName!CellRange c. [WorkbookName]WorksheetName!CellRange d. /WorkbookName/WorksheetName/CellRange ANS: B

REF: EX 352

18. If you want to create a cell reference to B6 on the Summary Info worksheet of the Sales.xls workbook, you would use the following external reference: ____. a. '[Sales.xlsx]Summary Info'/B6 c. '[Sales.xlsx:Summary Info]'!B6 b. '(Sales.xlsx)Summary Info'!B6 d. '[Sales.xlsx]Summary Info'!B6 ANS: D

REF: EX 352

19. Which of the following is NOT an option for arranging your workbooks? a. tiled c. horizontal b. cascade d. lateral ANS: D

REF: EX 354

20. The ____ arrangement option layers the open workbooks on the screen. a. Cascade c. Tiled b. Horizontal d. Lateral ANS: A

REF: EX 354

21. A formula can include a reference to another workbook (called an ____ reference), which creates a set of linked workbooks. a. internal c. import b. external d. export ANS: B

REF: EX 355

22. You can create a(n) ____ reference by deleting the $ from the row reference in the cell reference $B$6. a. mixed c. relative b. absolute d. indexed ANS: A

REF: EX 359

23. Formulas created using the point-and-click method contain ____ references. a. absolute c. relative b. mixed d. relational ANS: A

REF: EX 358

24. If you receive a destination workbook but the source files are not included, click the ____ Link button in the Edit Links dialog box to replace the external references with the existing values. a. Fix c. Update b. Break d. Check ANS: B

REF: EX 366

25. If you trust the provider of source files, you can choose to ____ the content, which allows the external reference formulas to function and updates the links in the destination workbook. a. enable c. concatenate b. embed d. consolidate ANS: A

REF: EX 365

26. When you save a workbook that contains external reference formulas Excel stores the most recent results of those formulas in the ____ file. a. source c. both a. and b. b. destination d. neither a. nor b.

ANS: B

REF: EX 365

27. If the destination file is closed when you make a change in the ____ file, you can choose whether to update the link to display the current values when you open the destination file. a. target c. source b. origin d. key ANS: C

REF: EX 361

28. If Excel successfully accessed the link and updated the values from the source document, the status in the Edit Links dialog box will be ____. a. Done c. OK b. Completed d. Good ANS: C

REF: EX 366

29. ____ are usually represented by words with colored letters and underlines or images. a. Indices c. Templates b. Hyperlinks d. Icons ANS: B

REF: EX 370

30. When you click a(n) ____, the computer switches to the file or portion of the file that it references. a. index c. template b. hyperlink d. icon ANS: B

REF: EX 370

31. The default ____ for a hyperlink is the folder location and the name of the file to which you will link. a. title c. description b. alt text d. ScreenTip ANS: D

REF: EX 372

32. To delete the hyperlink but keep the text, right-click the hyperlink cell, and then click ____ Hyperlink. a. Delete c. Archive b. Erase d. Remove ANS: D

REF: EX 372

33. The default template contains the default formatting you start out with in every new workbook, including ____ 11-point font. a. Calibri c. Geneva b. Helvetica d. Times New Roman

ANS: A

REF: EX 373

34. A ____ is a workbook that you can open with the text, formats, and formulas already built into it. a. boiler c. template b. summary sheet d. range master ANS: C

REF: EX 373

35. Which of the following is NOT a task-specific template available from the Microsoft Office Online Web site? a. Family Budget c. Team Roster b. Inventory List d. Fax Cover Sheet ANS: D

REF: EX 373

36. The ____ template in Excel creates an online time card to track employees’ work hours. a. Payroll c. Time Sheet b. Personnel Statement d. Employment Calculator ANS: C

REF: EX 373

37. The default template places the formula results aligned to the ____ of a cell. a. right side c. top b. left side d. bottom ANS: A

REF: EX 373

38. All template files have a(n) ____ extension. a. .xlsx b. .xltx ANS: B

c. .xldx d. .xtpl

REF: EX 378

Case-Based Critical Thinking Questions Case 6-1 Simon is having trouble using linked workbooks, so he consults his colleague Brooke for help.

39. Simon tells Brooke that when he attempts to open a workbook, the Edit Links dialog box appears with the message "This workbook contains one or more links that cannot be updated." Brooke suggests several possible causes of this error message. Which of the following is a possible cause? a. one of the workbook's source files was edited b. one of the workbook's source files was copied to a different folder c. one of the workbook's source files was replaced with an updated file of the same name d. the workbook was sent to him without links ANS: D

REF: EX 361

TOP: Critical Thinking

40. After Brook has helped Simon to resolve his original problem, Simon asks her about another linked workbook he has received. The source files for this workbook have not been included, but Simon would like to use the workbook with the most recent values from the source files. What should Brooke tell him? a. He can open the workbook and click the Continue button in the Edit Links dialog box. b. He can open the workbook and click the Break button in the Edit Links dialog box. c. He can open the workbook and click the Change Source button in the Edit Links dialog box.

d. He will not be able to use the workbook until he obtains copies of the source files.

ANS: A

REF: EX 361

TOP: Critical Thinking

41. Finally, Simon asks Brooke what steps (if any) need to be taken to keep a destination file up-to-date if he updates a value in one of the source files. Which of the following statements by Brooke would be correct? a. If a destination file is open when a source file is changed, the destination file will be updated automatically. b. Source files cannot be updated unless the corresponding destination file is open. c. It is not possible to choose whether a destination file should reflect changes made to the source files. d. If a destination file is open when a source file is changed, you need to update the destination file manually so that it reflects the changes.

ANS: A

REF: EX 361

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 6-2 Ryder has discovered the power of creating and viewing multiple workbooks.

42. Ryder wants to use a layout in which the workbooks overlap each other with all the title bars visible.

Ryder opens all of the workbooks, and then he clicks the VIEW tab on the Ribbon and clicks the Arrange All button. What should he do next to obtain the desired layout? a. Click the Tile Horizontally option button c. Click the Overlap option button b. Click the Tile Vertically option button d. Click the Cascade option button ANS: D

REF: EX 354

TOP: Critical Thinking

43. Ryder wants to use the workbooks he has created on another computer. What steps does he need to take? a. He needs to copy just the workbook file to the other computer. b. He needs to copy just the individual worksheets to the other computer. c. He needs to copy both the workbook file and the individual worksheets to the other computer. d. He cannot copy the workbook; instead, he needs to copy the individual worksheets and recreate the workspace on the other computer.

ANS: C

REF: EX 337

TOP: Critical Thinking

44. When Ryder goes to save the workbooks, what is the required file extension? a. .wxls c. .xlsx b. .xlww d. .xlwx ANS: C

REF: EX 376

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 6-3 Your friend Jared asks you to help him learn more about using hyperlinks in an Excel workbook file.

45. Which of the following statements about hyperlinks in Excel workbooks is correct? a. They are usually represented by bold faced words with underlines or images. b. They can be used to jump to a specific cell or range within the active worksheet, another

worksheet, or another workbook. c. They cannot be used to jump to other types of files or sites on the Web. d. To use a hyperlink, you can click anywhere in the cell that contains the link, including white space in the cell or any text that flows into an adjacent cell.

ANS: B

REF: EX 370

TOP: Critical Thinking

46. Jared wants to create a hyperlink to a location in his current workbook. He clicks the Hyperlink button in the Links group on the INSERT tab. What option should he select from the Link to list? a. Existing File c. Create New Document b. Place in This Document d. E-mail Address ANS: B

REF: EX 371

TOP: Critical Thinking

47. Jared has heard that ScreenTips can be used in conjunction with hyperlinks. Which of the following statements about ScreenTips is correct? a. They appear whenever you place the pointer over a cell. b. You can specify the contents of a ScreenTip when you create a hyperlink. c. The default ScreenTip for a hyperlink is the message "Active Hyperlink." d. You can change the contents of an existing ScreenTip by changing the value in the cell.

ANS: B

REF: EX 372

TOP: Critical Thinking

48. Jared wants to create a hyperlink to link to new a document. What should he click in the Link to list? a. Existing File or Web Page c. E-mail address b. Create New Document d. Place in This Document ANS: B

REF: EX 371

TOP: Critical Thinking

49. Click the button marked ____ in the accompanying figure to fix broken links. a. Break Link c. Check Status b. Update Values d. Change Source ANS: D

REF: EX 366

50. Click the button marked ____ in the accompanying figure to replace the link to a source file with the current values for the linked cells. a. Break Link c. Check Status b. Update Values d. Change Source ANS: A

REF: EX 366

51. In the Edit Links dialog box in the accompanying figure, ____ in the Update column indicates that links are updated automatically. a. Update Values c. A

b. Automatic ANS: C

d. UPD REF: EX 366

52. In the Edit Links dialog box in the accompanying figure, ____ in the Update column would indicate that a link has to be updated manually by the user. a. M c. U b. A d. UPD ANS: A

REF: EX 366

COMPLETION 1. A(n) ____________________ group is a collection of two or more selected worksheets.

ANS: worksheet REF: EX 328 2. A 3-D cell ____________________ specifies not only the rows and columns of a cell range, but also the range of worksheet names that the cells appear on.

ANS: reference REF: EX 342 3. A(n) ____________________ is a connection between two files allowing information to be transferred from one file to the other.

ANS: link REF: EX 350 4. When two files are linked together, the workbook that contains the information is the ____________________ file.

ANS: source REF: EX 352 5. When two files are linked together, the ____________________ file receives the information.

ANS: destination REF: EX 352 6. If both the ____________________ and source files are open, Excel will update the link between them automatically whenever a value in the source file is changed.

ANS: destination REF: EX 361

7. When the destination file is ____________________, and the source files are closed, the complete file path is included as part of the external reference formula that appears in the formula bar.

ANS: open REF: EX 364 8. You can use the ____________________ dialog box to break links between workbooks.

ANS: Edit Links REF: EX 365 9. ____________________ a worksheet duplicates all the values, formulas, and formats into the new worksheet, leaving the original worksheet intact.

ANS: Copying REF: EX 336 | EX 337 10. A(n) ____________________ is a link in a file, such as a workbook, to information within that file or another file.

ANS: hyperlink REF: EX 370 11. In the Links group, click the Hyperlink button and the _______ Hyperlink dialog box opens.

ANS: INSERT REF: EX 371 12. ____________________ appear whenever you place the pointer over a hyperlink.

ANS: ScreenTips REF: EX 372 13. To manually ____________________ a link and see the new data values, click the Update Values button.

ANS: update REF: EX 366 14. To use a template that you create, the template file is typically placed in the ____________________ folder.

ANS: Templates REF: EX 378

15. After you have saved a template, you can access the template from the New screen in ____________________ view.

ANS: Backstage REF: EX 380 ESSAY 1. Using multiple worksheets makes it easier to group and summarize data. Describe features that can be applied by forming a worksheet group.

ANS: By forming a worksheet group, you can: • Enter or edit data and formulas. Changes made to content in the active worksheet are also made in the same cells in all the worksheets in the group. You can also use the Find and Replace commands with a worksheet group.

• Apply formatting. Changes made to formatting in the active worksheet are also made to all the worksheets in the group, including changing row heights or column widths and applying conditional formatting.

• Insert or delete rows and columns. Changes made to the worksheet structure in the active worksheet are also made to all the worksheets in the group.

• Set the page layout options. Changes made to the page layout settings in one worksheet also apply to all the worksheets in the group, such as changing the orientation, scaling to fit, and inserting headers and footers.

• Apply view options. Changes made to the worksheet view such as zooming, showing and hiding worksheets, and so forth are also made to all the worksheets in the group.

• Print all the worksheets. You can print all of the worksheets in the worksheet group at the same time.

REF: EX 331

TOP: Critical Thinking

2. What are the steps to inserting a hyperlink and linking to: a file or Web page, a location in the current workbook, a new document, and an e-mail address.

ANS: • Select the text, graphic, or cell in which you want to insert the hyperlink.

• On the INSERT tab, in the Links group, click the Hyperlink button.

• To link to a file or webpage, click Existing File or Web Page in the Link to list, and then select the file or webpage from the Look in box.

• To link to a location in the current workbook, click Place in This Document in the Link to list, and then select the worksheet, cell, or range in the current workbook.

• To link to a new document, click Create New Document in the Link to list, and then specify the filename and path of the new document.

• To link to an email address, click E-mail Address in the Link to list, and then enter the email address of the recipient (such as name@example.com) and a subject line for the message.

• Click the OK button.

REF: EX 370

TOP: Critical Thinking

3. What is an example of when you would use a template? Name at least three benefits of using a workbook template.

ANS: (Answers will vary.)

A large organization may need to collect the same information from several regions. By creating and distributing a workbook template, each region knows what data to track and where to enter it. The template already includes the formulas, so the results are calculated consistently.

Advantages of using templates (answers will vary): - Templates save time and ensure consistency in the design and content of workbooks because all labels, formatting, and formulas are entered once.

- Templates ensure accuracy because formulas can be entered and verified once, and then be used with confidence in all workbooks.

- Templates standardize the appearance and content of workbooks.

- Templates prevent data from being overwritten when an existing workbook is inadvertently saved with new data rather than saved as a new workbook.

REF: EX 373

TOP: Critical Thinking

Excel Tutorial 7: Developing an Excel Application TRUE/FALSE 1. For cell and range names, names of 5 to 15 characters are considered practical, although names can be up to 255 characters.

ANS: T

PTS: 1

REF: EX 395

2. Clicking the Edit button in the accompanying figure opens the New Name dialog box in which you can modify the selected defined name.

ANS: F

PTS: 1

REF: EX 398

3. You can use the Name Manager dialog box, shown in the accompanying figure, to create a defined name.

ANS: T

PTS: 1

REF: EX 398

4. Defined names are automatically substituted for existing range references in a formula.

ANS: F

PTS: 1

REF: EX 400

5. The Stop alert prevents an entry from being stored in the cell.

ANS: T

PTS: 1

REF: EX 412

6. Even when the worksheet is unprotected, the locked property determines whether or not changes can be made to a given cell.

ANS: F

PTS: 1

REF: EX 420

7. Validation rules come into play only during data entry.

ANS: T

PTS: 1

REF: EX 412

8. The red triangle (which has an arrow pointing to it) in the accompanying figure indicates that a given cell does not have a comment.

ANS: F

PTS: 1

REF: EX 425

9. Each macro must have a unique name that begins with a letter.

ANS: T

PTS: 1

REF: EX 434

10. Running a macro means Excel performs a portion of the steps that were recorded.

ANS: F

PTS: 1

REF: EX 437

MODIFIED TRUE/FALSE 1. Cell and range references do not indicate what data is stored in those cells. _________________________ ANS: T

PTS: 1

REF: EX 394

2. Any blanks or parentheses in row or column labels are changed to underscore characters in defined names. _________________________ ANS: T

PTS: 1

REF: EX 397

3. The Name dialog box lists all of the names currently defined in the workbook, including Excel table names. _________________________ ANS: F, Name Manager PTS: 1

REF: EX 398

4. To open the Data Validation dialog box, click the DATA tab on the Ribbon, and then click Authentication in the Data Tools group. _________________________

ANS: F, Data Validation PTS: 1

REF: EX 410

5. When you lock a workbook, you control the ability users have to make changes to the file. _________________________ ANS: F, protect PTS: 1

REF: EX 420

6. Every cell in a workbook has a(n) locked property that determines whether or not changes can be made to that cell. _________________________ ANS: T

PTS: 1

REF: EX 420

7. A digital certificate confirms that the macro created by the signer has not been altered since the digital certificate was created. _________________________ ANS: F, signature PTS: 1

REF: EX 433

8. The Visual Basic Editor is a separate application that works with Excel and all of the Office products to edit and manage VBA code. _________________________ ANS: T

PTS: 1

REF: EX 441

9. One way to run a macro is to assign it to a(n) button that is placed directly on the worksheet. _________________________ ANS: T

PTS: 1

REF: EX 444

10. When Excel creates a macro button, the Assign Macro dialog box opens with the button’s default name showing. _________________________ ANS: T

PTS: 1

REF: EX 446

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. Stop f. VBA b. Warning g. defined name c. Information h. input message d. locked i. validation rule e. macro j. digital certificate 1. Error alert that rejects invalid data but allows you to override the rejection 2. Series of stored commands 3. Appears when the cell becomes active and can be used to specify the type of data the user should enter in that cell 4. Programming language that allows you to create macros

5.

6.

7.

8.

9.

10.

Needed before you can add a digital signature to a workbook Error alert that accepts the invalid data but still allows you to cancel the data entry Defines the criteria for storing data in a cell or range Property that determines whether or not changes can be made to a cell Error alert that prevents you from storing the data in the cell Word or string of characters associated with a single cell or a range

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: B ANS: E ANS: H ANS: F ANS: J ANS: C ANS: I ANS: D ANS: A ANS: G

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 412 EX 429 EX 413 EX 433 EX 433 EX 412 EX 410 EX 420 EX 412 EX 394

MULTIPLE CHOICE 1. Which of the following is NOT a valid defined name? a. netincome c. Net-Income b. Net_Income d. NetIncome ANS: C

PTS: 1

2. Range names must begin with _____. a. a letter or an underscore b. a number ANS: A

PTS: 1

3. Range names can be up to _____ characters. a. 15 b. 27 ANS: D

PTS: 1

REF: EX 395

c. a hash tag d. an asterisk REF: EX 395

c. 56 d. 255 REF: EX 395

4. In the accompanying figure, clicking the New button opens the New _____ dialog box. a. Name c. Label b. Macro d. Range ANS: A

PTS: 1

REF: EX 398

5. In the accompanying figure, the column labeled “Refers To” is the range’s _____. a. link location c. target location in the worksheet b. location in the workbook d. web name ANS: B

PTS: 1

REF: EX 398

6. Using defined names like those shown in the accompanying figure makes creating and understanding a(n) _____ easier. a. index c. formula b. log d. workbook ANS: C

PTS: 1

REF: EX 398

7. The current data in the SeasonFee cell, according to the accompanying figure, is _____. a. 5 c. 12/1/2016 b. SM d. 75 ANS: D

PTS: 1

REF: EX 398

8. The _____ dialog box displays all of the names in the workbook. a. Name Manager c. Definition b. Ranges d. Terms ANS: A

PTS: 1

REF: EX 398

9. Any blanks or parentheses in a row or column label will be changed to _____ in defined names. a. ampersands c. hyphens b. underscore characters d. dashes

ANS: B

PTS: 1

REF: EX 397

10. The Excel data _____ feature restricts data entry to follow a defined set of rules. a. error-checking c. validation b. rule-based d. contingent ANS: C

PTS: 1

REF: EX 410

11. An input message appears when the cell becomes active, and can be used to specify the _____ of data the user should enter in that cell. a. aspect c. format b. type d. cast ANS: B

PTS: 1

REF: EX 413

12. One way to ensure that correct data is entered into a cell or range is to use the Excel data _____ feature. a. validation c. security b. concatenation d. index ANS: A

PTS: 1

REF: EX 410

13. In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only integers. a. Integer c. Numerical b. Decimal d. Whole Number ANS: D

PTS: 1

REF: EX 410

14. In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only text of a specified number of characters. a. Custom c. Any Value b. Text Length d. Specified Text ANS: B

PTS: 1

REF: EX 410

15. In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only times. a. Chronology c. Time b. Clock d. Calendar ANS: C

PTS: 1

REF: EX 410

16. Sometimes when you build a worksheet for other users, you may decide to add a(n) _____ message to a cell even if you do not validate the data in that cell. a. value c. key b. input d. user ANS: B

PTS: 1

REF: EX 413

17. Which of the following tabs in the Data Validation dialog box allows you to set validation criteria? a. Criteria c. Error Alert b. Input Message d. Settings ANS: D

PTS: 1

REF: EX 411

18. You use the _____ tab in the Data Validation dialog box to enter the validation rules for the active cell. a. Active Cell c. Settings b. Criteria d. Input Message ANS: C

PTS: 1

REF: EX 415

19. _____ messages appear as ScreenTips next to the active cell. a. Value c. Key b. Input d. User ANS: B

PTS: 1

REF: EX 414

20. The following are Excel-supported error alert types: _____. a. Stop, Warning, and Alert c. Security, Warning, and Alert b. Alert, Warning, and Information d. Stop, Warning, and Information ANS: D

PTS: 1

REF: EX 412

21. The _____ alert accepts the invalid data but still allows you to cancel the data entry. a. Information c. Security b. Stop d. Warning ANS: A

PTS: 1

REF: EX 412

22. When you _____ a workbook, you control the ability users have to make changes to the file. a. ensure c. secure b. protect d. seal ANS: B

PTS: 1

REF: EX 420

23. Every cell in a workbook has a(n) _____ property that determines whether or not changes can be made to that cell. a. ensured c. security b. locked d. changeable ANS: B

PTS: 1

REF: EX 420

24. Usually, you will want to protect a worksheet but leave some cells _____. a. open c. clear b. empty d. unlocked ANS: D

PTS: 1

REF: EX 420

25. Protecting the _____ prohibits users from renaming, deleting, hiding, or inserting worksheets. a. skeleton c. format b. structure d. location ANS: B

PTS: 1

REF: EX 423

26. To remove workbook protection, click the Unprotect Workbook button in the _____ group on the REVIEW tab. a. Format c. Sheet b. Changes d. Options ANS: B

PTS: 1

REF: EX 425

27. Comments like those in the accompanying figure are often used to _____. a. explain the contents of a particular cell, such as a complex formula b. provide instructions to users c. share ideas and notes from several users collaborating on a project d. All of the above ANS: D

PTS: 1

REF: EX 425

28. To keep a comment like the one in the accompanying figure on the screen, in the Comments group on the _____ tab, click the Show/Hide Comment button. a. REVIEW c. OPTIONS b. FORMAT d. HOME ANS: A

PTS: 1

REF: EX 426

29. A _____ can be destructive and can modify or delete files that may not be recoverable. a. digital signature c. comment virus b. macro virus d. micro virus ANS: B

PTS: 1

REF: EX 431

30. The _____ Center is a central location for all the security settings in Office. a. Secure c. Trust b. Security d. Protection ANS: C

PTS: 1

REF: EX 432

31. If content is blocked, the _____ located under the Ribbon appears, notifying you that some content was disabled. a. Mini toolbar c. Message Bar b. Quick Launch toolbar d. Status Bar ANS: C

PTS: 1

REF: EX 432

32. A(n) _____ certificate is another name for a digital ID. a. analog c. digital b. signature d. secure ANS: C

PTS: 1

33. A digital _____ is like a seal of approval.

REF: EX 433

a. affidavit b. clause ANS: C

c. signature d. salutation PTS: 1

REF: EX 433

34. To create a macro, you can enter a series of commands in the _____ programming language. a. HTML c. VBA b. Java d. Perl ANS: C

PTS: 1

REF: EX 433

35. The _____ Macro workbook is a hidden workbook opened whenever you start Excel, making its macros available anytime you use Excel. a. Universal c. Microsoft b. Personal d. XP ANS: B

PTS: 1

REF: EX 434

36. You can turn off the macro recorder by clicking the Stop Recording button in the Code group on the _____ tab. a. INSERT c. LAYOUT b. DEVELOPER d. MACRO ANS: B

PTS: 1

REF: EX 435

37. If a _____ error appears when you run a macro that has worked in the past, some part of the macro code no longer makes sense to Excel. a. syntax c. logic b. run-time d. baseline ANS: B

PTS: 1

REF: EX 439

38. To view the code of a macro, you need to open the Visual Basic _____. a. Manager c. Reader b. Code Window d. Editor ANS: D

PTS: 1

REF: EX 441

39. In VBA, macros are called _____ procedures. a. body c. commented b. sub d. modular ANS: B

PTS: 1

REF: EX 443

40. Each sub procedure in VBA begins with the keyword "Sub" followed by the name of the sub procedure and a set of _____. a. brackets c. parentheses b. braces d. asterisks ANS: C

PTS: 1

REF: EX 443

41. After the comments, in VBA code the next item is the _____ of the macro. a. module c. procedure b. body d. command ANS: B

PTS: 1

REF: EX 443

42. To save a workbook with macros, the default Excel Workbook format needs to change to a macro-enabled workbook, which has the _____ file extension. a. xlsm c. xlsx b. xlst d. xls ANS: A

PTS: 1

REF: EX 450

Case-Based Critical Thinking Questions Case 7-1 Ted knows that macros can be helpful to him in his work with Excel spreadsheets, but he also knows they have their hazards, so he comes to you for guidance about what kind of security settings he should put in place, and at what times.

43. While just getting his feet wet with Excel, you suggest to Ted that he might want to begin without having his installation of Excel have any macros run at all. Which of the following options would he select in the Trust Center? a. Disable all macros without notification b. Cancel all macros c. Disable a macro with notification d. Disable all macros except digitally signed macros ANS: A

PTS: 1

REF: EX 432

TOP: Critical Thinking

44. As Ted gets more comfortable with Excel, he thinks that he might be able to decide whether or not to run a macro on a case-by-case basis. What should his option be in the Trust Center? a. Disable all macros without notification b. Cancel all macros c. Disable all macros with notification d. Disable all macros except digitally signed macros ANS: C

PTS: 1

REF: EX 432

TOP: Critical Thinking

45. As Ted does more even work in Excel, he has begun to develop a collection of publishers whom he trusts to deliver macros that are problem-free. What should his option be in the Trust Center? a. Enable all trusted macros b. Enable all macros c. Disable all macros except digitally signed macros d. Disable a macro with notification ANS: C

PTS: 1

REF: EX 433

TOP: Critical Thinking

46. After a year of work, Ted is taking coursework to become a VBA programmer. He is working on developing an application that contains macros. Which should his option be in the Trust Center? a. Enable all trusted macros b. Enable all macros c. Disable all macros except digitally signed macros d. Disable a macro with notification ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 7-2

REF: EX 432

TOP: Critical Thinking

Rina has just completed some coursework in Excel application writing and wants to begin developing her own macros to share more widely. She knows that she has to learn more about digital signatures in order to make her plan a reality.

47. What phrase could Rina use to best describe a digital signature? a. a merit badge c. an invitation to collaborate b. an indication of value d. a seal of approval ANS: D

PTS: 1

REF: EX 433

TOP: Critical Thinking

48. Based on what she has learned, which of the following would Rina say is a true statement about digital signatures? a. Adding a digital signature is the first step before you distribute a file. b. A digital signature is often used to identify the author of a workbook that contains macros. c. Before you can add a digital ID to a workbook, you need to obtain a digital signature. d. Digital certificates are usually issued by Microsoft.

ANS: B

PTS: 1

REF: EX 433

TOP: Critical Thinking

49. By digitally signing a workbook that contains a macro that she intends to publicly distribute, Rina assures her audience _____. a. of the identity of the user of the macro b. that the macro has not been altered since the digital signature was created c. that the macro will perform as expected d. that no data entry errors will stop the macro from running ANS: B

PTS: 1

REF: EX 433

TOP: Critical Thinking

50. Which of the following can Rina tell when she opens a digitally signed file? a. Who the author of the file is b. Whether the information in the file is correct c. Whether the macros in the workbook will produce accurate results d. Whether the macro will perform as expected ANS: A

PTS: 1

REF: EX 433

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 7-3 Juan tells you that your work in Excel will go a lot faster if you start using defined names instead of cell and range addresses. You believe him but you have been frustrated by defined names in the past because you did not know the rules for naming them and you have run into errors as a result. Juan offers to help you.

51. Which of the following is a true statement Juan can make about defined names as he is explaining the rules to you? a. The name can include letters, numbers, periods, and underscores b. To distinguish multiword names, use a hyphen between the words c. Defined names are case sensitive d. The name should start with a hash tag ANS: A

PTS: 1

REF: EX 395

TOP: Critical Thinking

52. Which of the following is an example of a valid defined name Juan can provide to you? a. .FY2010 c. FY2010

b. F-Y2010 ANS: D

d. FiscalYear2010 PTS: 1

REF: EX 395

TOP: Critical Thinking

COMPLETION 1. A(n) ____________________ is a spreadsheet written or tailored to meet the user’s specific needs.

ANS: Excel application PTS: 1

REF: EX 392

2. In addition to referring to cells by their cell references, it is easier to remember where different data is stored by assigning a meaningful, descriptive ____________________ to each cell or range.

ANS: name PTS: 1

REF: EX 394

3. One way to ensure that correct data is entered into a cell or range is to use the Excel data ____________________ feature.

ANS: validation PTS: 1

REF: EX 410

4. In the Allow list box in the Data Validation dialog box, the ____________________ option means that the cell will accept any value entered into the cell.

ANS: Any value PTS: 1

REF: EX 410

5. Input messages appear as ____________________ next to the active cell.

ANS: ScreenTips PTS: 1

REF: EX 413

6. The ____________________ alert, by default, rejects the invalid data, but allows you to override the rejection.

ANS: Warning PTS: 1

REF: EX 412

7. When you ____________________ a workbook, you control the ability users have to make changes to the file.

ANS: protect PTS: 1

REF: EX 423

8. A protected sheet can always be unprotected, but you can also require users to enter a(n) ____________________ before the protection is removed.

ANS: password PTS: 1

REF: EX 423

9. Protecting the ____________________ of a workbook prohibits users from moving, resizing, closing, or hiding parts of the Excel window.

ANS: windows PTS: 1

REF: EX 423

10. A(n) ____________________ is a text box that is attached to a specific cell in a worksheet.

ANS: comment PTS: 1

REF: EX 425

11. A(n) ____________________ is a series of stored commands that can be run whenever you need to perform a task.

ANS: macro PTS: 1

REF: EX 429

12. A macro ____________________ uses a program’s own macro programming language for distribution.

ANS: virus PTS: 1

REF: EX 431

13. You control how Excel handles suspect macros by setting Excel's ____________________.

ANS: security level PTS: 1

REF: EX 431

14. A digital ____________________ is often used to identify the author of a workbook that contains macros.

ANS: signature PTS: 1

REF: EX 433

15. A digital ____________________ is typically issued by a certificate authority.

ANS: certificate PTS: 1

REF: EX 433

ESSAY 1. List three advantages to using defined names instead of cell references.

ANS: Some advantages to using defined names instead of cell references include: • Names, such as TaxRate and TotalSales, are more descriptive than cell references, making it easier to remember what a cell or range contains.

• Names can be used in formulas, making it easier for users to understand the calculations being performed. For example, =GrossPay–Deductions is more understandable than =C15–C16.

• When you move a named cell or range within a worksheet, its name moves with it. Any formulas that contain the name automatically reference the new location.

• In a formula, referencing a named cell or range is the same as referencing the cell or range’s absolute reference. So, if you move a formula that includes a defined name, the reference remains pointed to the correct cell or range.

• By using defined names, you will often save time and everyone reviewing the worksheet will have a clearer understanding of what a formula is calculating.

PTS: 1

REF: EX 394

TOP: Critical Thinking

2. What are the steps for creating a validation rule?

ANS: In the Data Tools group on the DATA tab, click the Data Validation button. Click the Settings tab.

Click the Allow arrow, click the type of data allowed in the cell, and then enter the validation criteria for that data. Click the Input Message tab, and then enter a title and text for the input message. Click the Error Alert tab, and then, if necessary, click the Show error alert after invalid data is entered check box to insert a check mark. Select an alert style, and then enter the title and text for the error alert message. Click the OK button.

PTS: 1

REF: EX 415

TOP: Critical Thinking

3. Describe the four macro security settings.

ANS: Disable all macros without notification: All macros in all workbooks are disabled and no security alerts about macros are displayed. Use this setting if you do not want macros to run.

Disable all macros with notification: All macros in all workbooks are disabled, but security alerts appear when the workbook contains a macro. Use this default setting to choose on a case-by-case basis whether to run a macro.

Disable all macros except digitally signed macros: The same as the Disable a macro with notification setting, except any macro signed by a trusted publisher runs if you have already trusted the publisher.

Otherwise, security alerts appear when a workbook contains a macro.

Enable all macros: All macros in all workbooks run. Use this setting temporarily in such cases as when developing an application that contains macros. This setting is not recommended for regular use PTS: 1

REF: EX 431

TOP: Critical Thinking

Excel Tutorial 8: Working with Advanced Functions TRUE/FALSE 1. All the logical conditions listed in an AND function must be true for the AND function to return a TRUE value.

ANS: T

PTS: 1

REF: EX 470

2. If a formula is in a calculated table column, you need to make a change in only one cell and the change will automatically be copied to all the cells in the column.

ANS: T

PTS: 1

REF: EX 467

3. If you need to modify the formula in a calculated column, you edit the formula in one cell of the column and then you modify the formulas in the remaining cells in that table column.

ANS: F

PTS: 1

REF: EX 467

4. A formula that includes a structured reference can be fully qualified or unqualified.

ANS: T

PTS: 1

REF: EX 477

5. The ORERROR function can determine if a cell contains an error value and then display the message you choose.

ANS: F

PTS: 1

REF: EX 500

6. Nesting IF functions will allow for three or more outcomes.

ANS: T

PTS: 1

REF: EX 484

7. Two common categories of formula errors in Excel are syntax errors and logic errors.

ANS: T

PTS: 1

REF: EX 486

8. According to the dialog box in the accompanying figure, the MedicalRates table is the one from which the data in your funtion argument will be obtained.

ANS: T

PTS: 1

REF: EX 492

9. According to the dialog box in the accompanying figure, the column in the MedicalRates lookup table that stores the value to be returned is the first column.

ANS: F

PTS: 1

REF: EX 492

10. The COUNTAS function counts the number of cells within a range that meet multiple criteria.

ANS: F

PTS: 1

REF: EX 512

MODIFIED TRUE/FALSE 1. The IF function evaluates a(n) single condition. _________________________ ANS: T

PTS: 1

REF: EX 470

2. In a fully qualified structured reference, the table name follows the column qualifier. _________________________ ANS: F precedes comes before PTS: 1

REF: EX 477

3. In the DATEDIF function, the first date argument is the latest date. _________________________ ANS: F, earliest PTS: 1

REF: EX 474

4. You cannot nest more than one IF function to test for multiple outcomes. _________________________ ANS: F, can PTS: 1

REF: EX 484

5. If any logical condition listed in the function is true, the AND function returns a TRUE value. _________________________ ANS: F, OR PTS: 1

REF: EX 475

6. Duplicate value highlighting helps to verify that columns of data have unique entries. _________________________ ANS: T

PTS: 1

REF: EX 506

7. In most cases, when manually creating a conditional formatting formula, the formula should use absolute references and point to the first row of data in a table. _________________________ ANS: F, relative PTS: 1

REF: EX 511

8. An error value begins with a(n) number sign followed by an error name, which indicates the type of error. _________________________ ANS: T

PTS: 1

REF: EX 500

9. You can calculate the number of cells in a range that match criteria you specify using the SUMIF function. _________________________ ANS: F, COUNTIF PTS: 1

REF: EX 514

10. The VLOOKUP function searches vertically down the first column of the lookup table for the value you entered, and then retrieves the corresponding value from another column of the table. _________________________ ANS: T

PTS: 1

REF: EX 491

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. SUMIF f. AVERAGE b. VLOOKUP g. HLOOKUP c. COUNTIF h. TRANSPOSE d. conditional formatting i. logical condition e. DATEDIF j. SUM 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Searches down the first column of the lookup table Adds the values in a range that meet criteria you specify Searches across the first row of the lookup table Calculates the difference between two dates Calculates the number of cells in a range that match criteria you specify using this function Used to change the orientation of a range Adds the values in a range Calculates the average of the values in a range Changes a cell’s formatting when its contents match a specified condition Expression that returns either a TRUE value or a FALSE value

1.

2.

3.

4.

5.

6.

ANS: B ANS: A ANS: G ANS: E ANS: C ANS: H

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

EX 491 EX 514 EX 497 EX 474 EX 512 EX 517

7.

8.

9.

10.

ANS: J ANS: F ANS: D ANS: I

PTS: PTS: PTS: PTS:

1 1 1 1

REF: REF: REF: REF:

EX 511 EX 511 EX 506 EX 466

MULTIPLE CHOICE 1. When Excel automatically fills the rest of a table column with a formula entered into an empty table column, this is referred to as a(n) _____ column. a. concatenated c. autofilled b. calculated d. custom ANS: B

PTS: 1

REF: EX 467

2. The _____ function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if all the logical conditions are FALSE. a. IF c. OR b. AND d. THIS ANS: C

PTS: 1

REF: EX 475

3. To get the effect of a calculated column in a range of data, you must copy and paste the formula or use the _____ feature. a. Concatenation c. AutoFill b. Merged d. Indexed ANS: C

PTS: 1

REF: EX 467

4. If you edit a cell in a calculated column so it is no longer consistent with the other formulas in the column, a _____ appears in the upper-left corner of the cell. a. red circle c. red square b. blue square d. green triangle ANS: D

PTS: 1

REF: EX 467

5. The _____ function is a logical function that evaluates a condition and then returns one value if the condition is true and another value if the condition is false. a. THIS c. WHEN b. IF d. NOT ANS: B

PTS: 1

REF: EX 468

6. _____ just above the header row of an Excel table to select the column data but not the header. a. Click c. Triple-click b. Double-click d. Right-click ANS: A

PTS: 1

REF: EX 473

7. You could test two or more conditions and determine whether all conditions are true using the _____ function. a. OR c. AND b. IF d. THIS ANS: C

PTS: 1

REF: EX 470

8. If all of the logical conditions are true, the AND function returns the logical value _____. a. FALSE c. TRUE b. ON d. OFF ANS: C

PTS: 1

REF: EX 470

9. You can include up to _____ logical conditions in the AND function. a. 25 c. 225 b. 50 d. 255 ANS: D

PTS: 1

REF: EX 470

10. The _____ function calculates the difference between two dates and shows the result in months, days, or years. a. nested IF c. DATEDIF b. CALIF d. DATE ANS: C

PTS: 1

REF: EX 474

11. With the DATEDIF function, the _____ interval code means months. a. m c. mon b. mo d. mth ANS: A

PTS: 1

REF: EX 474

12. _____ references make it easier to create formulas that use portions or all of an Excel table because the names of headers are usually simpler to identify than cell addresses. a. Indexed c. Key b. Structured d. Logical ANS: B

PTS: 1

REF: EX 477

13. When you want to reference an entire column of data in a table, you create a column _____. a. label c. variable b. name d. qualifier ANS: D

PTS: 1

REF: EX 477

14. If you are not sure of a table’s name, click in the table, click the _____ tab on the Ribbon, and then click the Table Name box in the Properties group. a. LAYOUT c. TABLE TOOLS DESIGN b. FORMAT d. STRUCTURED REFERENCES ANS: C

PTS: 1

REF: EX 477

15. When you create a calculated column, you can use _____ references to create the formula. a. indexed c. key b. structured d. logical ANS: B

PTS: 1

REF: EX 477

16. If you are creating a calculated column or formula within an Excel table, you can use the _____ structured reference in the formula. a. basic c. qualified b. advanced d. restricted ANS: C

PTS: 1

REF: EX 478

17. You can _____ IF functions to allow for three or more outcomes. a. loop c. nest b. cycle d. link ANS: C

PTS: 1

REF: EX 484

18. Given the flowchart in the accompanying figure, the Perf Rating of _____ yields a Bonus value of 2500. a. 1 c. 0 b. 2 d. 7500 ANS: B

PTS: 1

REF: EX 486

19. Given the flowchart in the accompanying figure, the Perf Rating of _____ yields a Bonus value of 0. a. 1 c. 2500 b. 2 d. 7500 ANS: A

PTS: 1

REF: EX 486

20. The complete formula that corresponds to the flowchart in the accompanying figure is _____. a. =IF([Perf Rating]=2,2500,IF([Perf Rating]=1,2500,7500)) b. =AND([Perf Rating]=2,2500,AND([Perf Rating]=1,2500,7500)) c. =IF([Perf Rating]=1,0,IF([Perf Rating]=2,2500,7500)) d. =AND([Perf Rating]=1,2500,AND([Perf Rating]=2,0,7500)) ANS: C

PTS: 1

REF: EX 486

21. Lookup _____ allow you to use tables of data to “look up” values and insert them in another worksheet location. a. functions c. equations b. formulas d. calculations ANS: A

PTS: 1

REF: EX 489

22. The _____ function searches vertically down the first column of the lookup table. a. VLOOK c. HLOOK b. VLOOKUP d. HLOOKUP ANS: B

PTS: 1

REF: EX 491

23. When using VLOOKUP, the _____argument is optional. a. lookup_value c. range_lookup

b. table_array ANS: C

d. col_index PTS: 1

REF: EX 491

24. Using the VLOOKUP function, an exact match lookup occurs when the value entered must match one of the values in _____ of the lookup table. a. the first column c. the first row b. any column d. any row ANS: A

PTS: 1

REF: EX 491

25. If you see _____ as the result of a VLOOKUP formula, you might have entered a letter for the col_index_num instead of a number. a. #NULL c. #NAME? b. #REF? d. #DIV/0 ANS: C

PTS: 1

REF: EX 492

26. You can use a _____ function to return a value from a table that is based on an approximate match lookup. a. VLOOKUP c. VMATCH b. VAVERAGE d. VAPPROXIMATE ANS: A

PTS: 1

REF: EX 493

27. An error value begins with a(n) _____ followed by an error name that indicates the type of error. a. number sign c. exclamation point b. asterisk d. plus sign ANS: A

PTS: 1

REF: EX 500

28. The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____. a. named c. saved b. organized d. opened ANS: B

PTS: 1

REF: EX 497

29. The _____ error value means that a value is not available to a function or formula. a. #NAME? c. #NULL! b. #N/A d. #NUM! ANS: B

PTS: 1

REF: EX 498

30. The Conditional Formatting button provides access to the _____. a. Replicate Values conditional format c. Criteria option b. Manage Rules option d. Organize Rules option ANS: B

PTS: 1

REF: EX 508

31. You can edit existing conditional formatting rules from the Conditional Formatting _____ dialog box.

Click the Edit Rule button and make the appropriate changes. a. Rules Editor c. Editor b. Rules Manager d. Manager ANS: B

PTS: 1

REF: EX 508

32. The _____ function calculates the number of cells in a range that match criteria you specify. a. NUMBERIF c. NUMBERAS b. COUNTIF d. COUNTAS ANS: B

PTS: 1

REF: EX 512

33. The _____ function adds the values in a range that match criteria you specify. a. ADDIF c. SUMIF b. ADDAS d. SUMAS ANS: C

PTS: 1

REF: EX 514

34. The _____ function calculates the average of values in a range that match criteria you specify. a. AVERAGEAS c. AVERAGEAND b. AVERAGEOR d. AVERAGEIF ANS: D

PTS: 1

REF: EX 515

35. To highlight duplicate values, in the Styles group on the _____ tab, click the Conditional Formatting button, point to Highlight Cells Rules, and then click Duplicate Values. a. HOME c. FORMULAS b. PAGE LAYOUT d. DATA ANS: A

PTS: 1

REF: EX 506

36. To change the background fill color of duplicate values, in the _____ group on the HOME tab, click the Conditional Formatting button, and then click Manage Rules. a. Format c. Styles b. Options d. Tools ANS: C

PTS: 1

REF: EX 507

37. Each time you create a conditional format, you are defining a conditional formatting _____. a. theme c. condition b. object d. rule ANS: D

PTS: 1

REF: EX 508

38. You can edit existing conditional formatting _____ from the Conditional Formatting Rules Manager dialog box. a. themes c. tables b. objects d. rules ANS: D

PTS: 1

REF: EX 508

39. For conditional formatting, a formula must be in the form of a(n) _____ test that results in a true or false value. a. conditional c. logical b. hypothetical d. approximate ANS: C

PTS: 1

REF: EX 511

40. In the Edit Formatting Rule dialog box, click the _____ button to open the Format Cells dialog box. a. Format Cells c. Format b. Options d. Cell Tools ANS: C

PTS: 1

REF: EX 509

41. The COUNTIF function is sometimes referred to as a(n) _____ count. a. network c. conditional b. criterion d. basic ANS: C

PTS: 1

REF: EX 512

42. You can use _____ to reference cells within an Excel table. a. unstructured references c. macros b. cell and range addresses d. links ANS: B

PTS: 1

REF: EX 512

Case-Based Critical Thinking Questions Case 8-1 Evaluate the formula =AND(G2=“FT”,M2>=1) based upon the information provided.

43. What value is returned assuming that G2 contains the value PT and M2 contains the value 2? a. TRUE c. PT b. FALSE d. PT, 2 ANS: B

PTS: 1

REF: EX 472

TOP: Critical Thinking

44. What value is returned assuming that G2 contains the value FT and M2 contains the value 2? a. TRUE c. PT b. FALSE d. PT, 2 ANS: A

PTS: 1

REF: EX 472

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 8-2 Your boss in the human resources department asks you to write a function that calculates the length of time that employees have been with the firm, using the DATEDIF function. Assume that C2 contains the hire date for a given employee and that cell $AE$2 contains the cut-off date against which to compare the hire date to determine length of service.

45. For one service recognition program, you need to calculate the length of service in complete months.

What is the correct formula? a. =DATEDIF(C2, $AE$2, “m”) c. =DATEDIF($AE$2, C2, “mth”) b. =DATEDIF($AE$2, C2, “month”) d. =DATEDIF(C2, $AE$2, “mon”) ANS: A

PTS: 1

REF: EX 474

TOP: Critical Thinking

46. For a separate service recognition program, you need to calculate the length of service in complete years. What is the correct formula? a. =DATEDIF(C2, $AE$2, “yr”) c. =DATEDIF($AE$2, C2, “yr”) b. =DATEDIF($AE$2, C2, “year”) d. =DATEDIF(C2, $AE$2, “y”) ANS: D

PTS: 1

REF: EX 474

TOP: Critical Thinking

47. On a lark, for a year-end trivia contest, your boss wants you to calculate the length of service in complete days. What is the correct formula? a. =DATEDIF(C2, $AE$2, “today”) c. =DATEDIF($AE$2, C2, “day”) b. =DATEDIF($AE$2, C2, “dy”) d. =DATEDIF(C2, $AE$2, “d”)

ANS: D

PTS: 1

REF: EX 474

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 8-3 You have been using a variety of nested IF functions to calculate costs in your spreadsheet when a colleague stops by and comments on what you have been working on, suggesting that there is an easier approach to use.

48. Which of the following is an effective alternative for the use of extensive nested IF functions? a. compare table c. lookup table b. function table d. case table ANS: C

PTS: 1

REF: EX 489

TOP: Critical Thinking

49. You implement the VLOOKUP formula at your colleague’s suggestion, but you get the #VALUE! error as the result of the formula. Which of the following is likely true? a. You might have entered a table name for the table_array value instead of the cell reference of the lookup table. b. You might have indicated that the compare values are a range of values instead of an exact match. c. You might have entered a number for the col_index_num instead of a letter. d. You might have entered a letter for the col_index_num instead of a number.

ANS: D

PTS: 1

REF: EX 492

TOP: Critical Thinking

50. If you omit the range_lookup value from a LOOKUP function, which of the following is true? a. The function returns the #NAME? error value. b. Your function can only search the first column of the lookup table. c. It is considered an approximate match. d. It is considered an exact match.

ANS: C

PTS: 1

REF: EX 493

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 8-4 The following formula is used to determine Dean’s List status: =AND(B1>=12,B2>3.25).

Use the information provided to evaluate the formula.

51. Assuming that B1 contains 12 and B2 contains 3.25, then _____. a. the formula indicates that the student is on the Dean’s List b. the formula indicates that the student is NOT on the Dean’s List c. the formula returns a value of 0 d. the formula returns an error message ANS: B

PTS: 1

REF: EX 472

TOP: Critical Thinking

52. Assuming that B1 contains 15 and B2 contains 3.5, then _______. a. the formula indicates that the student is on the Dean’s List b. the formula indicates that the student is NOT on the Dean’s List c. the formula returns a value of 0 d. the formula returns an error message ANS: A

PTS: 1

REF: EX 472

TOP: Critical Thinking

COMPLETION 1. The ____________________ function is a logical function that returns a TRUE value if all the logical conditions are true and a FALSE value if any or all of the logical conditions are false.

ANS: AND PTS: 1

REF: EX 470

2. When you create a formula that references all or parts of an Excel table, you can replace the specific cell or range address with a(n) ____________________ reference, the actual table name or column header.

ANS: structured PTS: 1

REF: EX 477

3. The ____________________ function calculates the difference between two dates and shows the result in months, days, or years.

ANS: DATEDIF PTS: 1

REF: EX 474

4. A(n) ____________________ IF function is when one IF function is placed inside another IF function to test an additional condition.

ANS: nested PTS: 1

REF: EX 484

5. The ____________________ function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if the logical conditions are false.

ANS: OR PTS: 1

REF: EX 475

6. The ____________________ function adds the values in a range that meet criteria you specify.

ANS: SUMIF PTS: 1

REF: EX 514

7. The ____________________ function is used to change the orientation of a range—that is, return a vertical range of cells as a horizontal range, or vice versa.

ANS: TRANSPOSE PTS: 1

REF: EX 517

8. To retrieve a particular value from a lookup table, a(n) ____________________ needs to match the compare values.

ANS: lookup value PTS: 1

REF: EX 491

9. The ____________________ function searches vertically down a lookup table and is used when the compare values are stored in the first column of the lookup table.

ANS: VLOOKUP PTS: 1

REF: EX 491

10. The ____________________ function searches horizontally across a lookup table and is used when the compare values are stored in the first row of the lookup table.

ANS: HLOOKUP PTS: 1

REF: EX 497

11. ____________________ formatting changes a cell’s formatting when its contents match a specified condition.

ANS: Conditional PTS: 1

REF: EX 508

12. A(n) ____________________ specifies the type of condition, the type of formatting when that condition occurs, and the cell or range the formatting is applied to.

ANS: rule conditional formatting rule PTS: 1

REF: EX 508

13. The ____________________ error value means that the formula or function contains a number divided by 0.

ANS: #DIV/0!

PTS: 1

REF: EX 503

14. A(n) ____________________ match lookup occurs when the lookup value is found within a range of numbers in the first column (or row) of the lookup table.

ANS: approximate PTS: 1

REF: EX 489

15. The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

ANS: IFERROR PTS: 1

REF: EX 500

ESSAY 1. What are the guidelines to keep in mind when creating fields in an Excel table?

ANS: Excel tables should be easy to use as well as understand. This requires labeling and entering data in a way that effectively communicates a table’s content or purpose. If a field is entered in a way that is difficult to use and understand, it becomes more difficult to find and present data in a meaningful way.

To effectively communicate a table’s function, keep the following guidelines in mind when creating fields in an Excel table: • Create fields that require the least maintenance. For example, hire date and birth date require no maintenance after they are entered, unlike age and years of service, whose values change each year. If you need to know the specific age or years of service, use calculations to determine them based on values in the Hire Date and Birth Date columns.

• Store the smallest unit of data possible in a field. For example, use three separate fields for City, State, and Zip code rather than one field. Using separate field for each unit of data enables you to sort or filter each field. If you want to display data from two or more fields in one column, you can use a formula to reference the City, State, and Zip code columns. For example, you can use the & operator to combine he city, state, and zip code in one cell as follows: =C2&D2&E2 • Apply a text format to fields with numerical text data. For example, formatting fields such as zip codes and Social Security numbers as text ensures that leading zeros are stored as part of the data.

Otherwise, the zip code 02892 is stored as a number and displayed as 2892.

PTS: 1

REF: EX 467

TOP: Critical Thinking

2. Discuss strategies for checking formulas for matching parentheses.

ANS: A common problem when creating formulas is mismatched parentheses. As you write a formula, you should verify that you enclosed the correct argument, function, or term within the parentheses of the formula you are creating. This is especially important when you develop a complex formula that includes many parentheses, such as a nested IF function. Excel color-codes the parentheses as you build a formula so you can quickly determine whether you have complete pairs of them. You can also verify that the formula includes matching pairs of parentheses by selecting the cell with the formula and then clicking in the formula bar. Press the right arrow key to move the insertion point through the formula one character at a time. When the insertion point moves across one parenthesis, its matching parenthesis is also highlighted briefly. This color coding helps you ensure that all parentheses in a formula are paired (opening and closing parentheses).

PTS: 1

REF: EX 489

TOP: Critical Thinking

3. Sometimes the built-in conditional formatting rules do not apply the formatting you need. In these instances, you may be able to create a conditional formatting rule based on a formula that uses a logical expression to describe the condition you want. Give an example of when you would use this type of formula, and list two guidelines you must follow when creating this type of formula.

ANS:

Example (Answers may vary): You can create a formula that uses conditional formatting to compare cells in different columns or to highlight an entire row.

When you create the formula, keep in mind the following guidelines (Students should include two of these in their answer): - The formula must start with an equal sign.

- The formula must be in the form of a logical test that results in a True or False value.

- In most cases, the formula should use relative references and point to the first row of data in the table. If the formula references a cell or range outside the table, use an absolute reference.

-After you create the formula, enter test values to ensure the conditional formatting works in all situations that you intended.

PTS: 1

REF: EX 511

TOP: Critical Thinking

Excel Tutorial 9: Exploring Financial Tools and Functions TRUE/FALSE 1. The fv argument is required in the PMT function.

ANS: F

PTS: 1

REF: EX 532

2. Cash flow is concerned with the direction of money as it moves in and out of a company.

ANS: T

PTS: 1

REF: EX 533

3. If the NPER function returns #NUM!, the loan cannot be repaid in any length of time.

ANS: T

PTS: 1

REF: EX 537

4. As shown in the accompanying figure, earlier payment periods apply more of the payment toward the principal.

ANS: F

PTS: 1

REF: EX 540

5. In an amortization schedule, use relative references to refer to the original loan conditions and use absolute references to refer to information about specific payment periods.

ANS: F

PTS: 1

REF: EX 541

6. To calculate a declining balance depreciation, use the DVB function.

ANS: F

PTS: 1

REF: EX 559

7. The major advantage of the payback period is that it takes into account the time value of money.

ANS: F

PTS: 1

REF: EX 571

8. The list of values in the XNPV function must include at least one positive cash flow and one negative cash flow.

ANS: T

PTS: 1

REF: EX 577

9. The tracer arrow provides a visual clue to the source of the error.

ANS: T

PTS: 1

REF: EX 582

10. When you change a cell’s value, a Watch Window allows you to view the impact of the change on widely scattered dependent cells.

ANS: T

PTS: 1

REF: EX 585

MODIFIED TRUE/FALSE 1. The FV function calculates the full value of an investment or loan. _________________________ ANS: F, future PTS: 1

REF: EX 534

2. If you don’t include a pv value, Excel assumes a future value of 1. _________________________ ANS: F 0 zero PTS: 1

REF: EX 534

3. A(n) negative cash flow occurs when cash is going away from the investor. _________________________ ANS: T

PTS: 1

REF: EX 533

4. If you plan to invest money for 18 months, the value of the nper argument in the PMT function is 18. _________________________ ANS: T

PTS: 1

REF: EX 534

5. When using the PMT function to calculate monthly loan payments, the interest rate and the number of payments should be based on the interest rate per year and the total months to pay off the loan. _________________________ ANS: F, month PTS: 1

REF: EX 546

6. You can extrapolate a series of values from the Series dialog box. _________________________ ANS: T

PTS: 1

REF: EX 556

7. The function DDB is used to calculate a(n) declining balance depreciation. _________________________ ANS: F double-declining double declining PTS: 1

REF: EX 562

8. The possibility that the entire transaction will fail, resulting in a loss of the initial investment is called the chance. _________________________ ANS: F, risk PTS: 1

REF: EX 572

9. At higher rates of return, the net present value of the investment goes up. _________________________ ANS: F, down PTS: 1

REF: EX 574

10. A(n) dependent cell is one whose value depends on the values of other cells in the workbook. _________________________ ANS: T

PTS: 1

REF: EX 581

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. PPMT function f. RATE function b. rate of return g. PMT function c. IPMT function h. XIRR function d. IRR i. FV function e. PV function j. NPER function 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Calculates the payment in each period required to pay off a loan or reach an investment goal Calculates the number of payments required to pay off a loan or reach an investment goal Calculates the per period interest rate required to pay off a loan or reach an investment goal Calculates the amount of a loan payment applied to the principal Calculates the amount of a loan payment used to pay off the interest Calculates the present value of a series of future cash flows Calculates the internal rate of return for a series of unevenly spaced payments An interest rate that compares the value of current dollars to future dollars from a series of cash flows Calculates a loan’s future value Return rate for a series of future cash flows that will result in a net present value of zero

1.

2.

3.

4.

ANS: G ANS: J ANS: F ANS: A

PTS: PTS: PTS: PTS:

1 1 1 1

REF: REF: REF: REF:

EX 532 EX 536 EX 568 EX 541

5.

6.

7.

8.

9.

10.

ANS: C ANS: E ANS: H ANS: B ANS: I ANS: D

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

EX 541 EX 537 EX 578 EX 566 EX 534 EX 575

MULTIPLE CHOICE 1. The _______ function calculates the amount paid into an investment or loan during each payment period. a. NPER c. PMT b. RATE d. INT ANS: C

PTS: 1

REF: EX 532

2. The _______ function calculates the present value of an investment or loan. a. PRES c. NPER b. PV d. FV ANS: B

PTS: 1

REF: EX 537

3. The _______ function calculates the interest rate charged or received during each payment period. a. RATE c. NPER b. PV d. FV ANS: A

PTS: 1

REF: EX 567

4. The _______ function calculates the number of payment periods in an investment or loan. a. RATE c. NPER b. PV d. FV ANS: C

PTS: 1

REF: EX 536

5. To calculate the number of quarterly payments required to pay off a loan, use the _______ function. a. PMT c. FV b. PV d. NPER ANS: D

PTS: 1

REF: EX 536

6. Financial functions format calculated values as currency, with _______. a. positive cash flow appearing in red font b. positive cash flow enclosed in parentheses c. negative cash flow appearing in green font d. negative cash flow appearing in red font ANS: D

PTS: 1

REF: EX 532

7. To calculate the number of montly payments required to pay off a loan or meet an investment goal, use the _______ function. a. PMT c. PPMT b. FV d. NPER ANS: D

PTS: 1

REF: EX 536

8. With the PMT function, the type argument is _______ when the payments are made at the end of each period. a. 0 c. a b. 1 d. -1 ANS: A

PTS: 1

REF: EX 532

9. Which of the following shows the syntax of the NPER function? a. =NPER(pmt, pv, [fv=0], [type=0]) b. =NPER(rate, pmt, [fv=0], [type=0]) c. =NPER(rate, pmt, pv [, fv=0] [, type=0]) d. =NPER(pmt, rate, pv, [fv=0], [type=0]) ANS: C

PTS: 1

REF: EX 536

10. To calculate the annual rate, you must _______ the value returned by the RATE function by the number of payments per year. a. add c. multiply b. subtract d. divide ANS: C

PTS: 1

REF: EX 568

11. When you borrow money, the money you receive represents a _______ cash flow. a. positive c. fixed b. negative d. neutral ANS: A

PTS: 1

REF: EX 533

12. As shown in the accompanying figure, later payment periods apply more of the payment toward the _______. a. index c. interest b. interest rate d. principal ANS: D

PTS: 1

REF: EX 540

13. The _______ function returns the amount of a particular payment that is used to pay the interest on the loan.

a. PMT b. IPMT ANS: B

c. INPMT d. NPMT PTS: 1

REF: EX 541

14. In the function =CUMIPMT(rate, nper, pv, start, end, type), it is _______ that defines whether the payments are made at the beginning or end of each period. a. rate c. end b. nper d. type ANS: D

PTS: 1

REF: EX 543

15. Most Excel financial functions require a particular _______ to the cash flow to return the correct value. a. index c. orientation b. valence d. direction ANS: D

PTS: 1

REF: EX 546

16. The item marked 1 in the accompanying figure represents the _______ trend. a. periodic c. orthogonal b. growth d. linear ANS: D

PTS: 1

REF: EX 551

17. The item marked 2 in the accompanying figure represents the _______ trend. a. periodic c. orthogonal b. growth d. linear ANS: B

PTS: 1

REF: EX 551

18. As shown in the accompanying figure, a(n) _______ trend appears as a straight line. a. periodic c. orthogonal b. growth d. linear ANS: D

PTS: 1

REF: EX 550

19. As shown in the accompanying figure, in a(n) _______ trend, the values do not change by a constant amount. a. periodic c. orthogonal

b. growth ANS: B

d. linear PTS: 1

REF: EX 550

20. As shown in the accompanying figure, when plotted, a(n) _______ trend displays the greatest increases near the end of the series. a. periodic c. orthogonal b. growth d. linear ANS: B

PTS: 1

REF: EX 550

21. In a(n) _______ trend, the values change by a constant amount. a. linear c. periodic b. growth d. orthogonal ANS: A

PTS: 1

REF: EX 550

22. To _______ a series, the cells between the first and last cells in the series must be blank. a. extrapolate c. integrate b. concatenate d. interpolate ANS: D

PTS: 1

REF: EX 551

23. To calculate a series of values from a starting point without a defined ending point, you have to _______ the values. a. interpolate c. assess b. extrapolate d. plot ANS: B

PTS: 1

REF: EX 555

24. When you extrapolate a series, the _______ value represents the amount that each value is increased or multiplied as the series is extended. a. Increase c. Extension b. Step d. Advance ANS: B

PTS: 1

REF: EX 555

25. To project a trend that decreases rather than increases, use a step value of less than _______ for a linear trend. a. -1 c. 1 b. 0 d. 2 ANS: B

PTS: 1

REF: EX 556

26. The process of allocating the original cost of an investment over the years of use is known as _______. a. interest c. depreciation b. balance d. amortization ANS: C

PTS: 1

REF: EX 557

27. The _______ function takes only these arguments: cost, salvage, and life. a. SLN c. DB b. SYD d. VDB ANS: A

PTS: 1

REF: EX 562

28. The item marked 1 in the accompanying figure is the _______ value. a. sum c. initial asset b. declining balance d. salvage ANS: C

PTS: 1

REF: EX 558

29. The item marked 2 in the accompanying figure is the _______ value. a. sum c. initial asset b. declining balance d. salvage ANS: D

PTS: 1

REF: EX 558

30. The DB function used to calculate the declining balance depreciation has the following syntax: _______. a. =DB(salvage, life, period [month]) b. =DB(cost, salvage, period [month]) c. =DB(salvage, cost, life, period [month]) d. =DB(cost, salvage, life, period [month]) ANS: D

PTS: 1

REF: EX 559

31. Excel supports _______ function(s) to calculate depreciation under different assumptions. a. one c. five b. two d. seven ANS: C

PTS: 1

REF: EX 562

32. The _______ function returns the sum-of-years’ digit depreciation that results in a more accelerated depreciation than straight-line depreciation but less than declining balance depreciation. a. DB c. SYD b. SLN d. VDB ANS: C

PTS: 1

REF: EX 562

33. The _______ period is a quick method of projecting the value of an investment. a. investment c. cumulative b. payback d. time-value ANS: B

PTS: 1

REF: EX 571

34. To calculate the internal rate of return, use the _______ function with the cost of the initial investment as the first cash flow value in a series. a. IRR c. NPV b. XNPV d. XIRR ANS: A

PTS: 1

REF: EX 575

35. The NPV function is used in calculating the net present value of a transaction, which is the _______ value of all the cash inflows and outflows in the investment adjusted by the time value of money. a. current c. future b. projected d. total ANS: A

PTS: 1

REF: EX 572

36. The point at which the net present value of an investment equals _______ is the internal rate of return. a. -1 c. 0.1 b. 0 d. 1 ANS: B

PTS: 1

REF: EX 575

37. Use the XNPV and XIRR functions for cash flows that appear _______. a. with a regular pattern c. at evenly spaced intervals b. at unevenly spaced intervals d. to be cyclical ANS: B

PTS: 1

REF: EX 577

38. A(n) _______ worksheet projects the company’s expected assets, liabilities, and equity. a. Asset Report c. Balance Sheet b. Income Statement d. Transactions ANS: C

PTS: 1

REF: EX 580

39. If active cell D12 contains the formula =D10+D11, cells D10 and D11 are _______ cells for D12. a. feeder c. precedent b. dependent d. formula ANS: C

PTS: 1

REF: EX 581

40. After you select a cell with an error indicator, you can click the Error Checking _______ to display additional information about the possible error and determine if further action is needed. a. button c. ScreenTip b. icon d. menu ANS: A

PTS: 1

REF: EX 581

41. A(n) _______ arrow provides a visual clue to the relationship between two cells by pointing from the precedent cell to the dependent cell. a. tracer c. index b. outline d. key ANS: A

PTS: 1

REF: EX 582

42. To evaluate a formula, you select the cell and then click the _______ button from the Formula Auditing group on the FORMULAS tab. a. Define Formula c. Check Function b. Select Formula d. Evaluate Formula

ANS: D

PTS: 1

REF: EX 584

Case-Based Critical Thinking Questions Case 9-1 Charles is having a lot of problems with errors in a very complicated spreadsheet that he inherited from a colleague, and he turns to another co-worker, Seymour, for tips on how to trace errors in the sheet.

43. Seymour tells Charles that all error values begin with the same symbol, so they can be easily identified as errors. What is the symbol? a. # c. ^ b. & d. % ANS: A

PTS: 1

REF: EX 580

TOP: Critical Thinking

44. If Charles sees which of the following, Seymour explains, there is a mistyped function name in the sheet. a. #FNCT? c. #NAME? b. #FORM? d. ##### ANS: C

PTS: 1

REF: EX 581

TOP: Critical Thinking

45. If Charles sees red tracer arrows that means there is an error in the value being received from the precedent cell or passed to the _______ cell. a. child c. dependent b. precedent d. contingent ANS: C

PTS: 1

REF: EX 582

TOP: Critical Thinking

46. If in Charles’s sheet, cell A4 is a precedent cell for cell B7, what kind of a cell is B7? a. contingent cell c. antecedent cell b. dependent cell d. indicated cell ANS: B

PTS: 1

REF: EX 582

TOP: Critical Thinking

47. Seymour tells Charles to search all of the error values until he reaches one that lacks something, and that is the source of the error values in the dependent cells. What does that error value lack? a. contingencies c. antecedents b. dependencies d. precedents ANS: D

PTS: 1

REF: EX 583

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 9-2 Skylar is studying for her midterm exam in her accounting class. Her study partner Lisa is her go-to person for help with calculating depreciation.

48. Which of the following functions does Lisa say is used to calculate a straight-line depreciation? a. DB c. SYD b. SLN d. DDB ANS: B

PTS: 1

REF: EX 562

TOP: Critical Thinking

49. Which of the following functions does Lisa say is used to calculate a declining balance depreciation?

a. DB b. SLN ANS: A

c. SYD d. DDB PTS: 1

REF: EX 562

TOP: Critical Thinking

50. Which of the following functions does Lisa say is used to calculate a sum-of-years’ digit depreciation? a. DB c. SYD b. SLN d. DDB ANS: C

PTS: 1

REF: EX 562

TOP: Critical Thinking

51. Which of the following functions does Lisa say is used to calculate a double-declining balance depreciation? a. DDB c. SYD b. SLN d. VBD ANS: A

PTS: 1

REF: EX 562

TOP: Critical Thinking

52. Which of the following functions does Lisa say is used to calculate a depreciation if it is variable? a. DB c. SYD b. SLN d. VDB ANS: D

PTS: 1

REF: EX 562

TOP: Critical Thinking

COMPLETION 1. The ____________________ function calculates the number of payment periods in an investment or loan.

ANS: NPER PTS: 1

REF: EX 536

2. A(n) ____________________ cash flow occurs whenever money is flowing into the account.

ANS: positive PTS: 1

REF: EX 533

3. A(n) ____________________ cash flow occurs whenever money is flowing out of the account.

ANS: negative PTS: 1

REF: EX 533

4. The PMT value is also known as the ____________________ payment.

ANS: periodic PTS: 1

REF: EX 536

5. To determine the interest rate needed to reach your savings goal, you can use the ____________________ function.

ANS: RATE PTS: 1

REF: EX 568

6. The ____________________ function calculates the amount of a loan payment used to pay off the principal of the loan.

ANS: PPMT PTS: 1

REF: EX 541

7. A(n) ____________________ provides details about each loan payment, specifying how much of the payment is devoted toward interest and how much toward repaying the principal.

ANS: amortization schedule PTS: 1

REF: EX 540

8. A(n) ____________________ is also known as a profit and loss statement.

ANS: income statement PTS: 1

REF: EX 550

9. If you know the beginning and ending values in a series and the series trend, you can fill in the missing values through ____________________.

ANS: interpolation PTS: 1

REF: EX 551

10. When you extrapolate a series, you do not have to specify a(n) ____________________ value.

ANS: stopping ending final PTS: 1

REF: EX 555

11. The financial definition of ____________________ is the difference between the present value of future cash flows and the current cost of the initial investment.

ANS: net present value PTS: 1

REF: EX 572

12. You can use the ____________________ Window to view the impact of a changing value in one worksheet on the cell values in the other cells.

ANS: Watch PTS: 1

REF: EX 585

13. With ____________________ the asset loses value by equal amounts each year until it reaches the salvage value at the end of its useful life.

ANS: straight-line depreciation SLN PTS: 1

REF: EX 558

14. One simple measure of the return from an investment is the ____________________ period, which is the length of time required for an investment to recover its initial cost.

ANS: payback PTS: 1

REF: EX 569

15. A red tracer arrow indicates that an error has been received from the ____________________ cell or passed to the dependent cell.

ANS: precedent PTS: 1

REF: EX 582

ESSAY 1. Describe the steps to interpolate a series of values between starting and ending values.

ANS: Select the range with the first cell containing the starting value, blank cells for middle values, and the last cell containing the ending value. In the Editing group on the HOME tab, click the Fill button, and then click Series. Specify whether the series is organized in rows or columns and the type of series to interpolate. Check the Trend check box. Click the OK button to insert the interpolated series into the middle cells.

PTS: 1

REF: EX 551-EX 552

TOP: Critical Thinking

2. How do you determine which method of depreciation is most appropriate?

ANS: The answer depends on the type of asset being depreciated. Tax laws allow different depreciation methods for different kinds of assets and different situations. In general, you want to choose the depreciation method that most accurately describes the financial status of the company. In tax statements, depreciation appears as an expense that is subtracted from the company’s earnings. So, if you accelerate the depreciation of an asset in the early years of use, you might be underestimating the company’s profits, making it appear that the company is less profitable than it actually is. On the other hand, depreciating an asset slowly could make it appear that the company is more profitable than it really is. For this reason, the choice of a depreciation method is best left to a tax accountant, fully aware of the financial issues and the tax laws involved in depreciating an asset.

PTS: 1

REF: EX 562

TOP: Critical Thinking

3. Does the NPV function in Excel correspond to the financial definition of net present value? Explain your answer.

ANS: The financial definition of net present value is the difference between the present value of future cash flows and the current cost of the initial investment. One source of confusion for Excel users is that despite its name, the NPV function does not return the net present value of an investment. Instead it returns the investment’s present value. To calculate the net present value in Excel, the cost of the initial investment must be subtracted from the value returned by the NPV function using the formula NPV value – initial investment where NPV value is the value returned by the NPV function, and initial investment is the initial cost of the investment. This formula assumes that the initial investment is made immediately, and not in the future using discounted dollars.

PTS: 1

REF: EX 573

TOP: Critical Thinking

Excel Tutorial 10: Performing What-If Analyses TRUE/FALSE 1. At the break-even point, revenue equals expenses.

ANS: T

PTS: 1

REF: EX 600

2. The item marked 1 in the accompanying figure indicates the profit.

ANS: F

PTS: 1

REF: EX 600

3. As in traditional what-if analysis, Goal Seek starts with the end result and determines what value you should use to reach a desired answer.

ANS: F

PTS: 1

REF: EX 602

4. To open the Goal Seek dialog box, first click the What-If Analysis button in the Data Tools group.

ANS: T

PTS: 1

REF: EX 602

5. If your input values are in a column, you will use the Column input cell option when you use the Data Table command.

ANS: T

PTS: 1

REF: EX 604

6. Two-variable data tables are often used to explore the impact of changing two values on a single result.

ANS: T

PTS: 1

REF: EX 609

7. You cannot merge scenarios in situations involving multiple workbooks.

ANS: F

PTS: 1

REF: EX 621

8. Solver is an add-in, a program that adds customized commands and features to Microsoft Office programs such as Excel.

ANS: T

PTS: 1

REF: EX 634

9. The sensitivity report is most often used in science and engineering environments.

ANS: T

PTS: 1

REF: EX 642

10. A limits report allows you to quantify the reliability of a solution produced by Solver.

ANS: T

PTS: 1

REF: EX 642

MODIFIED TRUE/FALSE 1. Variable expenses change in proportion to the company’s sales volume. _________________________ ANS: T

PTS: 1

REF: EX 598

2. CVP analysis is sometimes called breakthrough analysis. _________________________ ANS: F break-even break even breakeven PTS: 1

REF: EX 600

3. In traditional what-if analysis, you change result values in worksheet cells. _________________________ ANS: F, input PTS: 1

REF: EX 603

4. The result cells are the cells containing the values you want to examine. _________________________ ANS: T

PTS: 1

REF: EX 603

5. An array argument performs multiple calculations on one or more sets of values and then returns either a single value or multiple values. _________________________ ANS: F, formula PTS: 1

REF: EX 615

6. The items marked 1 in the accompanying figure are the result cells. _________________________ ANS: F, input PTS: 1

REF: EX 620

7. Goal Seek is a program that searches for the optimal solution of a problem involving several variables. _________________________ ANS: F, Solver PTS: 1

REF: EX 631

8. A(n) optimal cell is a cell that Excel changes to produce the desired result in the target cell. _________________________ ANS: F variable changing PTS: 1

REF: EX 635

9. Nonbinding constraints always show a slack of 0. _________________________ ANS: F, Binding PTS: 1 10.

REF: EX 644

A(n) mixed expense is an expense that is part variable and part fixed. _________________________ ANS: T

PTS: 1

REF: EX 599

MULTIPLE CHOICE 1. _______ analysis is an important business decision-making tool because it predicts the effect of cutting overhead or raising prices on net income. a. CVP c. EVP b. MVP d. AVP

ANS: A

PTS: 1

REF: EX 598

2. _______ analysis expresses the relationship between a company’s expenses, its volume of business, and the resulting profit. a. MVP c. CVP b. ABC d. CBP ANS: C

PTS: 1

REF: EX 598

3. Which of the following expense types can be used in a CVP analysis? a. static c. dynamic b. variable d. declining ANS: B

PTS: 1

REF: EX 598

4. A business must pay insurance and rent; these are examples of _______ expenses. a. fixed c. mixed b. variable d. transitional ANS: A

PTS: 1

REF: EX 599

5. All three expense types can be classified as _______. a. mixed, fixed, and CVP c. asset-based, fixed, and mixed b. variable, fixed, and mixed d. variable, fixed, and cost-based ANS: B

PTS: 1

REF: EX 598

6. The accompanying figure is a typical _______ chart. a. AVP c. EVP b. CVP d. MVP ANS: B

PTS: 1

REF: EX 600

7. The item marked 2 in the accompanying figure is the _______ point. a. variable c. break-even

b. fixed ANS: C

d. absolute PTS: 1

REF: EX 600

8. The item marked 3 in the accompanying figure indicates the _______ expenses. a. fixed c. variable b. mixed d. absolute ANS: C

PTS: 1

REF: EX 600

9. The item marked 4 in the accompanying figure indicates the _______ expenses. a. fixed c. variable b. mixed d. absolute ANS: A

PTS: 1

REF: EX 600

10. Goal Seek uses a different approach from traditional _______ analysis. a. trial-and-error c. worksheet b. what-if d. hypothetical ANS: B

PTS: 1

REF: EX 602

11. With Goal Seek, you specify the _______ values you want. a. result c. exchange b. constraint d. index ANS: A

PTS: 1

REF: EX 603

12. If you orient a one-variable table so that the input values are in the first row, you would use the _______ input cell option. a. Column c. Values b. Row d. Table ANS: B

PTS: 1

REF: EX 605

13. In two-variable tables, you must always place a reference to the result cell in the _______ corner of the table at the intersection of the row values and the column values. a. upper-right c. lower-right b. upper-left d. lower-left ANS: B

PTS: 1

REF: EX 609

14. {=SUM(B1:B10*C1:C10)} is an example of a(n) _______. a. array formula c. scenario b. TABLE function d. input value ANS: A

PTS: 1

REF: EX 615

15. Once you enter an array formula, Excel will enclose the formula in a set of _______. a. parentheses c. brackets b. curly braces d. quotation marks ANS: B

PTS: 1

REF: EX 615

16. To calculate multiple cell values, select the range and type the array formula, then press the _______ keys to enter the formula. a. Alt+Enter c. Alt+Shift+Enter

b. Ctrl+Shift+Enter ANS: B

d. Ctrl+Enter PTS: 1

REF: EX 615

17. Before using Scenario Manager, you should assign _______ to all the input and result cells you intend to use in your scenarios. a. cells c. ranges b. names d. ranks ANS: B

PTS: 1

REF: EX 618

18. To perform a what-if analysis with more than two input cells, you _______. a. hide all references c. apply additional constraints b. use only exchange values d. create scenarios ANS: D

PTS: 1

REF: EX 618

19. The items marked 1 in the accompanying figure are _______ cells. a. result c. input b. constraint d. goal ANS: C

PTS: 1

REF: EX 620

20. To select nonadjacent changing cells, press and hold the _______ key as you click each cell. a. Alt c. Esc b. Shift d. Ctrl ANS: D

PTS: 1

REF: EX 619

21. You can switch from one scenario to another by clicking the _______ button in the Scenario Manager dialog box. a. Display c. Show b. View d. Effect ANS: C

PTS: 1

REF: EX 622

22. A scenario summary report’s _______ layout makes it simpler to compare the results of each scenario, and the automatic formatting makes it useful for reports and meetings. a. portrait c. row-based b. tabular d. columnar ANS: B

PTS: 1

REF: EX 625

23. _______ is a program that searches for the optimal solution of a problem involving several variables. a. Trial and error c. Goal Seek b. Solver d. Scenario Manager ANS: B

PTS: 1

REF: EX 633

24. The three items that you must specify when using Solver are _______. a. target cell, input value, and results b. target cell, variable cell, and parameters c. variable cell, parameters, and constraints d. variable cell, objective cell, and constraints ANS: D

PTS: 1

REF: EX 635

25. In Solver, changing cells are sometimes referred to as _______ cells. a. self-adjusting c. key b. variable d. constraint ANS: B

PTS: 1

REF: EX 635

26. Aside from Solver, other Excel _______ provide the ability to perform statistical analyses, generate business reports, and retrieve financial data from the Internet. a. parameters c. helper applications b. add-ins d. constraint applications ANS: B

PTS: 1

REF: EX 635

27. To activate Solver, click Solver Add-In in the _______. a. Add-Ins dialog box c. Analysis group b. Solver dialog box d. Data Tools group ANS: A

PTS: 1

REF: EX 634

28. A(n) _______ is a limit that is placed on a Solver problem's solution. a. max c. constraint b. target d. adjustment ANS: C

PTS: 1

REF: EX 637

29. _______ are important because they ensure a realistic solution to your problem. a. Constraints c. Targets b. Parameters d. Optimals ANS: A

PTS: 1

REF: EX 637

30. After trying out solutions for a short time, Solver displays the _______ dialog box, which displays the message that indicates Solver has found a solution that satisfies the constraints. a. Solver Answer c. Solver Results b. Solver Solution d. Solution ANS: C

PTS: 1

REF: EX 636

31. A(n) _______ procedure means that Solver starts with an initial solution and uses that as a basis to calculate a series of solutions. a. reciprocal c. secondary

b. iterative ANS: B

d. convergent PTS: 1

REF: EX 642

32. If Solver is taking too long to find a solution, you can halt the program at any time by pressing the _______ key. a. Alt c. Esc b. Enter d. Ctrl ANS: C

PTS: 1

REF: EX 642

33. The default length of time that Solver will spend on the iterative process is _______ seconds. a. 50 c. 500 b. 100 d. 1000 ANS: B

PTS: 1

REF: EX 642

34. The default convergence value in Solver is _______. a. 0.001 c. 0.1 b. 0.01 d. 1.0 ANS: A

PTS: 1

REF: EX 642

35. You can include an analysis of the constraints and conditions by which Solver arrived at its solution by creating a(n) _______ report. a. analysis c. condition b. answer d. constraint ANS: B

PTS: 1

REF: EX 642

36. Solver can create the following three different reports: _______. a. sensitivity, answer, analysis c. analysis, answer, limits b. constraint, sensitivity, analysis d. answer, limits, sensitivity ANS: D

PTS: 1

REF: EX 642

37. The _______ report summarizes the results of a successful solution by displaying information about the target cell, changing cells, and constraints. a. answer c. sensitivity b. constraint d. limits ANS: A

PTS: 1

REF: EX 642

38. The answer report is divided into _______ sections. a. two c. six b. three d. five ANS: C

PTS: 1

REF: EX 644

39. The slack is the difference between the value in the cell and the value at the limit of the _______. a. variable cell c. constraint b. result cell d. risk factor ANS: C

PTS: 1

REF: EX 644

40. Binding constraints always show a slack of _______. a. -0.01 c. 0.01

b. 0 ANS: B

d. 1 PTS: 1

REF: EX 644

41. Cells that display the value _______ indicate that all of the values in the worksheet satisfy the Solver constraints. a. TRUE c. 1 b. YES d. 0 ANS: A

PTS: 1

REF: EX 646

42. If a value in the worksheet violates one of the constraints, the Solver parameter cells will display the value _______. a. FALSE c. 1 b. NO d. 0 ANS: A

PTS: 1

REF: EX 646

Case-Based Critical Thinking Questions Case 10-1 You are planning to open a new restaurant and need to determine how many dinners need to be sold to stay above the break-even point. You will investigate Cost Volume Profit (CVP), also known as break-even analysis.

43. First you must understand break-even analysis. The definition of break-even point is where the total revenue _______ total expenses. a. is less than c. equals b. is greater than d. exceeds ANS: C

PTS: 1

REF: EX 600

TOP: Critical Thinking

44. In building an Income statement worksheet, you will calculate variable and fixed expenses based on number of meals sold. To explore the impact of changing different values in the worksheet, you can use _______. a. Solver manager c. What-If analysis b. Income Statement Analysis d. Cash Flow analysis ANS: C

PTS: 1

REF: EX 601

TOP: Critical Thinking

45. Another tool to help analyze several what-if analyses is data tables in Excel. Using a one-variable table, you can specify _______ input cell(s) and any number of result cells. a. many c. two b. one d. three ANS: B

PTS: 1

REF: EX 603

TOP: Critical Thinking

46. When creating a one-variable Data Table, which of the following should you NOT do? a. Enter a formula that references the input cell in the upper-left cell of the table. b. Enter input values in either the second row or the first column. c. Enter input values in either the first row or the first column. d. Select the table excluding any row or column headings.

ANS: B

PTS: 1

REF: EX 604

TOP: Critical Thinking

47. The data table provides the results of several what-if analyses, but results are often clearer if you include a(n) _______ chart. a. Excel c. Goal Seek b. CVP d. Income ANS: B

PTS: 1

REF: EX 607

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 10-2 As you begin to work with Solver, you have questions. So you turn to your co-worker, Aria, who you know is an expert in its usage.

48. Aria explains to you that if you want to use Solver to maximize a company’s net income, you first need to understand that a(n) _______ is a cell that is maximized, minimized, or set to a specific value. a. goal cell c. changing cell b. objective cell d. constraint ANS: B

PTS: 1

REF: EX 635

TOP: Critical Thinking

49. Aria explains that if you have a cell that is the sales price of a given product, the value of which will change to produce the desired Solver result in the target cell, this cell is what kind of cell? a. goal cell c. changing cell b. constraint d. absolute cell ANS: C

PTS: 1

REF: EX 635

TOP: Critical Thinking

50. In your company, there is a physical limit on the number of products that the factories can produce in a given year. What does Aria tell you this limit is called, in Solver’s terms? a. changing cell c. goal cell b. objective cell d. constraint ANS: D

PTS: 1

REF: EX 637

TOP: Critical Thinking

51. Aria explains that there are six possible constraints supported by Solver. Which of the following is one of those constraints? a. != c. b. >= d. # ANS: B

PTS: 1

REF: EX 637

TOP: Critical Thinking

52. The default convergence value is 0.001, which means that if the change in the value of the objective cell between the new solution and the previous solution is _______ 0.001, Solver stops the interative process and reports the current solution. a. equal to c. greater than or equal to b. less than or equal to d. greater than ANS: B

PTS: 1

REF: EX 642

TOP: Critical Thinking

COMPLETION 1. ____________________ expenses change in proportion to the amount of business a company does.

ANS: Variable

PTS: 1

REF: EX 598

2. A(n) ____________________ expense is an expense that must be paid regardless of sales volume.

ANS: fixed PTS: 1

REF: EX 599

3. In a data table, ____________________ cells are cells whose values are impacted by the changing input cells.

ANS: result PTS: 1

REF: EX 603

4. ____________________ cells are cells containing values you want to modify in what-if analysis cells.

ANS: Input PTS: 1

REF: EX 603

5. To set up a(n) ____________________ table, you first insert references to the input and result cells.

ANS: data PTS: 1

REF: EX 603

6. When creating a(n) ____________________ data table, you must identify the row input cell and the column input cell.

ANS: two-variable PTS: 1

REF: EX 609

7. The ____________________ input cell is the cell on which values placed in the first column of the data table are based.

ANS: column PTS: 1

REF: EX 605

8. The item marked 2 in the accompanying figure is the scenario ____________________.

ANS: description PTS: 1

REF: EX 620

9. Check the item marked 3 in the accompanying figure to hide the scenario in a(n) ____________________ worksheet.

ANS: protected PTS: 1

REF: EX 620

10. Click the Scenario PivotTable ____________________ option button to create a PivotTable describing the scenarios.

ANS: report PTS: 1

REF: EX 627

11. A(n) ____________________ is a customized program that adds commands and features to Microsoft Office programs.

ANS: add-in add in PTS: 1

REF: EX 634

12. In Solver, a(n) ____________________ cell is a cell that you want to maximize, minimize, or change to a specific value.

ANS: objective PTS: 1

REF: EX 635

13. The Scenario Manager refers to ____________________ cells as changing cells because these worksheet cells contain values that are changed under the scenario.

ANS: input PTS: 1

REF: EX 620

14. The ____________________ is the difference between the value in a cell and the value at the limit of the constraint.

ANS: slack PTS: 1

REF: EX 644

15. ____________________ constraints always show a slack of 0.

ANS: Binding

PTS: 1

REF: EX 644

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. Solver f. nonbinding b. changing g. Goal Seek c. constraint h. slack d. add-in i. limits report e. answer report j. result 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Changes one cell in order to force another to show a specific value Places limits on a problem's solution, in Solver Summarizes the results of a successful Solver solution Adds commands and features to Excel Cannot be used when a problem contains integer constraints Cells whose values you will change within each scenario Cells that contain the calculated values you want to examine Means that a constraint was not a limiting factor in a Solver solution Searches for the optimal solution of a problem involving several variables The difference between the value in a cell and the value at the limit of the constraint

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: G ANS: C ANS: E ANS: D ANS: I ANS: B ANS: J ANS: F ANS: A ANS: H

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 602 EX 637 EX 642 EX 634 EX 642 EX 619 EX 624 EX 644 EX 634 EX 644

ESSAY 1. How does Goal Seek differ from traditional what-if analysis?

ANS: Goal Seek uses a different approach from traditional what-if analysis, when you change input values in worksheet cells, and Excel uses these values to calculate result values. One way of finding the break-even point is to use Goal Seek. Recall that Goal Seek is a what-if analysis tool that can be used to find the input value needed for an Excel formula to match a specified value.

PTS: 1

REF: EX 602

TOP: Critical Thinking

2. What are the steps to create a one-variable data table?

ANS: Creating a One-Variable Data Table 1. In the upper-left cell of the table, enter a formula that references the input cell.

2. In either the first row or the first column of the table, enter input values.

3. For input values in the first row, enter formulas referencing result cells in the table’s first column; for input values in the first column, enter formulas referencing result cells in the table’s first row.

4. Select the table (excluding any row or column headings).

5. On the DATA tab, in the Data Tools group, click the What-If Analysis button, and then click Data Table.

6. If the input values are in the first row, enter the cell reference to the input cell in the Row input cell box; if the input values are in the first column, enter the cell reference to the input cell in the Column input cell box.

7. Click the OK button.

PTS: 1

REF: EX 604

TOP: Critical Thinking

3. How many different types of reports can Solver create? What are the characteristics of these report types, and which is the most useful?

ANS: Solver can create three different reports—an answer report, a sensitivity report, and a limits report. The answer report may be the most useful of the three because it summarizes the results of a successful solution by displaying information about the target cell, changing cells, and constraints. This report includes the original and final values for the target and changing cells, as well as the constraint formulas. The sensitivity and limits reports are often used in science and engineering environments when the user wants to investigate the mathematical aspects of the Solver solution. These reports allow you to quantify the reliability of the solution; however, you cannot use these reports when your problem contains integer constraints.

PTS: 1

REF: EX 642

TOP: Critical Thinking

Excel Tutorial 11: Connecting to External Data TRUE/FALSE 1. Excel can save a workbook as a text file, preserving only the data values, without any of the formats applied to it.

ANS: T

PTS: 1

REF: EX 661

2. Columns in delimited text files are always vertically aligned as they would be in a spreadsheet.

ANS: F

PTS: 1

REF: EX 662

3. For a fixed-width text file to import correctly into Excel, there must be some way for the Text Import Wizard to know where each column begins and ends.

ANS: T

PTS: 1

REF: EX 664

4. In addition to specifying the format of data using the Text Import Wizard, you can also indicate which columns you want to import.

ANS: T

PTS: 1

REF: EX 665

5. A data connection is like a pipeline from a data source to two or more locations in the workbook.

ANS: F

PTS: 1

REF: EX 669

6. A database is divided into separate tables.

ANS: T

PTS: 1

REF: EX 678

7. Saving the query to a file creates a text file containing the query’s definition in XML code.

ANS: F

PTS: 1

REF: EX 684

8. When no arrow connects a table to any of the other tables in the data model, there is no way to match the records of that table with the other tables.

ANS: T

PTS: 1

REF: EX 695

9. The text from a web query is placed in a worksheet as formatted text.

ANS: F

PTS: 1

REF: EX 705

10. One advantage of XML is that the document author can create rules specifying which elements are required and what types of values are allowed within each element.

ANS: T MODIFIED TRUE/FALSE

PTS: 1

REF: EX 712

1. Although text files contain only raw, unformatted data, they are very useful in situations when you want to share data across software programs and computer systems. _________________________ ANS: T

PTS: 1

REF: EX 661

2. The accompanying figure illustrates an example of space-delimited text. _________________________ ANS: F fixed-width fixed width tab delimited tab-delimited PTS: 1

REF: EX 662

3. In the Text Import Wizard, the Preview window shows the initial rows of the text file. _________________________ ANS: T

PTS: 1

REF: EX 664

4. The point at which one column ends and another begins in the Text Import Wizard is called a column cease. _________________________ ANS: F, break PTS: 1

REF: EX 664

5. In a delimited file, the delimiter automatically determines the column break. _________________________ ANS: T

PTS: 1

REF: EX 664

6. The item marked 1 in the accompanying figure is the row or field in a database table. _________________________ ANS: F, record PTS: 1

REF: EX 679

7. The item marked 2 in the accompanying figure is the column or record in a database table. _________________________ ANS: F, field PTS: 1

REF: EX 679

8. Full HTML formatting will result in imported data that most closely resembles the appearance of a webpage. _________________________ ANS: T

PTS: 1

REF: EX 705

9. When filtering data using the Query Wizard – Filter Data dialog box, the left column specifies the type of sort you want to make in the filter, such as “equals,” “greater than,” or “less than.” _________________________ ANS: F, comparison PTS: 1

REF: EX 683

10. The commands to access the data map of an XML document are part of the Excel Developer tools. _________________________ ANS: T

PTS: 1

REF: EX 713

MULTIPLE CHOICE 1. Most software programs can both save and retrieve data in a(n) _______ file format. a. HTML c. spreadsheet b. text d. picture

ANS: B

PTS: 1

REF: EX 661

2. The following is an example of a delimiter: _______. a. # c. tab b. $ d. period ANS: C

PTS: 1

REF: EX 662

3. One way to structure text files is to use a(n) _______. a. index c. delimiter b. matrix d. register ANS: C

PTS: 1

REF: EX 662

4. A _______ delimiter is often the best way of separating text columns because text delimited this way can include spaces or commas within each column. a. space c. tab b. comma d. dash ANS: C

PTS: 1

REF: EX 662

5. When you use Excel to connect to a text file, the _______ Wizard determines whether the data is in a fixed-width format or a delimited format. a. File c. Open Text b. Open File d. Text Import ANS: D

PTS: 1

REF: EX 663

6. The following is a common text filename extension: _______. a. .dat c. .xls b. .ppt d. .doc ANS: A

PTS: 1

REF: EX 663

7. The row at which you begin the import process is called the _______ row. a. starting c. opening b. origin d. key ANS: A

PTS: 1

REF: EX 664

8. You insert a new column break in the Text Import Wizard by _______ the position in the Data preview window where you want the break to appear. a. double-clicking c. triple-clicking b. clicking d. right-clicking ANS: B

PTS: 1

REF: EX 664

9. Unless you specify a format, Excel will apply the _______ format to all columns. a. Text c. Numerical b. General d. Logical ANS: B

PTS: 1

REF: EX 665

10. A(n) _______ is a file that stores a collection of related data. a. index c. database b. register d. table

ANS: C

PTS: 1

11. _______ is a database program. a. Word b. Access ANS: B

PTS: 1

REF: EX 678

c. PowerPoint d. Outlook REF: EX 678

12. When the location of a data source is defined as a(n) _______ location, Excel will access the connection to the data source without prompting you to confirm that the connection is secure. a. related c. encrypted b. trusted d. safe ANS: B

PTS: 1

REF: EX 673

13. The item marked 1 in the accompanying figure is a _______ in a database table. a. field c. record b. register d. key ANS: C

PTS: 1

REF: EX 678

14. The item marked 2 in the accompanying figure is a _______ in a database table. a. field c. record b. register d. key ANS: A

PTS: 1

REF: EX 678

15. You relate tables to one another by using _______ fields. a. key c. indexed b. common d. date ANS: B

PTS: 1

REF: EX 679

16. A(n) _______ might ask something like, "What are the names of all the stocks in the portfolio, and what are their corresponding ticker symbols?" a. index c. register b. matrix d. query ANS: D

PTS: 1

REF: EX 680

17. If you want to extract only specific records when you query a database, your query would contain _______. a. samples c. records b. criteria d. limiters ANS: B

PTS: 1

REF: EX 683

18. A data _______ is any file that contains the data you want to retrieve. a. roster c. dialog b. link d. source ANS: D

PTS: 1

REF: EX 681

19. You can define your own data source by clicking _______ from the list of databases in the Choose Data Source dialog box. a. <Select Data Source> c. <Create Source> b. <New Data Source> d. <Initialize Source> ANS: B

PTS: 1

REF: EX 681

20. In the Query Wizard, click the _______ in front of a table name to expand the display of fields in that table. a. key c. question mark b. exclamation point d. plus sign ANS: D

PTS: 1

REF: EX 682

21. To select fields within the Query Wizard, you can _______ the field name and Excel will automatically move it to the list of selected columns in your query. a. click c. triple-click b. double-click d. right-click ANS: B

PTS: 1

REF: EX 682

22. When you _______ data, you specify which records you want to retrieve using specific criteria. a. order c. strike b. filter d. rekey ANS: B

PTS: 1

REF: EX 683

23. Databases in which tables can be joined through the use of common fields are known as _______ databases. a. structured c. linked b. relational d. trusted ANS: B

PTS: 1

REF: EX 680

24. To create a web query, you need to know the _______ of the resource (usually a webpage) you are accessing. a. FTP c. HTTP b. URL d. HTML ANS: B

PTS: 1

REF: EX 702

25. The general form of a URL for a file located on your computer will be _______. a. file:///drive:/path/filename c. file://drive:/path//filename

b. file://drive://path/filename ANS: A

PTS: 1

d. file:///drive:///path///filename REF: EX 702

26. The _______ formatting only option retrieves the text from a web query along with simple formatting, but not advanced formatting such as hyperlinks or complicated table structures. a. Rich text c. Default b. Text d. Normal ANS: A

PTS: 1

REF: EX 705

27. The _______ formatting option retrieves all simple as well as advanced HTML formatting features, including hyperlinks. a. Full c. Rich text b. Full HTML d. Default ANS: B

PTS: 1

REF: EX 705

28. An XML document is structured like a tree in which elements are placed within one another, descending from a common _______ element. a. base c. tag b. root d. retrieval ANS: B

PTS: 1

REF: EX 712

29. The name of each element in an XML document is contained in a(n) _______. a. link c. file b. tag d. anchor ANS: B

PTS: 1

REF: EX 712

30. Excel can be used with Office _______, which are small, specialized programs that enhance the features of Excel, Word, PowerPoint, and other programs in the Office suite. a. apps c. plug-ins b. add-ons d. modules ANS: A

PTS: 1

REF: EX 707

31. Real-time _______ values from measuring devices and scientific equipment are a possible data source in Excel. a. link c. file b. data d. map ANS: B

PTS: 1

REF: EX 717

32. Once you insert the _______ function into a cell, the value of the cell will display the latest value retrieved from a measuring device. a. schema c. RTD b. XML d. real ANS: C

PTS: 1

REF: EX 717

33. In an XML element, a(n)_______ tag marks the end of the data value. a. opening c. closing b. index d. map ANS: C

PTS: 1

REF: EX 712

34. In XML, the rules about which elements are required and which are not are stored in a document called the _______. a. schema c. index b. dictionary d. glossary ANS: A

PTS: 1

REF: EX 712

35. In the accompanying figure, item 1 points to the _______. a. URL element specifying that the file is stored locally on the computer b. path name of the folder containing the file c. filename of the webpage d. name of the computer or web server storing the page ANS: A

PTS: 1

REF: EX 703

36. In the accompanying figure, item 2 points to the _______. a. URL element specifying that the file is stored locally on the computer b. path name of the folder containing the file c. filename of the webpage d. name of the computer or web server storing the page ANS: B

PTS: 1

REF: EX 703

37. In the accompanying figure, item 3 points to the _______. a. URL element specifying that the file is stored locally on the computer b. path name of the folder containing the file c. filename of the webpage d. name of the computer or web server storing the page ANS: C

PTS: 1

REF: EX 703

38. One of the advantages of using XML as a data source is that it allows you to attach, or _______, elements to specific cells in the workbook. a. map c. insert b. bind d. pin ANS: B

PTS: 1

REF: EX 714

39. One of the advantages of XML is that the XML author defines what constitutes a valid document.

Documents that fail to meet the rules can be rejected by an XML program as invalid, thus ensuring _______. a. coding c. data integrity b. data values d. external data ANS: C

PTS: 1

REF: EX 712

40. Databases such as those created in Access are examples of _______ databases which reduce data redundancy and increase data integrity. a. pivot c. linked b. relational d. embedded ANS: B

PTS: 1

REF: EX 680

41. One way to interact with a database is with a query language. The most popular query language is _______. a. ODC c. OLE b. UDC d. SQL ANS: D

PTS: 1

REF: EX 680

42. If you are importing international data from a text file, click the _______ button in Step 3 of the Text Import Wizard to specify a different character for the decimal point and thousands separator. a. Next c. Advanced b. Number d. General ANS: C

PTS: 1

REF: EX 666

Case-Based Critical Thinking Questions Case 11-1 Audra is a manager in the Sales Department for Creative Pastry Gadgets. She needs access to up-to-date financial data and sales reports. Some of this information comes from Excel workbooks, but other information is stored in other software programs. In addition, Creative Pastry Gadgets maintains a database with detailed financial information and sales figures. Audra uses Excel to analyze data from external data sources.

43. Audra decides to import data so it will be stored in an external data range. She learns that Excel automatically assigns the data range a defined name based on the filename of the _______. a. open source c. data source b. destination file d. file source ANS: C

PTS: 1

REF: EX 668

TOP: Critical Thinking

44. Audra needs to delete a connection to a data source. She should select the connection in the list in the Workbook Connections dialog box, and then click the _______ button. a. Delete c. Omit b. Remove d. Detach ANS: B

PTS: 1

REF: EX 670

TOP: Critical Thinking

45. To prevent other users from updating a data source without permission, what should Audra do? a. Define the data source. b. Remove the connection. c. Require a password. d. Nothing—Excel cannot prevent data from being refreshed.

ANS: C

PTS: 1

REF: EX 673

TOP: Critical Thinking

46. Audra wants the workbook to always display the most current values from a text file used when importing external sales data. What is the advantage of editing the properties of the connection versus editing the properties of the external data range?

a. The choices are applied to all of the external data ranges that use that connection. b. The choices are applied only to the current workbook. c. The choices are only applied the first time the workbook is opened after the properties have been changed. d. There is no advantage—it would be better to edit the properties of the external data range.

ANS: A

PTS: 1

REF: EX 669

TOP: Critical Thinking

47. After opening a workbook, Audra wants to update all of the external data ranges in the workbook.

What button should she click in the Connections group on the DATA tab? a. Refresh c. Update b. Refresh All d. Update All ANS: B

PTS: 1

REF: EX 672

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 11-2 William works for a nationwide real estate agency. He uses the agency’s website to promote the latest homes. He uses Access to store information concerning the home and Excel to analyze the data. Fall open houses include homes in Seattle, Chicago, and Denver. Spring open houses include tours of homes in Washington, D.C., New Mexico, Alaska, and Montana. Summer home tours include Paris, Rome, Denmark, and Australia.

48. William wants Excel to automatically refresh the connection between his Excel workbook and his Access database whenever the workbook is opened. He should click the Refresh data when opening the file check box in the Connection Properties dialog box. What steps should he take to open the Connection Properties dialog box? a. Click the DATA tab on the Ribbon, click the Connections button in the Connections group, then click the Connection button. b. Click the DATA tab on the Ribbon, click the Connections button in the Connections group, select the database, then click the Properties button. c. Click the EDIT tab on the Ribbon, then click the Connections button in the Connections group. d. Click the EDIT tab on the Ribbon, click the Connections button in the Connections group, select the database, then click the Properties button.

ANS: B

PTS: 1

REF: EX 670

TOP: Critical Thinking

49. William wants his connection files listed in the Existing Connections dialog box. He should save the connections in the _______ folder. a. My Connections c. My Documents b. My Data Sources d. My Sources ANS: B

PTS: 1

REF: EX 688

TOP: Critical Thinking

50. William learns that an advantage of using XML elements in a worksheet is that it allows the user to attach elements to specific cells. To map an XML document directly into an Excel table, William should click the From Other Sources button in the Get External Data group on the DATA tab, and then click _______. a. From XML Document c. From XML Documentation b. From XML Source d. From XML Data Import ANS: D

PTS: 1

REF: EX 715

TOP: Critical Thinking

51. The office manager at the real estate agency informs William that the internal code for Microsoft Office documents is now saved in an XML format called _______ XML. a. Open Office c. Access b. Excel d. Microsoft ANS: A

PTS: 1

REF: EX 711

TOP: Critical Thinking

COMPLETION 1. A(n) ____________________ file contains only text and numbers without any formulas, special fonts, graphics, or formatted text.

ANS: text PTS: 1

REF: EX 661

2. A(n) ____________________ is a symbol that separates one column of data from another.

ANS: delimiter PTS: 1

REF: EX 662

3. To better understand the structure of an XML document, an XML ____________________ can be created that displays the layout of the elements in a schematic diagram.

ANS: data map PTS: 1

REF: EX 712

4. In a(n) ____________________ text file, each column will start at the same location in the file.

ANS: fixed-width fixed width PTS: 1

REF: EX 662

5. The point at which one column ends and another begins in the Text Import Wizard is called a(n) ____________________ break.

ANS: column PTS: 1

REF: EX 664

6. Each piece of data in an XML document is contained within a(n) ____________________.

ANS: element PTS: 1

REF: EX 712

7. A(n) ____________________ is a collection of data arranged in rows and columns.

ANS: table

PTS: 1

REF: EX 678

8. Each column of a database table, called a(n) ____________________, stores information about a specific characteristic of a person, place, or thing.

ANS: field PTS: 1

REF: EX 678

9. Each row of a database table, called a(n) ____________________, displays a collection of characteristics of a particular person, place, or thing.

ANS: record PTS: 1

REF: EX 678

10. ____________________ are the conditions that limit the number of records displayed in the results of a query.

ANS: Criteria PTS: 1

REF: EX 680

11. Data ____________________ can be databases, text files, or other Excel workbooks.

ANS: sources PTS: 1

REF: EX 660

12. Database tables can be inserted directly into PivotCharts and ____________________.

ANS: PivotTables PTS: 1

REF: EX 692

13. When using a website as a data source, a(n) ____________________ specifies how data should be retrieved from the webpage and indicates exactly which parts of the webpage should be included and which parts should not be included.

ANS: web query PTS: 1

REF: EX 702

14. To facilitate real-time data acquisition, Excel provides ____________________ the function.

ANS: RTD (real-time data) real-time data (RTD) RTD() RTD real-time data PTS: 1

REF: EX 717

15. A(n) ____________________ is a data location that Excel will access without prompting you to confirm that the connection is secure.

ANS: trusted location PTS: 1

REF: EX 673

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. delimiter f. query b. database g. criteria c. record h. common field d. field i. element e. table j. Rich Text 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

A collection of data stored in rows and columns Conditions you set to limit the number of records a database extracts Contains data in an XML document Displays the collection of characteristics of a particular person, place, or thing A question you ask about the data in a database Format that allows for text formatting styles including boldface, italic, and color Collection of data organized to be easily retrieved and used by the end user Field that is present in both tables A symbol that separates one column of data from another Stores information about a specific characteristic of a person, place, or thing

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: E ANS: G ANS: I ANS: C ANS: F ANS: J ANS: B ANS: H ANS: A ANS: D

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 678 EX 683 EX 712 EX 678 EX 680 EX 705 EX 678 EX 680 EX 662 EX 678

ESSAY 1. Assume you have a blank workbook open in Excel. You have clicked on the Sheet1 tab and you are currently in cell A1. Describe the steps you would take to connect to a text file using the Text Import Wizard.

ANS: In the Get External Data group on the DATA tab, click the From Text button, and then select the text file containing the data.

In the first step of the Text Import Wizard, choose how the data is organized, and then specify the row in which to start the import.

In the second step, set the column breaks by clicking a location in the Data preview box to insert a column break, double-clicking a column break to delete it, and dragging a column break to move it to a new location.

In the third step, specify the data format for each column by clicking the column and then selecting the appropriate data format option button, or skip importing a column by clicking the column in the Data preview box and then clicking the Do not import (skip) option button.

Click the Finish button.

Specify where to insert the imported text, and then click the OK button to import the text file into the worksheet.

PTS: 1

REF: EX 663-EX 666

TOP: Critical Thinking

2. What are the steps to filter data in a query?

ANS: In the first step of the Query Wizard, select the columns from the different tables to include in your query, and then click the Next button. In the second step of the wizard, filter the data by selecting the columns to filter, specifying the nature of the filter in the Only include rows where list boxes, and then click the Next button. In the third step of the wizard, specify the sort order for the data values in the query, and then click the Next button. In the last step of the wizard, specify whether to return the query data to Excel, view or edit the data in Microsoft Query, or save the written query to a permanent file for use later.

PTS: 1

REF: EX 683

TOP: Critical Thinking

3. Define XML and discuss its applications. Define each of the following terms: elements, root element, tag, data map, and schema.

ANS: Extensible Markup Language (XML) is a language that structures data in text files that can be read by a wide variety of programs. In recent years, XML has been widely used in database programs, web programming, and word processing. Data in an XML document is contained within elements. The document is structured like a tree in which elements are placed within one another, descending from a common root element. The name of each element is contained in a tag. To better understand the structure of an XML document, a data map can be created that displays the layout of the elements in a schematic diagram. A schema is a separate document that stores information about whether or not an element is required—it contains the rules required for correctly inserting data into an XML document.

When you connect to an XML document, Excel creates a data map of the document’s structure and contents. If a schema is present, Excel uses it to create the data map; otherwise, it creates the map based on the contents of the XML document. After the data map is created, you can use it to place XML elements anywhere within the workbook.

PTS: 1

REF: EX 712

TOP: Critical Thinking

Excel Tutorial 12: Collaborating on A Shared Workbook TRUE/FALSE 1. Excel workbooks are shared by default.

ANS: F

PTS: 1

REF: EX 728

2. Excel tracks the changes made to a shared workbook, recording the name of the user who made the changes and when the changes were saved.

ANS: T

PTS: 1

REF: EX 730

3. In a shared workbook, all edits are stored in a tracking log for 60 days.

ANS: F

PTS: 1

REF: EX 733

4. Another way to collaborate on a document is to provide a separate copy of the same shared file to multiple users.

ANS: T

PTS: 1

REF: EX 737

5. Document properties or metadata are the descriptive details about a workbook.

ANS: T

PTS: 1

REF: EX 746

6. The Document Inspector will remove comments, annotations, document properties, and personal information from a shared workbook.

ANS: F

PTS: 1

REF: EX 750

7. An object is anything that appears on your screen that can be selected and manipulated.

ANS: T

PTS: 1

REF: EX 756

8. The encryption password is different from the passwords that prevent users from editing a worksheet or the entire workbook.

ANS: T

PTS: 1

REF: EX 752

9. Digital signatures provide a way for the author to authenticate the document by “signing off” on it.

ANS: T

PTS: 1

REF: EX 754

10. Screen elements that are part of the worksheet are modified only in that worksheet and not in other worksheets and workbooks.

ANS: T MODIFIED TRUE/FALSE

PTS: 1

REF: EX 764

1. The running conversation among users about a cell is displayed within a(n) double comment box connected to that cell. _________________________ ANS: F, single PTS: 1

REF: EX 731

2. You can accept an edit to keep its new value, or you can reject an edit to return to the original value. _________________________ ANS: T

PTS: 1

REF: EX 735

3. The Microsoft file hosting service offers seven megabytes of free storage for new users with the option to purchase additional storage space. _________________________ ANS: F, gigabytes PTS: 1

REF: EX 740

4. Standard properties are properties associated with all Office files and include the author, title, and subject. _________________________ ANS: T

PTS: 1

REF: EX 746

5. Workbook viruses are often signaled by a hidden worksheet or hidden code attached to the HTML code in the file. _________________________ ANS: F, XML PTS: 1

REF: EX 750

6. Encryption is the process by which a file is encoded so that it cannot be opened without the proper password. _________________________ ANS: T

PTS: 1

REF: EX 752

7. To add a digital signature to a document, you need a(n) digital code, which is an attachment to a document that authenticates the source of the signature. _________________________ ANS: F digital id digital certificate PTS: 1

REF: EX 754

8. A(n) source file contains the object that is displayed in the destination file. _________________________ ANS: T

PTS: 1

REF: EX 756

9. A(n) linked object is stored within the destination file and remains connected to the source file. _________________________ ANS: T

PTS: 1

REF: EX 757

10. The Standard option optimizes the PDF for use with online publishing and printing. _________________________ ANS: F, Minimum size PTS: 1

REF: EX 772

MULTIPLE CHOICE 1. A _______ Excel workbook tracks the changes made by different people so everyone can examine when and where each change was made. a. merged c. saved b. shared d. combined ANS: B

PTS: 1

REF: EX 728

2. Shared workbooks are usually stored in shared folders located on either a local network or the _______. a. sky c. external drive b. cloud d. satellite ANS: B

PTS: 1

REF: EX 728

3. To make it possible for other users to access and edit the same data _______, you need to share a workbook. a. simultaneously c. consecutively b. individually d. separately ANS: A

PTS: 1

REF: EX 729

4. The item marked 1 in the accompanying figure is checked to _______. a. allow individual editing of the workbook b. allow simultaneous editing of the workbook c. allow alternate editing of the workbook d. allow consecutive editing of the workbook ANS: B

PTS: 1

REF: EX 729

5. The item marked 2 in the accompanying figure displays a _______. a. list of users removed from editing the workbook b. list of edits added to the document c. list of users currently editing the workbook d. list of previous users currently editing the workbook ANS: C

PTS: 1

REF: EX 729

6. Excel tracks the changes made to a shared workbook, recording the name of the user who made the changes and _______ the changes were saved. a. when c. why b. how d. where ANS: A

PTS: 1

REF: EX 730

7. _______ occur when users try to save different values in the same cell. a. Issues c. Problems b. Errors d. Conflicts ANS: D

PTS: 1

REF: EX 730

8. To change the length of the tracking log history, specify a new length in _______ on the Advanced tab in the Share Workbook dialog box. a. months c. years b. days d. hours ANS: B

PTS: 1

REF: EX 733

9. The contents of the _______ log can be reviewed either in the form of comments attached to the edited cells, or within a list displayed in a separate worksheet. a. tracking c. mapping b. recording d. documenting ANS: A

PTS: 1

REF: EX 733

10. In the accompany figure, item _______ identifies the date and time of the edit. a. 1 c. 3 b. 2 d. 5 ANS: B

PTS: 1

REF: EX 735

11. In the accompanying figure, item _______ displays the description of the edit. a. 1 c. 4 b. 3 d. 5 ANS: D

PTS: 1

REF: EX 735

12. In the accompanying figure, item _______ displays the location of the edit. a. 1 c. 3 b. 2 d. 4 ANS: D

PTS: 1

REF: EX 735

13. An edit can be accepted to keep its new value, or you can reject an edit to return to the _______ value. a. beginning c. source b. ending d. original ANS: D

PTS: 1

REF: EX 735

14. The following condition must be met to merge two or more workbooks:

a. b. c. d.

The copies must originate from different shared files.

The copies cannot have different filenames.

The Track Changes feature must be turned off for all copies.

The copies must either have the same password or not be password-protected.

ANS: D

PTS: 1

REF: EX 738

15. Once workbook sharing is removed, the contents of the tracking log are _______, and there is no way to retrieve the history of edits made to the document. a. saved c. erased b. copied d. backed up ANS: C 16.

PTS: 1

REF: EX 739

The wide collection of file hosting sites that allow document sharing supported by the Internet include _______. a. Symphony c. Sortbox b. Google Docs d. FaceDocs ANS: B

PTS: 1

REF: EX 740

17. The _______ tools in Excel make it simpler for groups to share the work of writing, editing, and finishing a financial report. a. group c. collaborative b. interactive d. sharing ANS: C

PTS: 1

REF: EX 743

18. _______ properties are properties associated with all Office files and include the author, title, and subject. a. Custom c. Automatically updated b. Standard d. Organization ANS: B

PTS: 1

REF: EX 746

19. _______ properties are properties created for organizations to use in designing and distributing electronic forms. a. Document Library c. Custom b. Standard d. Organization ANS: D 20.

PTS: 1

REF: EX 746

_______ properties are properties associated with documents in a document library on a website or in a public network folder. a. Custom c. Organization b. Document library d. Automatically Updated ANS: B

PTS: 1

REF: EX 746

21. To determine whether a workbook contains sensitive or personal information, you use the _______ to search the workbook to locate data and text that you may want removed. a. Document Properties c. Document Specifications b. Document Inspector d. Document Search ANS: B

PTS: 1

REF: EX 748

22. The most serious differences between Excel 2013 and older versions of Excel include: _______. a. Worksheet size c. Sparklines b. International dates d. All of the above ANS: D

PTS: 1

REF: EX 750

23. The encryption password is _______ the passwords that prevent users from editing a worksheet or the entire workbook. a. similar to c. modified from b. different from d. copied from ANS: B

PTS: 1

REF: EX 752

24. Passwords can be up to _______ characters and can include numbers, symbols, uppercase letters, and lowercase letters. a. 8 c. 255 b. 16 d. 164 ANS: C

PTS: 1

REF: EX 752

25. Marking a workbook as final makes the workbook _______, discouraging a user from making any changes to it. a. write-only c. locked b. read-only d. protected ANS: B

PTS: 1

REF: EX 753

26. A final way to ensure that a document has not been changed by an unauthorized user is with a(n) ________ signature, which can be thought of as an electronic version of a written signature. a. digital c. automated b. electronic d. verified ANS: A

PTS: 1

REF: EX 754

27. If you do not have a digital certificate, you can get one from a third-party _______, known as a certificate authority (CA). a. source c. contact b. individual d. macro ANS: A

PTS: 1

REF: EX 754

28. In the accompanying figure, item 2 displays _______. a. a signed workbook marked as final b. a visible signature in the workbook that certifies the document c. a signed workbook marked as closed d. a verified signature in the workbook that certifies the document ANS: B

PTS: 1

REF: EX 755

29. In the accompanying figure, item 1 displays _______. a. a signed workbook that is marked as final b. a visible signature in the workbook that certifies the document c. a signed workbook that is marked as closed d. a verified signature in the workbook that certifies the document ANS: A

PTS: 1

REF: EX 755

30. A(n) _____ file contains the object that is displayed in the destination file. a. source c. beginning b. object d. completed ANS: A

PTS: 1

REF: EX 756

31. _______ displays an object in the destination file, but only stores the location of the source file. a. Copy and Paste c. Embedding b. Linking d. Inserting ANS: B

PTS: 1

REF: EX 756

32. _______ displays and stores an object within the destination file. a. Copy and Paste c. Embedding b. Linking d. Inserting

ANS: C

PTS: 1

REF: EX 756

33. A(n) _______ is stored within the destination file and remains connected to the source file. a. embedded object c. inserted object b. linked object d. copied object ANS: B

PTS: 1

REF: EX 757

34. The Paste option icon that links to the source file and keeps the formatting from the source file is _______. a. c. b.

ANS: B

d.

PTS: 1

REF: EX 758

35. The Paste option icon that keeps the formatting applied to the object in the source file is _______. a. c. b.

ANS: A

d.

PTS: 1

REF: EX 758

36. To capture a screen image, click the _______ tab on the ribbon, and then click the Screenshot button in the Illustrations group. a. INSERT c. FORMULAS b. HOME d. VIEW ANS: A

PTS: 1

REF: EX 758

37. One disadvantage of embedded objects is that they tend to greatly increase the size of the _______ file. a. destination c. object b. source d. original ANS: A

PTS: 1

REF: EX 763

38. Excel automatically recalculates any formula when the input cells to that _______ are changed. a. function c. format b. formula d. references ANS: B

PTS: 1

REF: EX 767

39. An International format, 53,08,421.64 with thousands above 9,999 marked in two-digit groups with a comma, and decimals marked with a period is used in _______. a. Switzerland c. Brazil b. India d. Denmark ANS: B

PTS: 1

REF: EX 768

40. The Standard option optimizes the _______ for use with online publishing and printing. a. DOCX c. XPS b. PDF d. HTML ANS: B

PTS: 1

REF: EX 772

41. Word _______ a linked object every time you open the Word document or any time the Excel source file changes while the Word document is open.

a. updates b. changes ANS: A

c. copies d. saves PTS: 1

REF: EX 760

Case-Based Critical Thinking Questions Case 12-1 Chloe is building a kiosk-based Excel application. She wants to make some modifications to the screen elements in order to keep users from being distracted by parts of the application that are irrelevant to her application. She turns to Henry for guidance as she knows he built a similar solution earlier this year.

42. Henry explains to Chloe that Excel screen elements fall into general categories. Which of the following is one of the categories into which the screen elements fall? a. taskbar c. Windows b. window d. status ANS: B

PTS: 1

REF: EX 764

TOP: Critical Thinking

43. If Chloe hides a screen element that is part of the worksheet, where is that screen element hidden? a. In that worksheet c. In any workbooks b. In any worksheets d. In all worksheets ANS: A

PTS: 1

REF: EX 764

TOP: Critical Thinking

44. Chloe has decided to hide the worksheet gridlines and the vertical scroll bar. What does Henry tell her to use to do this? a. Customization dialog box c. Excel Options dialog box b. Screen Elements dialog box d. Display Options dialog box ANS: C

PTS: 1

REF: EX 764

TOP: Critical Thinking

45. Where in the Excel Options dialog box would Chloe find the setting to change the default location for Excel workbook files? a. Advanced options, Display section b. Advanced options, Editing options section c. Save options d. Formulas options ANS: C

PTS: 1

REF: EX 767

TOP: Critical Thinking

46. Where in the Excel Options dialog box would Chloe find the setting to turn off AutoComplete for cell values? a. Advanced options, Display section b. Advanced options, Editing options section c. Save options d. Formulas options ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 12-2

REF: EX 764

TOP: Critical Thinking

Your cousin Rebecca knows that you are an experienced Excel user. She has created a workbook and needs input from three fellow workers. She wants to retain her workbook as the original copy. She will need your help in collecting input.

47. To begin Rebecca needs to share her workbook. She will create a shared folder. What would be the best way to distribute the workbook to others? a. Placing the file on the local drive c. Placing the file on flash drive b. Placing the file on the cloud d. Placing the file on a CD-ROM ANS: B

PTS: 1

REF: EX 728

TOP: Critical Thinking

48. Now that Rebecca has saved her workbook in a secure area, she can share the workbook with others.

To share the workbook, Rebecca will use the _______. a. Share Workbook dialog box c. Proofing dialog box b. Trust Center dialog box d. Comments dialog box ANS: A

PTS: 1

REF: EX 729

TOP: Critical Thinking

49. Since Rebecca needs to share her workbook with three or more co-workers, she will click _______ in the Shared dialog box. a. Allow changes by more than one user at different times b. Allow changes by only one user at the same time c. Allow changes by more than one user at the same time d. Remove users ANS: C

PTS: 1

REF: EX 729

TOP: Critical Thinking

50. Comments are a powerful collaboration tool, giving team members the ability to offer insights and make suggestions about the workbook and its content. Now Rebecca would like to review the comments by clicking the _______. a. View Tab, then click Next in the Comments groups b. Review Tab, then click Next in the Comments groups c. Home Tab, then click Next in the Comments groups d. Edit Tab, then click Next in the Comments groups ANS: B

PTS: 1

REF: EX 732

TOP: Critical Thinking

51. Rebecca would like to review track changes. In a shared workbook, all edits are stored in a tracking log for 30 days. On the REVIEW tab, she will _______. a. click the Track Changes button in the Changes group, and then click Highlight Changes. b. click the Track Changes button in the Language group, and then click Highlight Changes. c. click the Track Changes button in the Comments group, and then click Highlight Changes. d. click the Track Changes button in the Proofing group, and then click Highlight Changes.

ANS: A

PTS: 1

REF: EX 733

TOP: Critical Thinking

COMPLETION 1. A shared Excel workbook ____________________ the changes made by different people so everyone can examine when and where each change was made.

ANS: tracks PTS: 1

REF: EX 728

2. The running conversation among users about a cell is displayed within a single ____________________ box connected to that cell.

ANS: comment PTS: 1

REF: EX 731

3. You can accept an edit to keep its new value, or you can ____________________ an edit to return to the original value.

ANS: reject PTS: 1

REF: EX 735

4. The different versions of the workbook can be ____________________ so that the changes in the copies can be compared and conflicting edits can be resolved.

ANS: merged PTS: 1

REF: EX 737

5. Once workbook sharing is removed, the contents of the tracking log are ____________________, and there is no way to retrieve the history of edits made to the document.

ANS: erased deleted PTS: 1

REF: EX 739

6. The Excel ____________________ includes most of the tools for editing a workbook that are in the Excel desktop application such as the ribbon, sheet tabs, and command buttons.

ANS: web app PTS: 1

REF: EX 741

7. You can remove sharing from your workbook at any time by reopening the ____________________ screen and editing the list of recipients, removing users who should no longer have access to the file.

ANS: Share PTS: 1

REF: EX 742

8. ____________________ properties are properties you define and create specifically for a workbook.

ANS: Custom PTS: 1

REF: EX 746

9. To determine whether a workbook contains sensitive or personal information, you use the ____________________ to search the workbook to locate data and text that you may want removed.

ANS: Document Inspector

PTS: 1

REF: EX 748

10. ____________________ is the process by which a file is encoded so that it cannot be opened without the proper password.

ANS: Encryption PTS: 1

REF: EX 752

11. A workbook marked as ____________________ has the editing, typing, and proofing commands turned off.

ANS: final PTS: 1

REF: EX 753

12. The ____________________ acts like a notary public, verifying signatures and tracking those that have expired or been revoked.

ANS: CA Certificate authority CA (Certificate authority) Certificate authority (CA) PTS: 1

REF: EX 754

13. If you cannot obtain a digital certificate from a third party, you can create your own ____________________ certificate, which is a certificate created and signed by the person it certifies.

ANS: self-signed PTS: 1

REF: EX 755

14. A(n) ____________________ file contains the object that is displayed in the destination file.

ANS: source PTS: 1

REF: EX 756

15. ____________________ refers to the technology that allows one to copy and paste objects, such as graphics files, cells and ranges, and charts, so that information about the program that created the object is included with the object itself.

ANS: OLE Object linking and embedding OLE (Object linking and embedding) Object linking and embedding (OLE) PTS: 1

REF: EX 757

MATCHING Identify the letter of the choice that best matches the phrase or definition. a. linked object f. Object linking and embedding (OLE) b. embedding object g. Encryption c. PDF (Portable Document Format) h. digital certificate d. Standard properties i. certificate authority (CA) e. Custom properties j. source file 1. Stored within the destination file and remains connected to the source file 2. A file format developed by Adobe Systems for displaying formatted documents containing text, graphics, and complicated layouts 3. Defined and created specifically for a workbook 4. An attachment to a document that authenticates the source of the signature 5. Provider of verified digital signatures 6. Associated with all Office files and include the author, title, and subject 7. Refers to the technology that allows one to copy and paste objects 8. The process by which a file is encoded so that it cannot be opened without the proper password 9. Stored within the destination file and is no longer part of the source file 10. Contains the object that is displayed in the destination file 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: A ANS: C ANS: E ANS: H ANS: I ANS: D ANS: F ANS: G ANS: B ANS: J

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

EX 757 EX 772 EX 746 EX 754 EX 754 EX 746 EX 757 EX 752 EX 757 EX 756

ESSAY 1. Describe the steps to review tracked changes in a shared workbook.

ANS: Reviewing Tracked Changes in a Shared Workbook • On the REVIEW tab, in the Changes group, click the Track Changes button, and then click Highlight Changes.

• Specify when, who, and where in the Highlight which changes section.

• Click the Highlight changes on screen check box to see edits in comments.

• Click the List changes on a new sheet check box to view the tracking log.

• Click the OK button.

• Point to the highlighted cells to see the edits and/or view the tracking log in the History worksheet.

PTS: 1

REF: EX 733

TOP: Critical Thinking

2. Explain which of the following screen elements are part of the Excel window, the workbook window, and the worksheet: formula bar, ScreenTips, chart element names on hover, horizontal and vertical scroll bars, row and column headers, sheet tabs, and gridlines.

ANS: Display Location Screen Element Excel window Formula bar ScreenTips Chart element names on hover WorkBook Horizontal scroll bar Vertical scroll bar Sheet tabs Worksheet Row and column headers Gridlines PTS: 1

REF: EX 764

TOP: Critical Thinking

3. Describe how you can control Excel recalculating Formulas. Discuss the Automatic, Automatic except for data tables, and Manual options.

ANS: Excel automatically recalculates any formula when the input cells to that formula are changed. This process works seamlessly when the workbook is small and few formulas are involved. However, when the workbook increases in size—think several worksheets with thousands of rows with multiple formulas for each row—this process slows noticeably as Excel recalculates tens of thousands of formulas. One way to solve this problem is to change how Excel handles recalculation. You can choose from the following options: • Automatic—All dependent formulas are recalculated every time a change is made to their input cells.

• Automatic except for data tables—Automatic recalculation is done for all dependent formulas except for formulas within data tables created with the Data Tables feature.

• Manual—No recalculation is done except when manually requested by the user.

To manually recalculate the contents of the workbook, press the F9 key or click the Calculate Now button in the Calculation group on the FORMULAS tab. You can define calculation options on the Formulas tab in the Excel Options dialog box, or by clicking the Calculation Options button in the Calculation group on the FORMULAS tab. Another way to speed up a large workbook is to reduce the number of iterative calculations used in performing what-if analyses involving goal seeking. You can set this value using the Enable iterative calculation check box on the Formulas tab in the Excel Options dialog box.

PTS: 1

REF: EX 767

TOP: Critical Thinking

Managing Your Files TRUE/FALSE 1. Windows Vista, Windows 7, and Windows 8 were all designed for touch-screen computers such as tablets.

ANS: F

PTS: 1

REF: MYF 4

2. A computer is like a file cabinet that has two or more drawers.

ANS: T

PTS: 1

REF: MYF 5

3. Deleting folders from the root directory of your hard disk could disrupt the system so that you can’t restart or run the computer.

ANS: T

PTS: 1

REF: MYF 7

4. The accompanying figure shows the Windows 8 File Explorer.

ANS: T

PTS: 1

REF: MYF 9

5. File Explorer uses backslashes to separate locations in a file path.

ANS: F

PTS: 1

REF: MYF 11

6. You can switch to Details view or Large icons view in File Explorer by using the view buttons on the status bar.

ANS: T

PTS: 1

REF: MYF 12

7. Windows 8 automatically saves files after every update or change, so manually saving is no longer necessary.

ANS: F

PTS: 1

REF: MYF 16

8. When you move a folder, you also move all of the files contained in the folder.

ANS: T

PTS: 1

REF: MYF 20

9. When you delete a folder or file from the hard disk, it is permanently deleted immediately, and unrecoverable.

ANS: F

PTS: 1

REF: MYF 25

10. Extracting a file means to compress a file into an archive.

ANS: F

PTS: 1

REF: MYF 26

MODIFIED TRUE/FALSE 1. Using Windows 8 with a touch screen, pinch two fingers on the screen to zoom out. _________________________ ANS: T

PTS: 1

REF: MYF 5

2. Level 4 of the Windows 8 file system includes a folder for your user account, such as the User folder. _________________________ ANS: F 2 two PTS: 1

REF: MYF 7

3. In the accompanying figure, Box J points to the Forward button. _________________________ ANS: F Up one level Up a level PTS: 1

REF: MYF 9

4. File Explorer displays the file path in the Status bar. _________________________ ANS: F, Address PTS: 1

REF: MYF 11

5. Tiles view is the default view for all File Explorer folders except those stored in the Pictures library. _________________________ ANS: F, Details PTS: 1

REF: MYF 12

6. Sorting files means listing them in ascending order or descending order by a file detail. _________________________ ANS: T

PTS: 1

REF: MYF 14

7. Every file you save must have a storage location and a(n) filename. _________________________ ANS: T

PTS: 1

REF: MYF 16

8. You will find the New folder button in the Customize group on the Home tab of File Explorer. _________________________ ANS: F, New PTS: 1

REF: MYF 19

9. To select multiple files that are not listed together, you make use of the Shift key when selecting. _________________________ ANS: F, Ctrl PTS: 1

REF: MYF 24

10. You find the Zip button in the Send group on the Share tab in File Explorer. _________________________ ANS: T

PTS: 1

REF: MYF 26

MULTIPLE CHOICE 1. ____ is/are new to Windows 8. a. The Start menu b. The Charms bar ANS: B

PTS: 1

c. The Desktop d. Icons REF: MYF 4

2. Swiping from the right edge of the screen toward the center ____. a. drags an object c. displays the Charms bar b. displays thumbnails of open apps d. zooms the screen ANS: C

PTS: 1

3. Moving fingers apart on the screen ____. a. zooms the display in b. zooms the display out ANS: A

PTS: 1

4. You organize files by storing them in ____. a. namespaces b. drawers ANS: D

PTS: 1

REF: MYF 5

c. scrolls the Start screen d. displays the Apps bar REF: MYF 5

c. rings d. folders REF: MYF 5

5. A(n) ____ is an example of a fixed file storage system, permanently housed in a computer. a. USB drive c. cloud disk b. DVD d. hard disk ANS: D

PTS: 1

6. The hard disk is assigned to drive ____. a. A b. C

REF: MYF 6

c. D d. F

ANS: B

PTS: 1

REF: MYF 6

7. In the accompanying figure, the box marked ____ indicates the location of the root directory. a. A c. D b. B d. E ANS: A

PTS: 1

REF: MYF 7

8. The folders in the level marked by box ____ in the accompanying figure are designed to contain subfolders for your personal files. a. A c. C b. B d. D ANS: C

PTS: 1

REF: MYF 7

9. The folder that contains the Windows 8 operating system is located in the level marked by box ____ in the accompanying figure. a. A c. C b. B d. D ANS: B

PTS: 1

REF: MYF 7

10. File Explorer displays the contents of your computer by using ____ to represent drives, folders, and files. a. colors c. hypertext links b. sound effects d. icons ANS: D

PTS: 1

REF: MYF 8

11. The left pane of the File Explorer window is the ____ pane. a. status c. structure b. address d. navigation ANS: D

PTS: 1

REF: MYF 8

12. Box ____ in the accompanying figure points to an expand button. a. B c. F b. D d. J ANS: C

PTS: 1

REF: MYF 9

13. Clicking the button marked by box ____ in the accompanying figure will show the file & folder locations that you have recently visited. a. A c. F b. B d. I ANS: A

PTS: 1

REF: MYF 9

14. Box ____ in the accompanying figure points to the file path of the currently selected item. a. D c. H b. G d. I ANS: D

PTS: 1

REF: MYF 9

15. In the accompanying figure, the items pointed to by Box G are ____. a. folders c. files b. subfolders d. directories ANS: C

PTS: 1

REF: MYF 9

16. A file ____ shows the location of a file or folder. a. matrix c. run b. path d. circuit ANS: B

PTS: 1

REF: MYF 9

17. When working in the navigation pane, you ____ a folder to open it. a. double-click c. right-click b. click d. alt-click ANS: B

PTS: 1

REF: MYF 10

18. All of the following are File Explorer ribbon tabs EXCEPT ____. a. Home c. View b. Share d. Link ANS: D

PTS: 1

REF: MYF 10

19. If you are currently working in the F: directory, you are most likely working with folders and files ____. a. on the hard disk c. on a floppy drive b. on a USB drive d. in the cloud ANS: B

PTS: 1

REF: MYF 10

20. Printed documents use ____ to separate locations in a file path. a. backslashes c. colons b. arrows d. periods ANS: A

PTS: 1

REF: MYF 11

21. File Explorer displays the current file path in the ____ so you can keep track of your current location as you navigate. a. status bar c. address bar b. reading pane d. ribbon ANS: C

PTS: 1

REF: MYF 11

22. The default view for any folder in the Pictures Library is ____. a. Details c. Large icons b. Tiles d. Wallet ANS: C

PTS: 1

REF: MYF 12

23. File Explorer provides ____ ways to view the contents of a folder. a. 3 c. 6 b. 4 d. 8 ANS: D

PTS: 1

REF: MYF 12

24. The default view of all folders except those in the Pictures Library is ____. a. Details c. Large icons b. Tiles d. Wallet ANS: A

PTS: 1

REF: MYF 12

25. The File Explorer status bar displays ____. a. all view buttons b. only the Details and Large icons view buttons c. only the Tiles, List, and Content view buttons d. no view buttons ANS: B

PTS: 1

REF: MYF 12

26. When you change the view for a folder in File Explorer, ____. a. all views for all folders are changed b. it only changes the view for the currently selected folder c. you must do it for a folder that is not currently selected d. you should click “OK” in the Warning dialog box ANS: B

PTS: 1

REF: MYF 13

27. An example of sorting in ascending order is ____. a. Z to A c. latest to earliest date b. 9 to 0 d. A to Z ANS: D

PTS: 1

REF: MYF 14

28. You can sort files ____. a. only in Details view b. only in Details, Content, and Tiles views ANS: D

PTS: 1

c. only by file name or file creation date d. no matter which view you use

REF: MYF 14

29. A folder name can have up to ____ characters. a. 8 c. 32 b. 11 d. 255 ANS: D

PTS: 1

REF: MYF 15

30. The ____ character is allowable in a folder name. a. c. < b. / d. : ANS: A

PTS: 1

REF: MYF 15

31. A file ____ includes the three or four characters that follow the dot in the filename and identify the file’s type. a. tag c. hook b. extension d. delimiter ANS: B

PTS: 1

32. ____ is not a valid file name. a. This File Name Is Very Long.docx b. My-picture.jpg ANS: D

PTS: 1

REF: MYF 16

c. SA!AD-DRE33ING.mp4 d. Mary*Jon.doc REF: MYF 17

33. ____ is a Microsoft service that provides up to 7 GB of online storage space for your files at no charge. a. Picasa c. SkyDrive b. Flickr d. Mozilla

ANS: C

PTS: 1

REF: MYF 17

34. When you save a new file, you use the ____ dialog box to provide a filename and select a location for the file. a. Save As c. Save New b. Save d. Open ANS: A

PTS: 1

REF: MYF 17

35. When you edit a file you saved previously, you can use the application’s ____ command to save your changes, keeping the same file name and location. a. Save As c. Backup b. Update d. Save ANS: D

PTS: 1

REF: MYF 17

36. All of the following are methods for creating a new folder EXCEPT ____. a. using the New folder button in the New group on the Home tab b. double-clicking an existing folder and clicking “New” on the pop-up window c. using the New folder button on the Quick Access Toolbar d. right-clicking to display a shortcut menu that includes the New command ANS: B

PTS: 1

REF: MYF 19

37. ____ a file places a duplicate version of the file in a new location that you specify. a. Doubling c. Copying b. Moving d. Replicating ANS: C

PTS: 1

REF: MYF 20

38. The ____ is a temporary Windows 8 storage area for files and information that you copy or move from one location to place in another. a. Clipboard c. Recycle Bin b. SkyDrive d. cloud ANS: A

PTS: 1

REF: MYF 21

39. The Move to and Copy to buttons are found in the ____ group on the ____ tab on the File Explorer ribbon. a. Media, Manage c. Tools, Share b. Organize, Home d. Layout, View ANS: B

PTS: 1

REF: MYF 22

40. To deselect a single file in a list of selected files, you make use of the ____ key. a. Shift c. Tab b. Ctrl d. Alt ANS: B

PTS: 1

REF: MYF 24

TOP: Critical Thinking

41. In most cases, a file deleted from a USB drive ____. a. goes to the Recycle Bin c. is moved to the C:/Users folder b. is deleted permanently d. is moved to the cloud ANS: B

PTS: 1

REF: MYF 25

42. Windows 8 stores compressed files in a special type of folder called a(n) ____. a. extraction c. lockbox b. USB drive d. archive ANS: D

PTS: 1

REF: MYF 26

CASE MYF-1 You know that the Windows 8 File Explorer gives you a lot of power in managing and organizing your files. You decide to take some time to explore its features.

43. You have various song and album files scattered about your computer’s hard disk. You decide to make them all viewable in the handy built-in ____ library. a. Windows Tunes c. Audio b. Music d. Media ANS: B

PTS: 1

REF: MYF 8

TOP: Critical Thinking

44. You wrote a small paper about your visit to the Grand Canyon some weeks ago, but can’t remember the name of the file. The best way to find it would be to ____. a. type Grand Canyon in the Search box b. scan the Documents library for a file that looks like it may be right c. type C:/Grand Canyon in the Address bar d. click the Back button repeatedly until the correct file location displays ANS: A

PTS: 1

REF: MYF 9

TOP: Critical Thinking

45. You would like to find quickly all of the pictures that you took between three and four months ago.

You can do this most easily by ____. a. simply viewing the Pictures library in its default view b. viewing the Pictures library in its default view and then sorting on the date column c. simply viewing the Pictures library in List view d. viewing the Pictures library in Details view and sorting on the Date modified column ANS: D

PTS: 1

REF: MYF 12

TOP: Critical Thinking

46. You tried to change a file’s name, but Windows 8 did not permit the change. This is most likely because ____. a. the filename had a 4-character extension b. the filename had more than 32 characters c. the filename contained a colon d. the filename began with a number ANS: C

PTS: 1

REF: MYF 17

TOP: Critical Thinking

47. You have copied a file from one folder to a new folder. You decide to place the file in a third folder, but you find that you cannot paste the file. The most likely reason is that ____. a. the hard disk is malfunctioning b. you made the first copy by dragging, therefore the file was not placed on the Clipboard c. you made the first copy by using the Cut command, therefore the file was not placed on the Clipboard d. a file cannot exist in three folders at the same time ANS: B

PTS: 1

REF: MYF 21

TOP: Critical Thinking

48. You wish to select two files, which are not listed next to each other. But when you try to select them, you unintentionally select all of the files between the first and last. This is most likely because you ____. a. have a virus on your computer b. used the Ctrl button instead of the Shift button c. used the Shift button instead of the Ctrl button d. right-clicked instead of clicking ANS: C

PTS: 1

REF: MYF 24

TOP: Critical Thinking

CASE MYF-2 As an advocate of Windows 8, you are describing its new features to your friend James.

49. James would like to know of one completely new feature on Windows 8 that didn’t exist on Windows 7. You answer: ____. a. the Start menu c. vertical scrolling b. use of a physical keyboard d. the Charms bar ANS: D

PTS: 1

REF: MYF 4

TOP: Critical Thinking

50. As the trend for computer screens has gone toward shorter and wider screens, you tell James that Windows 8 ____. a. allows for more horizontal scrolling than Windows 7 b. allows for more vertical scrolling than Windows 7 c. allows for scrolling, whereas Windows 7 did not d. no longer has any need for scrolling ANS: A

PTS: 1

REF: MYF 4

TOP: Critical Thinking

51. James asks about touch-screen functionality, specifically how Windows 8 shows ScreenTips for items on a touch screen. You assure him that you simply ____ to show its ScreenTip. a. swipe an object from left to right c. tap an object b. touch and hold an object d. pinch two fingers on an object ANS: B

PTS: 1

REF: MYF 5

TOP: Critical Thinking

52. Lastly, James wants to know how you display a listing of all the apps currently open on a Windows 8 machine, on a touch screen. You tell him simply to ____. a. swipe from the right edge of the screen toward the center b. swipe from the left edge of the screen toward the center c. move two fingers apart in the center of the screen d. triple-tap the screen ANS: B

PTS: 1

REF: MYF 5

TOP: Critical Thinking

COMPLETION 1. The ____________________ appears when you point to the upper-right or lower-right corner of the screen, and displays buttons for interacting with Windows 8 and accessing applications.

ANS: Charms bar PTS: 1

REF: MYF 4

2. Windows stores folders and important files that it needs when you turn on the computer in the ____________________ directory.

ANS: root PTS: 1

REF: MYF 6

3. Windows provides ____________________ in File Explorer so you can organize files by category -documents, music, pictures, and video.

ANS: libraries built-in libraries PTS: 1

REF: MYF 8

4. The File Explorer window is divided into two sections, called ____________________.

ANS: panes PTS: 1

REF: MYF 8

5. Boxes D, E, and F in the accompanying figure point to items in the ____________________ pane.

ANS: navigation PTS: 1

REF: MYF 9

6. In the path “Computer ➔ Removable Disk (G:) ➔ Wizard Widgets ➔ Stock ➔ Staff.jpg” the name of the file is ____________________ .

ANS: Staff.jpg PTS: 1

REF: MYF 11

7. The non-icon views available in File Explorer include ____________________, Details, Tiles, and Content.

ANS: List PTS: 1

REF: MYF 12

8. When working in any view besides Details view, you sort by using the ____________________ tab on the ribbon.

ANS: View PTS: 1

REF: MYF 14

9. You can open a file using File Explorer by ____________________ it in the right pane of File Explorer.

ANS: double-clicking PTS: 1

REF: MYF 15

10. In order to use SkyDrive, you need to have a(n) ____________________ account.

ANS: Microsoft PTS: 1

REF: MYF 17

11. If you drag a file or folder to a location on a different drive, the file is ____________________, not ____________________.

ANS: copied, moved PTS: 1

REF: MYF 21

12. The ____________________ and ____________________ buttons are ideal when you want to move or copy files without leaving the current folder.

ANS: Move to, Copy to Copy to, Move to PTS: 1

REF: MYF 23

13. To select many files that are listed together in the right pane of File Explorer, you click the first item, press and hold ____________________, and then click the last item.

ANS:

Shift the Shift key PTS: 1

REF: MYF 24

14. A duplicate copy of important files is known as a(n) ____________________.

ANS: backup PTS: 1

REF: MYF 25

15. ____________________ a file means making an uncompressed copy of a compressed file.

ANS: Extracting PTS: 1

REF: MYF 26

MATCHING

a. b. c. d. e.

backslash Large icons Clipboard root directory SkyDrive

f. g. h. i. j.

extension folder archive Details library

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

organizes files by category -- documents, music, pictures, video identifies a file’s type a container for files the default view for all folders except in the Pictures library temporary storage area for files and information Microsoft cloud storage space top of the file hierarchy the default view for folders in the Pictures library used to separate locations in a file path a compressed folder

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

ANS: J ANS: F ANS: G ANS: I ANS: C ANS: E ANS: D ANS: B ANS: A ANS: H

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF: REF:

MYF 8 MYF 16 MYF 5 MYF 12 MYF 21 MYF 16 MYF 6 MYF 12 MYF 11 MYF 26

ESSAY 1. Describe some of the text’s best practices for determining where to save files.

ANS: When you create and save files on your computer’s hard disk, you should store them in subfolders.

The top level of the hard disk is off-limits for your files because they could interfere with system files.

If you are working on your own computer, store your files within the My Documents folder in the Documents library, which is where many applications save your files by default. When you use a computer on the job, your employer might assign a main folder to you for storing your work. In either case, if you simply store all your files in one folder, you will soon have trouble finding the files you want. Instead, you should create subfolders within a main folder to separate files in a way that makes sense for you.

Even if you store most of your files on removable media, such as USB drives, you still need to organize those files into folders and subfolders. Before you start creating folders, whether on a hard disk or removable disk, you need to plan the organization you will use. Following your plan increases your efficiency because you don’t have to pause and decide which folder to use when you save your files. A file organization plan also makes you more productive in your computer work—the next time you need a particular file, you’ll know where to find it.

PTS: 1

REF: MYF 8

TOP: Critical Thinking

2. What is SkyDrive? How does one gain access to it, and what are some of its benefits?

ANS: Some Windows 8 applications, such as Microsoft Office, include SkyDrive as a location for saving and opening files. SkyDrive is a Microsoft service that provides up to 7 GB of online storage space for your files at no charge. You can purchase additional space if you need it. For example, if you create a document in Microsoft Word, your SkyDrive appears as a location for saving the document. (Your SkyDrive appears with your username, such as Matt’s SkyDrive.) If you have a Microsoft account, you can select a folder on your SkyDrive to save the document online. (If you don’t have a Microsoft account, you can sign up for one by visiting the SkyDrive website.) Because the file is stored online, it takes up no storage space on your computer and is available from any computer with an Internet connection. You access the document by opening it in Word or by visiting the SkyDrive website, and then signing in to your Microsoft account. To share the document with other people, you can send them a link to the document via email. They can use the link to access the document even if they do not have a Microsoft account.

PTS: 1

REF: MYF 17

TOP: Critical Thinking

3. What are the text’s three main guidelines for creating folders?

ANS: • Keep folder names short yet descriptive of the folder’s contents. Long folder names can be more difficult to display in their entirety in folder windows, so use names that are short but clear. Choose names that will be meaningful later, such as project names or course numbers.

• Create subfolders to organize files. If a file list in File Explorer is so long that you must scroll the window, you should probably organize those files into subfolders.

• Develop standards for naming folders. Use a consistent naming scheme that is clear to you, such as one that uses a project name as the name of the main folder, and includes step numbers in each subfolder name (for example, 1-Outline, 2-First Draft, 3-Final Draft, and so on).

PTS: 1

REF: MYF 19

TOP: Critical Thinking