If you are a fresher or experienced, planning to prepare for an interview like Data Analyst, Business Analyst, or Data Entry Jobs roles. Then these 100+ excel interview questions for Data Analyst, Business Analyst, or Data Entry Jobs roles with answers can help you to prepare for your next interview.

**What are the different types of data that can be entered into an Excel cell?**

Excel cells can contain text, numbers, dates, and formulas.

**How do you create a basic formula in Excel?**

To create a formula, start with `=`

followed by the expression you want to compute, such as `=A1+B1`

.

**What is the difference between relative and absolute cell references?**

Relative references (e.g., `A1`

) adjust when copied to other cells, while absolute references (e.g., `$A$1`

) remain fixed.

**How do you use the SUM function in Excel?**

The SUM function adds up a range of numbers. For example, `=SUM(A1:A10)`

adds all the numbers from cells A1 to A10.

**What does the VLOOKUP function do? Can you provide a basic example?**

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. For instance, `=VLOOKUP("Apple", A1:B10, 2, FALSE)`

finds “Apple” in column A and returns the corresponding value from column B.

**How do you sort data in Excel?**

To sort data, select the range, go to the Data tab, and choose the Sort option

**What are filters in Excel, and how are they used?**

Filters hide non-relevant data based on specified criteria. You apply them through the Data tab by selecting Filter.

**Explain how to use the AVERAGE function.**

The AVERAGE function calculates the mean of a range of numbers. For example, `=AVERAGE(A1:A10)`

returns the average value of cells A1 through A10.

**What is Conditional Formatting and how is it used?**

Conditional Formatting highlights cells based on criteria you define, such as changing cell color for values above a certain threshold. This is found under the Home tab.

**How do you create a pivot table?**

To create a pivot table, select your data range, go to the Insert tab, and choose PivotTable.

**Data Analyst Jobs in Bangalore – Click Here**

**Describe how to use the IF function with an example.**

The IF function returns one value if a condition is true and another if it is false. For example, `=IF(A1>10, "Yes", "No")`

will display “Yes” if A1 is greater than 10 and “No” otherwise.

**How do you use the CONCATENATE function or the modern equivalent?**

CONCATENATE combines text from multiple cells into one. For example, `=CONCATENATE(A1, " ", B1)`

merges the contents of A1 and B1 with a space in between. The modern equivalent is `=A1 & " " & B1`

.

**What is the purpose of the TEXT function in Excel?**

The TEXT function formats numbers as text based on a specified format. For instance, `=TEXT(A1, "dd/mm/yyyy")`

formats a date in A1 as day/month/year.

**How do you use Excel’s built-in chart tools?**

Excel’s chart tools allow you to create various types of charts, such as bar, line, and pie charts, from the Insert tab.

**Explain the difference between a workbook and a worksheet?**

A workbook is the entire Excel file, while a worksheet is a single tab within the workbook.

**What is a named range, and how do you create one?**

A named range assigns a specific name to a cell or range of cells for easier reference. You can create one through the Formulas tab by selecting Name Manager.

**How do you use the COUNTIF function?**

The COUNTIF function counts the number of cells that meet a specified condition. For example, `=COUNTIF(A1:A10, ">10")`

counts how many cells in A1 to A10 have values greater than 10.

**What is the purpose of the MAX and MIN functions?**

MAX returns the highest value in a range, and MIN returns the lowest. For instance, `=MAX(A1:A10)`

and `=MIN(A1:A10)`

find the maximum and minimum values in the range A1 through A10, respectively.

**How can you protect a worksheet or workbook?**

You can protect a worksheet or workbook to prevent unauthorized changes by using the Protect Sheet or Protect Workbook options under the Review tab.

**How do you apply and remove cell formatting?**

To format cells, use the Home tab to apply styles, fonts, and colors, and to remove formatting as needed.

## Intermediate Excel Interview Questions for Data Analyst

**What is a pivot chart, and how does it differ from a regular chart?**

A pivot chart visualizes data from a pivot table and updates automatically when the pivot table changes.

**How do you use the INDEX and MATCH functions together?**

Using INDEX and MATCH together allows you to perform lookups with more flexibility than VLOOKUP. For example, `=INDEX(B1:B10, MATCH("Apple", A1:A10, 0))`

finds “Apple” in A1:A10 and returns the corresponding value from B1:B10.

**Explain how to use the HLOOKUP function**

HLOOKUP searches for a value in the top row of a range and returns a value from a specified row below it. For example, `=HLOOKUP("Apple", A1:D10, 2, FALSE)`

searches the top row for “Apple” and returns the value from the second row.

**How do you handle missing or incomplete data in Excel?**

Missing data can be handled using functions like IFERROR, filling in with placeholders, or manual data cleaning methods.

**What is data validation, and how is it set up?**

Data validation restricts the type of data that can be entered into a cell, such as allowing only numbers or dates. You set it up via the Data tab by selecting Data Validation.

**Describe how to use the LEFT, RIGHT, and MID functions.**

These functions extract portions of text. LEFT returns characters from the start, RIGHT from the end, and MID from a specified position. For example, `=LEFT(A1, 3)`

extracts the first three characters from A1.

**How do you create and use a dynamic named range?**

A dynamic named range adjusts automatically as data changes. This can be created using formulas like OFFSET and COUNTA.

**What is the purpose of the FIND and SEARCH functions?**

FIND and SEARCH locate text within a cell, with FIND being case-sensitive and SEARCH not. For example, `=SEARCH("apple", A1)`

finds “apple” in A1.

**How can you use Excel to perform a “What-If” analysis?**

This involves using tools like Scenario Manager or Data Tables to explore different outcomes based on varying input values.

**Explain how to use the SUMIF and SUMIFS functions**

SUMIF adds values based on a single criterion, while SUMIFS allows multiple criteria. For example, `=SUMIF(A1:A10, ">10", B1:B10)`

sums values in B1:B10 where corresponding A1:A10 values are greater than 10.

**What is the use of the PMT function in Excel?**

The PMT function calculates loan payments based on interest rate, number of periods, and loan amount. For example, `=PMT(rate, nper, pv)`

calculates the payment.

**How do you use Excel’s Goal Seek feature?**

Goal Seek adjusts the value of a specific cell to achieve a desired result in another cell. Use it via the Data tab under What-If Analysis > Goal Seek.

**Describe the process for creating a macro in Excel**

Macros are created to automate repetitive tasks. You can record a macro via the Developer tab or write one using the VBA editor.

**How can you automate repetitive tasks using Excel?**

Tasks can be automated using macros, or by leveraging Excel’s built-in automation features like conditional formatting and data validation.

**What are array formulas, and how do you use them?**

Array formulas perform calculations on multiple values at once. For example, `=SUM(A1:A10 * B1:B10)`

calculates the sum of products of corresponding values.

**How do you use the TRANSPOSE function?**

The TRANSPOSE function switches the rows and columns of a range. For example, `=TRANSPOSE(A1:B2)`

converts rows into columns and vice versa.

**What is a data table, and how is it created?**

A data table is used for sensitivity analysis, showing different results based on varying inputs. Create it via the Data tab under What-If Analysis > Data Table.

**Explain how to use the SUBTOTAL function**

The SUBTOTAL function performs calculations while excluding hidden rows. For instance, `=SUBTOTAL(9, A1:A10)`

calculates the sum, ignoring any filtered-out rows.

**What is Power Query, and how can it be used?**

Power Query is a tool for importing and transforming data. It is accessed through the Data tab under Get & Transform.

**How do you use the OFFSET function?**

OFFSET returns a cell or range of cells that is a specified number of rows and columns from a starting cell. For example, `=OFFSET(A1, 2, 3, 4, 5)`

refers to a range that starts two rows down and three columns over from A1.