01. John inserts a PivotTable in a worksheet, as shown in figure 1 of the given image. There are three columns in the data source: Employee, Product and Sales. Now John wants to show the list of products under the employee names as shown in figure 2 of the given image. Where should he double-click on the PivotChart in figure 1, so that he should be able to select the Product column?
On the header of the Employee column.
On the values of the Sum of Sales column.
On the values of the Employee column. ✔
On the header of the Sum of Sales column.
02. In Excel 2016, the & Reference Option is available under which Of the following tabs?
Insert
Page Layout
Formulas ✔
Review
03. In Microsoft Excel 2016, what does the VALUE function do?
It is used for converting a text argument to a number. ✔
It counts the cells that contain numbers in an Excel sheet.
It is used for adding numeric values.
It is used for calculating the ASCII values of a text string.
04. In Microsoft Excel 2016, what does the Look up value function do
Look up values vertically and horizontally in a list by using an exact match ✔
Look up values vertically in a list by using an exact match
Look up values horizontally in a list by using an approximate match
Look up values vertically and horizontally in a list by using an approximate match ✔
05. The cursor in the given image is on A3 cell. If you want to move the cursor to E8 cell, then which of the following shortcuts can be used?
Ctrl + L
Shift + Z
Ctrl + end ✔
Shift + end
06. Which of the given statements are correct about the slicers in Microsoft Excel 2016?
1. They can be used to filter data in a table.
2. They can be used to filter data in a PivotTable.
3. They can only be connected to PivotTables that share the same data source.
Only1 and 2
Only1 and 3
Only 2 and 3
All 1, 2, and 3 ✔
07. Which of the following functions checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE?
AND
OR
IF
XOR ✔
08. Which of the given types Of pivot charts can be used in Microsoft Excel 2016?
1. Pie chart
2. Waterfall chart
3. Treemap chart
All 1, 2, and 3
Only1 and 2
Only1 and 3
Only 2 and 3 ✔
09. What are the correct purposes of CEILINGMATHI) function in Microsoft Excel 2016?
It rounds a number up, to the nearest integer. ✔
It rounds a number up, to the nearest multiple of significance. ✔
It rounds a number down, to the nearest even integer.
It rounds a number up, to the nearest odd integer.
10. In Microsoft Excel 2016, which of the following commands should NOT be removed from the Quick Access Toolbar because these commands are NOT available on the ribbon
Undo ✔
Redo ✔
Save
Copy
None of the above
11. Which of the following tabs will you use to hide the header row of the table (given in the image)?
Insert ✔
Data
Design
View
12. While working in Microsoft Excel 2016, for which of the following options, if included in a worksheet, we cannot use the Track Changes option?
Comment
Table ✔
Chart
Picture
13. Can we change the default font size of a new workbook in Excel 2016 from the Backstage view?
Yes ✔
No
14. Which of the following options can be copied using the format painter in Microsoft Excel 2016?
1. Alignment
2. Background color
3. Cell text
Only 1 and 2 ✔
Only 1 and 3
Only 2 and 3
All 1, 2, and 3
15. 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?
They represent that the cells are containing formatted data.
They represent that some data validation is associated with the cells.
They represent that the comments are associated with the cells ✔
They represent that the cells cannot be edited.
16. In Microsoft Excel 2016, data sorting in columns can be performed on which Of the given Options?
1. Values
2. Cell Color
3. Font Color
4. Cell icon
All 1. 2. 3, and 4 ✔
Only1, 2. and 3
Only 2. 3. and 4
Only 1. 3. and 4
17. Which of the following commands is used in Microsoft Excel 2016 for totaling all the budget spreadsheets prepared by each department in an organization?
Sort
AutoSum
Filter
Consolidate ✔
18. 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?
Go to the File tab -> Click Info -> Click Excel Options -> Click Security-> Select Allow digitally signed macros only
Go to the File tab -> Click Info -> Click Excel Options -> Click Security-> Select Disable All macros except digitally signed macros
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 ✔
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
19. In Microsoft Excel 2016, Consolidate Option is available under which of the following tabs?
Data ✔
View
Formula
Insert
20. autotrueWhich of the given page margins are available under the Page Layout tab in Excel 2016?
Normal
Wide
Narrow
All 1, 2, and 3 ✔
Only1 and 2
Only1 and 3
Only 2 and 3
21. In the given diagram of a Pivot chart in Microsoft Excel 2016, the “brush” symbol indicates which of the following options?
Chart Color
Chart Elements
Format Painter
Chart Style ✔
22. Which of the following types of filters can be created using the AutoFilter option in Microsoft Excel 2016?
i) By list values.
ii) By format.
iii) By criteria.
Only option ii).
Only options ii) and iii).
Only options i) and iii).
All options i), ii) and iii). ✔
23. In Microsoft Excel 2016, Consolidate Option is available under which of the following tabs?
Data ✔
View
Formula
Insert
24. In Microsoft Excel 2016. which of the following is the correct syntax of the VLOOKUP command?
=VLOOKUP (value. table. coi_lndex. [range_lookup})
=VLOOKUP (col_ index. [range_lookup})
:VLOOKUP (table. Irange_lookup]),value, col_index)
=VLKUP (value, col_index. table. [range_lookup]i ✔
25. In Excel 2016, the Lookup & Reference Option is available under which Of the following tabs?
Insert
Page Layout
Formulas ✔
Review
26. in relation to the Backstage view in Microsoft Excel 2016, which of the given commands! options are available under the Info tab?
1. Protect Document
2. Manage Workbook
3. Browser View Options
All 1, 2, and 3✔
Only1 and 2
Only1 and 3
Only 2 and 3
27. Which of the following statements is correct about the #Data constant in Microsoft Excel 2016?
It is the set of all the cells that contains data, which includes any header rows but excludes the total or subtotal rows.
It is the set of all the cells that contain data, which includes any total or subtotal rows but excludes the header rows.
It is the set of all the cells that contain data including header rows as well as any total or subtotal rows.
It is the set of all the cells that contain data excluding header rows as well as any total or subtotal rows. ✔
28. Consider the Excel sheet given in the image. If you wanted to find out the average of all the cells that meet multiple criteria which function would you use?
AVERAGEIFS ✔
AVERAGEA
AVERAGEIF
AVERAGE
29. Which of the following options is the correct keyboard shortcut for accessing the backstage view in Microsoft Excel 2016?
Ctrl + B
Alt + F ✔
Alt + B
Tab + F
30. In Microsoft Excel 2016, the commands used for recording, creating and deleting macros is accessed from the Developer tab. Which of the following is the correct way of displaying the developer tab on the ribbon?
Click on the File tab -> Click Options-> In the Excel Options dialog box, click option Customize Ribbon -> Check the Developer tab option -> Click OK ✔
Click on the File tab -> Click Accounts-> In the Excel Accounts dialog box, click option Quick Access Toolbar -> Check the Developer tab option -> Click OK
Click on the View tab -> Click Options -> In the Excel Options dialog box, click option Quick Access Toolbar -> Check the Developer tab option -> Click OK
Click on the View tab -> Click Accounts-> Click Options -> In the Excel Options dialog box, click option Customize Ribbon -> Check the Developer tab option -> Click OK
31. While editing a cell’s content, if you change your mind about the changes you made before pressing the enter key, which of the following keys can be pressed to restore to the original text?
F4
Tab
Esc ✔
Alt
32. What happens when the Freeze Panes option is used on some of the rows and columns in Microsoft Excel 2016?
It keeps the rows and columns visible when the rest of the worksheet is scrolled. ✔
The selected rows and columns cannot be edited while the rest of the worksheet can be edited.
The selected rows and columns can accept only a particular type of data.
32. In Microsoft Excel 2016, which of the following keyboard shortcuts should be pressed to switch between relative and absolute cell references while writing a formula in a worksheet?
F1
Ctrl+6
F4 ✔
Ctrl+2
33. lf Sam wants to insert a pivot chart in his Excel worksheet, then which Of the following tabs should he use tO perform the same?
Developer
View
Review
Insert ✔
34. In Microsoft Excel 2016, if Sam wants to change the Office theme or background. then which of the following options will he use?
Export ✔
Account
Options
Info
35. Which of the following options can be used for checking the protection status of a file in Microsoft Excel 2016?
Click on theReviewtab > SelectNew> Click onProtect Workbook
Click on theFiletab > SelectOptions> Click onProtect Workbook
Click on theFiletab > SelectInfo> Click onProtect Workbook ✔
Click on theReviewtab > SelectAccount> Click onProtect Workbook
36. What will be the correct output if we apply the given formula in the B18 cell (as shown in the image)? = COUNT(B3:B17)
8 ✔
6
5
15
37. 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.
Only option i) is correct.
Only options i) and ii) are correct. ✔
Only options ii) and iii) are correct.
All options i), ii) and iii) are correct.
38. What does the blue triangle and border color imply in cell B4?
They imply that a comment is associated to cell B4.
They imply that a filter has been applied to cell B4.
They imply that the Goal Seek option of What-if Analysis is applied to cell B4.
They imply that Track Changes has been enabled and cell B4 is edited. ✔
