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.
Excel cells can contain text, numbers, dates, and formulas.
To create a formula, start with = followed by the expression you want to compute, such as =A1+B1.
Relative references (e.g., A1) adjust when copied to other cells, while absolute references (e.g., $A$1) remain fixed.
The SUM function adds up a range of numbers. For example, =SUM(A1:A10) adds all the numbers from cells A1 to A10.
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.
To sort data, select the range, go to the Data tab, and choose the Sort option
Filters hide non-relevant data based on specified criteria. You apply them through the Data tab by selecting Filter.
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.
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.
To create a pivot table, select your data range, go to the Insert tab, and choose PivotTable.
Data Analyst Jobs in Bangalore – Click Here
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.
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.
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.
Excel’s chart tools allow you to create various types of charts, such as bar, line, and pie charts, from the Insert tab.
A workbook is the entire Excel file, while a worksheet is a single tab within the workbook.
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.
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.
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.
You can protect a worksheet or workbook to prevent unauthorized changes by using the Protect Sheet or Protect Workbook options under the Review tab.
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
A pivot chart visualizes data from a pivot table and updates automatically when the pivot table changes.
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.
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.
Missing data can be handled using functions like IFERROR, filling in with placeholders, or manual data cleaning methods.
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.
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.
A dynamic named range adjusts automatically as data changes. This can be created using formulas like OFFSET and COUNTA.
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.
This involves using tools like Scenario Manager or Data Tables to explore different outcomes based on varying input values.
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.
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.
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.
Macros are created to automate repetitive tasks. You can record a macro via the Developer tab or write one using the VBA editor.
Tasks can be automated using macros, or by leveraging Excel’s built-in automation features like conditional formatting and data validation.
Array formulas perform calculations on multiple values at once. For example, =SUM(A1:A10 * B1:B10) calculates the sum of products of corresponding values.
The TRANSPOSE function switches the rows and columns of a range. For example, =TRANSPOSE(A1:B2) converts rows into columns and vice versa.
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.
The SUBTOTAL function performs calculations while excluding hidden rows. For instance, =SUBTOTAL(9, A1:A10) calculates the sum, ignoring any filtered-out rows.
Power Query is a tool for importing and transforming data. It is accessed through the Data tab under Get & Transform.
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.
