Excel Interview Questions and Answers

What is VLOOKUP and how is it used?

VLOOKUP (Vertical Lookup) is a function used to search for a value in the first column of a range

and return a value in the same row from another column.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example: =VLOOKUP(A2, B1:D10, 2, FALSE)

What is the difference between COUNT and COUNTA?

COUNT counts only the cells that contain numbers.

COUNTA counts all non-empty cells, including those with numbers, text, or dates.

What is a Pivot Table and how do you create one?

A Pivot Table is used to summarize and analyze data in Excel. To create one:

1. Select the data range.

2. Go to the Insert tab.

3. Click PivotTable.

4. Choose the location and click OK.

5. Drag and drop fields to Rows, Columns, Values, and Filters.

How do you apply conditional formatting in Excel?

To apply Conditional Formatting:

1. Select the range of cells.

2. Go to the Home tab.

3. Click on Conditional Formatting.

4. Choose a rule and specify the condition.

5. Choose the formatting and click OK.

What are Absolute, Relative, and Mixed references in Excel?

Absolute Reference (e.g., $A$1): Does not change when copied.

Mixed Reference (e.g., $A1 or A$1): One part is fixed, the other is relative.

How do you remove duplicates in Excel?

To remove duplicates:

1. Select the data range.

2. Go to the Data tab.

3. Click Remove Duplicates.

4. Choose the columns and click OK.

What is the IF function and how is it used?

The IF function checks whether a condition is true or false and returns one value for true and

another for false.

Syntax: =IF(logical_test, value_if_true, value_if_false)

Example: =IF(A2 > 50, "Pass", "Fail")

What is CONCATENATE (or CONCAT) used for in Excel?

The CONCATENATE function (or CONCAT in newer versions) is used to combine two or more text

strings into one.

Syntax: =CONCATENATE(text1, text2, ...)

Example: =CONCATENATE(A1, " ", B1)

How do you protect a worksheet or workbook in Excel?

To protect a worksheet:

1. Go to the Review tab.

2. Click Protect Sheet.

3. Enter a password (optional) and select actions users can perform.

To protect a workbook:
1. Go to the File tab.

2. Click Protect Workbook.

What is the difference between LEFT, RIGHT, and MID functions?

LEFT: Extracts characters from the beginning of a text string.

RIGHT: Extracts characters from the end of a text string.

MID: Extracts characters from the middle of a text string.

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

COUNT: Counts only the cells that have numbers in them.

COUNTA: Counts all cells that have any content (numbers, text, or dates).

COUNTIF: Counts cells that meet a specific condition (like counting cells where the number is

greater than 50).

How do you sort data in Excel?

To sort data: Select the range, go to the 'Data' tab, and click on 'Sort.' Choose the column to sort by

and select the order (ascending or descending).

What is the purpose of PivotTables?

PivotTables are used to summarize and analyze large datasets. They help organize data and create

reports, like grouping sales data by region or product.

How do you remove duplicate values in Excel?

To remove duplicates: Select the data range, go to the 'Data' tab, and click on 'Remove Duplicates.'

Choose the columns and click 'OK.'

What is data validation, and how is it used?

Data validation restricts the type of data entered in a cell. For example, you can restrict input to a

specific range of numbers or create dropdown lists for consistent data entry.
How do you link data from one worksheet to another?

To link data from one worksheet to another: Use the formula =SheetName!CellReference. For

example, =Sheet2!A1 links to cell A1 in Sheet2.

What is a named range in Excel?

A named range is a user-defined name for a cell or range of cells. It makes formulas easier to

understand and manage. To create a named range, select the range and define the name in the

Name Box.

What is the difference between a chart and a graph?

A chart is a graphical representation of data in Excel, while a graph is a visual representation of

data. Both are used to display data but charts are often more formal and include more features.

How do you create a drop-down list in Excel?

To create a drop-down list: Select the cell or range, go to the 'Data' tab, click 'Data Validation,' and

choose 'List' from the options. Then, enter the items for the list separated by commas or reference a

range.

What are macros in Excel?

Macros are automated sequences of actions that can be recorded and executed in Excel to perform

repetitive tasks. You can create a macro using VBA (Visual Basic for Applications).

What is the IFERROR function?

The IFERROR function returns a specified value if a formula generates an error; otherwise, it returns

the result of the formula. Example: =IFERROR(A2/B2, "Error in calculation")

How do you find and replace text in Excel?

To find and replace text: Press 'Ctrl + F' to open the Find dialog. Enter the text you want to find, click

'Replace,' and enter the replacement text. You can also select 'Replace All' to replace all

occurrences.
How do you convert text to columns in Excel?

To convert text to columns: Select the column, go to the 'Data' tab, and click on 'Text to Columns.'

Choose the delimiter (like a comma or space) that separates the data and follow the steps.

What is a cell reference in Excel?

A cell reference is the address of a cell or a range of cells, such as A1 or B2:B10. It is used in

formulas to point to data in other cells.

How do you insert a comment in Excel?

To insert a comment: Right-click on a cell, choose 'Insert Comment,' and type the comment.

Comments help you add notes or explanations to a cell.

What is the SUMIF function in Excel?

The SUMIF function adds up the values in a range based on a specific condition. Example:

=SUMIF(A1:A10, ">50") adds up the values in A1:A10 that are greater than 50.

What is conditional formatting in Excel?

Conditional formatting allows you to apply different formatting (like colors or font changes) based on

specific conditions. Example: You can highlight cells greater than 100 with a specific color.

How do you merge cells in Excel?

To merge cells: Select the cells you want to merge, go to the 'Home' tab, and click on the 'Merge &

Center' button. You can choose to merge cells horizontally, vertically, or both.

What is the use of the CONCATENATE function?

The CONCATENATE function is used to combine two or more text strings into one. Example:

=CONCATENATE(A1, " ", B1) combines the text in A1 and B1 with a space in between.

How do you change the page orientation in Excel?

To change the page orientation: Go to the 'Page Layout' tab, click 'Orientation,' and choose either
'Portrait' or 'Landscape.' This changes the way the document is printed.