Do you know that? Frequently use a to-do list. next

Contact Email: teammmrem@yahoo.com

<< All Upwork (oDesk) and Elance Tests << Elance test answers << Fin. & Mgt. category

Test answers for Excel 2016

(719 / 2) Last updated: January 23
Elance • Fin. & Mgt.
Questions and answers are regularly updated. Answered and not answered tests questions.

This helps getting job: Hundreds of (cover letter examples , interview questions , profile samples ) • Earn on Upwork (oDesk)
Job assistance: jobs popularityfreelance rates

Popular test answers: HTML, .Net, CSS, English, SEO, Photoshop, iOS, Java, Android, Ruby, Python, JavaScript

See all 6 tests answers updated

Find Upwork (oDesk) and Elance test answers on this website:



Collapse | Expand

719 Answered Test Questions:

1. A user can sort a maximum of...

Answers:

• 3 columns

• Infinite columns

• 10 columns

• 5 columns

• 7 columns

2. How do you make sure that a formula's result will always fit into the cell?

Answers:

• Set the cell's format to "Shrink to fit".

• Make the cell very large.

• Set the font size to 8 or less.

• Use Zoom to see the cell.

3. True or False: There are three worksheets with every new workbook. You can change that automatic number if you want to.

Answers:

• True

• False

4. To get all rows and columns of data to print on one sheet, you can adjust the page breaks in Page Break Preview or:

Answers:

• Select File, Print, Print Selection

• Select File, Print, Print Entire Workbook

• Select File, Print, Fit Sheet on One Page

• Select File, Print, Fit All Columns on One Page

5. What is the correct syntax for typing a function into a cell?

Answers:

• Function name, argument, equals sign

• Function name, equals sign, arguments

• Equals sign, function name, arguments

• Equals sign, function name, result

6. Which formula correctly references cell A1 on Sheet2?

Answers:

• =Sheet2:A1

• =Sheet2A1

• =Sheet2|A1

• =Sheet2-A1

• =Sheet2!A1

7. To view a cell comment, do which of the following?

Answers:

• click the comment command on the view menu

• position the mouse pointer over the cell

• click the display comment command on the window menu

• click the comment command in the insert menu

8. Does delete button remove cell?

Answers:

• DELETE has no effect on cells

• Deletes all spreadsheets data

• Deletes entire Row

• YES it removes cell

• NO, it clears cell contents

9. In a large data set with 121,005 rows, You are on the top row (row 1) of the data set. What is the fastest way to get to row 121,005?

Answers:

• ctrl + D

• ctrl + A + delete

• ctrl + down arrow

• double click down arrow

10. Which keyboard shortcut pastes copied information?

Answers:

• Shift + v

• Ctrl + v

• Ctrl + c

• Ctrl + p

• Ctrl + Shift + v

11. How many sheets are there in Excel Workbook by default?

Answers:

• 2

• 4

• 3

• 5

12. You can use the formula pallette to...

Answers:

• enter assumptions data

• create and edit formula containing functions

• copy a range of cells

• format cells containing numbers

13. How do you create Word Art?

Answers:

• On the Page Layout tab, in the Themes group, click Effects

• On the Insert tab, in the Text group, click WordArt

• On the Page Layout tab, in the Page Setup group, click WordArt

• On the Review tab, in the Text group, click WordArt

14. Which of the following formulas will Excel NOT be able to calculate?

Answers:

• =SUM(A1:A5)-10

• =SUM(A1:A5)*.5

• =SUM(Sales)-A3

• =SUM(A1:A5)/(10-10)

15. What is the correct formula to calculate the total of cell B3 and B4?

Answers:

• =SUMMARY(B3+B4)

• =SUM B4+B3

• =summary(b3+b4)

• =TOTAL(B3+B4)

• =SUM(B3:B4)

16. Which worksheet function returns the F probability distribution?

Answers:

• TDIST

• FDIST

• ZDIST

• SQDEV

• QIDIST

17. Which of the following formulas is not entered correctly?

Answers:

• =B1(N1)

• 98-B11=

• =98-B11

• =B1/N1

18. You can change the color of the following in Excel:

Answers:

• Borders

• Headers/Footers

• Background

• All Listed

• Font

19. True or False: A sparkline usually shows trend information.

Answers:

• False

• True

20. What is the home position in an Excel worksheet?

Answers:

• B3

• H1

• H

• A0

• A1

21. You can convert your Excel document into a Google doc.

Answers:

• False

• True

22. In order to change page margin, which menu would you choose to select "Margins"?

Answers:

• Review

• Insert

• View

• Data

• Page Layout

23. Each Excel file is called a workbook because

Answers:

• You have to work hard to create it

• It can contain many sheets including worksheets and chart sheets

• It will have text in it

• It can be modified

24. True or false:  To change column width in MS Excel, put cursor on the right side of the cell in that column,  in the first row.  Click and hold and drag to desired width.

Answers:

• False

• True

25. Clipart can be found under what menu?

Answers:

• Review

• Insert

• Data

• File

• Formulas

26. What is a cell?

Answers:

• A series of rows.

• The total of two numbers.

• A header.

• A series of columns.

• The intersection of a column and row.

27. True or False: You can create a two-input data table.

Answers:

• False

• True

28. A collection of worksheets contained within a single file is called a ____ .

Answers:

• Range

• Cell

• Book

• Workarea

• Workbook

29. In order to multiply items in Excel, you would use which symbol?

Answers:

• !

• /

• *

• #

• @

30. You can embed Tables from Excel in:

Answers:

• Both

• Neither

• Power Point

• Word

31. Where can you insert a text box in Excel?

Answers:

• Anywhere

• In cells that do not currently contain any data or text

• Only in designated rows

• Only in columns with red arrows in the corner

• Only in designated columns

32. What does Live Preview allow the user to do?

Answers:

• Store frequently used features

• Control the magnification of the screen

• See how a new font, font size, table size, or cell size will look on selected data before it applies

• View distant parts of a worksheet at the same time

33. What is one way to get a new workbook?

Answers:

• File, Save

• Edit, Cut

• File, New

• Escape Key

• Format, Row

34. Can Excel spreadsheets contain images?

Answers:

• No, Excel files cannot have images

• A special add-on must be installed

• Only when the Workbook is imported from another file format

• Yes

• They can be inserted only with Macros

35. How do you create a table on excel?

Answers:

• insert>table

• data>table

36. True or False: You can customize your toolbars and menus.

Answers:

• False

• True

37. Can you save a .xlsx file as a .xls file?

Answers:

• Yes

• No

38. Is there a way to share your spreadsheet and allow someone to change it?

Answers:

• Yes

• No

• Only by email

39. How does one change the font style of the selected cell to *bold*.

Answers:

• (Ctrl+B)

• click twice

40. Each location in an Excel spreadsheet is called a:

Answers:

• Cell

• Square

• Tab

• Cube

41. What-If analysis functionality in Excel includes:

Answers:

• Goal Seek

• Data Tables

• All of these

• Scenario Manager

42. Which of the following is not a term of MS-Excel?

Answers:

• Columns

• Rows

• Cells

• Document

43. Which of the following is NOT a way to align a cell?

Answers:

• Align Center

• Align Reverse

• Align Left

• Align Right

44. Which types of charts can excel produce?

Answers:

• Bar charts and line graphs only

• Line graphs and pie charts only

• Bar charts, line graphs and pie charts

• Only line graphs

45. Can you create Macros on excel?

Answers:

• false

• true

46. What does the function DATE(year, month, day) do?

Answers:

• Adds the number of days in between dates

• Extracts the day of the month from the date value

• Extracts the year number from the date value

• Creates a date value for the date represented by the year, month, and day arguements

47. Currency figures in Excel can be changed from US Dollars to the:

Answers:

• Euro

• None Listed

• Yuan

• Rupee

• All Listed

48. What is the short cut for saving a file in Excel?

Answers:

• Ctrl + F

• Command + K

• Ctrl + S

• Shift + S

• Shift + C

49. Excel can retrieve external data from which of the following sources?

Answers:

• All of these

• SQL Server

• Microsoft Query

• XML Data Import

• Data Connection Wizard

50. In order to multiply items in Excel, you would use which symbol?

Answers:

• &

• !

• *

• /

• #

51. The following functions are listed when the office button is selected:

Answers:

• Save as

• Publish

• all of these are functions

• Print

• Close

52. What does the Excel function Lookup & Reference do?

Answers:

• Provide statistical analyses of a set of data

• Return logical values

• Look up and return data matching a set of specified conditions from a range

• Analyze engineering problems

53. What does the function FV stand for?

Answers:

• Present Value

• Future Variety

• Future Value

• Current Value

54. What is one way to get a new workbook?

Answers:

• Escape key

• Format, Row

• File, New

• Edit, Cut

• File, Save

55. True or False: Excel has the capability to sort horizontally from left to right or right to left.

Answers:

• True

• False

56. The standard name of each cell is based on its ___________ and __________

Answers:

• Height and width

• Age and sequence

• Level and priority

• Row and column

57. To delete an embedded object,

Answers:

• select the object by clicking it

• double click the object

• select it and then press the delete key

58. Which of the following formats can you decide to apply in the AutoFormat dialog box?

Answers:

• Number format

• Border format

• Font format

• All of these

59. Which of the following is a "Lookup and Reference" function?

Answers:

• VLookup

• Average

• Sum

• Sumif

• Iferror

60. Which types of charts can excel produce?

Answers:

• Bar charts, line graphs and pie charts

• Line graphs

• Bar charts and line graphs only

• Line graphs and pie charts only

61. True or False: Formulas can be edited across multiple worksheets at the same time.

Answers:

• True

• False

62. A formula in Excel must begin with the operator:

Answers:

• =

• @

• --

• /

63. Is it possible to determine the day of the week for a particular date?

Answers:

• No, this will result to computer error.

• No, Excel does not have this capability

• Yes, Excel offers several ways of doing so.

64. a formula is always started with what symbol?

Answers:

• $

• -

• +

• *

• =

65. True or False: A Name can be applied to a single cell or a range of cells.

Answers:

• False

• True

66. What company distributes Excel?

Answers:

• Intuit

• Apple

• Intel

• Turbotax

• Microsoft

67. Where do you find the picture icon?

Answers:

• The review tab.

• The data tab.

• The insert tab.

• The formula tab.

• The home tab.

68. What does a formula usually start with?

Answers:

• -

• $

• =

• +

• /

69. True or False: Charts from Excel can be pasted into Power Point.

Answers:

• True

• False

70. Each Excel file is called a workbook because

Answers:

• It can be modified

• It can contain many sheets including worksheets and chart sheets

• It will have text in it

• You have to work hard to create it

71. Formulas in excel start with?

Answers:

• %

• =

• (

• -

72. In the formula =sum(B1:B5), what does the ":" represent?

Answers:

• Last Number

• Several Numbers

• All numbers between (if applicable)

• And

• First Number

73. Can you add borders to cells without having a border for the workbook?

Answers:

• No, cells cannot have borders, they can only be added to the entire workbook.

• No, this function is only available in Word.

• Yes, individual cells or a collection of them can have borders.

74. It is possible to password protect content of selected cells so that cannot be modified

Answers:

• True

• False

75. Can you print only a selected part of your workbook?

Answers:

• No, you cannot select parts of a workbook, only the number of pages to print.

• Yes, you can control what part of the workbook is printed.

76. True or False: It is possible to have a folder of templates in Excel to make it easier to access them.

Answers:

• True

• False

77. True or False: You can protect a single worksheet and leave the others with full access.

Answers:

• True

• False

78. Can you insert images from ClipArt into Excel?

Answers:

• Yes

• No

79. What do you use to create a chart?

Answers:

• Data Wizard

• Pie Wizard

• Chart Wizard

• Excel Wizard

80. What is the formula used to multiply cell A1 by cell C1?

Answers:

• = A1/AC

• =A1XC1

• =A1+AC,times

• = A*C,1

• =A1*C1

81. Cell A1 contains the formula =$F$39. Copying that formula to cell B2 will result in:

Answers:

• =$A$1

• =$F40

• =$F$39

• =G40

82. True or False: Excel has trigonometry functions built into it.

Answers:

• True

• False

83. Is it possible to sort by the color of a cell in the most recent version of excel?

Answers:

• No

• Yes

84. How do you change text's alignment within a cell?

Answers:

• Select the cell and click the appropriate alignment button in the alignment group on the Home tab

• Use the commands in the font group on the Home tab

• Select auto-fit in the cell

• You can not align an individual cell

85. How many conditions can be set for an individual cell's conditional formatting?

Answers:

• 0

• 2

• 1

• 3

• Any of these

86. You can sort rows by:

Answers:

• Smallest to Largest

• Oldest to Newest

• All listed are correct

• A to Z

• Z to A

87. The functions AVERAGEIF and SUMIF both use cell values that match specified criteria.

Answers:

• True

• False

88. Are you able to do spell check in Excel?

Answers:

• No

• Depends on Excel version

• Yes

• Yes, but only if there are no charts in the workbook.

89. What happens when you double click on a cell?

Answers:

• Nothing

• It become editable

• It is highlighted

90. How do you view the 5th decimal place when calculating costs?

Answers:

• Convert to text and view.

• Cannot view more than 2 decimals on currency.

• Increase the decimal to 5 digits.

• Set to smaller font size.

91. True or False: When you record a macro in Excel, you are actually creating a VBA program.

Answers:

• False

• True

92. True or False: A pivot table is a "dynamic summary report generated from a database."

Answers:

• True

• False

93. If you want to set the print area which ribbon should you use?

Answers:

• FORMULA

• DATA

• HOME

• PAGE LAYOUT

• REVIEW

94. True or False: It is possible to access Outlook, Word or other Microsoft products through Excel.

Answers:

• True

• False

95. A ________________ consists of a grid made from columns and rows?

Answers:

• Formula

• Spreadsheet

• Label

• Cell

96. Pressing CTRL-Home moves the cursor to:

Answers:

• moved down

• cell a1

97. It is normal practice that numbers are treated as decimals when entering into a cell, but is it possible to enter a fraction (eg 3/4) into a cell in Excel?

Answers:

• Yes

• No

98. Can a macro save a file?

Answers:

• No

• Yes

99. True or False: You can add new commands to the QAT.

Answers:

• False

• True

100. How do you close a formula?

Answers:

• (

• "

• ,

• )

• %

101. True or False: You can create a connection to a SQL Server Analysis Services Cube in Excel.

Answers:

• True

• False

102. Can the Match & Index Functions be combined together to perform a lookup?

Answers:

• Yes

• No

103. How do you ensure that the person using a spreadsheet can always see the column headings?

Answers:

• Reset the view Zoom percentage.

• Use Freeze Panes or Freeze Top Row.

• Re type the headings every few lines.

• Print to fit on one page.

104. Can Excel spreadsheets contain images?

Answers:

• Yes

• They can be inserted only with Macros

• No, Excel files cannot have images

• Only when the Workbook is imported from another file format

• A special add-on must be installed

105. If you have an error, how can you find ways to fix it?

Answers:

• You cannot request assistance.

• Left click on the triangle, pause until you see a question mark, click on the arrow, and scroll to " Help on this error."

• Go to "Data" and click on data tools.

106. True or False: Excel will recognize patterns in data and automatically enter formulas where the patterns continue.

Answers:

• False.

• True.

107. How do you change text's alignment within a cell?

Answers:

• Select auto-fit in the cell

• You can not align an individual cell

• Select the cell and click the appropriate alignment button in the alignment group on the Home tab

• Use the commands in the font group on the Home tab

108. How many sheets are there in Excel Workbook by default?

Answers:

• 5

• 3

• 4

• 2

109. True or False: A Name can be applied to a range of non-contiguous cells.

Answers:

• False

• True

110. Which one of the following formulas cannot be calculated by Excel?

Answers:

• =SUM(A1:A5)/(10-8)

• =SUM(A1:A5)*.5

• =SUM(Sales)-A3

• =SUM(A1:A5)-10

111. You can sort rows by:

Answers:

• All listed are correct

• Oldest to newest

• A to Z

• Z to A

• Smallest to largest

112. You have two columns summing your account totals.  The first column looks exactly right but the second one is not displaying the way you want.  How do you make the two columns look the same?

Answers:

• Copy the first column. Select the second column. Paste.

• Copy the first column. Select the second column. Use "Paste Special" to paste "Formats".

• Delete both columns. Reytpe values.

• Delete second column. Insert new column. Retype values.

113. Is SmartArt available in Excel?

Answers:

• No, it is only available in Word.

• Yes

• No, it is only available in PowerPoint.

114. A ________________ consists of spreadsheets.

Answers:

• form

• column

• row

• cell

• workbook

115. True or False: The value in a cell that contains the formula =RAND() changes every time any change is made to the workbook.

Answers:

• True

• False

116. Data can be arranged in a worksheet in an easy to understand manner by

Answers:

• Changing decimals

• Applying Styles

• All of these

• Changing fonts

• Auto Formatting

117. How does one save the active workbook without altering the file or folder name.

Answers:

• Highlight and drag a cell.

• Double click the box.

• Copy and paste.

• Click save button (on top of home)

118. Cells D3 through Z3 list sales total for various months.  What does the equation "=SUM(D3:Z3)" show?

Answers:

• Total sales for all the months in D3 through Z3.

• Sale averages for each month.

• Sales quota for each month.

• Average sales for all the months in D3 through Z3

119. True or False: You can convert an existing shape to a Freeform Shape.

Answers:

• False

• True

120. How would you rename the tabs at the bottom of the spreadsheet?

Answers:

• Both ways: right click on tab and type new name or double click tab and type new name.

• Left click tab and type new name.

• Single click on column header and type new name.

121. What is a logical function?

Answers:

• A function that inserts or calculates dates or times

• A function related to monetary calculations

• A function that works with statements that are either true or false

122. A pivot table allows you to:

Answers:

• Create a worksheet with data using a filtering window where you can include or exclude desired fields of the original table

• Merge columns and rows together

• Import data from MS Word

• Save data within a cell

• Maneuver from one application database such as Access to another such as Excel

123. Which keyboard shortcut copies cell data?

Answers:

• Ctrl + Shift + c

• Shift + c

• Ctrl + v

• Ctrl + c

• Shift + v

124. True or False: You can save any Excel document as a web page.

Answers:

• False

• True

125. Which is a type of operator?

Answers:

• Reference

• Arithmetic

• All of these

• Text Concatenation

• Comparison

126. What happens when you double click on a cell?

Answers:

• It become editable

• Its contents are deleted

• Nothing

• It is highlighted

127. What is the purpose of the ?? button?

Answers:

• Recording a macro

• Sorting Data

• Auto-saving

• Totaling a column or row

128. If you want to highlight a row of numbers by less than, greater than or equal to, what should you use?

Answers:

• Print Preview

• Page Layout

• Conditonal Formatting

• Font

• Fx

129. True or False: It is possible to sort information both vertically (by columns) and horizontally (by rows)

Answers:

• True

• False

130. True or false? The Quick Access Toolbar (QAT) is useful for adding popular commands.

Answers:

• False

• True

131. How do you insert saved images?

Answers:

• Time's Up!

• Choose Insert/Object

• Choose Home/Images

• Choose Insert/Picture

• Choose Home/Illustrations/Picture

132. Your spreadsheet is printing, but the grid lines aren't showing up.  What is most likely the problem?

Answers:

• Need to set the cell to bold.

• The formatting for grid lines isn't set on the cell.

• Change out the printer cartridge.

• Go to page set up and set to print as black and white.

133. Identify the error: When entering 325 in a cell, the result shows up as 3.25.

Answers:

• This is a common glitch in Excel 2007. Upgrade ASAP.

• "What-if" analysis has converted all entries for simple compiling.

• Rounding has been set to reduce all numbers by 99%.

• Fixed Decimal mode is turned on.

134. True or False: Once cells are merged in Excel, they can never be un-merged.

Answers:

• True

• False

135. Cells B3 through B121 list several values.  What equation shows the average of those values?

Answers:

• =SUM(B3:B121)/10

• =AVERAGE(B3:B121)

• =COUNT(B3:B121)

• =SUM(B3:B121)

136. How do you make a chart?

Answers:

• Highlight cells of interest go to Tools, Chart

• Highlight cells of interest go to Edit, Chart

• Highlight cells of interest go to File, Cart

• Highlight cells of interest go to Insert, Chart

137. How do you control which cells are printed?

Answers:

• Highlight the cells you want to print, go to File, and select Print Area => Set Print Area

• Highlight the cells you want to print, go to Format, select Format Cells, and select the Border option. Then place a border around the cells you want to print.

138. How do you keep others from changing the values on a worksheet?

Answers:

• Use track changes to see what has been changed.

• Use "Protect Sheet" and assign a password.

• Make all values Bold font.

• Name the file "Do not change.xls"

139. You have two columns summing your account totals.  The first column looks exactly right but the second one is not displaying the way you want.  How do you make the two columns look the same?

Answers:

• Delete both columns. Reytpe values.

• Copy the first column. Select the second column. Use "Paste Special" to paste "Formats".

• Copy the first column. Select the second column. Paste.

• Delete second column. Insert new column. Retype values.

140. True or False? A pivot table must have column headers.

Answers:

• True

• False

141. lookup _value argument is the value that is searched for in the first column of the table array

Answers:

• True

• False

142. The "Merge & Center" button does what?

Answers:

• Combines the quantities in two cells and centers the result

• Changes the font size on a cell

• Merges a cell with text with attached cells and centers the text over the range of cells

• Merges cells together that are not physically attached

143. To ensure a worksheet will print all on one page, adjust the settings in the:

Answers:

• Page Layout/Page Setup dialog box

• Paper size option

• Margin Dialog box: Shrink to Fit

• Zoom command

144. True or False: You can download content from the net directly into Excel.

Answers:

• False

• True

145. If $34 is entered into a cell, Excel will interpret it as:

Answers:

• A reference to the sum of values in the 34th row

• A value(number) with currency format

• Text

• A #NAME? error

146. True or False: Frequency is an array function.

Answers:

• False

• True

147. How can you change the date format of a cell or group of cells?

Answers:

• right click, copy, paste

• insert, data, choose format

• Ctrl + F3

• type preferred format into formula bar

• Select cell or cells, right click, format cells, date, choose option.

148. Which worksheet function returns the average of selected database entries?

Answers:

• DCOUNT

• DCOUNTA

• DAVG

• DAVERAGE

• DGET

149. When a formatted number does not fit within a cell, what does the cell display?

Answers:

• #DIV/0

• #####

• #DIV@

• none of these

150. True or False: You cannot insert comments for individual cells.

Answers:

• True

• False

151. What is the proper shortcut for "Refresh All"?

Answers:

• Ctrl + Alt + F5

• Ctrl + A

• Shift + A

• Ctrl + Shift + A

152. The formula =NOW() displays:

Answers:

• The time the file was last changed

• The time the file was last saved

• The local computer's actual date and time

• The time the file was opened

153. You can edit a cell by

Answers:

• Clicking the formula button

• Selecting Edit>Edit Cell from the menu

• None of above

• Double clicking the cell to edit it in-place

154. A pivot table allows you to:

Answers:

• Save data within a cell

• Create a worksheet with data using a filtering window where you can include or exclude desired fields of the original table

• Merge columns and rows together

• Maneuver from one application database such as Access to another such as Excel

• Import data from MS Word

155. True of False:  You can filter an Excel Pivot chart so that it shows only the information you want it to show.

Answers:

• True

• Only by creating completely new pivot tables

• False

• You can only filter subsets in a Pivot table

156. You can change the color of the following in Excel:

Answers:

• Font

• Background

• All Listed

• Borders

• Headers/Footers

157. What is the best formula to use to calculate 12 to the 4th power?

Answers:

• 4!12

• =12^4

• =12!4

• =12+12+12+12

• =12*12*12*12

158. Which worksheet function converts a time in the form of text to a serial number?

Answers:

• TIME

• SECOND

• NETWORKDAYS

• TIMEVALUE

• NOW

159. What does this formula do? =IF(E6=G6;"Nice";"Not so Nice")

Answers:

• It changes your font color to Red

• Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'

• Sums up the range E6 to G6 and if the sum is greater than zero then, the formula returns 'Nice'

• Creates borders around cell G6

160. A ________________ consists of a grid made from columns and rows?

Answers:

• Spreadsheet

• Label

• Formula

• Cell

161. How do you ensure that the person using a spreadsheet can always see the column headings?

Answers:

• Re type the headings every few lines.

• Print to fit on one page.

• Use Freeze Panes or Freeze Top Row.

• Reset the view Zoom percentage.

162. Which of the following Excel screen components CANNOT be turned on or off?

Answers:

• Formula Bar

• None of above

• Status Bar

• Tool Bar

163. What does this formula do? =IF(E6=G6;"Nice";"Not so Nice")

Answers:

• It changes your font color to Red

• Sums up the range E6 to G6 and if the sum is greater than zero then, the formula returns 'Nice'

• Creates borders around cell G6

• Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'

164. True or False: A fixed dollar sign appears to the far left in the cell often with spaces between it and the first digit.  Whereas a floating dollar sign appears immediately to the left of the first digit with no spaces.

Answers:

• False

• True

165. What does the equation =TODAY() show?

Answers:

• The Julian date

• The current date from Microsoft

• The current date on your computer

• Error since there has to be something in () for the equation to work.

166. What does an asterisk signify in a formula?

Answers:

• Add

• Subtract

• Multiply

• Divide

• Square Root

167. How to you create a pivot table?

Answers:

• Insert>pivot table

• right click> options> pivot table

168. You can edit a cell by

Answers:

• Selecting Edit>Edit Cell from the menu

• None of the Above

• Double clicking the cell to edit it in-place

• Clicking the formula button

169. Which function  will return the largest value within the range?

Answers:

• MID

• Excel does not have such a function.

• LN

• NLARGE

• MAX

170. How can an interactive chart be created?

Answers:

• Only with Active-X Controls

• Only with a third-party add-in

• Only with Form Controls

• With Form Controls, or with Active-X Controls, or with Custom VBA Userforms

• Only with Custom VBA Userforms

171. To create another worksheet that is exactly the same as the current worksheet you would...

Answers:

• Insert workbook

• Move or copy

• Insert worksheet

172. The Doughnut chart type

Answers:

• Compares values from different categories. Values are indicated by the height of the columns

• Compares relative values of different categories to the whole. Similar to the pie chart except that it can display multiple sets of data.

• Compares three sets of values in a three-dimensional chart

• Compares a collection of values from several different data sets

173. How do you turn data from rows into columns in Excel?

Answers:

• You Can't

• Regular Copy and Paste Process

• Copy data and then use Paste Special option with Transpose box checked.

• Sort Function

174. In Excel a ____ is an instruction in a spreadsheet to carry out a calculation

Answers:

• VLOOKUP

• Formula

• Function

• Data

• Insert

175. What tool is used to split the window into different parts of the same worksheet?

Answers:

• Title Bar

• Split Box

• Duplicate Box

• Seperate Box

• Remove Box

176. To create another worksheet that is exactly the same as the current worksheet you would...

Answers:

• Move or copy

• Insert workbook

• Insert worksheet

177. What will the formula =A1*C1 return?

Answers:

• An error

• The sum of A1 and C1

• A1 raised to the power of C1

• The product of A1 and C1

• B1

178. How do you change the orientation of a spreadsheet from portrait to landscape?

Answers:

• Page layout > orientation > landscape

• Page layout > landscape > orientation

• Page layout > scale to fit > landscape

179. True or False: Sparklines are a new part of Excel 2010/2011 and are not available in previous versions.

Answers:

• False

• True

180. Which would you choose to create a bar diagram?

Answers:

• Tools, Chart

• Format, Chart

• Edit, Chart

• Insert, Chart

181. To insert a character that is not part of an alphabetic font sent (such as copyright ??) use the:

Answers:

• Draw command to create it

• Character set in WORD and copy into the worksheet

• Insert/Symbol commands

• =@(C) function

182. Does delete button remove cell?

Answers:

• YES it removes cell

• NO, it clears cell contents

• Deletes entire Row

• DELETE has no effect on cells

• Deletes all spreadsheets data

183. The Solver in MS Excel is used for what?

Answers:

• To find only optimal minimum solutions to linear programming problems

• To find only optimal maximum solutions to linear programming problems

• To find optimal minimum or maximum solutions to linear programming problems

184. After typing data or a formula into a cell and hitting ENTER, the default action is:

Answers:

• Move Right one cell

• Stay in the cell

• Move up one cell

• Move down one cell

185. Pressing CTRL-Home moves the cursor to:

Answers:

• The upper left cell in the active range

• The A column within the current row

• The upper right cell in the active range

• Cell A1

186. In Excel a ____ is an instruction in a spreadsheet to carry out a calculation

Answers:

• Formula

• VLOOKUP

• Insert

• Data

• Function

187. What tool allows you to summarize and analyze data in various categories?

Answers:

• copy and paste

• Pivot tables

• Pie chart

• Square root analysis

188. What do you click on to navigate between worksheets in a workbook?

Answers:

• Tab

• Cell

• Rows

• Files

189. When you write up a lookup formula to look up the text "budget", the formula considers any of the following a match: BUDGET, Budget, or BuDgEt. Is it possible to perform a case-sensitive lookup?

Answers:

• No

• Yes

190. True or False: Sparklines are also often called "micro charts."

Answers:

• False

• True

191. Can Excel open two documents with the same file name simultaneously?

Answers:

• No

• Yes

192. In an alphabetical sort, If two cell contents are identical, Excel sort?

Answers:

• merging the two cells

• Placing the item that came first in the original list before the second item

• None of these

193. Which symbol is used as a reference operator?

Answers:

• A Space

• A Colon

• All of these can be used

• A Comma

194. Which solver method will change variable cells to meet an objective cell?

Answers:

• Evolutionary

• All Solver Methods

• LP Simplex

• Cannot be done in Solver

• GRG Nonlinear

195. When you insert a row, you have to reset all the equation references because:

Answers:

• The results will skew otherwise

• Inserting a row does not require you to reset the equation references

• The columns will resize

• The formatting has to change as well

196. If you would like to count the number of cells  within a range that fit a specific criteria, which function would use?

Answers:

• COUNTONLYFIT

• COUNTTHOSEARE

• NUMFITCRITERIA

• COUNTIF()

• IFFIT

197. What tool allows you to summarize and analyze data in various categories?

Answers:

• Pie Chart

• Copy and paste

• Pivot tables

• Square root analysis

198. A function inside another function is called a _____ function.

Answers:

• 2nd Level

• Nested

• Round

• Text

199. Where do you place a MACRO that is supposed to run automatically after every entry on a worksheet?

Answers:

• Must manually run the Macro after each entry.

• On the worksheet tab, go to "View Code" and place the Macro there.

• Must use a Form to do this.

• In a cell, place a hyperlink to the code on a webpage.

200. What tool allows you to summarize and analyze data in various categories?

Answers:

• Pivot Table

• Copy and Paste

• Pie Chart

• Square Root Analysis

201. The Freeze Panes function allows the user to:

Answers:

• keep an area of a worksheet visible while you scroll to another area of the worksheet.

• drag an existing formula across multiple cells within a range by utilizing the following.

• lock in all existing formulas and formats within the cells that they currently reside.

• add a macro which sets size-specific formatting within a worksheet.

202. You have a table of data listing each sales person in one column and their territory in a second column.  Each sales person is only listed once but there are hundreds of sales people.  What function could you use to quickly display a salesperson's territory by typing in their name?

Answers:

• SUBSTITUTE

• TEXT

• VLOOKUP

• ISERROR

203. Which worksheet function returns the individual term binomial distribution probability?

Answers:

• BETADIST

• BETAINV

• AVERAGEA

• BINOMDIST

• AVEDEV

204. If the source of sparkline data contains non-numeric data, they are neglected while plotting the sparklines.

Answers:

• True

• False

205. How do you make sure that a MACRO won't automatically run on a spreadsheet you've downloaded?

Answers:

• Set your Security settings so that Excel will ask permission first.

• Control-Alt-Delete

• Don't download spreadsheets from the Internet.

• Impossible to block unless you open the spreadsheet and delete/modify the MACROs

206. Pressing Ctrl - Home moves the cursor to

Answers:

• the A column within the current row

• cell A1

• the upper left cell in the active range

• the upper right cell in the active range

207. How could one delete an embedded object?

Answers:

• Press Alt + Delete

• Select it

• Double-click it

• Press Shift + Delete

• Select it, press Delete

208. If there is text in a column and you try to sort the entire column:

Answers:

• an error message will appear

• it will not sort the column

• sorting function will be disabled

• it will keep the text in the same cell and sort the numeric values

209. In which tab is the page breaks preview located?

Answers:

• file

• help

• tools

• View

• design

210. Which of the following cannot be included in a header/footer?

Answers:

• Dates

• Images

• Page Numbers

• Formulas that calculate

211. In a bar graph the horizontal axis is the:

Answers:

• X-axis

• Secondary axis

• Y-axis

• Latitudinal axis

212. How are columns named?

Answers:

• Metamorphically

• Numerically

• Alphabetically

• Symbolically

213. What is one easy method to see if your spreadsheet will print on one page?

Answers:

• Adjust the Zoom to 100%.

• Use Page Break Preview.

• Select "landscape" on paper set up.

• If it fits onto the screen, it will print on one page.

214. True or False: To calculate trig functions in degrees, you must convert them, or Excel will calculate them in radians.

Answers:

• True

• False

215. Excel's data validation feature enables you to...

Answers:

• only accept data with a citation

• limit what kinds of graphs a user can create

• set up certain rules that dictate what can be entered into a cell

• request that all edits be sent to you for approval

216. In Sheet1, column A is January's sales numbers.  In Sheet2, column A is February's sales numbers.  Which equation on Sheet 3 totals both columns, in row 5?

Answers:

• =Sheet1 + Sheet 2

• =Sheet1!A5+A5

• =Sheet1!A5+Sheet2!A5

• =A5+A5

217. What is a column on a worksheet?

Answers:

• A horizontal group of cells within a worksheet.

• A lateral group of cells within a worksheet.

• A diagonal group of cells within a worksheet.

• A vertical group of cells within a worksheet.

• A consecutive group of cells within a worksheet.

218. True or False: It is possible to upload a SWF file into Excel.

Answers:

• False

• True

219. What is one easy method to see if your spreadsheet will print on one page?

Answers:

• Use Page Break Preview.

• Select "landscape" on paper set up.

• If it fits onto the screen, it will print on one page.

• Adjust the Zoom to 100%.

220. If a set of numbers has an even number of items, the MEDIAN function will return:

Answers:

• The higher of the two numbers in the middle of the set

• The average of the two numbers in the middle of the set

• The lower of the two numbers in the middle of the set

• An error

221. The cell reference of a range of cells that starts in cell B1 and goes over to column G and down to row 10 is ???.

Answers:

• B1:G10

• B1;G10

• G1-G10

• B1*G10

222. When working in Page Break Preview, we can do which of the following?

Answers:

• Only add or remove page breaks and view exactly where the page break occurs

• Only change the print area and add or remove page breaks

• Only change the print area

• Only add or remove page breaks

• Change the print area, view exactly where the page break occurs, add or remove page breaks

223. Which is not a lookup function in Excel?

Answers:

• HLOOKUP

• #LOOKUP

• VLOOKUP

• LOOKUP

224. Formatting a cell in Currency, you can specify...

Answers:

• currency symbol

• both decimal places and the currency symbol

• none of the above

• decimal places

225. How do you change all positive numbers in a sheet to negatives?

Answers:

• Add "-" manually in front of each number

• Write -1 anywhere in the sheet, copy it, select the data to change, Paste Special, select Multiply

• Select - and Press Enter

• Type everything again with a - sign

• Delete the numbers and reenter

226. True or False: There is only ONE way to group a pivot table item.

Answers:

• True

• False

227. Which function repeats text a given number of times?

Answers:

• TEXT

• PROPER

• VALUE

• REPT

• LOWER

228. What is a circular reference?

Answers:

• A cell that points to a drawing object

• An animation tool

• Always wrong

• A modeling tool

• A formula that either directly or indirectly depends on itself

229. What is the correct forumla to calculate an average between cells B3 and B6?

Answers:

• =AVERAGE(B3:B6)

• =AVE(B3:B6)

• SUM(B3+B4+B5+B6)/4

• =AVE(B3+B4+B5+B6)/4

• =AVERAGE(B3+B4+B5+B6)/4

230. The process of identifying specific rows and columns so that certain columns and rows are always visible on the screen is called?

Answers:

• Fixing

• Selecting

• Holding

• Freezing

• Attaching

231. Which function copies data from a row into a column or vice versa?

Answers:

• Hyperlink

• Transpose

• Rows

• Index

232. Where do you change the default font for new workbooks?

Answers:

• File --> Options --> Proofing

• File --> Options --> General

• File --> Help

• File --> Info --> Permissions

• File --> Options --> Advanced

233. To unhide a sheet and make viewable with other tabs in the current window:

Answers:

• Right click on any tab, select Unhide, choose the tab from the list to unhide

• Adjust Custom View selection in View menu

• Press CTRL-U

• In the View menu select Unhide

234. How do you create a reminder to follow up on a document?

Answers:

• Data/Validation/Input Message

• Insert/New Comment

• Tools/Flag for Follow Up/Enter Date & Time

• Tools/Scenarios/Add/Merge

235. When you widen a column that results in excess row height, you can eliminate the excess row height by:

Answers:

• Using Home/Cells/Format/Auto Fit Row Height

• Double-clicking the bottom of the row number box

• All of these

• Using the shortcut keys Alt-O,R,A

236. How can you remove borders applied in cells?

Answers:

• Open the list on Border tool in Formatting toolbar then choose first tool (no border)

• Choose None on Border tab of Format cells

• Both of these

237. Double-clicking column separators will do what?

Answers:

• Hide Columns

• Automatically adjust column width

• Delete both columns

• Create and additional column in between the two columns

• Adjust number alignment

238. When entering formulas and functions in Excel, the symbols *, /, +, and - are called:

Answers:

• Operators

• Divisors

• Constants

• Changers

• Signs

239. The Remove Duplicates command:

Answers:

• Opens a dialog box asking to delete the duplicates in the existing range or copy to a new range

• Removes duplicates from the range that is highlighted based on selected criteria

• Copies and pastes the list without duplicates in the right-adjoining column

• Copies and pastes the list without duplicates in a new worksheet

240. How can you remove borders applied in cells?

Answers:

• Both by choosing "NONE" on the Border tab of Format cells AND opening the list on Border on the Formatting toolbar, then choosing "no border"

• By going to page set up

• Only by opening the list on Border on the Formatting toolbar, then choosing "no border"

• Only by choosing "NONE" on the Border tab of Format cells

241. The Remove Duplicates command:

Answers:

• Copies and pastes the list without duplicates in a new worksheet

• Removes duplicates from the range that is highlighted based on selected criteria

• Opens a dialog box asking to delete the duplicates in the existing range or copy to a new range

• Copies and pastes the list without duplicates in the right-adjoining column

242. Spell check can be found in the _______ menu

Answers:

• File

• Formula

• Review

• Data

• Insert

243. In an alphabetical sort, if two cell contents are identical, Excel sorts by:

Answers:

• Merging the two cells

• Placing the item that came first in the original list before the second item

• Adding a " ~ " to the end of the second item to make it unique

• None of these

244. In a Excel spreadsheet, It's possible to select multiple non-adjacent ranges of cells.

Answers:

• True. We select the first block of cells and then we use the SHIFT key + selecting the other blocks.

• False. It's not possible to select multiple non adjacent rages of cells, in Excel.

• True. We select the first block of cells and then we use the CTRL key + selecting the other blocks .

245. Attaining data from a cell located in a different sheet is called what?

Answers:

• travelling

• locating

• referencing

• updating

• functioning

246. To move around the tabs at the bottom of a workbook:

Answers:

• These are all correct

• Right click on desired tab, choose "move or copy", choose where to place it in list provided

• Click on desired tab and drag to new location

247. True or False: The formula =((A2+B5)*5% is valid.

Answers:

• False

• True

248. Identify all the arguments and the correct order of arguments needed to execute the IF() function.

Answers:

• logic test

• logic test, value if false

• logic test, value if false, value if true

• logic test, value if true, value if false

249. A function inside another function is called a _____ function.

Answers:

• Round

• Nested

• Text

• Sum

• Combined

250. Which one is NOT a column chart type?

Answers:

• Stacked pyramid

• Stacked column

• Doughnut

• Clustered cylinder

251. What type of chart would you create in order to present how much each component comprise the whole?

Answers:

• Scatter plot

• Ratio and proportion chart

• Bar chart

• Pie chart

• Bubble chart

252. How are rows named?

Answers:

• Numerically

• Alphabatically

• Symbolically

• Metamorphically

253. True or False: If there are duplicate items in a list, the COUNT function will not include the duplicates in the total count of items.

Answers:

• True

• False

254. In an Excel worksheet, the active cell is indicated by:

Answers:

• a thick black border

• None of these

• a blinking border

• a dotted border

255. What would the following formula do?  =Sheet1!D9+Sheet1!E9

Answers:

• This would select D9 and E9 and add the two together from sheet1

• This forumla doesn't do anything.

• This would select D9 and E9 and create an average from sheet2

• This would select D9 and E9 and add the two together from sheet2

256. Where can you see the contents of an active cell?

Answers:

• Page Preview bar

• Screenshot bar

• Tool bar

• View bar

• Formula bar

257. Data displays in the ____ as you type.

Answers:

• status bar

• name box

• formula bar

• insert function box

258. The formula =4>6 returns:

Answers:

• FALSE

• -2

• 2

• TRUE

259. If you place the cursor in cell A2 which contains text and press the keystrokes CTRL-B / CTRL-I / CTRL-U the result will be:

Answers:

• Text in cell A2 is bold, in italics and underlined

• Cell A2 and all cells under it are made bold and italic

• All cells in worksheet are Book Antiqua font, in italics and underlined

• Text is copied up to cells B1 and I1

260. True or False: If there are duplicate items in a list, the COUNT function will not include the duplicates in the total count of items.

Answers:

• True

• False

261. When is an absolute cell reference used?

Answers:

• When you want a cell reference to change when copied and pasted to other cells

• When you want a cell reference to stay fixed on a specific cell.

262. What would you use to only view the rows in a spreadsheet that meet the criteria you want?

Answers:

• Auto Complete

• Goal Seek

• Auto Filter

• Find

263. When the string 12345678910 is contained in Cell B3, which formula below returns the last 5 digits of the string?

Answers:

• =LEFT(B3,5,7)

• =LEFT(B3,7)

• =RIGHT(B3,5)

• =RIGHT(B3,7)

264. What kind of formatting allows you to create a set of rules to format cells based on a cell value or another cell?

Answers:

• Text Formatting

• Conditional Formatting

• Input Formatting

• Output Formatting

• Table Formatting

265. Which worksheet function returns the one-tailed probability of the chi-squared distribution?

Answers:

• CHIDIST

• AVERAGE

• BINOMDIST

• BETAINV

• BETADIST

266. You want to lock rows 1:2 and column A in place as you scroll through a large spreadsheet. Which command should you use?

Answers:

• Freeze Top Row

• Arrange

• Freeze Panes

• View Side by Side

• Split

267. What does the "V" in "VLOOKUP" stand for?

Answers:

• Valkyrie

• Validated

• Vertical

• Vitric

• Verified

268. The logic for the formula in cell B2:  =IF(A2>5,.2,0) is expressed as:

Answers:

• .2 in cell B2 if the value in cell A2 is less than 5; otherwise return 0

• 0 in cell B2 if the value in cell A2 is greater than or equal to 5; otherwise return .2

• .2 in cell B2 if the value in cell A2 is greater than 5; otherwise return 0

269. Which worksheet function returns the minimum value in a list of arguments?

Answers:

• MINVAL

• MIN

• MINIMUM

• MINARG

• MINA

270. What is the difference between a workbook and a worksheet?

Answers:

• A worksheet contains workbooks

• Workbooks are Access databases and worksheets are Excel

• They are the same

• A workbook contains worksheets

271. In the Find and Replace function it is NOT possible to replace what you've searched for with an alternative at the same time.

Answers:

• False

• True

272. What does letter H stands for in HLOOKUP formula?

Answers:

• However

• Highest

• Horizontal

• Header value

• Hidden

273. Which worksheet function returns covariance, the average of the products of paired deviations?

Answers:

• COUNT

• COVAR

• FREQUENCY

• DEVSQ

• CHITEST

274. How do you translate a time into a decimal value.

Answers:

• =TIMEVALUE

• =Time

• Ctrl T

• Look into the date and time function

275. What is the keyboard shortcut for moving the cursor to the first cell in the current worksheet (assuming no frozen panes, this would be cell A1)?

Answers:

• Ctrl + End

• Ctrl + Shift + End

• Ctrl + Home

• Ctrl + Shift + Home

276. The best way to automate a complex, repetitive task within a worksheet, is to create:

Answers:

• a bar chart.

• a hyperlink.

• a macro.

• a page break.

• a pivot table.

277. What's the function for calculating variance?

Answers:

• VARIANCE

• STANDARDDEVROOT

• It does not exist

• VAR

• VARI

278. Which function searches for a value in a specified table array?

Answers:

• =vlookup()

• =browse()

• =searchfile()

• =insert()

• =search()

279. Can macros close currently open worksheets?

Answers:

• No

• Yes

280. The definition of a macro is:

Answers:

• a feature that allows the user to see how a new font or font size will look before actually applied.

• an anchor which holds that value of a referenced component unchanged.

• the prevention of unauthorized use.

• a recording of commands that can be played back at any time.

281. An array range that includes both rows and columns is known as____?

Answers:

• A two-dimensional array.

• A single-dimensional array.

• All array ranges must have rows and columns.

282. When a list is filtered (showing only rows that meet the criteria) and highlighted,  the copy command will result in:

Answers:

• No option; Excel will not allow copying of filtered rows

• Only the highlighted cells being copied

• A dialog box asking which rows to copy

• Both visible and hidden rows being copied

283. How can you set 0.5 inch left indentation for a cell in Excel?

Answers:

• Indentation can be set from Format Cells box.

• This is impossible.

• You can specify indentation only if you turn the rulers on.

• The indentation can be specified only when printing.

• This is possible, but the answer is not listed here.

284. Which language is used to create macros in Excel?

Answers:

• C#

• Visual Basic for Applications

• C

• Java

• Visual C++

285. Which is NOT a function?

Answers:

• PMT

• SUM

• DMV

• MAX

• AVERAGE

286. Under which tab would you access Visual Basic?

Answers:

• Developer

• Home

• Formulas

• Data

• Insert

287. Data can be arranged in ascending or descending order by using?

Answers:

• Sort command from Tools menu

• Sort command from Table menu

• Sort command from Data menu

• None of these

288. The difference between centering text in a cell and centering a number is:

Answers:

• The text center command is part of the font selection

• A number must be centered with the "Accounting" format option

• A number must always be right justified and cannot be centered

• There is no difference

289. The formula ISERROR(A1) returns TRUE if A1 is :

Answers:

• #N/A

• #VALUE!

• Any of these

• #REF!

290. Which formula can add the all numeric values in range of cells, ignoring those which are not numeric, and place the result in a different cell?

Answers:

• Sum

• Average

• Adding

• Count

• Function

291. What is the operator for concatenating two text strings together?

Answers:

• &

• ^

• !

• +

• #

292. What can be protected from accidental modification?

Answers:

• Contents and objects

• Objects and scenarios

• Contents, objects, and scenarios

• Only objects

• Only contents

293. How do you make a long sentence fit into the column size?

Answers:

• Sentence Wrap

• Text Edit

• Wrap Text

• Increase the size of the column

• Shrink To Fit

294. Does conditional formatting override the formatting set in a cell?

Answers:

• No - the manually set format will override the conditional formatting

• No - unless a MACRO is used.

• Depends on the user settings.

• Yes - if the condition(s) are met

295. The name box does which of the following?

Answers:

• displays the contents of the currently active cell

• marks the currently active cell

• stores shortcuts and information about a workbook

• displays the name of the currently active cell

296. Concatenation of text can be done using

Answers:

• Semi-colon (;)

• Question mark (?)

• Exclamation (!)

• Ampersand (&)

• Apostrophe (???)

297. What is a function of data markers?

Answers:

• You can change the position of a data marker and automatically change the data point value in the worksheet

• You can automatically apply formatting to a data series

• You can change a data print value and automatically turn it into a chart

298. When multiplying A1 and B1, where A1=4.33 and B1=6.111, what is the formula to achieve an answer rounded to the closest tenth?

Answers:

• A1*B1

• =ROUNDA1

• =(ROUND)A1*B1

• =ROUND(A1*B1,1)

• =RND(A1/B1)

299. Cell A1 contains the number 3, Cell A2 contains the number 4.  If Cell A3 contains the formula =IF(A1>A2, "true", "false") what solution will Excel return?

Answers:

• #NUM

• #name?

• false

• N/A

• true

300. Which command locates values in a worksheet?

Answers:

• Save

• Locate

• Search

• Find

• Print

301. The function =VLOOKUP("Widget",D4:E9,2,False) is correct format

Answers:

• True

• False

302. Which Excel feature allows a user to split apart values in a text string into separate fields?

Answers:

• Remove Duplicates

• What-If Analysis

• Consolidate

• Text to Columns

303. Applying a $ to a cell reference in a formula:

Answers:

• Keeps the referenced component unchanged as you copy this formula to other locations.

• Applies currency formatting to the result of the formula.

• Converts the result of the formula to text.

• Turns on wrap text in that cell.

304. What character is used in a formula cell reference so that it can be copied elswhere but still refer to the same cell or cells?

Answers:

• *

• $

• /

• &

305. Which is an INCORRECT formula for sum of cells A1 to A5?

Answers:

• =sum(a1+a2+a3+a4+a5)

• =add(a1:a5)

• =sum(a1:a5)

• =a1+a2+a3+a4+a5

306. To speed up the calculations of a data table on a worksheet,

Answers:

• You can change the Calculation options to automatically recalculate the worksheet.

• Automatic option is not available

• You can create a running balance formula in Tools menu Formula auditing Mode

307. How do you copy paste only a number from a cell and not the formula that goes with it?

Answers:

• Edit, copy, desired location, paste, format

• Edit, copy, paste

• Edit, copy, desired location, edit, special paste, values

• Edit, copy, control + P

• Control + shift + V

308. The accounting style shows negative numbers in

Answers:

• bold

• quotes

• parentheses

• strikethrough font

• italics

309. Which worksheet function Estimates the standard deviation based on a sample of selected database entries?

Answers:

• DSTDEV

• DSTDEVP

• DGET

• DCOUNT

• DCOUNTA

310. The Quick Access toolbar does which of the following?

Answers:

• Displays the contents of the currently active cell

• Stores shortcuts to frequently used features

• Marks the currently active cell or range

• Displays the name of the currently active cell

311. A formula with brackets such as {sum((C2:C1)*(D2:D11))} is called:

Answers:

• A pivot formula

• A statistical function

• The geometric mean

• An array formula

312. If cell A1 contains the value "32.96" and you format the cell to show only 1 decimal place, which value is displayed?

Answers:

• 32.0

• 32.960

• None of These

• 33.0

• 32.9

313. What would the function "=Large(DataRange, 2)" return?

Answers:

• The number with the highest exponent in the DataRange

• The second largest value in the DataRange

• The longest string in the DataRange

314. What is the fastest way to retrieve more than one comment?

Answers:

• Click on "Review" and scroll down to "show all comment."

• Hold Shift and double-click any cell with a comment.

• Not sure, I would go to the "Help'' tab.

• Ctrl + F2

• Click the cells one by one to read comments.

315. How can you replicate only the formatting of a graph onto other graphs in your workbook?

Answers:

• This function does not exist

• Copy, Paste Special, Format

• Copy, Paste

• Format Painter

• Copy, Past Special, All

316. When a list is filtered (showing only rows that meet the criteria) and highlighted,  the copy command will result in:

Answers:

• No option; Excel will not allow copying of filtered rows

• Both visible and hidden rows being copied

• Only the highlighted cells being copied

• A dialog box asking which rows to copy

317. True or False: The SUMIF function adds values in a range that cannot be defined by the user.

Answers:

• True

• False

318. Which function provides similar functionality to using the & symbol in a formula?

Answers:

• =COUPPCD

• =CORREL

• =CONCATENATE

• =CUMIMPT

• =COMBIN

319. What "Insert" option should you use for a PDF?

Answers:

• Chart

• Spreadsheet

• Picture

• Object

320. The name box does which of the following:

Answers:

• marks the currently active cell.

• displays the contents of the currently active cell.

• displays the name of the currently active cell.

• stores shortcuts and information about a workbook.

321. In the formula, which symbol specifies the fixed columns or rows?

Answers:

• *

• %

• &

• $

322. Which character denotes a text entry?

Answers:

• #

• ()

• '

• @

• $

323. Which is NOT a method to calculate A1 to the power of B1

Answers:

• =A1^B1

• =SQRT(A1,B1)

• =POWER(A1, B1)

324. Which worksheet function returns the correlation coefficient between two data sets?

Answers:

• CHITEST

• CHIINV

• CORREL

• CHIDIST

• COREL

325. Which of the following functions returns the greatest numeric value?

Answers:

• MAX

• MAXIMUMVAL

• MAXIMUM

• MAXVAL

• MAXNUM

326. Which group under the Home tab contains the "Clear" command?

Answers:

• Alignment

• Font

• Tables

• Editing

• Format

327. What is the correct syntax of the worksheet function ???RANK????

Answers:

• RANK(known_y's,known_x's)

• RANK(x,mean,standard_dev)

• RANK(probability,mean,standard_dev)

• RANK(known_y's,known_x's,new_x's,const)

• RANK(number,ref,order)

328. True or False: When you write a formula in Excel, the references in the formula are limited to that worksheet only.

Answers:

• False

• True

329. Which of the following is correct syntax for typing a function into a cell?

Answers:

• =IF(A2>0,1,0)

• ABS(A2-A4)

• =Covar()

• All of these

• (B46-C46)^2=

330. Data can be arranged in ascending or descending order by using:

Answers:

• Sort Command from Tools Menu

• Sort Command from Table Menu

• Sort Command from File

• Sort Command from Data Menu

• Sort Command from Formatting Menu

331. How do you paste only the formulas to a new cell?

Answers:

• Sort

• Format cells

• Format Formulas

• Paste Special

• Copying a cell will also copy the formula when pasted

332. Using formulas, how could I combine text resulting in "Check # 1234 paid 02/15/2013 - pending cash application"?  Cell B4 refers to the date.

Answers:

• =SUMALL("Check # ",VALUE("1234"),"paid",DATE(B4,"mm/dd/yy")," - pending cash application")

• =CONCATENATE("Check # 1234 paid ",TEXT(B4,"mm/dd/yy")," - pending cash application")

• =TEXT(Check # 1234 paid, ADD(02/15/2013)," - pending cash application"END)

• =+VALUE("Check #",IF(1234),DATE(02/15/2013) =("- pending cash application")

333. A red triangle at the top right corner of a cell indicates what?

Answers:

• The cell is connected to another cell

• The font color of the text in cell is red

• The cell can???t accept a formula

• There is an error in the cell

• There is a comment associated with the cell

334. Which character always appears in formulas with external references?

Answers:

• &

• !

• *

• ^

335. Which of the following formulas shows the correct syntax of a VLOOKUP() formula in the 3rd column?

Answers:

• =VLOOKUP(B14,Data_Range,FALSE)

• =VLOOKUP(Data_Range,B14,3,FALSE)

• =VLOOKUP(B14,Data_Range,3,FALSE)

• =VLOOKUP(B14,Data_Range)

336. In Excel 2010, you can open the Highlight Changes dialog box by choosing Track Changes from which menu?

Answers:

• Review

• Format

• Edit

• Insert

337. What would you enter first in a cell to show formula text?

Answers:

• colon (:)

• apostrophe (')

• question mark (?)

• (ft)

338. Which of the following is an absolute cell reference?

Answers:

• #A#1

• %A%1

• A1

• $A$1

• $1$A

339. When a formula references cells that are affected by the same formula, what type of error results?

Answers:

• A circular reference.

• #DIV/0!

• Boundary error.

• Text error.

340. Which of the following is a concatenating operator?

Answers:

• All are concatenating operators

• #

• !

• &

• '

341. What can help format cell contents according to predetermined conditions?

Answers:

• Format

• All of these options

• Cell color

• None of these options

• Conditional formatting

342. Column A contains an apartment number, column B is the street number, and column C contains the street name. I would like all these values to be in the same cell; what function could I use to accomplish this?

Answers:

• Join Cells

• Concatenate

• Create String

• Not possible in Excel

343. A ____ is a predefined set of colors, fonts, chart styles, cell styles, and fill effects that can be applied to an entire workbook.

Answers:

• Theme

• Format painter

• Layout

• Format

344. When you type a formula into Excel, why should you type function names in lowercase?

Answers:

• Excel will convert the function names it recognizes to uppercase, leaving those that are not recognized in lowercase. This lets you easily see any problems in function names.

• Typing in uppercase is more difficult than typing in lowercase.

• Excel will convert any function names it does not recognize to uppercase. This lets you easily see any problems in function names.

• Excel only recognizes function names in lowercase.

345. What records processes in Excel?

Answers:

• Spreadsheet

• Macro

• Command

• Function

• Formula

346. To compare B14 and C12 and return the value of C12 if it is less than the value of B14, write =IF(____, B14, C12)

Answers:

• B19

• C12>B14

• B14>C12

• B15

347. The SUMIF function is very useful for single-criterion sum formulas. The SUMIF function takes three arguments NOT including:

Answers:

• Range

• Criteria

• Point

• Sum_range

348. Imported data appears entirely in column A. How do you separate the data into individual columns?

Answers:

• Ungroup

• Text to Column

• Data

• Unwrap text

• Data Validation

349. What does the Mod function do in an equation?

Answers:

• Returns the remainder

• Returns zero

• Returns divider

• Returns divisor

350. You can use a template on Excel by going to...

Answers:

• Page Layout > Background > then picking a template

• Insert > Smart Art > then picking a template

• Excel doesn't have this option.

• Page Layout > Themes > then picking a template

• File > New > then picking a template.

351. Where can I find formulas in excel without having to type it?

Answers:

• Home tab in toolbar-->Formulas

• Formulas tab in toolbar --> search for formula

• Data tab in toolbar-->Formulas

352. What is the shortcut key for changing a selected cell reference from relative to absolute by inserting dollar signs ($) into the reference?

Answers:

• Ctrl+$ (Cmd + F4 on a Mac)

• Ctrl+F8 (Cmd + F4 on a Mac)

• Ctrl+F4 (Cmd + F4 on a Mac)

• F4

• F8

353. If I enter the text =A2&B2 into a new cell what action is performed?

Answers:

• An error is produced.

• Cells A2 & B2 are added together in the new cell if they are both numerical / date fields or concatenated if text.

• Cells A2 & B2 are concatenated in the new cell.

• Cells A2 & B2 are added together in the new cell if they are both numerical / date fields or an error is produced for text.

354. If A1=6 and B1=4, then what is =A1&B1?

Answers:

• 2

• 24

• 10

• 1.5

• 64

355. A "frequency distribution" is a summary table that shows the frequency of each value in a ___?

Answers:

• Workbook

• Cell

• Column

• Row

• Range

356. The two types of cell references are:

Answers:

• Figurative and Numeric

• Relative and Absolute

• Type 1 and Type 2

• Basic and Absolute

• Numeric and Data

357. In the formula =VLOOKUP(a2,c2:d100,2,false) the final argument  indicates:

Answers:

• Find an exact match

• Find all numbers ending in zero

• Return the first column

• Find the closest match

358. When a cell is entered in a formula without using the $ symbol, what type of reference occurs?

Answers:

• Mixed Reference

• Verification Reference

• Absolute Reference

• Cross Reference

• Relative Reference

359. When entering a formula, what key or combination of keys can be hit to quickly make a cell an absolute reference?

Answers:

• F4

• F6

• Control + J

• Control + G

• F8

360. What does the keyboard shortcut F5 do?

Answers:

• Exits Excel.

• Displays the "Paste Name" dialog box.

• Maximizes or restores the selected workbook window.

• Opens the "Go To" dialog box.

• Inserts a new worksheet.

361. What shortcut can you use to convert a relative reference to absolute, and vice versa?

Answers:

• cntrl+tab

• f5

• cntrl+shift+4

• f4

362. How do you create a cell featuring a drop down list of values?

Answers:

• Using Data Validation and "any value" as validation criteria

• Simply creating a name range

• Using List Box (form controls) from the Developer tab

• Using Data Validation and "list" as validation criteria

363. The date algorithm attributes the number 1 to January 1st of which year?

Answers:

• 1980

• 1900

• 2000

• 1990

364. When a cell containing a formula is copied and then pasted into a new cell, Excel:

Answers:

• Only adjusts absolute cell references

• Erases the original copy of the formula

• Edits cell references in the newly copied formula

• Does nothing

• Doesn't adjust relative cell references

365. How would you find the absolute value of A1?

Answers:

• =A(A1)

• =A1

• =AB(A1)

• =ABSOLUTE(A1)

• =ABS(A1)

366. What function would you use to extract a portion of a text string from the center of the string?

Answers:

• find

• trim

• text

• mid

• search

367. Does changing the number of displayed decimals change the value Excel is storing?

Answers:

• No - the number remains the same no matter how it is displayed.

• Yes - changing the decimals will cause the number stored to round to the nearest decimal.

• No - Unless you zoom first.

• Yes - Unless you set your cell to "Auto fit"

368. Which worksheet function estimates standard deviation based on a sample?

Answers:

• STANDDEV

• STDSAMPLE

• STDEV

• STDEVIATION

• STANDARD

369. What formula returns the number of cells in column A that are greater than 50?

Answers:

• =COUNTIF(A:A,">50")

• =COUNTIF(A1:A100,">50")

• =COUNT(A1:A100)+50

• =SUMIF(A:A,">50")

370. Which formula will extract the first name out of a cell(A2) with first and last names in it like "David Hawley".

Answers:

• =DEL(A2," ")

• =Collect(A2," ")

• =LEFT(A2,FIND(" ",A2))

• =RIGHT(A2,FIND(" ",A2))

371. What does the VALUE function do?

Answers:

• Counts the number of cells in a range of cells

• Adds all the numbers in a range of cells

• Adds the cells specified by a certain criteria or condition

• Converts a string that represents a number to a number

372. Column A lists a long string (example:A3= 1234Smith1740.434.21) where the first four digits are the employee number.  What formula in B3 will extract only the employee number from the string in cell A3?

Answers:

• =VALUE(TEXT(A3,4))

• =IF(A3="Ted","1234","")

• =RIGHT(A3,4)

• =VALUE(LEFT(A3,4))

373. What is the effect of adding an apostrophe before the equals sign in the cell entry =(98-42)^0.5?

Answers:

• Excel enters the value of the result rather than the formula

• The result is centered when viewed in the cell

• That cell will require recalculation (press F9) to update

• Excel treats it as a text entry and displays the formula

• The result is #VALUE!

374. Which of the following is NOT an Excel file format?

Answers:

• .xls

• .xlt

• .xlsx

• .xlv

375. You are in cell A2 and want to select all of the cells from A2 until the last entry in column A. Assuming that there are no gaps in the data, what key combination can you use to do this?

Answers:

• This cannot be done by a single key combination

• Shift+DownArrow

• Ctrl+Shift+DownArrow

• Ctrl+PageDown

• Ctrl+DownArrow

376. To control the display of the numbers along the horizontal axis of a line graph, adjust the:

Answers:

• Maximum value of the Y axis

• Scale of the y axis

• Scale of the x axis

• Range of the source data

377. Cell A1 contains "Chang", and cell A2 contains "Pie". What is the result of the formula: =CONCATENATE(A1," ","and"," ",A2)

Answers:

• Chang and Pie

• Pie, Chang

• Chang Pie

• Chang & Pie

• Change, Pie

378. The Format Painter can be deactivated by:

Answers:

• Selecting the "Clear Format" function.

• Double clicking on the "Format Painter" button.

• Pressing ESC on the keyboard or clicking the "Format Painter" button again.

• Clicking on "Undo".

379. Which of the following types of files preserves cell properties, formulas, graphics, and other formatting?

Answers:

• .txt

• All of these

• .slk

• None of these

• .csv

380. If I want to unlock some cells after protecting the spreadsheet, what should I do?

Answers:

• First, select the cells. Then, remove the check before the LOCKED option under the PROTECTION tab of the FORMAT CELLS menu.

• First, select cells. Then, right click and select the Unlock option.

• First, select cells. Then, go to the REVIEW menu and select PROTECT SHEET.

• First, select cells. Then, Under the Insert menu, select Unlock.

381. True or False: Converting a table back to a range will maintain the table style used

Answers:

• True

• False

382. Which of the following ways does NOT allow you to import audio into Excel?

Answers:

• Browse from file

• Audio Browser

• Retrieve from email

383. Which formula will provide the same result as the following:  =CONCATENATE("Smarter","er")

Answers:

• ="Smarter"+"er"

• =COMBINE("Smarter","er")

• =JOIN("Smarter","er")

• ="Smarter"&"er"

384. Which function returns the Student's t-distribution?

Answers:

• TDISTRIB

• STUDENT

• TDIST

• DISTIRBT

385. To show a "-" instead of a zero in cells with zero value, you must adjust the settings in what way?

Answers:

• Set the number system to "accounting"

• Requires a macro

• View / 'Show/Hide' menu / Suppress zeros

• Font Settings / Suppress Zeros

386. The function DAY(date)

Answers:

• Extracts the day of the month from the date value

• Displays the current date

• Displays the current date and time

• Extracts the year number from the date value

387. When you create a "Table" in excel, does Excel automatically give it and each of its cells within it a Named Range?

Answers:

• Yes

• No

388. When using the PERMUT (number,number_chose) function, what syntax error will occur if the number_chosen is non-numeric?

Answers:

• #VALUE!

• #NUM!

• #N/A

• #NAME?

389. True or False: The formula =((A2+B5)*5%) is valid.

Answers:

• False

• True

390. In the following formula, where is the Table Array located?  =VLOOKUP(A5:A19,[RegionalSales]FY2012!$D$2:$J$31,7,FALSE)

Answers:

• Regional Sales Worksheet, Cell Range A5:A19

• FY2012 Workbook, Regional Sales Worksheet, Cells D2:J31

• Cell Range A5:A19

• Regional Sales Workbook, FY2012 Worksheet, Cells D2:J31

391. How do you debug a formula by looking at each part of the formula individually?

Answers:

• Formula Auditing > Evaluate Formula

• Conditional Formatting

• Formula Auditing > Error Check

• Data Validation

• Impossible in Excel

392. Which worksheet function returns the rank of a number in a list of numbers?

Answers:

• RANKL

• RANKN

• RANKLIST

• RANK

• NRANK

393. Which function calculates your monthly mortage payment?

Answers:

• NPER (number of periods)

• All of these

• PMT (payments)

• PV (present value)

394. What is the default alignment for the number in a cell?

Answers:

• top alignment

• bottom alignment

• right alignment

• left alignment

• center alignment

395. What does the function FV stand for?

Answers:

• Fully Verified

• Fraction Value

• Financial Value

• Find Version

• Future Value

396. Which formula allows you to find a desired result when you don't know the input value that will return it?

Answers:

• OPTIMIZER

• FIND and REPLACE

• PIVOTTABLES

• GOALSEEK

397. What happens to a "relative" cell when it is copied to a new cell?

Answers:

• It returns an error message

• It's relatively modified

• It's reformatted

• ####

• Nothing

398. The transpose action in Excel is found in:

Answers:

• Paste Special Command

• An add-in

• TRANSP() formula

• A format option

399. What does the function PV do?

Answers:

• Past Value

• Post Value

• Percentage of Variance

• Returns the present value of an investment

400. What shortcut will toggle the absolute setting for the selected cell?

Answers:

• F2

• F5

• F4

• Ctrl + Esc

• Alt + F9

401. Of the following, which is the quickest way to copy the format of one cell onto another?

Answers:

• Select the cell you want to copy the format from, choose the Format Painter, and click on the new cell.

• Select the cell you want to copy the format from and select Edit > Copy Format. Then click on the new cell and select Edit > Paste Format.

• Right click on the cell you want to copy the format from and drag it to the cell you want to copy the format to.

• Select both cells and click the Synchronize Formats button.

• Go to Format > Cells..., check the Copy Format From button and enter the cell you want to copy the format from.

402. What conclusion could you reach if Cell B13 contained the text Apple and the formula =LEN(B13) returned 6?

Answers:

• There are 6 spaces after the word.

• There is an error in the formula. The correct result should be 4 as the LEN() function does not count capital letters.

• There is an extra space somewhere in the text.

• Cell B13 is formatted as a number.

403. Which of following is NOT one of Excel???s what-if functions?

Answers:

• Goal seek

• Solver

• Auto Outline

• Scenario manager

404. How do you create a Pivot Chart?

Answers:

• Click Insert Tab and choose "Pivot Table", then save as "Pivot Chart" in File Menu

• Click "Insert Pivot Chart" on Insert tab

• Click "Charts" under "Other" tab and select "Pivot Chart"

• Right-click dataset and click "Edit"

405. How many workbooks can a user maintain open simultaneously?

Answers:

• Limited only by system resources and available memory

• A maximum of 40 workbooks or 250 worksheets

• This is a user-customizable feature

• 45 workbooks, except Excel 2008 for Mac which accepts only 40

406. Which of the following is not true about Find and Replace in Excel?

Answers:

• You can search within sheets, by rows, or by columns

• You can search in formula too

• You can decide whether to look for the whole word or not

• You can search for bold and replace with italics

407. How do you generate random numbers 1-100?

Answers:

• =NUM1-100

• =RAND:1,100

• =RANDBETWEEN(1,100)

• :RAND (1,100)

• +RAND:1,100

408. Which feature allows you to display multiple copies of your worksheet in side-by-side windows?

Answers:

• Panes

• Freeze

• Views

• Subsheets

• Sections

409. What is the correct formula to reflect the logic "If cell A1="Pass" and cell B1="Pass" then cell C1 should equal "Pass", otherwise C1 should equal 'Fail' "?

Answers:

• =IF(AND(A1="Pass",B1="Pass"),"Pass","Fail")

• =IF(A1="Pass" AND B1="Pass"),"Pass","Fail")

• =(A1="Pass" and B1="Pass", then "Pass", else "Fail")

• =IF(OR(A1="Pass",B1="Pass"),"Pass","Fail")

410. Which worksheet function returns the most common value in a data set?

Answers:

• MEDIAN

• MINIMUM

• MAXIMUM

• MODE

• MEAN

411. Which worksheet function returns the k-th smallest value in a data set?

Answers:

• MINA

• SMALL

• LOGEST

• RANK.AVG

• COUNT

412. Each column in Excel has a corresponding number. To extract this number you can use the following formula:

Answers:

• countcolumn()

• column()

• colcount()

• colnumber(column letter)

413. If A1 contains the text "Red" then =IF(A1="Blue",1,"")

Answers:

• 0

• FALSE

• 1

• TRUE

• Blank

414. If A1=7 and B1=7 what will the formula "=A1=B1" return?

Answers:

• FALSE

• 7

• 14

• It will be an error

• TRUE

415. Which worksheet function returns the k-th largest value in a data set?

Answers:

• LARGEST

• HIGHEST

• LARGE

• LARGEK

• KLARGE

416. The common Illustrations possible under the Insert tab include all of these, EXCEPT?

Answers:

• Shapes

• WebArt

• Picture

• Clip Art

• SmartArt

417. What function would you use if you want to find the 3rd largest value in a set of data?

Answers:

• TREND

• MAXA

• DEVSQ

• LARGE

• PERCENTILE.INC

418. Given range of cells A1:A100, select the formula that would return the 3rd lowest value from the selected range.

Answers:

• =FLOOR(A1:A100,3,TRUE)

• =MIN(A1:A100;3)

• =FLOOR(A1:A100,3,FALSE)

• =SMALL(A1:A100;3)

419. A green flag will appear on the upper right-hand side of a cell when it contains:

Answers:

• A number with leading or trailing spaces

• An entry obtained from a formula

• An ignored circular reference

• A number formatted as text or preceded by an apostrophe

420. A cell with a numerical value has a green corner. What does this mean?

Answers:

• Cell has a comment

• Result of a formula

• Formatting error

• Number stored as text

421. Which group under the Home tab contains the "Clear" command

Answers:

• Font

• Format

• Tables

• Editing

• Alignment

422. How many arguments exist in the IF function?

Answers:

• 2

• 5

• 3

• 4

423. Which worksheet function counts all cells with numerical and text values?

Answers:

• COUNTA

• COUNT

• COREL

• CHITEST

• DEVSQ

424. Identify the one function in the following list that does not require the use of any argument(s):

Answers:

• IF

• SUM

• COUNTA

• COUNT

• RAND

425. What is the most elegant way to change the Tab Color for all the Sheets in a document?

Answers:

• Right-click on any Sheet Tab and choose "Select All Sheets"

• Click on any Sheet Tab and choose "Select All Sheets" then click on any Sheet Tab again and choose "Tab Color"

• Right-click on any Sheet Tab and choose "Select All Sheets" then right-click on any Sheet Tab again and choose "Tab Color"

• Right-click on any Sheet Tab and choose "Tab Color/Apply to All Cells"

426. When the formula bar is active, you can see:

Answers:

• the enter button

• the edit formula button

• (all of these)

• (none of these)

• the cancel button

427. Transparent objects will appear in all versions of Excel, no matter what the year, true or false?

Answers:

• True

• False

428. If A1 contains the text "Red" =OFFSET(A1,0,0) will return

Answers:

• #REF

• Red

• 0

• #Name?

429. Which is not a type of Cell Format?

Answers:

• Digit

• Fraction

• Text

• Time

• Date

430. What Tab do you use to create a background Graphic?

Answers:

• Insert > Background

• Page Layout > Themes

• Page Layout > Background

• Insert > Illustrations

• Insert > WordArt

431. In Excel 2010, to protect a worksheet, choose Protection and then Protect Sheet from which menu?

Answers:

• Data

• Edit

• Review

• Format

432. What is the main difference between a sumif equation and a sumifs equation?

Answers:

• sumifs equations do not exist. Sumif equations do exist.

• sumifs equations allow multiple criteria. Sumif equations allow only one criteria.

• sumifs equations allow only one criteria. Sumif equations allow multiple criteria.

• sumifs equations allow multiple sum_range columns. Sumif equations allow only one sum_range column.

433. Cell A1 contains the string "  Tom Jones" (two blank spaces before the name Tom). To remove the two blank spaces, the formula is:

Answers:

• =EXACT(A1)

• =TRIM(A1)

• =LEFT(A1,2)

• =CLEAN(A1)

434. Which worksheet function counts the number of cells containing data (either numbers or labels) in a selected range?

Answers:

• COREL

• DEVSQ

• CHITEST

• COUNT

• COUNTA

435. What does COUNTA () function do?

Answers:

• counts cells containing letters

• counts empty cells

• counts cells containing numbers

• counts non-empty cells

436. Which Function should be used to count all the cells which are not empty?

Answers:

• =CountIFS()

• =CountA()

• =Count()

• All of the Above

• =CountIF()

437. True or False: The print area cannot be set to discontinuous ranges.

Answers:

• True

• False

438. Which error message will appear if you enter the following wrong function in a cell? =CONCATINATE(A1,A2)

Answers:

• #NUM!

• #NUL!

• #NULL!

• #VALUE!

• #NAME?

439. How would you ensure that B1 divided by A1 always equals zero when the value of A1 is zero?

Answers:

• =IF(B1/A1=Error, 0, B1/A1)

• =B1/A1

• =IFERROR(A1/B1, 0)

• =IF(A1/B1=Error, 0, A1/B1)

• =IFERROR(B1/A1, 0)

440. What function will display current date and time in MS Excel?

Answers:

• C. now ()

• D. time ()

• A. date ()

• B. Today ()

441. What does the keyboard shortcut F12 do?

Answers:

• Displays the Paste Name dialog box.

• Opens the Microsoft Visual Basic For Applications Editor.

• Maximizes or restores the selected workbook window.

• Inserts a new worksheet.

• Displays the Save As dialog box.

442. A formula to show the date for the previous Sunday could be:

Answers:

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

• =TODAY()-MOD(NOW()-1,3)

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

• =TODAY()-(TODAY()-1,3)

• =TODAY()-MOD(TODAY()-1,3)

443. You can replace a formula with its _____ so it remains constant.

Answers:

• Total Value

• Function

• Results

444. How would you determine how many blanks are in the range of cells between A2 and A35?

Answers:

• =COUNTIFBLANK(A2:A35)

• =IFBLANKCOUNT(A2:A35)

• =COUNTBLANK(A2:A35)

• =COUNTIFBLANK A2:A35

• =COUNT("A2:A35")

445. Using IF(), to test if a number in cell D2 was between 10 and 1000 the correct function would be:

Answers:

• =IF(10<D2<1000,TRUE)

• =IF(10<D2,D2>1000,TRUE)

• =IF(AND(D2>10,D2<1000),TRUE)

• =IF(10>D2,D2<1000,TRUE)

446. The key board short-cut to insert TODAY'S DATE in a cell is:

Answers:

• Ctrl + ;

• Ctrl + 0 (zero)

• Ctrl + V

• Ctrl + D

447. Suppose cell A1 has value "1" and cell B1 has value "2". After merging cell A1 and B1, what will be the value of Cell A1?

Answers:

• 12

• #NUM

• 1

• 2

• 3

448. Cell A1 has the number "3" in it.  Cell A2 has the value "5" in it.  If you merge these two cells together, what is left in the merged cell?

Answers:

• 5

• 8

• 3

• Merge is not allowed betweens cells that each contain a value.

• Merging deletes the values in both cells, so the cell will be blank.

449. Under what tab can the Scenario Manager be found?

Answers:

• Data

• Insert

• Home

• Formulas

• View

450. The formula QUARTILE(a1:a100,1) returns:

Answers:

• 75% Percentile Value of the A1:A100 range

• 25% Percentile Value of the A1:A100 range

• 100% Percentile Value of the A1:A100 range

• 1% Percentile Value of the A1:A100 range

451. The difference between the COUNT function and the COUNTA function is:

Answers:

• COUNT only recognizes numbers; COUNTA only recognizes cells that contain text

• COUNT recognizes numbers and text; COUNTA only recognizes cells that contain text

• They are interchangeable and return the same value

• COUNT only recognizes cells in the range that contain values; COUNTA will also include cells that are text

452. If you copy a cell and move to another area of the worksheet and first want to insert a row before pasting, the copied data is erased from the clipboard.

Answers:

• False

• True

453. Which worksheet function returns the average of its arguments, including numbers, text, and logical values?

Answers:

• BETAINV

• AVERAGE

• AVEDEV

• BETADIST

• AVERAGEA

454. Cell A1 contains the text string 'X489Smith1740'. To determine the number of characters in the text string, use the formula:

Answers:

• =LEN(A1)

• =LENGTH(A1)

• =CHAR(A1)

• =COUNT(A1)

455. Up to how many IF statements can be nested in a single IF function?

Answers:

• 12

• 10

• 64

• 48

• 24

456. What does CTRL(CMD)+D do?

Answers:

• Navigate to the Data ribbon

• Fill down

• Opens the Font dialog box

• Deletes the active cell

457. Cell A1 has this information "Current Value: $10.00" Which function extracts "Value:" from "Current Value: $10.00"?

Answers:

• Left()

• Right()

• None of the above.

• Mid()

458. How to make the same data appear in cell A3 on every worksheet?

Answers:

• It's only possible if you have 3 sheets or less.

• All of these.

• Right-click on any Worksheet Tab and choose Select All Sheets. Then type the data in cell A3 and go to the following worksheet to annul the All Sheets selection.

• Right-click on any Worksheet Tab and choose Select All Sheets. Then type the data in cell A3, right-click the cell, and choose Clear All Sheets Selection.

459. What is the shortcut to launch the Visual Basic editor?

Answers:

• Alt + Shift + F7

• Home + F5

• Ctrl + Shift + F11

• Ctrl + F1

• Alt + F11

460. Which worksheet function extracts from a database a single record that matches the specified criteria?

Answers:

• DMIN

• DSTDEVP

• DGET

• DCOUNTA

• DCOUNT

461. Double clicking the currently-selected tab on The Ribbon will do what?

Answers:

• Add another ribbon menu

• Open customize window

• Pull up dialog box

• Collapse the ribbon

• Automatically format worksheet

462. What's the recalculate shortcut?

Answers:

• F9

• Shift

• Tab

• F5

• F4

463. Which of these characters is not allowed as part of a tab name?

Answers:

• "

• ;

• ?

• <

• >

464. Which worksheet function returns a value along a linear trend?

Answers:

• FDIST

• FISHER

• FINV

• FTEST

• FORECAST

465. True or False: The value in a cell that contains the formula =RAND() only changes when the file is resaved.

Answers:

• True

• False

466. If you want to make a text box transparent, you should:

Answers:

• Left click the text box > Home tab > Fill > No Fill

• Right click the text box > Format Shape > Fill > No Fill

• Right click the text box > Options > Transparent

• Not possible with a text box; only Word Art allows this option

467. How do you close a excel program using keyboard shortcut?

Answers:

• alt+w

• shift+w

• ctrl+w

• shift+ctrl+w

• ctrl+alt+w

468. If A1 contains the text "Red" and B2 contains "Blue" then =OFFSET(A1,1,1) returns

Answers:

• #NAME?

• Blue

• Red

• #REF

469. What does a ???Green Triangle??? in the top-left corner of a cell indicate?

Answers:

• Error checking options.

• The cell contains a smart tag.

• The cell contains a comment.

• The cell is empty.

470. The formula EXACT("Fish","fish") returns:

Answers:

• FALSE

• None of these

• #NAME?

• TRUE

471. To convert the date 12/14/2014 in cell A1 to text in the format of YYYYMMDD, which is a valid option?

Answers:

• =RIGHT(A1,4) & LEFT(A1,2) & MID(A1,5,2)

• =DateText(A1)

• =DATE(YEAR(A1),MONTH(A1),DAY(A1))

• =TEXT(A1,"YYYYMMDD")

• =TEXT(DATEVALUE(A1),"YYYYMMDD")

472. Array formulas in Excel work with...

Answers:

• one or more data values

• only one data value

• more than one data value

• none of these

473. What combination of keys do you need to use for an array formula to work?

Answers:

• Ctrl+Shift+Enter

• No key combination is required, an array formula can be entered in the same way as any other formula

• Ctrl+a

• Ctrl+Shift+A

474. What will be the result of this formula =MOD (40, 0) ?

Answers:

• #DIV/0!

• Blank

• 40

• 0

• #VALUE

475. All of the following are recognized file extensions in Excel except:

Answers:

• .htm

• .xlsb

• .pdf

• .xlsbb

• .csv

476. Cell A1 has text in the format YYYYMMDD. How do you convert this to a valid Excel date?

Answers:

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

• =DateText(A1)

• =DATEVALUE(A1)

• =CONVERT(A1,"YYYYMMDD",Date)

• =MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4)

477. If you choose Find & Replace All when the cursor is in one cell, it will result in:

Answers:

• None of these

• The command replacement of the entire worksheet

• Replacement only in that column.

• Replacement only in that cell

478. You are given an Excel 2007 spreadsheet with two columns of data. Cells A1:A1000 have dates and cells B1:B1000 have counts corresponding to those dates. You are asked to find the sum of all of the counts for dates between 01-Jul-2012 and 31-Dec-2012 (inclusive), what is the correct formula:

Answers:

• =COUNTIFS($A$1:$A$1000,">=01-Jul-2012",$A$1:$A$1000,"<=31-Dec-2012")

• =SUMIFS($B$1:$B$1000,$A$1:$A$1000,">=01-Jul-2012",$A$1:$A$1000,"<=31-Dec-2012")

• =SUMIF($A$1:$A$1000,">=01-Jul-2012 and <=31-Dec-2012",$B$1:$B$1000)

• =sum(if(and($B$1:$B$1000 >= DATEVALUE("01-Jul-2012"), $B$1:$B$1000 <=DATEVALUE("31-Dec-2012")),$A$1:$A$1000,0))

• =sum($A$1:$A$1000,if($B$1:$B$1000 between 01-Jul-2012 and <=31-Dec-2012))

479. True or False: Zero values are not plotted using sparklines.

Answers:

• True

• False

480. When you use the Freeze Panes command, Excel freezes all of the columns and rows:

Answers:

• above and to the right of the selected cell.

• below and to the left of the selected cell.

• that are currently selected.

• above and to the left of the selected cell.

• below and to the right of the selected cell.

481. What do you call the chart that shows the proportions of how one or more data elements relate to another data element?

Answers:

• Column Chart

• Line Chart

• Pie Chart

• XY Chart

482. What is the maximum number of columns allowable in an Excel worksheet?

Answers:

• 65536

• 16,384

• 256

• 512

483. A custom list gives you the ability to:

Answers:

• There is no such thing as a custom list in Excel.

• create a drop down list within your workbook.

• Enter the first word in the list and use the fill handle to complete the list.

• create column or row headings within a table.

484. Which of these choices is NOT one of the four function arguments in a VLOOKUP?

Answers:

• Table_lookup

• Range_lookup

• Lookup_value

• Col_index_num

• Table_array

485. If you want to freeze the top row and first column which cell must you highlight before clicking Freeze Panes?

Answers:

• B2

• A1:B2

• A1

• B1

486. what is the function key for creating a chart for a given table?

Answers:

• F4

• F10

• F11

• F1

487. What is the correct term for separating contents from one cell into two by a common character?

Answers:

• Delimited

• Fixed Width

• Text to Columns

• VLOOKUP

• Seperated

488. How do you create a range for a sum function that anchors itself on A1, but will increase in range downwards as you drag and drop the formula?

Answers:

• Sum(A1:$A$2)

• Sum(A$1:A$2)

• Sum($A1:A2)

• Sum(A$1:A2)

• Sum(A1:A2)

489. Which worksheet function returns the skewness of a distribution?

Answers:

• SKEWDIST

• SKEWNESS

• SKEW

• SKEWDISTRIB

• SKEWNESSDIST

490. How can you cause text to display vertically in a cell?

Answers:

• Choose Vertical on Text alignment in Format Cells box

• Choose 90 Degrees in Orientation of Format Cells box

• Choose Center Across Selection from Horizontal combo box in Format Cells box

• Choose 180 Degrees in Orientation of Format Cells box

• Choose Distributed from the Vertical drop down list of Format Cells box

491. A Sparkline is basically______________ set that allows you to quickly and easily spot trends at a glance.

Answers:

• a little chart displayed in a cell representing your selected data

• A chart displaying the selected data

• a chart displayed either in the same worksheet or a selected destination, representing your data.

492. What function returns information about the formatting, location or contents of a cell?

Answers:

• =Verify()

• =Sheet()

• =Cell()

• None of these are correct.

• =Audit()

493. Rounding errors can occur when you use _____ in a formula.

Answers:

• Only multiplication

• None of these

• Only exponentiation

• Multiplication, division, or exponentiation

• Only multiplication and division

494. One of the major differences between the inStr function in VBA and the Excel Search function is that...

Answers:

• inStr value can never be printed to a cell, while the Search function always prints to a cell.

• inStr returns 0 if the value is not found, while the Search function produces an error.

• inStr will not search a whole string, while Search always searches a whole string.

• Search can never be called in a VBA module, while inStr can.

495. Cell B1 contains the text "A1". What function do you enter in cell C1 that refers to B1 and as a result shows the value in cell A1?

Answers:

• Substitute()

• Replace()

• Exact()

• Search()

• Indirect()

496. Where do you enable/disable the Developer Tab?

Answers:

• File --> Options --> General

• File --> Options --> Proofing

• File --> Options --> Customize Ribbon

• File --> Options --> Add-ins

• File --> Options --> Advanced

497. True or False: The visual basic editor can be used even when Excel is not running

Answers:

• False

• True

498. How many types of operators can be used in Excel?

Answers:

• 2

• 1

• 8

• 3

• 4

499. What function converts a text string into a cell reference?

Answers:

• =Text()

• =Reference()

• =Indirect()

• =AddressT()

• =Convert()

500. Which function lets you reference a cell or range (that hasn't been named) using text values?

Answers:

• INDIRECT

• VALUE

• INDEX

• REFERENCE

• REFTEXT

501. Column A contains first names and Column B contains last names.  What formula would output in the format "Last, First" in cell C7?

Answers:

• =B7 ", " A7

• =B7 & ", " & A7

• =A7 ", " B7

• =A7 & ", " & B7

502. Which of the following is not a date and time function?

Answers:

• Workday

• Weekday

• Networkdays

• Today

• Weekend

503. The formula QUARTILE(a1:a100,4) returns:

Answers:

• The highest number in the list

• Max(A:1:A100)

• 100% Percentile Value of the A1:A100 range

• All of these

• The maximum value of the range

504. Under which ribbon item can you find protection options?

Answers:

• View

• Security

• Edit

• Review

• Data

505. What command reveals which cells must contain data for the function in the currently selected cell to work?

Answers:

• Trace Precedents command

• DEPENDENT() function

• Trace Dependents command

• PRIOR() function

506. The Char() formula does the reverse of which worksheet formula?

Answers:

• Hex2Dec()

• IsNumber()

• Text()

• Code()

507. What keyboard shortcut will create a chart of the data in the current range in a separate Chart sheet?

Answers:

• Alt + Shift + F11

• Alt + F11

• Ctrl + F11

• F11

• Ctrl + Shift + F11

508. Which of the following formulas exists in excel, but does not appear on the formula pallet?

Answers:

• =SERIESSUM

• =YEAR

• =DATEDIF

• =COUNTA

509. Which worksheet function calculates the standard deviation based on the entire population of selected database entries?

Answers:

• DGET

• DCOUNT

• DCOUNTA

• DSTDEV

• DSTDEVP

510. Why will Excel return an error for the formula =HLOOKUP(A1,B2:E6,6,0)?

Answers:

• A1 should be entered as $A$1

• There is no exact match

• There is no row "0"

• The column of cells (sixth) to evaluate is outside of the lookup range

511. How do you create a dynamic drop-down menu in Excel that automatically detects if new options (strings) were added to the source list?

Answers:

• Go to Data Validation, select list and in the source use offset(Source, 0, 0,CountA(Source),0).

• Go to Data Validation, select list and in the source use Offset(Source,0,0,CountA(Source),1).

• Go to Data Validation, select list and in the source use offset(Source, 0, CountA(Source),0,1).

• Go to Data Validation, select list and highlight the source list.

512. Sometimes, data that you're charting may be missing one or more data points. Excel offers three ways to handle the missing data. All of the following are ways to handle missing data EXCEPT:

Answers:

• Text: Missing data is bridged with words

• Connect with line: Missing data is interpolated

• Gaps: Missing data is simply ignored

• Zero: Missing data is treated as zero

513. You have entered data in a cell B2. You want cells B3:B16 to have the same data. What is the fastest way?

Answers:

• CTRL-D? or CTRL+D?

• Select B3, enter first character of data, press enter. Select B4, enter first character of data, press enter...up to B16.

• Select B2, Copy. Select B3:B16, Paste.

• Select B2, Copy. Select B3, paste. Select B4, paste... up to B16.

• Highlight B2:B16 and press CTRL+D.

514. While you are typing data into a cell, which of these commands cannot be performed?

Answers:

• Close the Workbook

• Translate

• Edit Font Color

• Switch Windows

• Insert Symbol

515. What is the function to display only the current date?

Answers:

• NOW()

• TODAY()

• DATE()

• CURRENT()

516. What VLOOKUP Function provides the multicolumn range or name of the range or date table to be searched?

Answers:

• range_lookup

• Table_array

• Col_index_num

• Lookup_value

517. What is the fastest way to convert an array containing unrelated formulas that begin with the equal sign, but are stored as text into real formulas?

Answers:

• First select the array. Press F2. Hit CTRL+ENTER together.

• First select the array. Then, CTRL + H. Choose replace: = and replace_with : =. Click replace_all

• Hit the F9 button

• Select the array. Change the number format to text. Change it back to general.

• It is impossible that a formula beginning with = , shouldn't display the result.

518. Cell B3 has the formula =F13+G13; cutting and pasting that formula into cell C4 results in the formula:

Answers:

• =G13+H13

• =F13+G13

• =G14+H14

• =F14+G14

519. Which is false regarding Conditional Formatting?

Answers:

• We can set conditions to look for BOLD and apply ITALICS on cells

• We can apply FONT, BORDER, and PATTERN formats that meet specified conditions

• We can delete any condition from Conditional Formatting if it is not required

• We can add more than one condition to check

520. What is the keyboard shortcut to copy a formula/value to the highlighted cell from the cell to the left?

Answers:

• ctrl+atl+right arrow

• ctrl+2

• ctrl+right arrow

• shift+ctrl+right arrow

• ctrl+r

521. What is the formula to find the first day of the following month?

Answers:

• =(LASTDAY(NOW)+DAY()+1)

• =EOMONTH(TODAY(),0)+1

• =EOMONTH()+1

• =((NOW)+1)

• All of them

522. How will you apply restrictions on data entry except the whole number to selected cells on the worksheet?

Answers:

• Click the data validation option ???Whole number??? in the Allow box, click desired Data option under Tools menu then click OK.

• Click the data option ???Whole number??? in the Allow box under Data menu then click OK.

• Click the data option ???Whole number??? in the Data box, click desired Data option under View menu then click OK.

• Click the data validation option ???Whole number??? in the Allow box, click desired Data option under Edit menu then click OK.

• Click the data validation option ???Whole number??? in the Allow box, click desired Data option under Data menu then click OK.

523. What does the 1 represent in this formula: =Now()+1

Answers:

• one day

• an error

• one minute

• one hour

524. Which setting must you modify to print a worksheet using a letterhead?

Answers:

• Orientation

• Lay-out

• Paper

• Margin

525. Suppose you wanted A1, a non-whole number, to round up to the nearest whole number.  What function(s) would NOT work?

Answers:

• =INT(A1)+1

• =ROUNDDOWN(A1,0)+1

• All of these will not work

• =ROUNDUP(A1,0)

• =ROUNDUP(A1)

526. If A1 is "=4*2" and B1 is "=6/2", what result would appear if C1 was "=A1(#)B1

Answers:

• 24

• 0

• Error

• VALUE

• #REF

527. I have a price list stored in a worksheet, and I need to increase all prices by 10%. Can I do this without reentering all the prices?

Answers:

• Yes, Put codes in your work sheet tabs.

• Yes, several utilities exist for security minded.

• No, you need to work all your data at once.

• All of these.

• Yes, Insert or find a blank column near the prices.

528. Which of these characters is not allowed as part of a tab name?

Answers:

• &

• @

• #

• /

• !

529. Which of the following is not a standard Excel cell format?

Answers:

• Scientific

• Boolean value

• Radian

• Date

• Text

530. The aggregate function extends the capability of the subtotal function.  Which function calculates the variance of values in the range by examining a sample of the values?

Answers:

• SUMSAMPLE

• VSAMPLE

• VAR.S

• VAR.P

• SUBVAR

531. A drop down menu contains "Sum","Average" and "Max", a table exists mapping these values to 1,2 and 3 respectively.  What function next to the drop-down will Sum, Average or Max A1:A100 based on the drop-down input?

Answers:

• =CHOOSE(VLOOKUP(input cell, mapping table, 2, FALSE), SUM(A1:A100),Average(A1:A100),Max(A1:A100))

• =VLOOKUP(input cell, mapping table, 1, FALSE), SUM(A1:A100),Average(A1:A100),Max(A1:A100)

• =VLOOKUP(input cell, mapping table, 2, FALSE), SUM(A1:A100),Average(A1:A100),Max(A1:A100)

• =CHOOSE(VLOOKUP(input cell, mapping table, 1, FALSE), SUM(A1:A100),Average(A1:A100),Max(A1:A100))

• =CHOOSE(IF(input cell = mapping table, 1,2),SUM(A1:A100),Average(A1:A100),Max(A1:A100))

532. Where can you find used names for some areas in workbook?

Answers:

• Right click in name of spreadsheet.

• With the right click on the Name Box.

• With the click in formula bar.

• With the left click on the Name Box.

• Clicking the arrow on the right of Name Box.

533. When I have selected a column, what is the shortcut I can use to completely delete it?

Answers:

• CTRL + D

• CTRL + !

• CTRL + BACKSPACE

• CTRL + "-"

534. If there is text in cell C5 and the formula in cell F10 =SUM(B2:E6) then the formula result will...

Answers:

• return a #NUM! Error

• ignore the text value and add the numeric values

• return a #VALUE! Error

• not be entered; Excel will prompt to correct the error

535. How can you do "SUMIFS" in Excel prior to the 2003 version?

Answers:

• Use "SUMPRODUCT"

• Multiply the "SUM" with "CONDITION"

• Use imbricated "IF" with "SUM"

536. What function do you use to find the present value of a series of annual cash flows at a specified discount rate?

Answers:

• NPV

• PVCF

• NPVD

• PV

• PVA

537. In Excel 2010, how many types of icon sets are available to be implemented for an icon set rule on a set of cells?

Answers:

• 3

• 7

• 4

• 9

• 2

538. The formula in cell C2 is =$A$3*B3. When you copy it to D2, the formula in D2 will be:

Answers:

• $A$3*D3

• $A$3*B3

• $A$3*C3

• $B$3*C3

539. Cell A1 contains the formula =$F39. Copying that formula to cell B2 will result in:

Answers:

• =$F40

• =$F39

• =F40

• =G40

540. What is the simplest possible way to insert picture in cell A1 which shows on Mouse Hover on cell A1?

Answers:

• Right-click cell > Insert Comment > right-click on shaded (grey area) on outside of comment box > Format Comment > Colors and Lines > Fill > Color > Fill Effects > Picture > (Browse to picture) > Click OK

• All of the them

• Go to Insert Tab and select "Picture" from illustrations group.

• Right-click cell > Insert Comment > right-click on (yellow area) inside comment box > Format Comment > Fill > Fill Effects > Picture > (Browse to picture) > Click OK

• None of them

541. How may criteria can the SUMIF function have?

Answers:

• 3

• Unlimited

• 1

• 0

• 2

542. Which keyboard shortcut select cells that contain formulas that directly or indirectly reference the active cell?

Answers:

• CTRL+]

• CTRL+SHIFT+}

• CTRL+[

• CTRL+SHIFT+O (the letter O)

543. Which keyboard shortcut selects all cells referenced by formulas in the selection?

Answers:

• CTRL+SHIFT+{

• CTRL+SHIFT+}

• CTRL+SHIFT+O (the letter O)

• CTRL+[

• CTRL+]

544. If you wanted to calculate the number of "workdays" between two specific points, which function do you use?

Answers:

• Networkdays

• Any of these

• Workday

• Date

545. Which formula will provide the same result as the following:  =SUMPRODUCT(A1:A10,B1:B10)

Answers:

• {=PRODUCT(A1:A10+B1:B10)} (array formula)

• {=SUM(A1:A10*B1:B10)} (array formula)

• =SUM(A1:A10)*SUM(B1:B10)

• =PRODUCT(A1:A10)+PRODUCT(B1:B10)

546. How many users can work simultaneously on a shared workbook?

Answers:

• As many as indicated in the Shared Workbook options

• Maximum of 256 users

• Excel is a front-end application that does not support dynamic sharing of workbooks

• Only one user can edit, and unlimited users may open the file as Read Only

547. I have a column of values in ranges A2:A100 that I need to concatenate with the number 2.  Which of the following is not a way to do this?

Answers:

• Go to B2, enter A2:A100&2 then highlight B2:B100, execute the Array formula.

• Go to B2, enter Concatenate(A2,2) then drag and drop formula to B100.

• Go to B2, enter =A2&2 then drag and drop formula to B100.

• Go to B2, enter =A2&2 then highlight B2:B100, execute the Array formula.

• Go to B2, enter Concatenate(A2:A100,2) then highlight B2:B100, execute the Array formula.

548. The value of cell C3 is 23 and the cell has the name Days; the formula =Days is entered into cell D3; does Excel return an error?

Answers:

• The formula should be =textvalue(Days)

• The formula should be ="Days"

• The formula should be =$Days

• It will not return an error, the formula is correct and will return the value of 23

549. The shortcut to highlight a row is

Answers:

• Shift+Space

• Control+Enter

• Control+Space

• Control+Shift+L

• Control+Shift+R

550. Which of the following CANNOT be used as a worksheet name?

Answers:

• Sheet

• History

• File

• Date

551. Which function, other than "transpose", can be used to transpose the contents of an array of cells?

Answers:

• "sumif"

• "offset"

• "mirror"

• "invert"

552. A Shared Workbook will allow users to do all of the following except:

Answers:

• Save changes by multiple users at the same time

• Remove other users viewing the workbook

• Make changes to the workbook's structure

• Hide/Unhide tabs

553. Can you set 1-inch left indentation for a cell in Excel?

Answers:

• You can specify indentation only if you turn the rulers on

• The indentation can be specified only when printing

• Indentation can be set from Format Cells dialog box

• You can specifiy indentation only if you turn the rulers off

• Excel does not have indentation feature

554. How do you add a trendline when you are plotting data?

Answers:

• Chart Tools -- Analysis -- Trendline

• Chart Tools -- Analysis -- Layout -- Trendline

• Chart Tools -- Trendline

• Chart Tools -- Layout -- Analysis -- Trendline

• Chart Tools -- Layout -- Trendline

555. What term refers to a specific set of values saved with the workbook?

Answers:

• Trend line

• Range

• Scenario

• What-if analysis

556. When exporting with a .slk (symbolic link) format, what is preserved?

Answers:

• none of these things

• cell properties

• formulas

• all of these things

• graphics

557. If a cell contains a value that does not begin with the correct operator, Excel evaluates it as...

Answers:

• An error

• A reference to another file

• An array formula

• Text

558. Which worksheet function returns the standard normal cumulative distribution?

Answers:

• NORMDIST

• NORMSDIST

• NSDIST

• NMSDIST

• NORSDIST

559. =SUMPRODUCT(A1:A2,B1:B2) For the data in rows 1 and 2: A {2 3} B {4 5}

Answers:

• 12

• 22

• #REF

• 10

• 26

560. How do you change the column labels from numbers to letters?

Answers:

• Go to File tab, then to Options, then to formulas, than choose R1C1 reference style.

• Go to File tab, then to Options, then to advance and choose columns R1C1

• Go to File tab, then to Options, then proofing and then choose number column style.

• Go to Excel options, then choose advance and then columns style #.

561. Which of the following is true about PivotTables?

Answers:

• The format of the value fields are always in the same format as they are in the base table.

• Data fields automatically come in to the PivotTable in averages.

• When you initially create a PivotTable, value fields are brought in to the PivotTable in a horizontal format.

• If a value field has null values in the base data, the field is initially brought in with a Count setting.

562. To change the color of a slice in a pie chart:

Answers:

• Double click pie slice, click the fill button from the toolbar

• Right click; Format Data Series; Line Style; Solid Fill; Color

• Format Chart Area; Fill; Solid Fill; Color

• Right click; Format Data Series; Chart Type; Solid Fill; Color

563. What tab would you use to format a section of text as a table?

Answers:

• Home tab, Styles Section

• Formula Tab

• Home tab, Editing Section

• Home tab, Cells Section

• Page Layout Tab

564. The values for A1:A5 are A1=1, A2=3, A3=2, A4=5, A5=4. The formula PERCENTRANK(A1:A5,A2) returns the value:

Answers:

• 0.5

• 0.75

• 0.20

• 0.25

565. Imagine a leaderboard of 1000 players. Column A shows player rankings, B their names and C their points.  You want to sum the points of the top X players, where X is defined in D1. What is the correct formula?

Answers:

• None of these

• sumif(A:A,"<="&D1,C:C)

• sumif(A:A,<=D1,C:C)

• sumif(C:C,<=D1,A:A)

• sumif(A:A,OR(<D1,=D1),C:C)

566. Cell A1 contains the formula =F39. If you copy that to cell B2 the formula in B2 will be:

Answers:

• =G39

• =G40

• =F40

• =A1

567. Suppose you enter =EOMONTH(A1,1) and the date entered in A1 is 2/14/2012.  What will be the result of this function?

Answers:

• 3/31/2012

• 2/29/2012

• 1/31/2012

• 3/30/2012

568. The formula =EOMONTH(2/1/2011,4) will return what date, if the cell is formatted MM/DD?

Answers:

• 2/5

• 6/1

• 6/31

• 5/31

569. Which tab will you select to unhide and hide the sheet?

Answers:

• Page Layout

• Format

• Conditional Formatting

• Insert tab

• Review

570. Which worksheet function returns the probability that values in a range are between two limits?

Answers:

• PROB

• PROBLIM

• PROBABILITY

• PROBVAL

• PROBLIMIT

571. What function can be used to identify duplicate values in Column A?

Answers:

• =COUNT(A:A,A:A)

• =VLOOKUP(A1,A:A,1,0)

• =COUNTIF(A:A,A:A)

• =RANK(A:A)

• =VALUE(A:A,A:A)

572. What is the default shortcut to apply accounting-style cell formatting?  ex: ($5,454.25) for the value -5454.25

Answers:

• CTRL + SHIFT + A

• CTRL + SHIFT + 2

• CTRL + SHIFT + D

• CTRL + SHIFT + 4

• CTRL + SHIFT + 8

573. What does "Merge Cells" do?

Answers:

• Reverses a merge, returning the merged cell back into a range of individual cells

• Merges the range into one cell and horizontally centers the content

• Merges each of the rows in the selected range across the columns in the range

• Merges the range into a single cell, but does not horizontally center the cell content

574. What is a known bug in Excel?

Answers:

• Excel dates are represented by a number of days after a set date in 1900.

• Excel represents 0 as 1/0/1900.

• Excel can't visually represent dates prior to 1900.

• Excel assumes that 1900 is a leap year.

575. How do you insert a blank worksheet using keyboard shortcut?

Answers:

• Ctrl + N

• Shift+F11

• Shift + N

• Alt + N

• Shift + F 10

576. Which solver method will solve non-smooth and nonlinear problems?

Answers:

• Evolutionary

• LP Simplex

• GRG Nonlinear

• All solving Methods

• Cannot be done in Solver

577. The auto-calculate feature does which of the following?

Answers:

• Automatically performs mathematical operations commonly used in Excel.

• It can do none of these things.

• Automatically adds values in a range of cells.

• Automatically creates formulas and add them to a worksheet.

578. Cell A1 contains the string "tom jones". To transform the contents to "Tom Jones" the formula is:

Answers:

• =CAPS(A1)

• =UPPER(A1)

• =NAME(A1)

• =PROPER(A1)

579. If you save a workbook with the results of a Goal Seek calculation in place what will occur?

Answers:

• Nothing, the only way to save your Goal Seek results is to do a save as.

• You will overwrite the values in our workbook.

• Nothing, your workbook will go back to the same calculations before you performed Goal Seek.

• You will have saved your workbook.

580. In cell E2, you write the formula =$C2*E$1.  You then copy that formula to cell F6.  What does it update to?

Answers:

• =$C6*F$1

• =$D6*F$5

• =$C6*E$1

• =$C$2*F$1

581. Which worksheet function returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value?

Answers:

• CRITBIN

• CRITBINOM

• CRITBINM

• DISBINOM

• CUMBINOM

582. Which worksheet function returns the square of the Pearson product moment correlation coefficient?

Answers:

• RSQ

• PEARSONSQ

• SQR

• RSQPCOR

• RSQCOR

583. A common nested formula that allows more flexibility than VLOOKUP/HLOOKUP is:

Answers:

• =INDEX(MATCH())

• =INDIRECT(INDEX())

• =LOOKUP(TRANSPOSE())

• =OFFSET(ROWS())

584. What symbol is used before a number to make it a label?

Answers:

• ___

• '

• =

• ?

• "

585. The chart wizard term data series refers to:

Answers:

• a set of values you plot in a chart

• a collection of chart data markers

• none of these

• a data label

• a chart legend

586. Which of the following options is NOT located in Page Setup?

Answers:

• margins

• page orientation

• headers and footers

• all of these are located in Page Setup

• page break preview

587. Which of the following is NOT a 'Solving Method' available in the Solver Add-in?

Answers:

• Regression

• Evolutionary

• Simplex LP

• GRG Nonlinear

588. Which worksheet function returns the negative binomial distribution?

Answers:

• NEBINOMDIST

• NBINOMDIST

• NGBNDIST

• NEGBINDIST

• NEGBINOMDIST

589. Which of the following is a valid type of graph?

Answers:

• Exploded Doughnut

• PivotTable Graph

• Regression

• Series Graph

590. Which of the following series type is not valid for Fill Series dialog box?

Answers:

• Growth

• Time

• Autofill

• Linear

591. Which of the following formulas contains the correct syntax for a calculated field within a PivotTable?

Answers:

• ="Sales Revenue"-Expenses

• ='Sales'-'Expenses'

• =Sales-Expenses

• ='Sales Revenue-Expenses'

592. In print preview, what is selected/displayed?

Answers:

• all of these are selected/displayed

• the entire worksheet is selected

• the selected range is displayed

• the active portion of the worksheet is displayed

593. Suppose you want VBA to run a loop that will continue until it finds an empty cell. Which of the following will allow you to do this?

Answers:

• Do loop and the isblank() function

• If loop and the isempty() function

• If loop and the isblank() function

• Do loop and the ismissing() function

• Do loop and the isempty() function

594. In Solver, the Set Objective cell

Answers:

• Can be set to a max and min value, but not exact value

• Does not need a formula

• Can be any cell

• Must contain a formula

• Can't be set to an exact value

595. Does adding a password cause the excel document to be encrypted when its saved?

Answers:

• yes, but only if a password "to open" is added.

• No, excel supports encryption, but documents will not be encrypted simply by adding a password.

• No, excel does not support encryption.

• yes, if either a password "to open" or "to modify" is added.

596. The formula =countif(a1,a1) typed in cell b1 will result in:

Answers:

• #NAME!

• 1, unless a1 is blank

• #VALUE!

• 1, always

• 1, unless a1 has an error value

597. Which worksheet function returns the standard error of the predicted y-value for each x in the regression?

Answers:

• STDYX

• STEYX

• STEXY

• STDYXREG

• STDYREG

598. Assuming Cell A1 is populated with 16.8942, which of the following formulas would result in 16?

Answers:

• =ROUND(A1,-1)

• =RAND(A1)

• =INT(A1)

• =ROUND(A1)

• =ROUND(A1,0)

599. How do you add chart gridlines?

Answers:

• Right click, add gridlines

• Click on chart, click on format, select the option

• Left click on chart, click on format, click on add gridlines

• Select chart, click Gridlines button in Axes group on Chart Tools Layout tab, select option

600. Suppose you enter ="$30"+10 into a cell. What will the cell display?

Answers:

• $40.00

• 40

• An error message

• $40

• $30+10

601. Which worksheet function returns the sum of squares of deviations?

Answers:

• DEVSQR

• SQDEV

• DEVSQ

• DEVSQRT

602. Which of the following is a correct way to hide the contents of a cell?

Answers:

• Click on the Format icon, point to Hide & Unhide, and choose Hide Cells

• Use a custom format of ;;;

• Right-click on the cell and choose Clear Contents

• Click on the Font icon drop down menu and choose Hide

603. Which worksheet function returns the inverse of the Student's t-distribution?

Answers:

• INVT

• TINVERSE

• STDINV

• TINV

• INVTDIST

604. Which worksheet function returns a normalized value?

Answers:

• NORMALVAL

• NORMAL

• NORMVALUE

• NORMALIZE

• STANDARDIZE

605. What is the formula for the Mean of a set of numbers?

Answers:

• =MEAN()

• =AVG()

• =AVER()

• =AVERAGE()

606. Which of the following terms is NOT part of pivot table terminology?

Answers:

• Item

• Table filter

• Values area

• Table area

• Column labels

607. What does SHIFT+DELETE do?

Answers:

• Delete

• Delete Column

• Copy

• Cut

• Delete Row

608. Which of the Following is an Evolutionary Solving Method Option in Solver?

Answers:

• Show Iteration Results

• Constraint Precision

• Mutation Rate

• Derivatives

• Use Automatic Scaling

609. The shortcut for transpose is

Answers:

• ALT, S, E, S, Enter

• CTRL + T

• CTRL + SHIFT + T

• CTRL + ALT + t

• ALT, H, S, V, E, Enter

610. Which worksheet function returns the poisson distribution?

Answers:

• POISONDISTRUB

• POISONDIST

• POISSONDIST

• POISSON

• PDIST

611. Which is the proper vba naming convention for a macro?

Answers:

• Name

• MacroName()

• MacroName

• SubMacroName()

• SubMacroName

612. Which Solver Solving Method will solve non-smooth problems?

Answers:

• Evolutionary

• LP Simplex

• Cannot be solved in Solver

• All Solving Methods

• GRG Nonlinear

613. Pre-made sheet formats like Simple, Classic, Accounting, Colorful etc. can be applied from:

Answers:

• Format >> Cells

• All listed

• Format >> Autoformat

• Table >> Autoformat

614. Which worksheet function returns the serial number of a particular time?

Answers:

• TIME

• TIMEVALUE

• SECOND

• NOW

• NETWORKDAYS

615. To observe the change to a cell on another tab based on changes made in the current tab, use

Answers:

• Freeze Panes functionality

• Split Window functionality

• Data Tables

• Watch window functionality

616. The status bar does which of the following:

Answers:

• stores shortcuts and information about a workbook.

• displays the contents of the currently active cell.

• allows viewing of distant parts of a worksheet at the same time.

• hosts specific shortcuts and information about a workbook.

617. What is the result of the formula =FLOOR(23.4, 0.5)?

Answers:

• 23.4

• 23

• 24

• 23.9

• 22

618. Entering =1/12/2011 into a cell with default formatting  results in:

Answers:

• 4.14E-05

• Displayed date value Jan-12-2011

• Displayed date value Jan-12-2011 00:00:00

• Displayed date value 1/12/2011

619. Which of the following is not information you can specify using the solver?

Answers:

• Target cell

• Changing cells

• Input cells

• Constraints

620. What is the Major Version of Microsoft Excel 2010?

Answers:

• 14

• 13

• Depends on installed service pack.

• 16

• 15

621. When creating a vertical page break without creating a horizontal page break as well, the active cell

Answers:

• must be A1

• must be in the last row

• can be anywhere in the worksheet

• must be in column A

• must be in row one

622. Which worksheet function returns the parameters of an exponential trend?

Answers:

• LOGEST

• LOGEXP

• EXPONEN

• LOG

• EXPONENTIAL

623. Which worksheet function returns the mean of the interior of a data set?

Answers:

• TRIMMEAN

• INTERMEAN

• MEANINTER

• MEANTRIM

• MEANSET

624. How do you begin to record a macro?

Answers:

• Click on the macro icon located to the right of the "In Ready Mode" text at the lower left corner of the screen

• Right-click anywhere in the spreadsheet and choose Record Macro

• Write the code in straight VBA

• Click on the Developer tab, click on the drop down arrow on the Macros icon in the Macros group, and choose Record Macro

625. Which of the following is not a style of conditional formatting for value based rule for cells?

Answers:

• three color scale

• four color scale

• eight color scale

• two color scale

• five color scale

626. =SUMPRODUCT(--(A1:A2="z"),B1:B2,C1:C2) for the data in rows 1 and 2: A B C z 2 5 y 3 4

Answers:

• 26

• 22

• #REF

• 10

• 12

627. You want to pick a winner from a set of names in A:6 to A:34.  Which methodology will allow you to perform this?

Answers:

• =RANDFROM(A6,A34)

• =RAND(A6,A34)

• =offset(a5,randbetween(1,29),0)

• Create data column B with sequential integers for each name, =RANDBETWEEN(A6:A34,B6:B34). Look up name that corresponds to result.

• RANDINT(A6,A34). Look up name that corresponds to result.

628. A sales worksheet shows all sales for the year. Customer name in column A, sale status in column B and sale value in Column C. What value will the formula  "=SumProduct(--(B3:B70="pending"))" return?

Answers:

• The number of accounts which have pending status.

• #N/A

• The sum of the sale value of all accounts with pending status.

• #Value

629. What is the standard number of figures excel can display in a cell?

Answers:

• 23

• 5

• 9

• 15

• 19

630. Which menu option can be used to split a window?

Answers:

• Window > split

• Format > window

• View > split

• View > window > split

631. What is a sparkline in Excel?

Answers:

• An in-cell chart that displays with the purpose of showing trend.

• A type of trend chart which floats on the spreadsheet.

• A trend indicator add-on of a pre-existing line chart.

• A format type of a line chart.

632. What formula permits the use of multiple lookup criteria?

Answers:

• Vlookup(Index())

• Hlookup

• Indirect

• Sumproduct

• Index(match())

633. What does VLOOKUP formula return when the columns in an array area exceed the available columns in your table?

Answers:

• #FALSE!

• #COLUMN!

• #ERROR!

• #VALUE!

• #REF!

634. Which function is used to create dynamic range names?

Answers:

• MOD

• OFFSET

• CONCATENATE

• RANGE

635. You can set Page Border in Excel from...

Answers:

• Line Style tool in Drawing toolba

• Border tab in Format Cells dialog box

• Border tool in Formatting toolbar

• you cannot set page borders

636. What function is NOT considered a Math & Trig function?

Answers:

• INT

• ROUND

• SUM

• MAX

• RAND

637. What is not a Excel function category?

Answers:

• Cube

• Text

• Numbers

• Database

• Financial

638. You want a drop down list with a blank space between "apple" and "banana". How do you do this?

Answers:

• Not possible.

• Go to data validation, list, source and type the following: "apple", "", "banana"

• =list("apple","blank","banana")

• Create a Named Range with "apple" in the first cell, nothing in the second cell, and "banana" in the third cell. Then, point the list to that named range.

639. Which worksheet function Calculates standard deviation based on the entire population, including numbers, text, and logical values?

Answers:

• STDEV

• STDEVPA

• STDEVP

• STDEVA

• STDEVPOP

640. The correct formula to generate a random number between 20 and 100 is:

Answers:

• =RANDBETWEEN(20,100)

• =RAND(20,100)

• =VARIABLE(20,100)

• =RANDOM(20,100)

641. If A1=4.33, B1=6.111, and C1=26.5, then entering =A1:C1, when will return the answer ____ .

Answers:

• #VALUE!

• 36.941

• ERROR!

• FUNCTION?

• 37

642. Which feature of Excel allows the user to limit the number of text characters in a cell?

Answers:

• Format Column

• Range Names

• Data Validation

• Concatenation

643. Cell A1 has the formula =5+A+6. The formula result of B1 = IFERROR(A1,22) is:

Answers:

• TRUE

• FALSE

• 11

• 22

644. Which worksheet function returns the probability associated with a Student's t-test?

Answers:

• TTEST

• PROBTEST

• STDPROB

• TSTDTEST

• PROBTTEST

645. An .xlsx file cannot store VBA macro code or Excel 4.0 macro sheets.

Answers:

• True

• False

646. Which function checks to see that all conditions are true before returning a TRUE response?

Answers:

• OR

• IF

• AND

• VLOOKUP

• TRUE

647. Under the Format menu, under COLUMN, which of the following is NOT an option?

Answers:

• Autofit

• Width

• Hide

• Standard Width

• Unhide

648. What function do you use to insert the current date and time into a cell?

Answers:

• DATE()

• Today()

• CURRENT()

• Now()

649. The displayed value of cell A1=FALSE. The formula in cell A2=ISLOGICAL(A1) returns:

Answers:

• TRUE

• 0

• FALSE

• 1

650. How does a user prevent circular references conflict errors?

Answers:

• Click on the "Enable Circular References" box

• click on the "Enable All Calculations" box

• Click on the "Enable All Functions" box

• Click on the "Enable Iterative Calculations" box

651. Which of the following is NOT an option to erase the contents of a cell?

Answers:

• Clear Formulas

• Clear All

• Clear Comments

• Clear Contents

• Clear Formats

652. In order to create a new report in Excel you have to go to:

Answers:

• Data Tab ---> New Report

• Insert Tab ---> New Report

• Team Tab --->New Report

• Create Tab ---> New Report

653. The definition of the array formula {=SUM((C90:C93)*(D90:D93))} is:

Answers:

• The formula will result in an error because the "=" operator is inside the brackets

• = C90*C91*C92*C93+D90*D91*D92*D93

• = (C90 + C91 + C92 + C93) * (D90 + D91 + D92 + D93)

• = C90*D90 + C91*D91+C92*D92+C93*D93

654. To print a page number on the bottom of every printed page:

Answers:

• Select the option in the format menu

• Insert the function =PAGENUMBER(True) as a footer

• Make a selection in the Page Setup dialog box

• Type a number in the bottom-center cell of each page

655. You can use the SUMIFS function if you are only limiting to one criteria.

Answers:

• False

• True

656. How are dates stored in Excel?

Answers:

• dd/mm/yyyy hh:mm:ss

• ddddd.tttttt

• dd/mm/yyyy.tttttt

• dd/mm/yyyy

657. June 6th, 2011 was a Monday. Based on the normally set week, date value 6/6/2011 is in cell A1. The formula in B1 =Weekday(A1) returns:

Answers:

• None of the above

• 6

• Monday

• 2

658. Which worksheet function returns the slope of the linear regression line?

Answers:

• LINSLOP

• SLOPLENEAR

• SLOPE

• SLOPELIN

• SLOPLINREG

659. The source range for a Pivot table may not contain:

Answers:

• Two columns with the same header row

• A mix of columns containing values and text

• A column with data formatted as Scientific abbreviation

• A column with no header row in the top cell

660. The formula =IFISERROR(A2) returns:

Answers:

• #NAME?

• TRUE if A2 has a #REF! value

• FALSE if A2 has a numeric value

• #VALUE! If A2 is text

661. What will F11 command do?

Answers:

• It will toggle the task pane

• It will open up the Visual Basic Editor

• It will insert a chart in a new worksheet

• The macros dialog box will appear that allows the user to manage macros

• It will open up the Microsoft Script Editor

662. The arguments of the formula =SUMIF($G$5:$G$257,"Large",H$5:H$257) are:

Answers:

• Sum the numbers in the range H5 to H257 that have the word "Large" in the same row in the column range G5 to G257

• For every cell in range G5:G257 that contains the word "Large" multiply the corresponding value in the H: column by 5 and add together

• Sum the numbers in the range G5 to G257 that have the word "Large" in the same row in the column range H5 to H257

• None of these

663. In the formula =(B6+39)/SUM(D4:F8), which part is evaluated first?

Answers:

• B6+39

• Neither is evaluated first.

• Both of the above are evaluated at the same time.

• SUM(B6+39)

• SUM(D4:F8)

664. Excel accepts the following range name:

Answers:

• A33

• All of these

• Sheet4

• This+Rate

665. In Excel, you can choose a picture as the background of a:

Answers:

• Cell, or a comment.

• Only sheets can have background pictures.

• Cell, but not a comment.

• Comment, but not a cell.

• Cell, or comment or sheet.

666. Which Group on the ribbon gives you quick access to the Sum, Average, Count, Min, and Max functions?

Answers:

• Paste

• AutoSum

• Insert Function

• AutoFill

667. What additional ability does the Classic PivotTable Layout provide?

Answers:

• Set custom sort orders

• Change the number format for multiple fields simultaneously

• Drag & Drop fields into the grid

• Create PivotTables compatible with older versions of excel

• Create calculated fields

668. How do you print an Excel Workbook with the Title Row A1:A2 on each page?

Answers:

• Page Layout, Print Titles, Rows to Repeat at Top and type "A1"

• Page Layout, Print Titles, Rows to Repeat at Top and type "A1:A2"

• Go to Headers and add Title from A1:A2

• Page Layout, Title Header, Rows to Repeat at Top and type "A1:A2"

669. The formula =VLOOKUP(A1,B2:D100,3,0) returns a result from column:

Answers:

• D

• A

• C

• B

670. Evaluating cell A1 based on font color:

Answers:

• Requires conditional formatting

• Requires the function =COLOR(A1,"type")

• Requires a macro

• Requires the function =INFO(A1,RC1C)

671. How can we delete a record from an Excel database?

Answers:

• Select Data > Delete Record from the menu

• Select Data > Form from the menu to open the Data Form dialog box, find the record and Click the Delete button

• Click the Delete button on the Standard toolbar

• None of these things.

• Delete the column from the worksheet

672. If you add a column within the source data range of a pivot table, then the pivot table...

Answers:

• will require recreation of the pivot table on a new tab

• will incorporate the new field upon "Refresh"

• will require a reset of the source data manually or by using the Wizard

• will automatically incorporate the new field

673. Cell A1 contains two blank spaces (eg press the space bar twice). The formula =ISBLANK(A1) returns:

Answers:

• TRUE

• 0

• FALSE

• 2

674. What is the default number for maximum iterations?

Answers:

• 10

• 100

• 1000

• 50

675. Which of the following chart types is featured in Excel?

Answers:

• Vertical Charts

• Pointed Charts

• Dense Charts

• Square Charts

• Surface Charts

676. Which tab do you click to make paragraphs in Excel?

Answers:

• Paragraph tab

• Text Tab

• International Tab

• Theme Tab

• Format Tab

677. For a formula that calculates gross pay based on hourly wage  (column A) and hours worked (column B), what are the cells in columns A and B called?

Answers:

• Precedent Cells

• Antecedent Cells

• Proprietary Cells

• Dependent Cells

• Calculated Cells

678. How do you manage range names?

Answers:

• Choose Formulas/Named Cells/Name Manager

• Choose Home/Named Cells/Name Manager

• Choose Format/Named Cells/Name Manager

679. When editing an advanced filter, which choice is NOT available?

Answers:

• List range

• List variance

• Copy to

• Criteria Range

680. How can you display the names assigned to cells/ranges in your workbook?

Answers:

• =CellRange("Name")

• =Name("Defined Name")

• =DefineName("Name")

• Formulas/Defined Names/Name Manager

681. In the formula "=VLOOKUP(A3,$J$3:$K$12,2,TRUE)", the 4th argument of the VLOOKUP function, "TRUE", indicates

Answers:

• that the table array for the look up has already been validated

• to use a range look up, returning the closest matches to the lookup values

• to only look up values that are greater than zero

• to ignore blank values in the index column and return the next value in the index column when a blank is encountered

• to use an exact look up, only returning exact matches to the lookup value

682. The formula QUARTILE(a1:a100,2) returns the same value as:

Answers:

• =MEAN(a1:a100)

• =PERCENTRANK(a1:a100,.5)

• =AVERAGE(A1:A100)

• =MEDIAN(a1:a100)

683. If you enter the formula =mod(2,5) into a cell, what will the cell display?

Answers:

• 5

• 3

• An error message.

• 2

• 2.5

684. Which of the below is true of Excel Macro-enabled templates and Excel Add-ins?

Answers:

• Excel Add-ins can't access userforms; Excel Macro-enabled templates can.

• Excel Add-ins can't embed controls on their worksheets; Excel Macro-Enabled templates can.

• Excel Macro-enabled templates can use conditional formatting on their spreadsheets; Excel Add-ins can't.

• Excel Macro-enabled templates contain modules; Excel Add-ins don't.

685. You're writing a macro and want to add the following comment: use this code. How would you add this comment?

Answers:

• <>use this code

• <comment>use this code<end/comment>

• 'use this code

• "use this code"

686. What is the quickest way to highlight all of the blank cells in selected section that has both filled and blank cells?

Answers:

• Write a conditional format with ISBLANK

• All of the above

• Select each blank cell while holding down the control key

• Choose the "Blank" function in the Go To menu

687. The formula =REPLACE("the cat in the hat",12,3,"red") returns:

Answers:

• red cat in red hat

• red cat in the hat

• the red cat in the hat

• the cat in red hat

688. What does the Surface chart type describe?

Answers:

• It compares three sets of values in a three dimensional chart

• It shows the patterns or relationship between two or more sets of value

• Compares a collection of values from several different data sets

• Compares values from different categories

689. Which worksheet function estimates standard deviation based on a sample, including numbers, text, and logical values?

Answers:

• STDEV

• STDEVNTL

• STDEVA

• STDEVALL

690. You can automatically adjust the size of text in a cell if they do not fit in width by

Answers:

• From Format Cells dialog box mark Shrink to fit check box

• From Format choose Columns and then Autofit Selection

• Right Clicking on the Column Header

• Double clicking on the right border of column header

• Double clicking on the left border of Column header

691. Which of the following is an example of a conditional formatting rule?

Answers:

• Color Scales

• Font Sets

• Formatting Scales

• Create Rules

• Past Rules

692. The formula SUMPRODUCT(a1:a4,b1:b4) is equivalent to:

Answers:

• (a1+b1+a2+b2+a3+b3+a4+b4)*a1

• a1*b1*a2*b2*a3*b3*a4*b4

• a1*b1+a2*b2+a3*b3+a4*b4

• sum(a1:a4)*sum(b1:b4)

693. Which automation function calls a procedure in a dynamic link library or code resource?

Answers:

• SQL.REQUEST

• CALLDYN

• REGISTER.ID

• CALL

• GETPIVOTDATA

694. Which worksheet function returns the maximum value in a list of arguments, including numbers, text, and logical values?

Answers:

• MAX

• MAXA

• LARGEST

• LONGEST

• MAXIMUM

695. A 3D reference means

Answers:

• Reference to cells on other worksheets

• None of these

• Adding a graphic to the spreadsheet

• Formatting with 3 colors

• A special kind of function that uses 3 numbers

696. When does the #NAME? error occur?

Answers:

• When you have entered an invalid SYNTAX.

• When you enter the wrong name for a function.

• When you have forgotten to save your current sheet.

• None of these. That is not an error code in Excel.

697. Which worksheet function returns the sum of squares of deviations?

Answers:

• DEVSQRT

• DSQRT

• SQDEV

• DEVSQR

• DEVSQ

698. If you enter "August 1, 2012" (without quotes) in cell A1 in a new worksheet, which data type would it be?

Answers:

• formula

• label

• value

• text string

• both a label and a value

699. True or False: The list of items for a dropdown box must be on the same worksheet as the dropdown box.

Answers:

• True

• False

700. Cell A1 contains the text string 'BA1740'. The formula to extract the last four characters as a value in A1 is:

Answers:

• =VALUE(RIGHT(A1,4))

• =TEXT(RIGHT(VALUE(A1),4))

• =CHAR(LEFT(A1,2))

• None of these

701. To add a comment, select the:

Answers:

• Formulas tab

• Review tab

• None of these

• File tab

• Insert tab

702. By placing the cursor in a cell and inserting a picture, the picture is then "attached" to that cell.

Answers:

• True

• False

703. When you compress pictures to reduce file size, what is the ppi that works best for viewing on screen?

Answers:

• 220 ppi

• Keep current resolution

• 150 ppi

• 96 ppi

704. How do you remove a field from a pivot table?

Answers:

• Deselect the field in the bottom part of the pivot table field list

• Select it in the bottom part of the pivot table field list and "drag it away"

• Double click on the field you do not want to make it disappear

• X out the field you do not want to the side of the pivot table field list

• Select it in the top part of the pivot table field list and "drag it away"

705. Which option won't allow you to print an embedded chart?

Answers:

• Selecting the chart before you print and clicking "Print selection"

• Formatting the chart before you print

• Moving the chart to a chart sheet before you print

706. Which of the following is NOT an option for formatting a row?

Answers:

• Autofit

• Width

• Hide

• Height

• Unhide

707. Which worksheet function returns values along a linear trend?

Answers:

• TREND

• TRENDLIN

• LINEAR

• TRENDLN

• LINTREND

708. True or False: The Remove Duplicates command treats items that are spelled the same but capitalized differently as two distinct items.

Answers:

• False

• True

709. The formula ISERR(A1) does not return TRUE if A1 is :

Answers:

• #NAME?

• #N/A

• #VALUE!

• #REF!

710. What formula generates a fractional number that is greater than 0 and less than 1?

Answers:

• =RANDBETWEEN(0,1)

• =RANDOM(0,1)

• =VARIABLE(0,1)

• =RAND()

711. Which of the following is NOT TRUE  about scatter plot graphs?

Answers:

• They can be used to perform multivariate regressions

• They can be used to perform both linear and non-linear regressions

• They have built in functionality to display the R^2 of the regression

• They have built in functionality to display the regression equation

712. True or False: If a column is hidden, Excel will not copy the contents of the hidden cells in a copy/paste set of commands.

Answers:

• False

• True

713. Which worksheet function estimates variance based on a sample?

Answers:

• sVAR

• VARIANCE

• VARS

• VARA

• SVAR

714. When working in a Pivot Table, the additional menu items "Options" and "Design" are found:

Answers:

• In the system tray

• At the top of the Pivot Table

• In the right click menu with the Pivot Table is activated

• As tabs on the far right side of the ribbon

715. If two formulas that depend on one another need to be solved at once and a circular reference is created, this can be resolved by:

Answers:

• tracing precedents and reconfiguring the feed cells

• copying/pasting formula(s) into a different cell to avoid a circular reference

• a circular reference is an error and cannot be resolved without changing or correcting the formulas

• allowing iterations

716. True or False: If a file is saved, the actions that preceeded the save command cannot be undone.

Answers:

• False

• True

717. Which of the following is a valid option in the Data Validation Allow settings?

Answers:

• Duplicates

• Text Length

• Text Size

• Warning

718. Which keyboard shortcut selects cells that contain formulas that directly reference the active cell?

Answers:

• CTRL+SHIFT+}

• CTRL+SHIFT+O (the letter O)

• CTRL+[

• CTRL+] (closing bracket)

719. True or False? The sumif function can sum data based on multiple criteria.

Answers:

• True

• False


2 NOT Answered Yet Test Questions:

(hold on, will be updated soon)
720. If cell A1="Smith,Joe" then =Find(",",A1) returns:

Answers:

• FALSE

• 4

• TRUE

• 6

721. In default formatting, when you preface the string  "10500" with a dollar sign ($) and press enter, what is the result?

Answers:

• $10,500

• $10500

• $10500.00

• $10,500.00