MCQs: Unit I – Data Analysis Using MS Excel B) Text to Columns ✅
Section A: Introduction to Spreadsheets C) Filter
1. What is the default file extension of a D) Flash Fill
workbook in Excel 2016 and later? 8. Which file format is commonly used for
A) .xls data import/export in Excel?
B) .xlsx ✅ A) .exe
C) .docx B) .pdf
D) .csv C) .csv ✅
2. Which of the following refers to an D) .png
absolute cell reference in Excel? 9. Which function is used to remove
A) A1 duplicate records in Excel?
B) $A1 A) Data Sort
C) A$1 B) Text to Column
D) $A$1 ✅ C) Remove Duplicates ✅
3. Which type of cell referencing allows row D) Filter
to change but keeps the column fixed? 10. What happens when you filter data in
A) Relative Excel?
B) Absolute A) Data gets deleted
C) Mixed ✅ B) Hidden rows are shown
D) Dynamic C) Only matching rows are shown ✅
4. In Excel, labels and numbers are D) Columns are sorted
examples of: Section C: Logical Functions
A) Graphics 11. Which function is used to perform a
B) Data types ✅ logical test in Excel?
C) Tables A) AND
D) Templates B) IF ✅
5. The intersection of a row and a column is C) OR
called: D) SUM
A) Field 12. What does the AND function return if all
B) Cell ✅ conditions are TRUE?
C) Box A) TRUE ✅
D) Table B) FALSE
Section B: Data Entry and Editing Techniques C) 1
6. Which option is used to bring data from D) Error
another file into Excel? 13. Which of these is a correct syntax for a
A) Export nested IF?
B) Save As A) =IF(A1>50, "Pass", "Fail", "Retry")
C) Import ✅ B) =IF(A1>50, IF(A1>80, "A", "B"),
D) Cut "C") ✅
7. Which Excel feature splits a column of C) =NESTEDIF(A1, B1)
data into multiple columns based on a D) =IF.AND(A1>50, B1<100)
delimiter? 14. Which function returns the opposite of a
A) Data Validation logical value?
A) AND A) Adds formulas
B) NOT ✅ B) Automatically colors cells based on
C) OR conditions ✅
D) TRUE C) Locks cells
15. Which formula returns TRUE only if one D) Protects worksheets
or more conditions are TRUE? 22. To highlight all cells with values greater
A) AND than 100, you would use:
B) OR ✅ A) Cell Formatting
C) NOT B) Conditional Formatting ✅
D) IFS C) Table Filter
16. The result of =IF(5>3, "Yes", "No") is: D) Goal Seek
A) No 23. Which tab contains Data Validation in
B) Yes ✅ Excel?
C) Error A) Insert
D) TRUE B) Page Layout
17. Which of the following is NOT a logical C) Data ✅
function in Excel? D) Review
A) IF 24. Which feature is best suited to visually
B) OR identify top 10 values in a list?
C) NOT A) Filter
D) SUM ✅ B) Sort
18. What will be the result of: =IF(AND(2>1, C) Conditional Formatting ✅
3<5), "OK", "Fail")? D) Data Validation
A) OK ✅ 25. Which of the following is NOT an option
B) Fail in Conditional Formatting?
C) Error A) Color Scales
D) TRUE B) Data Bars
Section D: Data Validation & Conditional C) Icon Sets
Formatting D) Spell Check ✅
19. Which feature lets you restrict what users 26. To restrict users to enter only numbers
can enter in a cell? between 1 and 100, use:
A) Filter A) Sort
B) Data Validation ✅ B) Data Validation ✅
C) Pivot Table C) Format Cells
D) Goal Seek D) Macro
20. Which Excel feature is used to create a Section E: Conceptual & Outcome Based
drop-down list in a cell? 27. Why is cell referencing important in
A) Filter Excel formulas?
B) Conditional Formatting A) To make charts
C) Data Validation ✅ B) To fix data types
D) Table C) To allow reuse of formulas
21. What does Conditional Formatting do in dynamically ✅
Excel? D) To hide values
28. Which function helps in creating decision- A) =ISBLANK(A1)
making formulas based on criteria? B) =OR(ISBLANK(A1), A1="N/A") ✅
A) SUM C) =IF(A1="", "Blank", "Data")
B) IF ✅ D) =A1=NA()
C) MAX 5. In conditional formatting, which of the
D) AVERAGE following formula highlights even-
29. Which of the following is best for numbered rows?
preparing visual alerts in reports? A) =MOD(ROW(),2)=0 ✅
A) Sorting B) =EVEN(ROW())
B) Conditional Formatting ✅ C) =ISODD(ROW())
C) Import Data D) =INT(ROW())=EVEN
D) Merge Cells 6. What will =IF(AND(A1>0,
30. Using logical functions in Excel helps in: ISNUMBER(A1)), A1*10, "Invalid")
A) Making graphs return if A1 contains the text “ten”?
B) Auto-saving A) 100
C) Automating decisions ✅ B) 10
D) Hiding sheets C) Invalid ✅
✅ Advanced MCQs on Unit I – Data Analysis D) Error
Using MS Excel 7. What is the role of the $ sign in Excel
1. What will the formula =IF(OR(A1>100, formulas?
B1<50), "Review", "OK") return if A) Applies currency formatting
A1=120 and B1=70? B) Prevents formatting
A) Review ✅ C) Locks the cell reference in formulas ✅
B) OK D) Multiplies values
C) TRUE 8. If cell A1 contains a formula =B1+C1,
D) Error and you copy A1 to cell A2, what does the
2. In nested logical functions, what is the formula become?
maximum number of IFs allowed in a
A) B2+C2 ✅
formula in Excel 365?
B) B1+C1
A) 7 C) A2+C2
B) 64 ✅ D) Fixed reference
C) 256 9. Which validation rule prevents duplicate
D) Unlimited entries in a column?
3. You want to color rows in a table where A) Use UNIQUE function
sales exceed ₹10,000 and region is "East". B) Use formula: =COUNTIF(A:A, A1)=1
Which Excel tool would you use?
✅
A) AutoFilter C) Use =ISUNIQUE(A1)
B) Conditional Formatting with a D) Set max length to 1
formula ✅ 10. How can you show errors in red using
C) Data Validation conditional formatting?
D) Flash Fill
A) Use ISERROR() formula ✅
4. What formula checks if a cell is blank or
B) Use Filter > Error
contains the text “N/A”?
C) Format Cells > Number > Error
D) Use AutoCorrect
11. What is the effect of using mixed
reference like $A1 in copying formulas?
A) Both row and column change
B) Column remains fixed, row changes ✅
C) Row remains fixed, column changes
D) It becomes an absolute reference
