Microsoft Excel Basic and Advance 4 — Questions and Answers
Question 1: What is the purpose of the IFERROR function in Excel?
- It prevents formulas from being edited
- It returns a custom value when a formula produces an error (Correct answer)
- It highlights error cells in red
- It deletes rows that contain errors
Correct answer: It returns a custom value when a formula produces an error
IFERROR(value, value_if_error) evaluates an expression and returns a specified result if any error occurs, keeping the spreadsheet clean.
Question 2: Which keyboard shortcut inserts the current date as a static value in a cell?
- Ctrl+Shift+T
- Ctrl+; (Correct answer)
- Ctrl+D
- Ctrl+Shift+:
Correct answer: Ctrl+;
Ctrl+; inserts today's date as a fixed value that does not change when the workbook is recalculated.
Question 3: What does the SUMPRODUCT function do?
- Sums numbers then multiplies by a constant
- Multiplies corresponding elements of arrays and returns the sum of those products (Correct answer)
- Finds the product of a range of numbers
- Sums only positive numbers in a range
Correct answer: Multiplies corresponding elements of arrays and returns the sum of those products
SUMPRODUCT multiplies corresponding values in two or more arrays and then sums all those products in a single step.
Question 4: How do you freeze the top row in Excel so it remains visible while scrolling down?
- Home tab → Freeze Panes → Freeze First Row
- View tab → Freeze Panes → Freeze Top Row (Correct answer)
- Page Layout tab → Freeze Panes → Freeze First Row
- Data tab → Freeze Row
Correct answer: View tab → Freeze Panes → Freeze Top Row
On the View tab, clicking Freeze Panes and then Freeze Top Row locks row 1 in place during vertical scrolling.
Question 5: Which Excel tool applies different formatting to cells automatically based on the values they contain?
- Data Validation
- Conditional Formatting (Correct answer)
- Cell Styles
- Format Painter
Correct answer: Conditional Formatting
Conditional Formatting changes a cell's appearance (color, font, borders) dynamically based on rules tied to the cell's value.
Question 6: What does the INDEX function return when given an array, a row number, and an optional column number?
- The position of a value in the array
- The value at the specified row and column intersection (Correct answer)
- The count of values in the range
- The largest value in the row
Correct answer: The value at the specified row and column intersection
INDEX(array, row_num, [col_num]) returns the value at the intersection of the given row and column within the specified range.
Question 7: Which function finds the position of the first occurrence of a substring within a text string and is case-sensitive?
- SEARCH
- FIND (Correct answer)
- MATCH
- LOCATE
Correct answer: FIND
FIND is case-sensitive and returns the starting position of one text string inside another; SEARCH is the case-insensitive alternative.
Microsoft Excel Basic and Advance 4
What is the purpose of the IFERROR function in Excel?