Top 10 Excel interview questions with answers:

1. What are the different types of cell references in Excel?

Solution:

1. Relative Reference: Changes when copied (e.g., A1).

2. Absolute Reference: Remains constant when copied (e.g., $A$1).

3. Mixed Reference: Partly absolute and partly relative (e.g., $A1 or A$1).


2. How do you remove duplicates in Excel?

Solution:

1. Select the data range.

2. Go to Data > Remove Duplicates.

3. Choose the columns to check for duplicates and click OK.


3. What is the difference between COUNT, COUNTA, and COUNTIF?

Solution:

COUNT: Counts numeric values.

COUNTA: Counts all non-empty cells (numbers, text, etc.).

COUNTIF: Counts cells based on a condition.

Example:
=COUNT(A1:A10) // Count numbers
=COUNTA(A1:A10) // Count all non-empty cells
=COUNTIF(A1:A10, ">50") // Count numbers > 50


4. What are pivot tables, and why are they used?

Solution:
Pivot tables summarize and analyze large datasets, allowing dynamic filtering and aggregation (e.g., sum, average, count) without altering the original data.


5. How do you protect a worksheet in Excel?

Solution:

1. Go to Review > Protect Sheet.

2. Set a password and select allowed actions (e.g., selecting cells).

3. Click OK to apply.


6. What is the difference between VLOOKUP and HLOOKUP?

Solution:

VLOOKUP: Searches for a value vertically in the leftmost column.

HLOOKUP: Searches for a value horizontally in the topmost row.
Example:


=VLOOKUP(101, A2:D10, 2, FALSE) // Find data for 101 vertically
=HLOOKUP("Jan", A1:Z2, 2, FALSE) // Find data for "Jan" horizontally.


7. What is conditional formatting in Excel?

Solution:
Conditional formatting highlights cells based on rules.
Steps:

1. Select cells.


2. Go to Home > Conditional Formatting.


3. Choose a rule (e.g., values greater than 50) and apply formatting.



8. How do you find duplicates using a formula in Excel?

Solution:
Use the COUNTIF function:

=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")


9. How do you use the IF function?

Solution:
The IF function performs logical tests and returns a value based on the result.

=IF(A1 > 50, "Pass", "Fail") // Returns "Pass" if A1 > 50, else "Fail"


10. What is the purpose of the TEXT function?

Solution:
The TEXT function formats numbers and dates into specified text formats.
Example:

=TEXT(A1, "DD-MMM-YYYY") // Converts date to "22-Nov-2024"
=TEXT(1234.56, "$#,##0.00") // Formats number as "$1,234.56"

Like for more ❤️

Share with credits: https://t.me/sqlspecialist

Hope this helps you 😊