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
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