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

**This helps getting job:** Hundreds of (cover letter examples , interview questions , profile samples ) • Earn on Upwork (oDesk) **Job assistance:** jobs popularity • freelance 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:

`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

• 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

`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

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

`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