MIS 112 FINAL EXAM

Yes you can

1. Would you be able to apply the "watch window" option in multiple sheets at the same time?

.xls

10. Which of the following is the file format of the Excel 97-2003 workbook?

Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables

100. How do you change the calculation mode while dealing with a calculation intensive data table?

interest = Principal * Rate *Term

101. The basic formula for computing simple interest is

Both A and B. A) computes depreciation at a fixed rate. B) its arguments are cost, salvage, life, period, [MONTH]

102. With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()?

NPER()

103. Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?

All of the above statements are true

104. Which of the following is TRUE about a Watermark in Excel?

Choose the print scaling option of "Fit All Columns on One Page"

105. If your worksheet is a bit too wide to print on a single page, but you want to shrink it to fit on a single printed page, which of the following should you choose?

Descriptive information about a workbook

11. What is metadata?

SHIFT + S

12. Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?

(=blank/blank)

13. To calculate the total sales of something you simply

A negative number

14. You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry?

To prevent blank cells in the copied range from overwriting existing data

15. What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area?

Alt+PgDn

16. Which of the following options moves the active cell right one screen?

Replace the digits exceeding 15 with the number zero

17. What happens if you try to store a number greater than 15 digits in Excel?

No

18. Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?

Cell

19. A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

[ ]

2. What characters are NOT allowed in sheet names?

Trendline

20. Which of the following chart-related items identifies general trends in your data?

Design (from chart tools) to chart layouts.

21. Which of the following is a VALID sequence of options for selecting a different chart layout?

After you create a chart, you have great deal of flexibility in customizing it.

22. Which of the following is TRUE with respect to charts?

Add chart element then data labels

23. If you want to remove chart labels, where do you find the options to do so within the Design tab (Chart Tools)?

Click on: (chart tools) design tab, move chart (location) then new sheet option

24. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use

Changing the font color in the worksheet

25. Which of the following option is NOT available after right clicking on an Excel worksheet tab?

?, *

26. Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?

10%, 400%

27. What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?

ISLOGICAL

28. Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value?

#VALUE!

29. Corner to corner selected cells will give you the

CTRL + N

3. Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

4500

31. What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000

Allows you to total up numbers in a specified range of cells

32. The =SUM() function does which of the following in Excel?

a comma '

33. Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)?

=A1*Sheet2!A2

36. Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?

_2ndyear

37. Which of the following is a VALID range name in an Excel workbook?

Use the review (proofing) tool of Spelling Error Frequency per Worksheet.

38. Which of the following is NOT a method of creating frequency distributions?

3

39. The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text

A workbook can have many worksheets

4. Which of the following statements is true?

A comment exists for that cell

40. What does the small red triangle on the upper right corner of cell B1 denote?

Shift + F2

41. To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

Selects a rectangular range of cells around the active cell.

42. What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?

?NULL?

43. Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

Y-Test

44. Which of the following is NOT a tool in Analysis ToolPak for Excel?

Field

45. Excel uses column headers to create labels for each _____ on data entry form.

Click on any cell containing salesperson data, from the ribbon pick the data tab, and then click on the "sort Z to A" command button.

46. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct?

delete a record/row

47. A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following:

Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."

48. How can such a Total Row be added to a table?

Table

49. Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or

The ribbon (the tool box in excel)

5. What Excel element is shown in the red box?

The formula returns an error

51. A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?

Left Align

52. Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell)

Sorting a table re-arranges the rows based on the contents of a particular column.

53. Which of the following is TRUE about sorting a table?

When you copy data from a filtered table, both visible data and rows hidden by filtering are copied

54. What of the following is FALSE about filtering a table?

Clear Rules

55. What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

Specific

56. Highlight Cell Rules and Top/Bottom Rules use a single type of formatting to highlight ________ cell(s) based on the condition or requirement.

Top/Bottom Rules, Bottom 10%

57. What conditional formatting rule is applied in cells A1:A5?

Rows

58. The HLOOKUP function searches for data in ______.

=HLOOKUP(B2, E1:J3.3)

59. What formula (in cell B3) would return the tax rate for the income in cell B2?

AA

6. In an Excel worksheet, what column name comes directly after Column Z?

INDEX()

60. Which function returns the contents of a cell at the intersection of a specified row and column from a range?

It returns the relative position of an item in an array that matches a specified value in a specified order.

61. Which of the following best explains the MATCH function?

28

62. The formula =LOOKUP("Mary", A2:A5, C2:C5) returns what value?

31%

63. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,500?

The formula contains an invalid cell reference

65. Which of the following returns a #REF! error value?

#REF!

67. Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet

data is typed directly into the cells

68. Input messages and error alerts (for data validation) appear only when

Data Tab , Data Tools (group), Data Validation, Error Alert, style warning

69. How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?

Home

7. After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?

none of the options provided is valid

70. __ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox

forces the cell A1 to accept only text

71. What is the use of the following data validation formula: =ISTEXT(A1)?

All of the file types

72. Which of the following file types can Excel import?

Choose Data, Get External Data \ From Text

73. What command would you use to import a CSV file?

Link_location is required. Friendly_name is optional

74. What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)

75. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4?

Data (tab), connections (group), Properties.

76. Which is one of the ways to refresh a query used to import data from external data source using the query wizard?

Columns

77. In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query.

Copy the URL and paste it into the name field in the open dialog box

78. To open an HTML file stored on a Web server using Excel, what needs to be done

Data in a table

79. Which of the following can be retrieved from a web page using web queries?

begin the URL query string

80. While creating a parameterized web query the question mark (?) is used to

also changes

81. A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other

Insert (Tab), charts (group), PivotChart

82. Which of the following is one of the ways to create a Pivot Chart?

Compares the amount in the three branches on the basis of AcctType

83. What does the pivot chart depict?

specify a time period for which you need the data

84. A date filter in Pivot Tables is used to

Columns from the sources data and Groups defined in the Pivot Table

85. The Pivot Table Field List shows which of the following?

clear all

86. When working with Pivot Tables you can remove all the fields from the report so that you can start over. To do this, from the Excel Ribbon, on the Analyze tab, go to the Actions group. Then click the arrow on the Clear button, and select

slicer

88. __ is an interactive control that makes it easy to filter data in a pivot table

June 23 2014

89. Which of these dates will Excel store as a text string rather than a serial number?

Synchronous Scrolling

9. While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called?

=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))

90. 20130315 represents a date March 15, 2013. To convert this string to an actual date, which of the following formulas can be used? (Assume the data 20130315 is in cell A1)

It searches for a date as displayed in the formula bar, and then does a replace

91. How does the Excel search/replace function work for dates?

Converts the input hours, minutes and seconds to a serial number.

92. What is the purpose of the function TIME()?

None of the options provided is correct

93. How do you format a valid date serial number as a date?

=TODAY()-MOD(TODAY))-1,7)

94. Which of the following formulas is used to determine the date of the Sunday that was most recent?

SEARCH()

95. locates a substring in another text string and returns the start position of the substring

255

96. For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.

CONCATENATE()

97. This function combines its arguments into a single text string. It is an alternative to using the ampersand operator

The PROPER() function doesn't handle names with an embedded capital letter such as McDonald

98. Which of the following options is TRUE with respect to the function PROPER() in Excel?

RATE(nper,pmt,pv,[fv],[type],[guess])

99. What is the syntax for the RATE() function?

Available, deleted

Using the =NA() function in a cell, makes it clear that data is not _______ and hasn't been ______ accidentally.

=COUNT ()

can total the number of days or weeks

=SUM

formula will give you total. Meaning adding