Advanced Excel Exam Questions and Answers

1.Which of these will you use to separate data into multiple columns instantly?
Fill Handle
Text to Columns
Filtering
Sorting

2.You will get the same result if you use the Fill Handle or the Flash Fill feature.
TRUE
FALSE

3.Which of the following is not a conditional formatting rule category?
Highlight Cells Rules
Left/Right Rules
Data Bars
Icon Sets

4.You have the marks of 50 students in an Excel sheet. You want to highlight the 3 highest scorers. Which conditional formatting rules should you use?
Highlight Cells Rules
Top/Bottom Rules
Data Bars
Color Scales

5.You have the marks of 50 students in an Excel sheet. You want to highlight the 3 highest scorers. Which conditional formatting rules should you use?
Highlight Cells Rules
Top/Bottom Rules
Data Bars
Color Scales

6.You have the expense figures for 12 months. You want coloured bars of different lengths based on the values to be displayed in the cells. Should you use Color Scales conditional formatting for this?
Yes
No

7.When icon sets are used for conditional formatting, small……………..are displayed in the cells.
Charts
Bars
Lines
Graphic

8.Data bars can be created in both left-to-right and right-to-left direction.
TRUE
FALSE

9.You can use a template to
quickly create pivotTables
sort and filter data
create new workbooks with predefined element and formatting
analyze data

10.Excel templates are stored with the extension .xltm
TRUE
FALSE

11.Custom templates cannot be based on Excel’s ready-to-use templates.
TRUE
FALSE

12.Custom templates must include custom cell styles.
TRUE
FALSE

13. You can add—————to your worksheets by using Form Controls
formulas
filters
interactivity
conditional formatting

14.You cannot insert form controls if the Developer tab is not displayed.
TRUE
FALSE

15.A Spin Button control is used to run macros.
TRUE
FALSE

16.Command buttons generally have macros attached.
TRUE
FALSE

17. A————–control is used to increase or decrease a value within a defined range.
command Button
List Box
Group Box
Sapin Button

18.A Group Box organizes a set of controls into—————–
a sorted list
a group of charts
a single element
a set a formulas

19.If you want to add some descriptive text below an element, you need to use this control.
Label
Caption
Title
Description

20.This group in the Developer tab is used to insert Form Controls.
Insert
Forms
Controls
Styles

21.You have the French dictionary installed on your computer. If you want to proof in French, you need not change the Display Language to French.
TRUE
FALSE

22.To check spelling in another language, select the language in the——–
Help language box on the language page of the excel options window
ScreenTip Language box on the language page of the excel options window
Display Language box on the language page of the excel options window
Dictionary Language box on the language page of the excel options window

23.You can group columns only. Rows cannot be grouped.
TRUE
FALSE

24.If a group is hidden, it can be redisplayed at any time.
TRUE
FALSE

25.The SUM function allows you to automatically create groups.
TRUE
FALSE

26.A/An———-is a hierarchy of groups that allow you to organize your worksheet.
scenario
Custom view
Outline
Connection

27.You can create a Custom View to save the print settings of a worksheet.
TRUE
FALSE

28.If a specific row is hidden in a custom view, you can unhide it without editing the view.
TRUE
FALSE

29.A worksheet can have——-custom views.
unlimited
maximum 5

30.Your worksheet has a table and you want to create a custom view. Is this possible?
Yes
No

31.In order to work correctly, a function must be written according to certain rules called
Groups
Guidelines
Syntax
Methods

32.A function argument can refer to a single cell only.
TRUE
FALSE

33.Which of these functions are listed under the Logical category?
AND
IF
SUM
OR

34.The AutoSum button can be used to serve the same purpose as the SUM function.
TRUE
FALSE

35.The MODE function returns—————
the most frequently accuring value in a range of numeric data.
the mode in which the worksheet is opened
the most frequently accuring value in a range of alphabetic data.

36.If you apply the AVERAGE function or the MEDIAN function to any range of cells, you will get the same result.
TRUE
FALSE

37.This function calculates the payment for a loan based on constant payments and a constant interest rate.
EMI
NPER
PMT
PV

38. You can open the “Function Arguments” window by clicking
fx
the formula bar
the name box
the tick mark

39.The MATCH function searches for a specified item in a range of cells and returns the——-of that item in the range.
Position
value

40.In the MATCH function, the match_type argument -1 indicates——–
exact match
greater then
less then

41.The CELL function can be used to find the column and row number of a cell.
TRUE
FALSE

42.The———–function can be used to retrieve information such as contents, formatting and size of a cell.
Format
size
contents
cell

43.The VLOOKUP function is used to insert values in vertical columns.
TRUE
FALSE

44.If the ISERROR function is applied to a cell containing an error it will return
TRUE
FALSE

45.This function returns the current date along with the current time.
TODAY()
NOW()

46.A cell contains the NOW() function. Is it possible to get the Year, Month and Date based on this.
Yes
No

47.This function allows you to extract a substring from a string, starting from the left-most character.
LEN
LOWER
FIND
LEFT

48.The formula =RIGHT(A5, 4) will extract the last 5 characters from the cell A5.
TRUE
FALSE

49.The LEN function returns the number of characters in a string——spaces.
inculding
exculding

50.The TRIM function removes——spaces from text.
all
extra 

51.What-If Analysis tools allow you to
apply filters to data
validate data
see how changed values affect the outcomes of formulas
represent data in visual form

52.work/s only with one or two variables, but can accept many different values for those variables.
scenarios
Goal Seek
Data Tables

53.You want to calculate your profit based on your sales and expenses figures. Will Goal Seek help you with this?
Yes
No

54.You have calculated your profit based on your sales and expenses figures. You want to know what your sales figures should be if you want your profit to increase by 10 percent assuming your expenses stay unchanged. Can Goal Seek help you with this?
Yes
No

55.Different Scenarios must have the same rows and columns displayed while different custom views can have different rows and columns displayed.
TRUE
FALSE

56.Scenarios are the same as Custom Views.
TRUE
FALSE

57.A scenario can accommodate up to 2 variable values.
TRUE
FALSE

58.Naming the cells used in building scenarios is
Advisable
Mandatory

59.Can you merge scenarios from workbooks with different layouts?
Yes
No

60.Merging scenarios creates multiple custom views.
TRUE
FALSE

61.You have updated some Scenario data. The Scenario Summary and Scenario PivotTable Reports do not reflect the changes. What should you do?
Revert to the old data to keep the reports relevant
Recreate the summary reports

62.Outlines created in a scenario summary report can be used to update the report according to the latest data in the scenario sheets.
TRUE
FALSE

63.Using—————makes it easy to examine a range of possibilities on a single worksheet.
scenarios
Custom views
Data Tables
Goal Seek

64.You can create Data Tables using maximum 32 variables.
TRUE
FALSE

65.An Area Chart is an enhanced version of a _______ chart.
Line
Bar
Column
Pie

66.In a Line Chart, the X axis displays _____ while in a Scatter Chart, the X axis displays ______.
categories, values
values, categories

67.You cannot create Stock Charts if the data is not organized correctly.
true
false

68.You can use a Chart template to change the chart type of an existing chart.
true
false

69.You can make background areas in an Area chart visible by _______.
making background areas transparent
making foreground areas transparent
making background areas opaque
making foreground areas opaque

70.A scatter chart plots the values for ______ variable/s as a set of points on a graph.
one
two
three
four

71.This is NOT a type of Scatter Chart.
Scatter with Smooth Lines
Scatter with Plain Lines
Scatter with Straight Lines
Scatter with Straight Lines and Markers

72.Wireframe Contour is a type of _______ chart.
Scatter
Surface
Bubble
Stock

73.Stock Charts can be used to plot stock market data only and no other type of data.
true
false

74.A surface chart uses colors to distinguish the _______.
data series
values

75.A Bubble Chart is a variation of a ______ Chart.
Line
Column
Scatter
Pie

76._______ Charts are used to emphasize different types of information in a chart by combining multiple chart types.
Joint
Double
Combination
Couple

77.A surface chart shows a _________surface that connects a set of data points.
two-dimensional
flat
three-dimensional

78.A surface chart is useful when you need to plot ______ dependent variable against _______ independent variables.
one, three
two, three
two, one
one, two

79.A _________ Chart plots the values of each category along a separate axis that starts in the center of the chart and ends on the outer ring.
Scatter
Surface
Bubble
Radar 

80.Can you apply conditional formatting to PivotTables?
Yes
No

81.When there is a change in source data, use this option to update PivotTable data.
PivotTable Tools > Analyze tab > Data group > Refresh
PivotTable Tools > Analyze tab > Data group > Update

82.You can sort the data in a PivotTable like regular spreadsheet data.
true
false

83.Filters applied to a PivotChart ______ the related PivotTable.
affect
do not affect

84.When there are changes in the source worksheet, PivotTables update automatically.
true
false

85.Scenario Summary and Scenario PivotTable Reports update automatically if the Scenario data changes.
true
false

86.If you change the data in the source worksheet, the PivotTable _______ automatically.
updates
does not update

87.You cannot create a PivotChart without creating a PivotTable first.
true
false

88.Slicers can be used to _______ PivotTable data.
sort
summarize
filter
enhance

89.When you conditionally format a PivotTable, you can apply rules to the _______ of the PivotTable itself so that the formatting accurately responds to changes.
values
source
structure

90.The AutoCorrect feature needs to be turned on to retrieve versions of workbook saved during the current session
TRUE
FALSE

91.To be able to recover a workbook, you need to have saved it at least once.
TRUE
FALSE

92.——-cell styles are saved with the workbook they are created in.
built-in
Custom

93.Is it possible to use a custom cell style created in one workbook in another workbook by
copying the style
linking the workbooks

94.You can copy a macro created in one workbook to another workbook using the Project Explorer Pane. The Ribbon option used to display this is
Home tab > Clipboard group > copy
Developer tab > Code group > Macros
Data tab > Get External Data grouop > Existing connections
Developer tab > Code group > Visual Basic

95.To be able to copy macros between two workbooks, both need to be open.
TRUE
FALSE

96.For the merging of workbooks to be successful, each user must use a differently designed workbook.
TRUE
FALSE

97.To be able to merge copies of a workbook, they should be saved in a different folder from the original workbook.
TRUE
FALSE

98. Password details of a workbook are stored in the metadata.
TRUE
FALSE

99.To be able to remove password protection, you need to know the password.
TRUE
FALSE

100.You want users to be able to edit only certain parts of your worksheet. Is this possible?
Yes
No