MS Excel: Functions, Errors and Features MCQs

Solved MS Excel MCQs on functions (VLOOKUP, SUMIF, COUNTIF, TODAY, LEN), error values like #DIV/0!, shortcuts, Freeze Panes, Pivot Tables and formatting.

1. 1

   Which Excel function searches for a value in the first column of a table and returns a value from the same row?

   AHLOOKUPBMATCHCVLOOKUPDINDEX

   Correct answer: C. VLOOKUP

   VLOOKUP (vertical lookup) searches down the first column. HLOOKUP searches across the first row instead.

   mediumShow answer
2. 2

   Which Excel function adds only the cells that meet a given condition?

   ASUMBSUMIFCCOUNTIFDIF

   Correct answer: B. SUMIF

   For example, =SUMIF(A2:A10,"Lahore",B2:B10) adds the values in B where column A says Lahore.

   mediumShow answer
3. 3

   Which Excel function counts the cells that meet a given condition?

   ACOUNTIFBSUMIFCCOUNTDCOUNTA

   Correct answer: A. COUNTIF

   For example, =COUNTIF(C2:C50,"Pass") counts how many cells in the range contain "Pass".

   mediumShow answer
4. 4

   The Excel function =TODAY() returns:

   AThe number of days in the monthBThe day of the week as textCThe current dateDThe current date and time

   Correct answer: C. The current date

   TODAY() returns the current date. NOW() returns the current date and time. Both update when the sheet recalculates.

   easyShow answer
5. 5

   The Excel function =NOW() returns:

   AThe time since the file was openedBOnly the current yearCThe current date and timeDOnly the current time

   Correct answer: C. The current date and time

   NOW() returns both the date and the time. Use TODAY() if you need only the date.

   easyShow answer
6. 6

   The Excel function =LEN(A1) returns:

   AThe number of words in A1BThe length of column ACThe width of the cellDThe number of characters in A1

   Correct answer: D. The number of characters in A1

   LEN counts every character, including spaces. For example, =LEN("Pakistan") returns 8.

   mediumShow answer
7. 7

   Which Excel function removes extra spaces from text, leaving single spaces between words?

   ASUBSTITUTEBLEFTCCLEANDTRIM

   Correct answer: D. TRIM

   TRIM removes leading and trailing spaces and reduces repeated spaces between words to one.

   mediumShow answer
8. 8

   Which Excel function converts text to capital letters?

   AUPPERBLOWERCPROPERDCAPS

   Correct answer: A. UPPER

   UPPER converts text to all capitals, LOWER to small letters, and PROPER capitalizes the first letter of each word.

   easyShow answer
9. 9

   Which Excel function joins text from several cells into one?

   AJOINBCOMBINECMERGEDCONCAT (or CONCATENATE)

   Correct answer: D. CONCAT (or CONCATENATE)

   CONCAT (and the older CONCATENATE) join text values. The & operator does the same, as in =A1&" "&B1.

   mediumShow answer
10. 10

    The Excel formula =ROUND(3.456, 1) returns:

    A3.46B3.5C3.4D3

    Correct answer: B. 3.5

    ROUND rounds to the given number of decimal places. 3.456 rounded to 1 decimal place is 3.5.

    mediumShow answer
12. 11

    A cell in Excel shows "#####". This usually means:

    AThe file is corruptedBThe cell is lockedCThe column is too narrow to display the valueDThe formula is wrong

    Correct answer: C. The column is too narrow to display the value

    Widening the column normally reveals the number or date. It is a display issue, not an error in the value.

    easyShow answer
13. 12

    The Excel error #DIV/0! appears when a formula:

    ADivides by zero or an empty cellBRefers to a deleted cellCMixes text and numbersDUses a misspelled function name

    Correct answer: A. Divides by zero or an empty cell

    For example, =A1/B1 shows #DIV/0! if B1 is 0 or empty.

    easyShow answer
14. 13

    The Excel error #NAME? usually means:

    ADivision by zeroBA circular referenceCThe column is too narrowDExcel does not recognize a function or name in the formula

    Correct answer: D. Excel does not recognize a function or name in the formula

    A typo such as =SUMM(A1:A5) produces #NAME? because SUMM is not a valid function.

    mediumShow answer
15. 14

    The Excel error #REF! indicates:

    AA value that is too largeBA wrong data typeCA missing file extensionDAn invalid cell reference, often after deleting cells used in a formula

    Correct answer: D. An invalid cell reference, often after deleting cells used in a formula

    If you delete a row or column that a formula depends on, the reference breaks and Excel shows #REF!.

    mediumShow answer
16. 15

    The Excel error #VALUE! usually appears when:

    AA cell is emptyBThe sheet is protectedCThe column is too narrowDA formula uses the wrong type of data, such as adding text to a number

    Correct answer: D. A formula uses the wrong type of data, such as adding text to a number

    For example, =A1+B1 gives #VALUE! if A1 contains text that cannot be treated as a number.

    mediumShow answer
17. 16

    Which feature keeps the top row or first column visible while you scroll through a large sheet?

    ASplit CellsBHide RowsCFreeze PanesDWrap Text

    Correct answer: C. Freeze Panes

    Freeze Panes, on the View tab, locks rows or columns in place so headings stay visible.

    easyShow answer
18. 17

    Conditional Formatting in Excel is used to:

    AChange cell formatting automatically based on the cell's valueBProtect a worksheetCSort data alphabeticallyDCreate charts

    Correct answer: A. Change cell formatting automatically based on the cell's value

    For example, marks below 33 can be shown in red automatically. It is on the Home tab.

    easyShow answer
19. 18

    A Pivot Table in Excel is mainly used to:

    AInsert picturesBDraw shapesCSummarize and analyze large amounts of dataDCheck spelling

    Correct answer: C. Summarize and analyze large amounts of data

    Pivot Tables quickly group, total and compare data, for example sales by city and month. They are on the Insert tab.

    mediumShow answer
20. 19

    Data Validation in Excel is used to:

    ACalculate averagesBRestrict what can be entered in a cellCMerge worksheetsDCheck spelling

    Correct answer: B. Restrict what can be entered in a cell

    For example, it can allow only dates, numbers within a range, or choices from a drop-down list. It is on the Data tab.

    mediumShow answer
21. 20

    Which type of chart is best for showing parts of a whole as percentages?

    ABar chartBPie chartCScatter chartDLine chart

    Correct answer: B. Pie chart

    A pie chart shows how a total is divided into parts. Line charts are best for trends over time.

    easyShow answer