Excel Interview Questions and Answers
In an attempt to add more value to our students, we have this special section where we list the interview questions and answers. These are collected from the feedback of other students who have attended interviews and also from our internal recruitment process.
Make sure to attend our mock interviews by our expert panel who would test and give you feedback to help you crack your interviews.
And also add value to your resume by adding certification from Microsoft.
Q1: How do you hide or show ribbon in Excel.
Ans: Ribbon can be hidden or showed by using the shortcut CTRL+F1
Q2: What is the quick way of knowing the sum of a range.
Ans: Just select the range and the sum will be displayed on the status bar.
Q2: What is the difference between Paste vs Paste Special.
Ans: When paste is used everything gets copied (formatting, comments, formulas, values…). But using paste special we can choose what to paste either values , formulas, comments, validation and so on..
Q3: What are the uses of Pivot Tables in Excel, explain?
Ans: As provided in reference notes.
Q4: Sometimes when a date is entered it comes up as a number. Have you encountered something like that.? Why does it happen and how to fix it.
Ans: As explained in the classroom for Date issues.
Q5: A date entered in cell like "11/2/2016" can be interpreted as 2nd Nov or 11th Feb. How do you format it to solve this confusion.
Ans: As explained in the classroom for Date issues.
Q6: How do you copy validation to other cells.
Ans: Using paste-special and choosing validation option.
Q7: Can you show a line and bar types in a single chart. How?
Ans: Yes. Check practice files for how to create such a chart.
Q8: Give an example of Nested-IF condition in excel.
Ans: As explained in the logical function class.
Q9: How is conditional formatting different from normal formatting?
Ans: As explained in the class of conditional formatting.
Q10: Explain how do you highlight/remove duplicates in a column?
Ans: As explained in the class of conditional formatting.
Q11: What are the steps you would take to maintain accuracy of your report
Ans: Most of the times a report is made from a large set of data or multiple sets of data. I would note down the totals before making a report and match those totals with my output in the report. And also I would do a random check of numbers by using filters and other options instead of formulas. Etc...
Q12: Given a table of numerous records which are the features in excel which can be used to create a summary?
Ans: The 3 best methods to create a summary from a datasheet are 1) Creating a summary using formulas like Sumif, Countif, If and linking the summary to charts 2) Using Pivot Table to create summary and providing slicers for further analysis 3) Using Subtotal features to transform the datasheet into a report.
Q13: In Vlookup formula the search column for the lookup value should always be the left most column. Are you aware of this limitation and do you know any workaround for this.
Ans: Yes if this is a one-time task then the best way is to copy the search column and paste it at the far left, perform vlookup, kill formulas and delete the copied column. Else a combination of index & match has to be used.
Q14: What are the advantages of a table in Excel?
Ans: a) Excel tables are easy to format with lot of inbuilt styles b) Easy to filter or sort c) the headers are always visible when you scroll down d) adding a new row automatically takes the format e) adding a new formula in a column automatically adds the formula to the end f) charts created with tables update automatically when table structure change g) totals can be added easily using the formula dropdown in the last column.
Q15: What are named ranges in excel and how to define them?
Ans: As explained in the class of validation
Q16: Can we create a dynamic list which updates itself based on the number of items? How?
Ans: Yes by defining a named range and using offset formula to make it dynamic.
Q17: Sometimes opening a workbook displays a prompt "This workbook contains links to other sources". Why do we get those messages and any possible solution for that.
Ans: It might be that you have a workbook A which derives a value via formula from a cell in Workbook B. And if Workbook B is closed and when you try to open Workbook A, you might get this prompt as Excel tries to update the file with the latest value. If you no longer need the link then hit ALT+E+K to get a prompt to "Edit Links" and choose "Break Link" and save the file.
Q18: Why do we use sumif/sumifs/sumproduct formulas?
Ans: As explained in the formulas class.
Q19: Can vlookup be used instead of nested if condition?
Ans: Yes, vlookup can be used instead of nested if condition; the range_lookup should be set to "True".
Q20: Can you protect the data from getting copied in a sheet. If yes, how?
Ans: As explained during the class for protecting range.
Q21: Can you password protect an excel file? If yes, how?
Ans: Yes. As explained during the class for protecting a workbook.
Q21: What is the difference between count and counta function?
Ans: Count functions counts only numbers in a range. Counta counts any non-blank cells in a range.
Q21: Is there a function you are aware of which can extract the month name from a cell that has date?
Ans: Yes the formula is "Text"
Q22: Which formula would you use to determine the difference in 2 dates in terms of month.
Ans: The formula would be "DatedIf"
Q23: Which formula is used to determine the difference in terms of days between two dates excluding weekend and holidays.
Ans: Networkdays
Q24: What is the shortcut to apply and remove filter?
Ans: Alt+D+F+F
Q25: All colums in "sheetA" are hidden and its password protected. Is there a way to extract the contents of the sheet without unprotecting.
Ans: Yes- By using cell referencing in another sheet.
Q26: Do you know how to freeze range in a formula and what's the purpose of it. Can you explain with an example.
Ans: To freeze a range in formula put '$' symbol to make it absolute. Give an example of the currency conversion as shown in the class.
Q27: Sometimes numbers get formatted as text and they will have a green triangle on the top in the cell. How do you convert them back to number.
Ans: Select the cells and hit the error button that appears on the top right of the selection and choose convert to number. You can also use text to columns to convert it as taught in the class.
Q28: Have you ever created an excel dashboard? How does it work?
Ans: Dashboard is a one pager report instead of the huge datasheets to have a quick glance. Generally dashboard contains block of important numbers formatted properly and also lot of charts. We can also create dynamic dashboard which changes on selection. We've seen 2 dashboard models one built using slicers and pivot charts and the other one using formulas (Sumif, countif, rank, offset, indirect) and charts with validations.
Q29: Do you know how to use Offset function and can you share an example where it can be used?
Ans: Explain what you learnt and give the example of YTD P&L shared by your trainer.
Q30: Why do we use indirect function?
Ans: Give the example of project you did for dynamic validation.
Q31: Do you know how to get data from web?
Ans: Explain what you learnt in the class to get data from Amazon/Yahoo finance.
Q32: Have you ever created an excel model, can you give an example of how it works?
Ans: Check with your tutor.
Q33: Can you explain the process of making a waterfall chart?
Ans: Check with your tutor.
Q34: Have you extracted data from Access into excel anytime, can you explain how to do it.
Ans: Yes, using the option "Get External Data" from access. Check with your tutor to explain in detail.
Q34: How are slicers used in a pivot table?
Ans: As explained in the class of pivot table
Q35: Do you know how to use a pivot chart?
Ans: As explained in the class of pivot table
Q36: What is a calculated field in a pivot table and can you give an example of how to use it?
Ans: As explained in the class of pivot table
Q37: What is a calculated item in a pivot table and what are its uses?
Ans: As explained in the class of pivot table
Q38: What is scenario manager in excel, can you give a real time use of it.
Ans: As explained in the example of project management / house budget decision.
Q39: What is the use of Goal Seek and how do you use it
Ans: As explained in the example of Goal Seek to identify the increase in % of an individual product sale to achieve a desired margin % for the entire business using sumproduct formulas.
Q40: What is the use of flashfill in Excel 2016?
Ans: As explained in the class of filling data.
Q41: What is the use of timeline.
Ans: Explain for normal data using timeline to slice the data or explain the use in pivottable with the data used in the classroom training.
Q42: What does trace precedents and trace dependents used for in excel
Ans: Trace precedents shows which cells are involved in the result of the selected cell. Trace dependents will point to all the cells which are dependent on the active cell. Show an example as taught in the class.
Q43: Why do you use watch window?
Ans: You can add cells to the watch window to monitor how it changes. It is useful when you are working in multiple sheets and want to see how few cells on other sheets are impacted on any change in current sheet. Explain with an example as taught in the class.
Q44: What are the various calculations options in Excel and why do we use it?
Ans: Check with your tutor.
Q45: How do you consolidate data in various sheets?
Ans: You can use the sum function and give the beginning and ending sheet reference in the sum formula or you can use the feature "consolidate" from the data tab.
Q46: Do you know how to share a workbook, have you ever worked on it. Do you know what are the challenges while working with a shared workbook?
Ans: Check with your tutor or answer based on your experience.
Q47: What is absolute reference and relative reference in formulas, explain?
Ans: Explain with a simple summary of exchange rate and conversions.
Q48: Can you freeze only row or column in a formula and in which scenario you use it.
Ans: Explain with an example of vlookup to give the column reference from a cell or using PMT function.
Q49: What is the difference between iferror formula and iserror formula.
Ans: Explain with an example in vlookup
Q50: Do you know how to record a macro.
Ans: As explained by tutor.
Schedule a mock interview with the trainer after completion of the course and all assigned projects if you are trying for a job which would require excellent excel skills.
Tags: excel interview questions, excel interview hyderabad, excel jobs hyderabad, excel job oriented course, excel questions and answers, excel interview questions for job seeker, top 50 excel questions,