Microsoft Excel Basic and Advance — Questions and Answers
Question 1: Which function returns the number of cells in a range that meet a given condition?
- COUNT
- COUNTIF (Correct answer)
- SUMIF
- COUNTA
Correct answer: COUNTIF
COUNTIF counts cells within a range that satisfy a single specified criterion.
Question 2: What does the $ symbol do when placed before a column letter or row number in a formula?
- Formats the cell as currency
- Anchors the reference so it does not change when copied (Correct answer)
- Adds a dollar sign to the displayed value
- Marks the cell as protected
Correct answer: Anchors the reference so it does not change when copied
A $ before a column letter or row number creates an absolute reference that stays fixed when the formula is copied.
Question 3: Which Excel feature automatically suggests a formula pattern based on adjacent data you have already entered?
- AutoCorrect
- Flash Fill
- AutoFill (Correct answer)
- Quick Analysis
Correct answer: AutoFill
AutoFill extends a pattern (numbers, dates, or formulas) by dragging the fill handle across adjacent cells.
Question 4: What is the result of =MOD(17, 5)?
Correct answer: 2
MOD returns the remainder after division; 17 divided by 5 is 3 remainder 2.
Question 5: Which keyboard shortcut applies bold formatting to the selected cells?
- Ctrl+I
- Ctrl+U
- Ctrl+B (Correct answer)
- Ctrl+F
Correct answer: Ctrl+B
Ctrl+B toggles bold formatting on or off for the selected text or cells.
Question 6: When you sort a table by a column in ascending order, how does Excel sort text values?
- Z to A
- A to Z (Correct answer)
- By cell color
- By font size
Correct answer: A to Z
Ascending sort orders text alphabetically from A to Z.
Question 7: Which function would you use to round 4.567 to two decimal places?
- =TRUNC(4.567,2)
- =INT(4.567,2)
- =ROUND(4.567,2) (Correct answer)
- =CEILING(4.567,2)
Correct answer: =ROUND(4.567,2)
ROUND(number, num_digits) rounds a number to the specified number of decimal places using standard rounding rules.
Question 8: What does VLOOKUP's fourth argument (range_lookup) do when set to FALSE?
- Returns an approximate match
- Returns an exact match only (Correct answer)
- Ignores case sensitivity
- Searches from right to left
Correct answer: Returns an exact match only
Setting range_lookup to FALSE forces VLOOKUP to find an exact match and return #N/A if none exists.
Question 9: Which Excel chart type is best suited for showing the proportion of parts to a whole?
- Line chart
- Bar chart
- Pie chart (Correct answer)
- Scatter chart
Correct answer: Pie chart
A pie chart displays each category as a slice of the whole circle, making part-to-whole relationships immediately visible.
Question 10: What does the CONCATENATE function (or & operator) do in Excel?
- Adds numbers together
- Joins two or more text strings into one (Correct answer)
- Counts characters in a cell
- Converts numbers to text
Correct answer: Joins two or more text strings into one
CONCATENATE (or &) combines multiple text values into a single string.
Question 11: Which function extracts a specified number of characters from the left side of a text string?
- MID
- RIGHT
- LEFT (Correct answer)
- FIND
Correct answer: LEFT
LEFT(text, num_chars) returns the first num_chars characters from the beginning of a text string.
Question 12: In a PivotTable, which area determines what values are calculated and displayed in the data cells?
- Rows area
- Columns area
- Filters area
- Values area (Correct answer)
Correct answer: Values area
The Values area contains the fields that are summarized (summed, averaged, counted, etc.) and shown in the body of the PivotTable.
Question 13: What error does Excel display when a formula attempts to divide by zero?
- #VALUE!
- #REF!
- #DIV/0! (Correct answer)
- #NULL!
Correct answer: #DIV/0!
#DIV/0! appears whenever a formula divides a number by zero or by an empty cell.
Question 14: Which function returns the current date and time that updates each time the worksheet recalculates?
- =DATE()
- =TODAY()
- =NOW() (Correct answer)
- =TIME()
Correct answer: =NOW()
NOW() returns the current date and time as a serial number and refreshes every time Excel recalculates.
Question 15: 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 16: 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 17: 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 18: 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 19: 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 20: 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 21: 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.
