Are you going for an interview??? Feeling nervous?? Do you worry there may be Excel-related questions that you may not be able to answer?
… Don’t worry!
To make your interview successful, we have listed more than 50 basic and advanced excel interview questions for Data Analyst, Business Analyst, and Financial Analyst interviews that can be asked by the interviewer.
Common MS Excel Interview Questions
Below I have listed some commonly asked MS Excel interview questions and answers.
1. How can you format a cell?
There are formatting options in the home tab (such as applying color, making the font bold, adding borders, etc.) You can also use the Format Cells dialog box to format a cell in Excel (the shortcut to open the format cells dialog box is Control + 1)
To get more details, click Excel cell formatting.
2. How many types of formats available in Excel? What are they?
There are different types of formats available in Microsoft Excel. Few of them are:
4. Can you make a pivot table using two different worksheets?
Creating a pivot table on the worksheet using multiple sources on the different worksheet is possible.
The workbook of these worksheets must be the same. Click on the link to get details about How to create an Excel pivot table using multiple worksheets.
5. Can you resize the column with a single click?
There are three ways to resize the column.
- One way is to drag the boundary of the column towards the right side.
- The second is to go to the format icon available at the home tab and choose the Auto size column width.
- The third is to go to the boundary where alphabets are written and double click on the boundary as shown in the image.
6. How does the “If function” in Excel works?
The function is used to test the condition whether it is false or true.
If the condition is in favor then it gives a favorable result and if the condition is false then the result comes differently. In fact, you can apply Excel If a statement in multiple conditions ranges.
7. How to protect the cell of your worksheet from copying by someone else?
To protect your worksheet, go to the menu-> review ->protect sheet-> password. Now you need to enter your password so that you can prevent your worksheet from getting copied.
NOTE: To learn more functions and formulas of Excel, you can buy any of the Best Excel Books from the link at a discounted price.
8. Can you highlight cells containing duplicate values?
Yes, we can highlight cells using conditional formatting.
- Select the data set.
- Go to the home tab.
- Click on the conditional formatting icon.
- You will find highlight cell rules.
- Press on duplicate values option.
For more details, click conditional formatting excel formula and check how to apply
9. By using formula, can you combine the text available on multiple cells?
You can combine text via three different methods:
10. How will you find the length of the text?
To find the length of the string, you need to use the LEN formula.
For example, if your string is in A2 then type LEN(A2).
11. What is the format to write VLOOKUP formula? How can it be used?
VLOOKUP is probably one of the most asked Excel interview questions. It’s quite commonly used and a good understanding of this function may very well be the difference between you getting or not getting the job.
Below is the syntax of the VLOOKUP formula:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
VLOOKUP function is a part of the lookup function family that allows you to look for a data point in a column (vertical data – the V in Vlookup stands for vertical).
You can find a detailed description and examples of the Excel VLOOKUP function here.
12. What is a pivot chart?
After creating a pivot table, you will get a data set that you can use to create a pivot chart.
The advantage of the pivot chart is whenever you make changes in your pivot table than that changes will reflect in the pivot chart.
13. Which chart is the most suitable for showing the performance?
For showing performance based on the target completion then a bullet chart is the most preferred chart.
The chart will show you lots of information and consume lesser space in your report.
14. Can you create a hyperlink in excel?
To add a hyperlink, you need to select the cell on which you want to apply hyperlink and use Ctrl+K. By pressing,
Control key along with K, you will find a hyperlink dialog box where you need to specify your URL.
15. Define ribbon?
The ribbon is the top side area where menu items and toolbars are located.
16. What is the syntax of Sum and Average formula in excel?
The syntax of these formulas is very easy. For Sum “=SUM(A2, A3)” whereas for average “=AVERAGE(A1: A5).
For more details, go to excel formulas basic.
17. What are wildcard characters in Excel?
There are 3 wildcard characters available:
- Asterisk (*)
- Question Mark (?)
18. For business purposes, which Excel chart is best and why?
For business-related issues, the Pareto chart is the best chart for you.
Now the question is “Why Pareto chart is best to solve your business issues?”
The Pareto chart works on the basis of the Pareto Principle that helps in identifying the root cause and problems occurring in your business.
If you find lots of problems and want to focus on the biggest problem then you need the Pareto Chart.
19. How you can remove duplicates in excel?
To remove duplicates, to need to find duplicates using conditional formatting AND Countif function.
Click here to check the process of removing duplicate values in excel.
20. How to add Bullet Points in Excel?
Like MS Word, you will not find the shortcut of adding bullets. But you can use different methods to add bullets in excel.
- Using the symbol box.
- Copy Paste from MS Word
- Using Formula
To know its step by step process, you need to Click Here.
21. What is Data Validation?
The data validation is an option that is available in the data tab.
This option allows you to enter data that fully satisfies the criteria.
For example, if you want to enter the roll no. or the age of the student then you need to use Data validation so that it can accept the numeric values only.
You can also create an in-cell drop-down list in Excel that will allow the user to manually select the data from a drop-down. The option to create drop-down lists in Excel is a part of data validation.
22. How to wrap text in Excel?
The wrapping up of text is to avoid the overflow of text out of the cell.
By doing wrap text, you need to be sure that the text fits in the cell. The option to wrap text in Excel is available in the Home tab (in the alignment group).
23. What are the top 5 functions in Excel?
In Excel, there are lots of functions and as such, you cannot say that these are your top 5 functions.
But here I am listing five functions that are asked in the interviews
Apart from this, Sum and Average, Left/Len/Mid/Right are the functions that also can be asked by the interviewers.
24. How to combine the text of multiple cells using the formula?
There are lots of functions available in excel to combine text available in the multiple cells. These are:
25. What is frequently used Excel Shortcuts?
Here you can check few excel shortcuts that you need frequently:
- Ctrl+U: Underline
- Ctrl+L: Create Table
- F12: Save as
- Shift+F11: New Worksheet
Apart from these, there are other shortcuts also that are frequently used while writing any post.
26. What is Slicer in Excel?
The Excel slicer is used to filter the data whenever you choose one or more than one option in the slicer box.
To get the detailed information Click Here.
27. What is the use of the Pareto Chart?
The Pareto chart is mainly used by a business person.
With this chart, you can make an analysis of the flaws coming to your business. You can check here how to create a Pareto chart in excel by clicking on the link.
28. Which function will you use to replace the value with another value in Excel?
By using Excel Replace function, you can replace one value with another.
If you want to know how to use replace function in excel then Click Here.
29. What is the Goal Seek function?
The Goal Seek function in Excel gives you the input value by altering the output value.
To know how to use goal seek function, you need to Click Here.
30. What is Excel Add-Ins?
In Excel Add-Ins, you will find various optional commands that you need to install and then activate them for using it.
Click Here to check add-Ins in excel.
31. How to add Strikethrough in excel?
The Strikethrough in excel is putting a line on the text which has already been checked or the task has completed.
To add strikethrough in excel, you need to click Ctrl+S. If you want to know more methods to add strikethrough in excel then click here.
32. How to calculate the standard deviation in excel?
To calculate the standard deviation in excel, you need to apply the STDEV formula.
The syntax is STDEV(number 1, [number 2], …..). If you want to know other methods then Click Here.
33. How to lock rows and columns in excel?
To lock rows and columns in excel, you need to freeze panes in excel.
Click here to check the steps to freeze the panes in excel.
34. How to perform multiplication in excel?
To multiply two columns in excel you need to use multiplication operator (*).
If you want to know other functions then Click Here.
35. How to find square root in excel?
To find square root in excel, you need to use the SQRT function.
The syntax of the SQRT is SQRT(Number). To get the detailed information, Click here and check how to use square root function in excel.
36. How to apply Excel if statement in multiple conditions?
In order to apply excel if statement in different conditions you need to click here and check the formula to apply for performing the logical test and if inside if.
37. How to resize the column?
To resize the column, go to the format icon available at the home tab and then select Autofit Column Width.
Another way to resize the column is to change the width of one column and then drag the column boundary as per the width you require.
38. How you calculate the unique address of a cell?
The unique address of any cell is calculated on the basis of the row and column of any cell.
39. What is the best way to learn Microsoft Excel?
If you want to be an expert in Excel then you can either join some coaching classes, online tutorials or subscribe to our site to get insight knowledge about Excel. You can learn all excel formulas and functions from here.
Apart from this, you can buy the best excel books also from the link. A book can be the best way to be an expert as you can learn to excel in the future also.
40. To solve any formula, what is the order of different operators?
To evaluate any formula, you need to use PEMDAS rule, i.e,
By following this rule you will get the right outcome.
41. How does the Vlookup function work?
The Vlookup function is the most commonly used function that the interviewer will definitely be going to ask you. He or she might ask you the format and syntax of the formula or can give you any question or query to solve using Vlookup formula
=Vlookup(lookup_value, table_array, col_index_num, range_lookup)
- Lookup_value is the value that you need to lookup.
- Table_array is the array where you can put data to find the lookup_value.
- Col_index_num is the function that tells you the number of columns available in table_array.
- Range_lookup is the function that tells True or False
42. What macro languages used in MS-Excel?
XLM and VBA are the macro languages used in Excel.
43. What are the different types of charts available in MS-Excel?
Charts are used for the graphical representation of the list of data.
In Excel you will find charts including column, bar, line, pie, scatter, and many more.
44. What are the most useful functions in Excel?
To manipulate data, there are many functions available in Excel including:
- Math and Financial: SQRT, Degree, GCD
- Logical: If, And, False, True
- Index and Match functions: Vlookup and Index Match
- Date and Time function: Now, Today(), Weekday()
45. How to describe excel skills on a resume?
While mentioning your excel skills in your resume, you need to focus on a few aspects:
- Specify yourself: You need to mention your proficiency in excel. Never say that you have advanced excel skills. Different people take this in different ways. Instead of saying advanced excel proficiency; you can say that you have advanced proficiency in MS Excel including pivot tables, Index/Match, VLookup, and Data Tables.
- Excel Courses: Mention the excel courses you have undergone whether it is online or offline. After the name of the course, you need to briefly explain the learning you got from that course.
- Honest: While mentioning everything you need, to be honest. For example, if you know the basics of excel then mention clearly. Do not mention that you know advanced excel as the interviewer may ask you some Excel practical questions based on advanced excel and at that time you will be caught.
- Highlight your knowledge or topics in which you are master.
So these are few advanced excel questions and answers for an interview that you can practice at home, college or anywhere you want. Practice these top excel interview questions
You may also like the Following Excel Tutorials: