Sample Questions and Answers
Which Excel function is best used to summarize and analyze large datasets by creating dynamic summary reports?
A) SUMIF
B) VLOOKUP
C) Pivot Table
D) IFERROR
Answer: C
Which chart type is typically used to display the relationship between two variables in a dataset?
A) Line chart
B) Column chart
C) Scatter plot
D) Pie chart
Answer: C
To calculate a moving average over a period of 12 months in Excel, which function is most suitable?
A) SUM
B) AVERAGE
C) AVERAGEIF
D) AVERAGEIFS
Answer: B
Which feature in Excel allows you to automatically fill in a series of numbers or dates based on the pattern you define?
A) Data Validation
B) Flash Fill
C) Fill Handle
D) AutoSum
Answer: C
Which of the following is NOT an option in Excel’s “Conditional Formatting” feature?
A) Highlight Cell Rules
B) Data Bars
C) Sparklines
D) PivotTable Styles
Answer: D
In Excel, which function would you use to combine data from two or more cells into one?
A) CONCATENATE
B) CONCAT
C) JOIN
D) Both A and B
Answer: D
Which function can help to find the intersection of two datasets in Excel?
A) VLOOKUP
B) INDEX-MATCH
C) XLOOKUP
D) SUMIF
Answer: B
In a Pivot Table, which option allows you to show data in both a tabular form and a graphical form simultaneously?
A) Slicers
B) Pivot Charts
C) Data Model
D) Power Query
Answer: B
How do you prevent a value from changing when copying a formula in Excel?
A) Use absolute cell references (e.g., $A$1)
B) Use relative cell references (e.g., A1)
C) Use mixed references (e.g., A$1 or $A1)
D) Use the F4 key for fixed values
Answer: A
Which of the following is true about Excel’s “VLOOKUP” function?
A) It can only search for exact matches.
B) It works by searching for a value in the first column of a table.
C) It can only return values to the left of the search key.
D) It is only available in Excel 2016 and newer.
Answer: B
What Excel tool would you use to split data in one column into multiple columns based on a delimiter (e.g., commas, spaces)?
A) Text to Columns
B) Flash Fill
C) Power Query
D) CONCATENATE
Answer: A
Which Excel chart type is best for comparing the percentage contributions of different categories to the total?
A) Bar chart
B) Pie chart
C) Line chart
D) Histogram
Answer: B
Which function is best used to calculate the number of cells that meet a specific criterion?
A) COUNT
B) COUNTIF
C) COUNTBLANK
D) COUNTA
Answer: B
Which of these would be used to check the consistency of formulas across a range of cells?
A) Formula Auditing Tools
B) Goal Seek
C) Data Validation
D) Error Checking
Answer: A
In a Pivot Table, which option is used to filter data by specific values?
A) Slicers
B) Report Filters
C) Row Labels
D) Value Field Settings
Answer: B
What is the purpose of using “Power Pivot” in Excel?
A) Create Pivot Tables
B) Perform advanced data modeling
C) Create Pivot Charts
D) Automatically clean data
Answer: B
Which of the following can be used to quickly sort data in Excel?
A) Filter
B) Sort dialog box
C) Data Validation
D) All of the above
Answer: D
To create a dynamic chart that changes based on user input, which Excel feature is most useful?
A) Data Validation
B) Dynamic Named Ranges
C) Chart Templates
D) Power BI
Answer: B
Which function would you use to return a value when a condition is met and another value if it’s not met?
A) IF
B) AND
C) OR
D) IFS
Answer: A
Which of the following functions is used to calculate the internal rate of return (IRR) in Excel?
A) IRR
B) RATE
C) NPV
D) PMT
Answer: A
In Excel, what is the primary benefit of using “Slicers” with Pivot Tables?
A) To sort data in a Pivot Table
B) To create dynamic charts
C) To filter data easily in a visual format
D) To format data cells
Answer: C
What is the purpose of the “Flash Fill” feature in Excel?
A) Automatically completes data entries based on patterns
B) Highlights data that is incomplete
C) Creates a histogram automatically
D) Splits data into multiple columns
Answer: A
Which function would you use to find the highest value in a range of cells?
A) MIN
B) MAX
C) AVERAGE
D) COUNT
Answer: B
Which chart type in Excel is useful for showing trends over time, such as stock price movement?
A) Area chart
B) Line chart
C) Column chart
D) Scatter plot
Answer: B
What is the most efficient way to analyze complex datasets with multiple dimensions in Excel?
A) Use PivotTables
B) Use the SUM function
C) Use Excel Tables
D) Use Conditional Formatting
Answer: A
Which function allows you to extract a specific part of a text string in Excel?
A) LEFT
B) MID
C) RIGHT
D) All of the above
Answer: D
How do you remove duplicates from a dataset in Excel?
A) Use the Remove Duplicates tool
B) Use Conditional Formatting
C) Use the Advanced Filter
D) Use the COUNTIF function
Answer: A
Which of these is a feature of “Power Query” in Excel?
A) Combining multiple data sources
B) Creating Pivot Tables
C) Cleaning and transforming data
D) Automating repetitive tasks
Answer: C
Which function would you use to look up a value in a table based on a row and column reference?
A) VLOOKUP
B) HLOOKUP
C) INDEX-MATCH
D) LOOKUP
Answer: C
To perform a “what-if” analysis based on different scenarios in Excel, which feature would be most appropriate?
A) Solver
B) Goal Seek
C) Scenario Manager
D) Power Query
Answer: C
Which Excel function is used to return a value based on multiple conditions in different ranges?
A) SUMIFS
B) COUNTIFS
C) IF
D) INDEX
Answer: A
What is the primary purpose of using “Data Validation” in Excel?
A) To automatically format cells
B) To control the type of data entered into a cell
C) To generate random numbers
D) To sort data automatically
Answer: B
Which chart type is ideal for showing a part-to-whole relationship?
A) Line chart
B) Pie chart
C) Scatter plot
D) Bar chart
Answer: B
Which Excel feature allows you to highlight trends or patterns within your data without creating a graph?
A) Conditional Formatting
B) Data Validation
C) Flash Fill
D) Goal Seek
Answer: A
In Excel, which function would you use to calculate the cumulative sum of a range of values?
A) SUMIF
B) CUMULATIVE
C) SUM
D) SUMPRODUCT
Answer: C
Reviews
There are no reviews yet.