Interviews are challenging. Job interviews that require any sort of tracking data or simple calculations are even trickier, as this would require a knack of MS-Excel.
Excel usage has been increasing in the industry; individuals manage their data in Excel to analyze the business trend. And, it could be nerve-wracking for both the interviewer and the interviewee to face such questions. To prepare you for those frequently asked questions, here we present a library of 30 questions and answers picked from real interviews.
All the best!
Please treat this as a refresher before you prepare yourself for the interview, as this is only a guide to crack interviews
Q1. What is Microsoft Excel?
Answer: Microsoft Excel is an electronic spreadsheet program, created by multiple highly skilled engineers from Microsoft. It enables users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and column.
We also use this tool for storing, organizing and manipulating the data. In addition, it also offers programming that supports VBA, and we can use external databases to make dynamic reports, analysis etc. Smart use of this program saves a lot of time and helps in creating our own applications too.
Q2. What is the easiest solution to reduce the file size?
Answer: Below are the steps to reduce the file size:
Q3. How many rows and columns are there in Microsoft Excel 2003 and later versions?
Answer: Refer to the table below for the number of rows, columns and cells for Microsoft Excel 2003 & later version:-
|Excel Versions||Rows||Columns||Total Cells|
|MS Excel 2003||65536||256||16777216|
|MS Excel 2007||1048576||16384||17179869184|
|MS Excel 2010||1048576||16384||17179869184|
|MS Excel 2013||1048576||16384||17179869184|
|MS Excel 2016||1048576||16384||17179869184|
|MS Excel 2019||1048576||16384||17179869184|
|MS Excel 365||1048576||16384||17179869184|
Q4. What is Syntax of VLOOKUP?
Answer: VLOOKUP Syntax: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Q5. What is the shortcut to put the filter on data in Microsoft Excel 2013?
Answer: Ctrl+Shift+L is the shortcut key to put the filter in data.
You can find more shortcuts on the below links:
Q6. How many report formats are available in Excel and what are their names?
Answer: In Excel, we have three formats available:
Q7. What is the difference between function and formula in MS-Excel?
Answer: Formula:- It’s a statement which is written by the user (user-created) to be calculated. A formula can contain values, cell references, defined names, and functions.
Functions:- Functions are in-built codes provided in MS-Excel, such as IF, COUNT, SUM, AVERAGE and so on. If additional functions are required we can create them through VBA. They are called user defined functions. This will carry out an operation of some kind and provide an output value. Functions are used inside formulas.
Q8. What is Chart in MS-Excel? Why is it important to you an appropriate chart?
Answer: Chart is a medium to present the data in graphical visualization, and it is the most important insight of the data. To present the data with perfect visualization and appropriate information, we should always pre-decide on the information to be presented.
As appropriate charts lead to the right decision, it's necessary to use relevant charts. Refer to the process chart for appropriate charts:
Here I have explained the Best Charts in Excel and How To Use Them
Q9. What is a Dashboard and what are the important things we should keep in mind while creating a dashboard?
Answer: Dashboard is a technique used to present important information through graphical representation. It is helpful in presenting huge data in a single computer screen so it can be monitored with a glance.
There are few things that should be taken care of while preparing the dashboards:
1) Minimum distraction
2) Simple, easy to communicate
3) Important data
4) A Few Colors
5) Relevant graphs
6) Dashboard should be on a single computer screen
Here I have mentoined 10+ Creative Advanced Excel Charts to Rock Your Dashboard.
Q10. How can you format a cell? What are the options?
Answer: We can format a cell by using the “Format Cells” option and there are 6 options:-
It is possible by using data modelling techniques.
Start by collecting data from various sources:
Q11. How to use Data Modeling for creating Pivot Table:-
After creating relationships between tables, make use of the data for analysis.
Q12. What is Ribbon in MS-Excel?
Answer: The ribbon in Excel consists of the tabs at the top. These tabs are split into groups which categorize related command buttons into sub tasks.
Each group has its respective command button and the dialog box launcher, which are present in the lower right corner in some of the groups.
This opens a dialog box containing a bunch of additional options we can choose from.
As per Excel’s default settings, we have 8 tabs. Which are:
To find out more in detail, click here.
Q13. Which option do we use to adjust the text within a cell and what is the procedure to do it?
Answer: To adjust text in a cell, we use Wrap text option. It can be used in two ways:
Option 1: In the Home tab > Alignment > Wrap Text.
Check for more examples:
Q14. How do I select all the objects in the sheet?
Answer: To select the object, we use the Go to Special option.
Follow the steps below to select the objects:
Q15. What is the IF function in Microsoft Excel?
Answer: ‘If function’ is one of the logical functions in Excel. We use this function to check the logical condition and specify the value whether it’s true or false. ‘If function’ has three arguments but only the first argument is mandatory and other two are optional.
Q16. What is the use of Name box?
Answer: Name Box is located in the left most corner of the Excel sheet. Usually, we use Name box to check the cell reference to the active cell but it has several other uses too.
For Example: We can define the name of the range through Name box. Below are the steps to understand this statement:
Q17. What is the use of VLOOKUP and how do we use it?
Answer: VLOOKUP is used to find the data in a large spreadsheet by lookup value in another worksheet. To use the lookup function, we should have common values in both data. For example, we want to search for the phone number of a person. So, in order to find out the phone number, we will need the concerned person’s name.
How do we use it?
We have 2 sets of HR data in Excel. In the second data, we want to update joining date of every employee from the first data. To use the Vlookup function, data must have the common value.
Follow below steps:-
Formula Explanation: =VLOOKUP(F3,$A$3:$D$13,2,0)
Q18. How can we view the values in the right most column in Excel?
Answer: You can view the value from the right most column through Index and Match function.
Example: We have 2 HR data in Excel. In the second data, we want to update joining date of every employee, from the first data. To use the Vlookup function, data must have the common value.
Follow below steps:-
Formula Explanation: =INDEX($A$3:$D$13,MATCH(F3,$B$3:$B$13,0),1)
Q19. How can we merge multiple cells text strings in a cell?
Answer: We can merge multiple cells text string by using the Concatenate function and “&” function.
Example: We have three names: First Name, Middle name, Last name in 3 columns. To merge the names and make it a full name, follow the steps below:
“&” use in formula to merge the text:
Q20. What is SUMIF function and how to use it?
Answer: We use Sumif function to add the cells specified by a given condition or criterion.
|=SUMIF(range, criteria,[sum_range])||Data range from which we want to retrieve the sum||For which we want to calculate the sum from the data||The range of column from which we want calculate the sum|
How to use it?
We have HR data in which we have salary details of every employee, department wise. Now, we want to retrieve the total salary amount department wise.
Follow these steps:
Q21. What is COUNTIF function and how to use it?
Answer: We use Countif function to count the specified cells, with a given condition or criterion.
Example: We have HR data with salary details of every employee, department wise. Now, we want to count number of employees department wise.
Few more examples:
Q22. What is Nested IF function?
Answer: When we have multiple conditions to meet, we can make use of IF function 7 times, which is called Nested IF function.
Example: In cell A1, there is drop down list of A, B, C & D. If A is selected then cell B1 should return Excellent, on selection of B result should be good, for C result should be Bad and D should be poor.
Q23. What is Pivot table and why we use it?
Answer: Pivot table allows quick summarizing of large data. We can calculate the field and arrange the data in presentable way in just few minutes. Most Excel experts believe that Pivot table is the most powerful tool.
Why do we use it?
Q24. How to use advanced filters?
Answer: We can use Advanced filters to extract the unique list of items or we can extract the specific item from different worksheets. We can say that Advanced filter is an advanced version of Auto filter.
Example: In a range, we have duplicate products and we only want to filter a unique list.
Follow below steps:
Q25. How can we change the cell formatting?
Answer: To change the cell formatting “Format cell” option is used.
Example: In cell A1, the value is to be converted into percentage, change the number appearance by following these steps:
Q26. What is conditional formatting and how to use it?
Answer: Conditional formatting is a tool that allows us to highlight the cells or range on the basis of few conditions and that formatting is always based on the values or text which can be automatically changed.
Example: In cell A1, there is a drop down list of A, B, C & D. If A is selected, then cell should be highlighted in green color, If B1 is selected then cell color should be blue, in case of C it should be yellow and if D is selected, then it should be highlighted in red color.
Follow these steps:
Q27. How do I make a drop down list?
Answer: We make the drop down list by using the data validation in Microsoft Excel.
Example: We want to create weekday’s list in a cell.
Follow these steps:
Make the weekday’s list in column A.
Select the cell in which we want to create the drop down list.
Q28. How to make dynamic drop down list?
Answer: To add item in the list, always create a dynamic list. This list picks the added value automatically and no editing is required within the list. To create a dynamic drop down list, we use offset function along with Countif function.
Steps to create a dynamic list:
Q29. How can we determine the day of the week for a particular date?
Answer: By using the Weekday function, we can return to the day of the week of a particular date.
Example: In cell A1, its today’s date and we want to return the weekday and count from Sunday. Follow these steps:
Q30. What is a chart and how can we use it?
Answer: Chart is the way to represent the data in graphical visualization. We can present the data in a more informative, easy to understand manner by using the chart. In Excel, we have 10 types of charts.
Example: For representation of sales performance chart, bar chart is suitable.
Say, we have manufacturers’ data with purchase price. We want to see the contribution of every manufacturer; therefore, we will use pie chart.
In the above image, we can see very clearly that which manufacturer has contributed more than others and which manufacturer has contributed the least.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.