Top 100 Excel Interview Question And Answers For Data Analyst

Question-What are some differences between Google sheets and Excel.

Google sheet is not paid, excel is paid
Easy collection & collaboration in Google Sheets. Difficult collaboration in Excel
Clear revision history in Google Sheets. Excel requires manually saving each new version.
Excel is more capable of heavy calculations and data models. Google Sheets tends to slow down when many complex calculations are added.
Excel has tools like What-if Analysis. Google Sheets has functions like GoogleFinance, Sort, Filter, Query, Import Functions, etc. that are not available in Excel.

Question– How can you make a drop down in excel cell?
Answer-Use Data Validation, you can use values from a list to appear in the drop down.
Question– How can you wrap text within a cell?
Answer-Select the cell and then click WRAP Text in Home tab in excel

Question– What is macro in excel?
Answer– Macros are used to automate tasks in excel.
Users can create macros by writing coded instruction for a task or can record for some simpler task.
Question– How can you say if there is a comment associated with a cell?
Answer– Red triangle is visible in the cell which tells that a comment is available, and the comment would be visible if you hover over the cell.
Question– How to extract first name from full name.
Answer-Use =LEFT(A1,FIND(” “,A1)) formula.
Question- What are some useful functions in excel?
Answer–
Math and Financial Functions – SQRT, DEGREE, RAND (), ABS ()
Logical Functions – IF, AND, FALSE, TRUE
Date and Time functions – NOW (), DATEVALUE ()
Index Match – VLOOKUP and INDEX MATCH
Text Function-LEFT (), RIGHT (), REPLACE ()

Question- Explain the difference between SUBSTITUTE and REPLACE function in MS-Excel?
Answer–
The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.
Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])
The REPLACE function swaps part of the text string with another set of text.
Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)

Question- How can we get the weekday from a day in excel?
Answer-WEEKDAY function returns the number ranging from 1 to 7.

Question- How can we get the weekday from a day in excel?
Answer-WEEKDAY function returns the number ranging from 1 to 7.

Question-What is the default value of last parameter of VLOOKUP?
Answer-Default value of fourth parameter in VLOOKUP function is 1 or TRUE.
Providing 1/TRUE as the fourth parameter in VLOOKUP will give you approximate match (closest match), to get the exact match we provide 0/FALSE.

Question– What is the restriction on VLOOKUP function
Answer-Lookup value should be the first column in the table array, it always lookup value from right side.

Question– Is string “Apple” and “apple” same to VLOOKUP function?
Answer-Yes, VLOOKUP function is not case sensitive.

Question– How will you remove duplicate values from a column?
Answer-Using Remove duplicates option by pressing ALT A, M, A.

Question– How to find if a value is present multiple times in a column/data range.
Answer-Use =COUNTIF($A$1:$A$4,A1) formula.

Question– How to extract first name from full name.
Answer-Use =LEFT(A1,FIND(” “,A1)) formula.
Find gives the position of space and we extract the texts before the space.

Question . How to create a pivot table which is dynamic (We should not be changing the data source in the addition of new rows).
Answer:

There are two ways in which we could create a dynamic pivot table.
Using Data Table-Create a data table and then base your pivot table on that data table, in that case whenever a new record is added to the table, you just need to refresh the pivot table to have that new record included in the pivot table.
Using OFFSET function –Use OFFSET function to create a dynamic range for your data and give the name of the range while creating the pivot table, when a new record is added to the data, the range would automatically include that new record and since the pivot table is created on the range and not in the data, pivot table will include the new record.
OFFSET function return a range that is a specified number of rows and columns from a reference cell or range. =OFFSET(reference, rows, cols, [height], [width])

Question . How to make Pivot table automatically include new record when it is added in the data.
Answer :Create a Pivot table by using a dynamic name range, we can define a name range with the help of OFFSET function in excel.
=OFFSET(reference, rows, cols, [height], [width]).

Question . Can we add text column in the “Values” section of the pivot table
Answer :We can, but it won’t be making much sense as that would give us the counts of records and we would not be able to do any analytical task.
If taking the count of the values in your text filed is the purpose here, yes we can do that otherwise text columns should be placed in “Row Labels” or “Column Labels” or “Report Filter”

Question . How could we restrict pivot table from not losing its formatting after data refresh?
Answer :Go to “Pivot Table Options” and turn on the “Enable Preserve Formatting” and disable the “Auto Format” option.

Question . How can we change the data value function to MAX or MIN in a pivot table.
Answer :By right clicking in the field in “Values” section and clicking on “Value Field Settings”, we can change the function MAX/MIN/AVG etc