Microsoft Excel Basic and Advance 5 — Questions and Answers
Question 1: What does the MATCH function return?
- The value of the matched cell
- The relative position of a value within a range (Correct answer)
- TRUE or FALSE depending on whether a value exists
- The address of the matched cell
Correct answer: The relative position of a value within a range
MATCH returns the relative position (1-based number) of a lookup value within a one-dimensional range.
Question 2: Which Excel data tool removes duplicate rows from a selected range or table?
- Filter
- Remove Duplicates (Correct answer)
- Text to Columns
- Consolidate
Correct answer: Remove Duplicates
The Remove Duplicates command on the Data tab deletes rows where every selected column's value is identical to another row.
Question 3: What is the function of Data Validation in Excel?
- It checks formulas for syntax errors
- It restricts the type or range of data a user can enter in a cell (Correct answer)
- It encrypts cell contents
- It auto-formats numbers as you type
Correct answer: It restricts the type or range of data a user can enter in a cell
Data Validation sets rules (whole numbers, dates, list choices, etc.) that limit what values can be entered into a cell.
Question 4: In Excel, what is a named range?
- A range that has been formatted with a border
- A descriptive name assigned to a cell or range for use in formulas (Correct answer)
- A range that spans multiple worksheets
- A range locked with a password
Correct answer: A descriptive name assigned to a cell or range for use in formulas
A named range assigns a meaningful label to a cell or range so formulas can reference it by name instead of cell address.
Question 5: Which function calculates the payment for a loan based on constant payments and a constant interest rate?
- FV
- PV
- NPV
- PMT (Correct answer)
Correct answer: PMT
PMT(rate, nper, pv) calculates the fixed periodic payment required to pay off a loan over a set number of periods at a given interest rate.
Question 6: What happens to a PivotTable when the underlying source data changes?
- It updates automatically in real time
- It must be manually refreshed to reflect the new data (Correct answer)
- It deletes and must be recreated
- It displays a #REF! error
Correct answer: It must be manually refreshed to reflect the new data
PivotTables do not update automatically; you must right-click and choose Refresh (or use Data → Refresh All) to pull in changed source data.
Question 7: Which Excel feature records a sequence of actions so they can be replayed automatically on demand?
- Conditional Formatting
- Macro (Correct answer)
- Quick Analysis
- Goal Seek
Correct answer: Macro
A macro records a series of Excel commands and keystrokes that can be run again with a single click or keyboard shortcut, automating repetitive tasks.
Microsoft Excel Basic and Advance 5
What does the MATCH function return?