MS Excel Test Upwork Answers 2019

1. Suppose you clone the slicer shown in the image above into another worksheet in Microsoft Excel 2010. Considering that you make changes to the tile selected, in one of the slicers, which of the following statements shall be true?

Answers:

• All cloned copies of that slicer are also updated instantly.
• Only the original slicer reflects the changes being made.
• An error is displayed because no updations are allowed in slicers having cloned copies.
• None of the above.

2. Microsoft Excel Services is a component of:

Answers:

• Microsoft SharePoint Technology
• Microsoft Server Technology
• Microsoft Virtualization Technology

3. Which of the following statements is NOT true regarding import of data into PowerPivot?

Answers:

• Data can be imported into PowerPivot by either selecting list of tables or by writing SQL query.
• The data being imported into PowerPivot can be filtered before the import.
• The PowerPivot Import Wizard provides you with an SQL Query builder.

4. Which among the following settings is the default macro security setting?

Answers:

• Disable all macros except digitally signed macros.
• Disable all macros with notification.
• Enable all macros.

5. Which of the following sorting options is NOT available under the Sort On option while sorting the data of a table in an Microsoft Excel 2010 worksheet?

Answers:

• Cell Color
• Font Color
• Font Style

6. Analyze the syntax of LOOKUP function when used in vector form?

LOOKUP(lookup_value, lookup_vector, result_vector)

The values in the lookup_vector must be placed in ________ order.

Answers:

• Ascending
• Descending
• Either ascending or descending

7. You are creating a new formatting rule which will be used to format a PivotTable report. Which of the following formatting styles is NOT available while formatting all cells of PivotTable report based on their values in an MS Excel 2010 worksheet?

Answers:

• 2-Color Scale
• 3-Color Scale
• 4-Color Scale
• Data Bar

8. Analyze the image shown. What would be the output when you use the following formula in an MS Excel 2010 worksheet?

=SUM(A2:CHOOSE(2,A3,A4,A5))

Answers:

• 67
• 80
• 90
• 60

9. Analyze the syntax of LOOKUP function when used in vector form?

LOOKUP(lookup_value, lookup_vector, result_vector)

Which of the following arguments of the LOOKUP function is optional?

Answers:

• lookup_value
• lookup_vector
• result_vector

10. How would you assign a macro to a graphic?

Answers:

• On the Developer tab, in the Code group, click on Macros.
• On the View tab, click on Macros and then click on Assign Macro on the shortcut menu. In the Macro name box, click on the macro that you want to assign.
• On a worksheet, right-click on the graphic, then click on Assign Macro on the shortcut menu. In the Macro name box, click on the macro that you want to assign.

11. Which of the following statements is not true regarding offline cube files in Microsoft Excel 2010?

Answers:

• An offline cube file stores data in the form of an OLAP cube.
• If you delete the offline cube file for the report, still you can create a new offline cube file for the report.
• An offline cube file allows you to work with PivotTable and PivotChart reports even when the server is unavailable.

12. In the given screenshot, there are red triangles in the upper-right corner of each cell under the column titled ‘Registrations’. What do these signify?

Answers:

• The red triangles represent the comment associated.
• The red triangles signify an associated data validation control.
• The red triangles represent the tracking of the cell.
• The red triangles signify that the cell is a hidden cell.

13. When you double-click on a pivot report value of a PivotTable in Microsoft Excel 2010, Excel creates new ________ with data corresponding to the pivot report value.

Answers:

• Table
• Worksheet
• Workbook

14. State whether following statement regarding cell references in Microsoft Excel 2010 is true or false:

A Relative cell reference consists of the column letter and row number surrounded by dollar signs ($).

Answers:

• True
• False

15. Slicers are filtering components which are used to filter the data in a PivotTable report or CUBE functions. Which of the following data sources is/are supported by Slicers in Microsoft Excel 2010?

Answers:

• Office Database connections
• Online Analytical Processing (OLAP) Queries
• Online Analytical Processing (OLAP) Cube Files
• All of the above

16. Which of the following axis settings are available for the appearance of data bars for negative values in Microsoft Excel 2010?

Answers:

• Cell midpoint
• Cell left
• Cell right

17. Which of the following areas in the Microsoft Excel 2010 PivotTable Field List, calculates the aggregates of columns?

Answers:

• Report Filter
• Row Labels
• Values

18. Which of the following statements is true regarding PowerPivot in Microsoft Excel 2010?

Answers:

• One limitation of PowerPivot is that it does not allow access to Slicers when working on data in PowerPivot.
• A connection to a server running SQL Server Analysis Services is required to answer PivotTable queries by the PowerPivot engine.
• Version control and tracking is supported by PowerPivot.

19. Suppose you have created a report in Excel named ‘report.xlsx’. Now you want your team members to be able to see the report remotely on a web browser, and edit the report, as desired. Also it is required that the previous versions of the report are maintained to protect against any loss of data. Which of the following options is the most suitable one to deploy this report?

Answers:

• Share ‘report.xlsx’ by saving it to SkyDrive.
• Share ‘report.xlsx’ by saving it to a SharePoint site.
• Share ‘report.xlsx’ by sending it through email.

20. Which of the following tools is used to format Excel worksheet data, as shown in the figure above?

Answers:

• Slicer
• SmartArt
• Macros

21. In the given screenshot, the letter X is pointing to an area between the first Row header, and the first Column header. What will happen when this particular area is selected?

Answers:

• It will select the whole sheet.
• It will close and save this particular worksheet of the workbook.
• Nothing will happen.
• It will hide this particular worksheet.

22. Which of the following components of Excel Services enables the user to edit a published workbook inside a web browser?

Answers:

• Excel Calculation Services (ECS)
• Excel Web Access (EWA)
• Excel Web Services (EWS)

23. What is the filename extension of Excel workbooks which are saved as ‘Excel Templates’?

Answers:

• .xlsx
• .xslt
• .xltx
• .slk

24. State whether following statement regarding images in Microsoft Excel 2010 is true or false:

Microsoft Excel 2010 supports 3D rotation of images.

Answers:

• True
• False

25. In the above given screenshot of a Microsoft Excel 2010 Worksheet, there are two columns B and D containing some integer data.
Considering that cell D9 is assigned the formula: =SUM(D3:D7), which of the following statements is true?

Answers:

• The value of cell D9 will be 24
• The value of cell D9 will be 42
• The value of cell D9 will be #VALUE!

26. Which of the following statements are true regarding PowerPivot in Microsoft Excel 2010?

Answers:

• PowerPivot streamlines the process of integrating data from multiple sources like databases, spreadsheets, reports and text files.
• PowerPivot does not allow access to slicers when working on data in PowerPivot.
• Version control and tracking are NOT supported by PowerPivot.

27. With reference to the screenshot given, which of the following formulae CANNOT be used to calculate the average of the column ‘Marks’ in the cell ‘E17’?

Answers:

• =AVERAGE(E6,E7,E8,E9,E10,E11,E12,E13,E14,E15)
• =AVERAGE(E6:E15)
• =(SUM(E6:E15)/COUNT(E6:E15))
• =AVERAGE(E6 to E15)

28. Which of the following statements is true regarding MS Excel Web Access on a SharePoint Foundation 2010 Server?

Answers:

• The worksheet grid in Excel Web Access on SharePoint Foundation 2010 Server is limited to 20 columns and 75 rows.
• It is possible to select contiguous and noncontiguous cells on a worksheet using mouse and keyboard.
• It is not possible to use the CTRL+Enter keyboard combination on formulas entered in multiple cells in a worksheet.

29. State whether the following is true or false:

Excel Web App supports Undo/Redo but it is disabled when multiple users are collaborating on the same workbook

Answers:

• True
• False

30. State whether the following statement regarding cell references in Microsoft Excel 2010 is true or false:

An Absolute cell reference consists of the column letter and row number.

Answers:

• True
• False

31. In the given screenshot, the contents of the ‘Name of User’ column have been oriented at an angle. How is this done?

Answers:

• By using the ‘Orientation’ button in the alignment group of the ‘Home’ tab.
• By using the ‘Orientation’ button on the ‘Page Layout’ tab.
• By using the ‘WordArt’ button on the ‘Insert’ tab.

32. What are the benefits of digitally signing an e-mail in Microsoft Excel 2010?

Answers:

• Digital signatures help authenticate the source of the messages.
• Both the sender and the receiver of a message can verify that the message has not been altered during transmission, if the message is digitally signed.
• Digitally signed e-mails meet legal requirements for admissibility and authenticity.
• All of the above.

33. Deleting a PivotChart report automatically deletes the associated PivotTable report.

Answers:

• True
• False

34. You are a financial consultant to multiple clients and you made an investment model for them. Now you want to find out how investment rates change under different market conditions. Which of the following Microsoft Excel 2010 What-if analysis tools will you use to study such conditions?

Answers:

• Scenarios
• Data tables
• Goal Seek

35. State whether the following statement regarding cell references in Microsoft Excel 2010 is true or false:

A Relative cell reference consists of the column letter and row number surrounded by dollar signs ($).

Answers:

• True
• False

36. What does the icon marked with a red square in the figure above signify?

Answers:

• It signifies that a macro is enabled.
• It signifies that the document contains digital signatures.
• It signifies that a macro is disabled.
• It signifies that a webcam has been detected.

37. Excel keeps the changed history of a workbook for ________ days by default.

Answers:

• 10 days
• 20 days
• 25 days
• 30 days

38. Which of the following is available as fill options while formatting cells of an Excel worksheet using the format style as Data Bar?

Answers:

• Gradient Fill
• Crystal Fill
• Texture Fill

39. Suppose you create a workbook in Microsoft Excel 2010 and run the ‘Check Accessibility’ option. This option is used to check the workbook for ____________.

Answers:

• Content that people with disabilities might find difficult to read.
• Checking features not supported by earlier versions of Excel.
• Both a and b

40. Which of the following axis settings are available for the appearance of data bars for negative values in Microsoft Excel 2010?

Answers:

• Automatic
• Cell right
• Cell left

41. What is the purpose of time stamping a digital signature in Microsoft Excel 2010?

Answers:

• It helps you mitigate integrity threats.
• It ensures that digital signatures remain valid and legally defensible even if the certificate that is used to sign the document expires.
• It enables you to specify cryptographic settings for encrypting documents.
• None of the above

Question: 1

In Microsoft Excel 2016, which of the following options can be locked in a worksheet?
i) Ranges
ii) Formulas
a. Only option i).
b. Only option ii).
c. Both options i) and ii).
d. Neither option i) nor ii).

Answer: a. Only option i).

Question: 2

This question is based upon the figure shown below.

Consider the Excel sheet given in the image. What will be the output if you type the formula, =COUNTA(A1:B8), in cell B9?
a. 5
b. 6
c. 8
d. 12

e. It will return an error because the formula is incorrect.

Answer: d. 12

Question: 3

This question is based upon the figure shown below

Consider the given image for the following question.

There are red triangles in the upper-right corner of each cell under the column titled 'Class'. What do they signify?
a. They represent that the cells are containing formatted data.
b. They represent that some data validation is associated with the cells.
c. They represent that the comments are associated with the cells.
d. They represent that the cells cannot be edited.

Answer: c. They represent that the comments are associated with the cells.

Question: 4
This question is based upon the figure shown below

Consider the data given in the image. What will be the output if the formula, =SKEW(A2:A11), is applied to the given content in Microsoft Excel 2016?
a. 0
b. 0.14111
c. -1.21111
d. Error

Answer: b

Question: 5
While using cell references with multiple worksheets in Microsoft Excel 2016, which of the following options is the correct absolute cell reference that must be used for cell B2 on a sheet named 'Sheet5'?

a. $Sheet5$B2
b. $Sheet5.B2
c. Sheet5!B2
d. $Sheet5!B2

Answer: c. Sheet5!B2

Question: 6
Which of the following Microsoft Excel 2016 functions is used in order to return the serial number that is used for representing the date, which is the indicated number of months before or after a specified date?
a. EDATE
b. EOMONTH
c. DATEVALUE
d. NUMBER

Answer: c. DATEVALUE

Question: 7

This question is based upon the figure shown below

Which of the following keyboard shortcuts is used for achieving the strikethrough effect as shown in the given image?

a. ALT+F9

b. CTRL+ALT+F10

c. SHIFT+F5

d. CTRL+5

Answer: d. CTRL+5

Question: 8

In Microsoft Excel 2016, how can we publish a Workbook to Power BI?

a. File > Options > Publish to Power BI

b. File > Save As > Change "File as type" > Publish to BI

c. File > Share > Publish to BI > Save to Cloud

d. File > Publish > Publish to Power BI

Answer: d. File > Publish > Publish to Power BI

Question: 9

Which of the following options can be copied using the format painter in Microsoft Excel 2016?

a. Alignment

b. Background color

c. Cell text

d. None of the above

Answer: c. Cell text

Question: 10

This question is based upon the figure shown below

Consider the Excel sheet given in the image. What will be the output if you enter the formula, =AVERAGE(A3:C8), in cell C9?

a. 7

b. 11.25

c. 13.5

d. It will throw an error.

Answer: b. 11.25

Question: 11

This question is based upon the figure shown below

Suppose that you have a table in a worksheet of Microsoft Excel 2016 workbook, as shown in the image. Which of the following output is obtained if you select the table and check the Total Row option under the Design tab?

a. 15

b. 18

c. 29

d. 79

Answer: d. 79

Question: 12

By default, how many worksheets are there in a new Microsoft Excel 2016 workbook?

a. One

b. Two

c. Three

d. Five

Answer: a. One

Question: 13

This question is based upon the figure shown below

Consider the Excel sheet given in the image. What will be the output if we apply the formula, =AVERAGEIFS(D2:D7, A2:A7, 2016, B2:B7, ">6")?

a. 4

b. 6

c. 10

d. 16

Answer: d. 16

Question: 14

Which of the following functions is used for providing the decimal number of the time represented by a text string?

a. Now()

b. Trend()

c. TimeValue()

d. Numbervalue()

Answer: c. TimeValue()

Question: 15

In Microsoft Excel 2016, when do we receive a "#Value!" error in the VLOOKUP function?

i) When the "lookup_value" argument is more than 255 characters.

ii) When the "col_index_num" argument contains text.

iii) When the "col_index_num" argument is equal to 0.

a. Only option i) is correct.

b. Only options i) and ii) are correct.

c. Only options ii) and iii) are correct.

d. All options i), ii) and iii) are correct.

Answer: d. All options i), ii) and iii) are correct.

Question: 16

Which of the following keyboard shortcuts is used to display or hide the ribbon in Microsoft Excel 2016?

a. Alt+F1

b. Alt+Shift+F1

c. Ctrl+F1

d. F1

Answer: d. F1

Question: 17

By default, all cell references in Microsoft Excel 2016 are:

a. Relative.

b. Absolute.

c. none of the above.

Answer: a. Relative.

Question: 18

What will happen if you enter XYZ1 in a cell and drag the fill handle down?

a. It will fill XYZ1 in all the cells that are dragged through.

b. It will fill the series with XYZ2, XYZ3, XYZ4 and so on, respectively.

c. It will fill the series with XYZ2, ABC3, DEF4 and so on, respectively.

d. None of the above.

Answer: b. It will fill the series with XYZ2, XYZ3, XYZ4 and so on, respectively.

Question: 19

Which of the following options is the correct keyboard shortcut for accessing the backstage view in Microsoft Excel 2016?

a. Ctrl + B

b. Alt + F

c. Alt + B

d. Tab + F

Answer: b. Alt + F

Question: 20

In Microsoft Excel 2016, which of the following tabs will you click for accessing the View Macros option?

a. Developer

b. Review

c. View

d. Home

Answer: c. View

Question: 21

This question is based upon the figure shown below

Consider the data given in the image. What will be the output if the formula, =PROPER(A1), is applied to cell A2?

a. This is a Book

b. This Is A Book

c. This is a book

d. It will throw an error because the formula cannot contain cell reference.

Answer: b. This Is A Book

Question: 22
This question is based upon the figure shown below

In Microsoft Excel 2016, what will happen when we click on the marked triangle as shown in the given image?
a. It will select cell A1.
b. It will select the complete Worksheet.
c. It will select all the Worksheets available in a Workbook.
d. It will add a Worksheet to the file.

Answer: b. It will select the complete Worksheet.

Question: 23
Which of the following logical functions doesn't return True or False as a result in Microsoft Excel 2016?
a. AND Function
b. IF Function
c. OR Function
d. XOR Function

Answer: d. XOR Function

Question: 24

This question is based upon the figure shown below

Consider the data given in the image. What will be the output if the formula, =SKEW(A2:A11), is applied to the given content in Microsoft Excel 2016?

a. 0

b. 0.14111

c. -1.21111

d. Error

Answer: b. 0.14111

Question: 25

What is/are the default file extension(s) of a Microsoft Excel 2016 workbook?

a. . xls

b. . xlsx

c. . doc

d. Both options a and b.

Answer: b. . xlsx

Question: 26

Which of the following options can be used for checking the protection status of a file in Microsoft Excel 2016?

a. Click on the Review tab > Select New > Click on Protect Workbook

b. Click on the File tab > Select Options > Click on Protect Workbook

c. Click on the File tab > Select Info > Click on Protect Workbook

d. Click on the Review tab > Select Account > Click on Protect Workbook

Answer: d. Click on the Review tab > Select Account > Click on Protect Workbook

Question: 27
The IT security department of Mark's organization has decided that only digitally signed macros will be allowed in Microsoft Excel 2016. Which of the following is the correct way to restrict the unsigned macros?
a. Go to the File tab -> Click Info -> Click Excel Options -> Click Security-> Select Allow digitally signed macros only
  b. Go to the File tab -> Click Info -> Click Excel Options -> Click Security-> Select Disable All macros except digitally signed macros
    c. Go to the File tab -> Click Options -> Click Trust Center under the Excel Options dialog box-> Click Trust Center Settings -> Click Macro Settings -> Select Disable All macros except digitally signed macros
  d. Go to the File tab -> Click Options -> Click Trust Center under the Excel Options dialog box -> Click Macros -> Select Disable All macros except digitally signed macros

Answer: c. Go to the File tab -> Click Options -> Click Trust Center under the Excel Options dialog box-> Click Trust Center Settings -> Click Macro Settings -> Select Disable All macros except digitally signed macros

Question: 28
In Microsoft Excel 2016, which of the following options provides a user the access to options such as Share, Export, Account, Publish, etc.?
  a. Ribbon
  b. View tab
  c. Backstage View
  d. Page Layout tab

Answer: c. Backstage View

Question: 29

Choose True or False.

If a user specifies a print area from the backstage view, then it saves the setting with the workbook.

a. True

b. False

Answer: b. False

Question: 30

This question is based upon the figure shown below

From the given image, which of the following options should be selected, in order to enable Page Break Preview in Microsoft Excel 2016?

a. 1

b. 2

c. 3

d. None of the above.

Answer: c. 3

Question: 31

In Microsoft Excel 2016, which of the followings is NOT a valid option for Macro Settings under the Trust Center?

a. Disable all macros without notification.

b. Disable all macros with notification.

c. Disable all macros including digitally signed macros.

d. Enable all macros.

e. Disable all macros except digitally signed macros.

Answer: c. Disable all macros including digitally signed macros.

Question: 32

In Microsoft Excel 2016, the CHOOSE function can be used for selecting up to how many values based on an index number?

a. 64

b. 128

c. 254

d. 508

Answer: c.

Question: 33
Which of the following commands in Microsoft Excel 2016 allows a user to automatically create groups and use common functions such as sum, count and average for helping in summarizing data?
a. Group
b. Filter
c. Data Validation
d. Subtotal

Answer: d. Subtotal

Question: 34

In Microsoft Excel 2016, which of the following arguments of the YEARFRAC function is/are optional?

i) Start date.

ii) End date.

iii) The type of day count basis to use.

a. Only option ii).

b. Only options i) and ii).

c. Only option iii).

d. Only options ii) and iii).

Answer: c. Only option iii).

Question: 35

In Microsoft Excel 2016, the Save As option can be accessed from which of the following tabs?

a. File

b. Home

c. Review

d. View

Answer: a. File

Question: 36
In Microsoft Excel 2016, what is the correct function of the Format Painter option?
  a. To edit font.
  b. To insert functions.
  c. To copy and paste the formatting of one cell to another in a Worksheet.
  d. To sort and filter data.

Answer: c. To copy and paste the formatting of one cell to another in a Worksheet.

Question: 37

Which of the following statements is correct about subtotal and total fields in a Microsoft Excel 2016 PivotTable report?

a. If a calculated item is contained by a field, then the subtotal summary function cannot be changed.

b. Sum, count and average functions can be used as a subtotal.

c. Var and Varp functions cannot be used as a subtotal.

d. None of the above.

Answer: b. Sum, count and average functions can be used as a subtotal.

Question: 38

How can we define a name to a cell in a Microsoft Excel 2016 Worksheet?

a. Right-click on the cell and click on "Define Name".

b. Click on the "Formulas" tab and click on "Define Name" under the "Define Names" group.

c. Click on the "Home" tab and click on "Format" under the "Cells" group.

d. Click on the "Home" tab and click on "Define Name" under the "Define Names" group.

Answer: b. Click on the "Formulas" tab and click on "Define Name" under the "Define Names" group.

Question: 39

This question is based upon the figure shown below

Suppose that you conducted an aptitude test in a class room to check if the students are ready for competitive exams. Now you want to calculate the average of the total marks obtained by the students in the test. Which of the following formulas can be used to calculate the average of the "Marks Obtained" in the cell "I4"?

a. =AVERAGE(B4:G4)

b. =AVG(B4 C4 D4 E4 F4 G4)

c. =(SUM(B4:G4)/COUNT(B4:G4))

d. Both options a and c.

e. Both options a and b.

Answer: d. Both options a and c.

Question: 40

Which of the following keyboard shortcuts is used for moving from any cell to the beginning of a worksheet, i.e. cell A1?

a. Windows + Home key

b. Alt + 1 key

c. Ctrl + Home key

d. Windows + 1 key

Answer: c. Ctrl + Home key

Question: 41

This question is based upon the figure shown below

Consider the Excel sheet given in the image. What will be the output if you type the formula, =COUNTA(A1:B8), in cell B9?

a. 5

b. 6

c. 8

d. 12

e. It will return an error because the formula is incorrect.

Answer: d. 12

Question: 42

Which of the following cell references in a formula should be used for making sure that a column value does not change when copied?

a. A$2

b. $A2

c. !A2

d. A!2

Answer: a. A$2

Question: 43

Choose True or False.

A PivotChart is a graphical representation for a summary of data displayed in a Pivot table.

a. True

b. False

Answer: b. False

Question: 44

The IT security department of Irvin's organization decides that the Office Data Connection Files must be opened in a protected view. Which of the following procedures should be opted by Irvin?

a. Go to the View tab -> Click on Security -> Click on Protected File Types -> Check the Office Data Connection Files check box

b. Go to the File tab -> Click on Options -> Click on Excel Options -> Click on Security -> Check the Block Office Data Connection Files check box

c. Go to the File tab -> Click on Options -> Click on Trust Centre under the Excel Options dialog box -> Click on File Block Settings -> Check the Office Data Connection Files check box

d. None of the above.

Answer: c. Go to the File tab -> Click on Options -> Click on Trust Centre under the Excel Options dialog box -> Click on File Block Settings -> Check the Office Data Connection Files check box

Question: 45

This question is based upon the figure shown below

Consider the given image for the following question.

What does the blue triangle and border color imply in cell B4?

a. They imply that a comment is associated to cell B4.

b. They imply that a filter has been applied to cell B4.

c. They imply that the Goal Seek option of What-if Analysis is applied to cell B4.

d. They imply that Track Changes has been enabled and cell B4 is edited.

Answer: d. They imply that Track Changes has been enabled and cell B4 is edited.

Question: 46

While entering data in an Excel 2016 worksheet, what happens if you press the tab key in a cell?

a. You will move to next column.

b. You will move to the first column.

c. You will move to the last column.

d. You will move to the previous column.

Answer: a. You will move to next column.

Question: 47

Peter was working in a Microsoft Excel 2016 workbook. His workbook closed due to a system error and he couldn't save it. Which of the following is the correct way to recover an unsaved workbook?

a. Go to the File tab -> Click on Save -> Click on Recovered Workbook

b. Go to the File tab -> Click on Open -> Click on Recover Unsaved Workbooks

c. Go to the File tab -> Click on Options -> Click Save -> Click on Recover Unsaved Workbooks

d. Go to the File tab -> Click on Options -> Save Workbooks -> Click on Recover Unsaved Workbooks

Answer: b. Go to the File tab -> Click on Open -> Click on Recover Unsaved Workbooks

Question: 48

In Microsoft Excel 2016, the =TODAY() command returns:

a. the current day name.

b. the date when the Excel file was created.

c. the current day and date.

d. the current date.

Answer: d. the current date.

Question: 49

In Microsoft Excel 2016, which of the following options is used to access the option that is used to get data from a text file?

a. Click the Data tab -> Select the option "From Other Sources" from the Get External Data group-> Click "From Text"

b. Click the Data tab -> Select the option "Existing Connections" from the Get External Data group-> Click "From Text"

c. Click the Data tab -> Select the option "From Text" from the Get External Data group

d. Click the Formulas tab -> Select the option "From Text" under the Connections group

Answer: c. Click the Data tab -> Select the option "From Text" from the Get External Data group

Question: 50

Which of the following keyboard shortcuts is used for displaying the Record Macro dialog box that is used for recording macros?

a. CTRL+A+R+M

b. CTRL+ALT+M+R

c. CTRL+M+R

d. ALT+T+M+R

Answer: d. ALT+T+M+R

Question: 51

How can we remove password encryption from a Microsoft Excel 2016 file?

a. Open the Workbook and save it again with a new password.

b. Copy and paste the data in another Workbook and save it.

c. Enter the password of the Workbook and delete the password in the Permissions - Encrypt with Password box.

d. Open the Workbook and from the file menu, select Protect Workbook-Decrypt the Workbook

Answer: c. Enter the password of the Workbook and delete the password in the Permissions - Encrypt with Password box.

Which of the following options are available for auto fill, when you release the mouse button after using AutoFill, Excel not only fills in the series but also displays the Auto Fill options button ?

Copy Cells
Fill Formatting Only

In the case of synchronizing changes between Table and model, a linked table is a live connection between the range or named table that contains the data values, and the Data Model that powers the report, by default. The Data Model is updated automatically if changes occur in ___________ ?

Tables

Which among following settings is the default macro security setting by?

Disable all macros with notification

Information Rights Management (IRM) allows individuals and administrators to specify access permissions to documents, workbooks, and presentations. This helps present sensitive information from being printed, forwarded, or copied by unauthorized people. IRM cannot help with the following though:

Prevent content from being copied by using third-party screen capture programs.

Which of the following are advantages of range names ?

Names are easier to remember than range coordinates.
Names don’t change when you move a range to another part of the worksheet.
Named ranges adjust automatically whenever you insert or delete rows or columns within the range.

Which of the following formulas yields the address of the last cell in a range ?

ADDRESS(ROW(rng)+ROWS(rng)-1,COLUMN(rng)+COLUMNS(rng)-1)

Some features work differently in Excel online as compared to the Excel desktop version. Which of the following is not true for Excel online?

Workbooks that have Information rights management(IRM) settings applied at the workbook level cannot be viewed in a browser window.

In Excel, a data list, or database table, is a table of worksheet data that utilizes a special structure, a built-in Data from which allow the users to edit records in a data list. For smaller data lists, you can use the navigation keys as well the scroll bar of the data form to locate a record which requires editing. What type of commands can be used for locating a record if we want to make changes in it while using larger data lists?

Filter command

Excel contains a conditional formatting present that highlights top/bottom values. However, using a formula instead provides more flexibility. If you have a series of data with high & low magnitude values and you want to format only N number of high magnitude values in the series of data, which of the following formulas is valid?

= A1<= MAX(data,N)

What is the filename extension of Excel workbooks which are saved as ‘Excel Templates’?

.xltx

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Names must be no more than 255 characters long. You can also use any single letter as a range name except the following ?

A _________ is similar to a data bar in that it compares the relative values of cells in a range. Instead of bars in each cell, you see cell shading, where the shading color reflects the cell’s value.

color cell

What are Information Functions in Excel?

ISFOMULA
SHEET
SHEETS

If cell A2 has a full name (Firstname, Middle Initial and Lastname) like “Karen E. McRich” which of the following formulas will extract the lastname ?

=Right(a2,find(“.”,a2)-1,1)

We can perform what If Analysis in Excel with the use of Data Tables in which we can see the effect of changing an input value on the result returned by a formula as soon as we enter a new input value in the cell that feeds into the formula.
which types of Data Tables can we create to perform what- If Analysis in Excel?

One & Two Variable Data Tables

The auto calculate feature in Excel does which of the following ?

Provides a quick way to view the result of an arithmetic operation on a range of cells.

A Data Model integrates the tables, enabling extensive analysis using PivotTables ________ , ________

Power Pivot, Power View

What could you do to stop the pivot table from losing the column width upon refreshing?

Format loss in a pivot table can be prevented simply by changing the pivot table options. under the “pivot Table Options” turn on the “Enable Preserve Formatting” and disable the “Auto Format” options.

Select Home->Fill->Series. Excel displays the series dialog box. Which of the following represent the “Growth” type?

None of above

Excel provides _______, which are labels applied to a single cell or to a range of cells.

Range Names

An Excel workbook is a collection of?

Worksheets and charts

Anytime when you return more than single cell, you should be using array formula. Array formulas are special formulas that work on an array of numbers and it will work effectively if you press the following keys combination?

Ctrl+shift+enter

Which of the following formulas uses absolute references?

=$A$1*2
=D11 * $C$3

In most cases the PivotTable loses the column width when it is refreshed. which of following options can be used when the PivotTable is refreshed, so as to not lose the width of column ?

Enabling Preserve formatting and disabling Auto Format in PivotTable Options

Tables offer which of the following advantages?

All of the above

Use_____ to visualize the relative values of cells in a range. In this case, however, Excel adds a particular icon to each cell in the range, and that icon tells you something about the cell’s value relative to the rest of the range?

Icon Sets

All 2-D and 3-D charts have an x-axis known as the horizontal axis and a y-axis known as the vertical axis. Which of the following charts don’t have x-axis and y-axis?

Pie Chart

To show the actual formula instead of Cell Values which of the following function can be used?

FORMULATEXT(cell)

A worksheet range can be defined as which of the following?

A Group of cells

Which of the following formulas yields the work hours between dates on a custom schedule?

{=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&”:”&date2))),1,2,3,4,5,6,7))}

When you have many different PivotTables in one report, you can share a slicer that you created in one PivotTable with other PivotTables. Which of the following statements is not true?

Any changes that you make to a shared slicer are immediately reflected in all PivotTables that are connected to that slicer

Which of the function listed will not work if you are looking for an exact value based on a right looking column?

VLOOKUP

Which of the following can be accomplished by Creating Highlighted Cell Rules?

What are the top 10 values
Which cell values are above average, and which are below average ?
Which cells values are duplicate � ?

Which of the following could be true when #NAME, error appears in an Excel cell?

You Spelled a function name incorrectly
you used a string value without surrounding it with quotation marks.

DGET Database Function in Excel extracts a single value from a record in the data list that matches the criteria you specify. DGET Function return error value if no record matches the specified criteria or the match criteria is greater than the specified criteria.

Which of the following error values is returned if multiple records are matched?

#NUM

Which of the following depicts a correct range?

B1:B30

Which of the following is/are array formulas?

=B12:B16*E12:E16
=SUM(B12:B16*E12:E16)

Upwork Test Answers 2019