Advanced Excel Interview Quiz Quiz

Test your understanding of advanced Microsoft Excel interview topics with this easy-level quiz. Learn about drop-down lists, handling null values, formulas, correlation, and more core Excel concepts to boost your job preparation and Excel skills.

1. Creating a Drop-Down List

   Which feature in Excel allows you to create a cell with a drop-down list of pre-defined values, such as a list from A1:A5?

   1. Flash Fill
   2. Data Validation
   3. Conditional Formatting
   4. Pivot Table

2. Filling Null Values

   If you want to replace all blank cells in a range with the value '0', which Excel function or tool would you commonly use?

   1. Find and Replace
   2. Subtotal
   3. Transpose
   4. Slicer

3. Go To Special for Blanks

   What Excel shortcut opens the 'Go To Special' dialog to help you quickly select all blank cells in a selected range?

   1. Alt + F11
   2. Ctrl + 1
   3. Ctrl + T
   4. Ctrl + G

4. Using Previous Value for Blanks

   To fill blank cells in a column with the value above them, which method involves selecting blanks and pressing Ctrl + Enter after entering the formula?

   1. Fill Down Series
   2. Transpose
   3. Flash Fill
   4. Fill with Previous

5. Interpolation for Missing Data

   Which Excel function can you use to handle missing data by returning the average of a range or 'No Data' if there is an error?

   1. COUNTIFS
   2. SUMIF
   3. IFNA
   4. IFERROR

6. Correlation vs. Covariance

   Which function in Excel would you use to measure how two variables are linearly related, ranging from -1 to 1?

   1. COVARIANCE.P
   2. MODE
   3. COUNT
   4. CORREL

7. Covariance Calculation

   If you want to find whether two variables increase or decrease together using all data points in Excel, which function should you use?

   1. COVARIANCE.P
   2. IFAND
   3. CORREL
   4. AVERAGEIF

8. Data Validation Source Reference

   When setting up a drop-down list with Data Validation, which of the following is a correct source reference for items in cells A1 to A5?

   1. =A1-A5
   2. =(A1:A5)
   3. Sum(A1:A5)
   4. =$A$1:$A$5

9. Replacing All Blank Cells

   In the Find and Replace dialog, what should you leave in the 'Find what' field to target all blank cells for replacement?

   1. Enter a space
   2. Leave it empty
   3. Use * (asterisk)
   4. Type BLANK

10. Resolving Errors with IFERROR

    What does the IFERROR function do in the context of calculations with potential errors like division by zero?

    1. Deletes error cells
    2. Ignores all errors
    3. Returns a custom value if there is an error
    4. Stops calculation completely