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:-
1) Number
2) Alignment
3) Font
4) Border
5) Fill
6) Protection
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.
Option 2:
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:
Concatenate Function
“&” 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.
| Syntax | Range | Criteria | Sum_Range |
|---|---|---|---|
| =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:
Formula Explanation:
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 info@exceltip.com
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.
These interview questions are fantastic! Do you have an updated list? I also like the quizzes. Are they updated daily or are there just a couple versions? So far this is one site I have found that is more comprehensive for testing one's understanding of Excel.
That's a good idea. We are working on it.
It is very useful information,Thank you..
thank you very much admin... i have an interview tomorrow and this info helped me a lot to know... such an organized information
HOW TO LINK MULTIPLE EXCEL SHEET LIKE SHEET 1, SHEET2, SHEET 3 AND etc... to obtain the ranks from overall comparative data analysis by linking all excel sheets. pls suggest me how to slove the problem.
How to open a excel page without any rows or columns?
Don't want to do hide or anything....
The content is very helpful and thanku very much to the team for providing such a helpful data..
Hi,
During a Interview, Interviewer asked a question to me that "Can you do that like If I press a first word of your name so automatic you full name come in cell without create list???
I said! No....
Am I right.....................
Very well guys!
I liked your article and I hope you will have many entries or more
It gave me a lot of useful information. thank you very much
Thank you for sharing information,its really so Good
I appreciate the tips, but some of the questions are interesting. I suppose they're geared to determine how much you know and don't know, but asking for syntax of a formula? Those are things that I don't necessarily remember but can easily look up.
Here's one for screening VBA developers: what are the keyboard shortcuts for displaying a list of macros (Alt-F8) and for entering the VBIDE (Alt-F11)?
Thank a lot
We appreciate your effort
As a trainer, this article will be quite helpful for me to prepare my students for the job Interview. You guys are so amazing and I do not miss your even one article as it helps me to teach something new everyday to my students.
Though few questions are very basic but are tricky to answers. I remember during one interview, the interviewee asked me to name the 10 functions which I daily use at work but I couldn't name it all 10 however I used to use more than 20 functions daily. 🙂
Thanks once again team for your hard work to bring such a wonderful list for all of us.
BR
Ricky
Thanks for the excellent list of questions. As a teacher, I appreciate your willingness to share this information. I know how much time it take to create a comprehensive list like this so I wanted you to know that my students and I value your contribution to our Excel lessons.
Big shout-out from Chicago!
-Joan
Perfect! Knowledge sharing..It is really wonderful. Most interesting thing is even though you are using regularly most of them you may not be remember. It won;t come come out in right time. This type of knowledge sharing and discussion will help a lot to learn.
A must have for Excel user.
Thanks a lot for putting such a nice surprise.
A useful article for anyone seeking a role requiring excel. I have used Excel Forum for several years now and describe it to others as excellent and essential.
Please could I expand on Q2. Whilst efficient sheet discipline will shrink the file size somewhat and is good practice, there is an amazingly simple way of dramatically getting the size down whilst leaving everything in tact (macros, vba projects/classes and data connections). Save the workbook as.xlsb (a binary file). In so doing, you save the book in its native format but without the 'shell' which allows other excel formats to work on various other apps and platforms. Reducing 20mb to 7mb, making it a feasible email attachment, is a very credible outcome. Using options, xlsb can be set as the default save format. Be aware that it will no longer be possible to determine if a file contains code.
I use xlsb by default. I regularly draw from several data sources including servers, websites and other workbooks all for one workbook model with heavy VBA and SQL to create the dynamic dashboards modules included within that model. I will admit that it is scary at first but after you come to trust the format, you will never again use xlsx and xlsm unless you need to.
Really good stuff. thanks for sharing.
Keep it up!
Best regards,
Mohan
Thank you for this nice resource, but I have a bit of an issue with the "answers" to Q7 about the difference between Formulas and Functions, most specifically about "A function cannot be typed as its built into the software." This answer is categorically wrong. The sample you give =SUM(A2:A4) certainly could be typed into the formula bar and into the cell if a user wanted to. Grant you, it is easier to use the AutoSum, or the formula tips, but to say "cannot be typed" is incorrect. Also, even if it were correct, the word "its" is a contraction of IT IS, so the appropriate word would be "it's." I also take a little bit of an issue with the answer "A function is a piece of code designed to calculate specific values and are used inside formulas" since a function could more properly be considered a sub-set of a formula. In other words, all functions are formulas, but not all formulas are functions.. To say "A function... are used inside formulas" implies that there must be a way of placing, say, SUM(A2:A4) into the formula A2+A3+A4, which of course is misleading, but if in the definition you define a function as a SUBSET of a formula, it would make more sense. Similarly, I take issue with the answer "Formula cannot be nested." While I understand what is meant by the statement, it is technically not true. For instance the following: =(((A2+A3+A4)/1+1+1)*3), is a perfectly legal formula, and a formula nested within a formula, nested within another formula. How then can an answer say "Formula cannot be nested?"
Q9: Could be done a different way: in the "Create PivotTable" dialog box, one could check-mark "Add this data to the Data Model," then go to the DATA ribbon tab and set-up relationships, if all sets of data in the local file were TABLES and not RANGES of data.
Finally, I noticed a few article words (a, an, the) are missing from some questions, and possible answers: eg. "What is Ribbon in MS-Excel?" and "What is Chart in MS-Excel?" Shouldn't it ask: "What is *the* Ribbon in MS-Excel?" and "What is *a* Chart in MS-Excel?"? Just a few observations FYI. But once again, a nice resource.
Hi Kevin R Araki,
First of all, we would like to take this opportunity of thanking you for being our loyal user. And, also thank you for your constant efforts in our improvement.
This detailed feedback is very helpful and we are working on the same to make it more comprehensive for our users.
Once again thank you for taking time and appreciating us.
Thanks
Site Admin
wonderful it required seriousness and dedication
it is a very robust programme that required serious training and dedication
appreciate your work
thumbs UP!!!!
You guys are amazing with this content. It is amazing that you are sharing your knowledge for free. One can only thank you for this... keep up the good work.
Best Regards
Thanks you. This information is very interesting and useful. Howerer I like to answer your Q9
Hi Hien,
Thank you for taking your valuable time in reading this article.
Yes, you can proceed please. We welcome if you have any alternative / suggestion for us. 🙂
Best Regards,
Team Excel Forum & Excel Tip
This article helped me a great deal in my interview. I just wanted to take a moment and thank you for all of the excel information that you have provided on your website in these 30 questions. Please continue to add content as it's the best excel website on the internet.It's Very clear, uncluttered, non-offensive to the eye! Well laid out with just the essentials! 😀
Hello Ranu,
We are glad to know that this article helped you during your interview. 🙂
Keep visiting and learning with us.
Regards,
Team Excel Tip & Excel Forum
PS. In case you have any suggestion for us, do write us back at info@exceltip.com.
my answer to Q2 "What is Ribbon in MS-Excel" ?
Ribbon is a counterproductive and unintuitive arrangement of command buttons and a waste of desktop space.
Which can be minimised should that be so desired.
Which now that I've done that I need to work out to make visible again. Sorted
it is always at least one click more 🙁
my preferable arrangement is menu and a custom toolbar together in one only line.
Hi Dim,
Thank you for your feedback. However, kindly specify the question to which you are referring and we’d be happy to look into the same. Or you can also send us the detailed query or suggestion on us in case of any suggestion at info@exceltip.com.
Thanks
Site Admin
Hi Dim,
Thanks for taking the time and giving your point on one of the the listed questions. 🙂
Keep visiting us.
Regards,
Team Excel Tip & Excel Forum
I considered that I was a moderate user of excel, but by reading your post, I analysed that I was a basic user!
So much needs to be learned.
Some of these things, I took a lot of time to learn, but anyone who gets to know your website or this article particularly, would learn it in few days.
Great post.
Hello Techieey,
That's the beauty of the learning. We think that we are masters in something but when we face new things in our life, we are just a basic learner. 🙂
Keep learning and visiting us. 🙂
Regards,
Team Excel Forum & Excel Tip
"Tell me and I forget. Teach me and I remember. Involve me and I learn."
-Benjamin Franklin
I think it's all a matter of how they word the questions. I consider myself a power user but I would do very poorly if the questions were phrased in this manner. Don't be intimidated. I'm sure you have precisely the skill level you initially perceived.
Prior to obtaining my current position I had to take an Excel exam. It was a computer based test and you had to perform various different tasks. I aced it but the exam expected you to do everything the long way. Again, it's like poorly framed questions. I use every short cut available to me and it was cumbersome to do it step by step.
Overall, I'd say when interviewing someone for a position that requires Excel skills, one should ask more specific questions. If you know Excel you will quickly discern whether the candidate really knows what they're talking about.
Hi Sasspants,
Thank you for your valuable feedback. You’ve been one of our loyal users and never missed a chance to complement or improve us. Like you’ve already mentioned, “it’s all a matter of how you word the questions”, the idea behind this guide is the same. With the basic knowledge on the above questions, it’ll enable the user to answer such questions, irrespective of the manner in which they are asked.
However, with due weightage to your feedback and constant efforts, we’ll strive hard to make it more comprehensive.
Once again, thank you for taking the time and appreciating our efforts.
Thanks
Site Admin
Thanks for the information. This has been very informative.
Hello Ron,
Thank you for taking the time and appreciating our efforts.
Keep learning with us. 🙂
Regards,
Team Excel Tip & Excel Forum
Very nice blogs.You sharing for excellent information.
Hello,
Thank you for taking the time and appreciating our efforts.
Keep learning with us. 🙂
Regards,
Team Excel Tip & Excel Forum
"adjust the text within a cell" is too vague to be a sensible question. The given "answer" only deals with one of the possible meanings.
Hi Kickaha,
We found these questions are very commonly asked questions during the interview. If you have alternate ways of doing it, we would request you to please share it with us. 🙂
Write us in case of any suggestion at info@exceltip.com
Regards,
Team Excel Tip & Excel Forum
I agree, the question is too vague. One can easily respond what functions and formula to use to concatenate or trim text in cells.
Hi Ciprian,
Thank you for your valuable feedback. As the guide comprises of the most commonly asked questions in the MS Excel interview, we’ve included the same to maintain the credibility of this guide. We’ve targeted the interviews that include even a bit of MsExcel, which clearly explains the necessity of such questions even.
Once again we’d like to thank you for your continuous efforts to improve us.
Thanks
Site Admin
I never thought this content is available for free, I just followed my newsletter from my email and this content is very good, fantastic. thanks guys.
Hello Balaji,
Thank you for taking the time and appreciating our efforts.
Keep learning with us. 🙂
Regards,
Team Excel Tip & Excel Forum
I like to learn how to add colors in a formula in excel. For example I want to color all the training completed in Green and all the training not completed in Red. What will be the formula for that
Thank you I will greatly appreciated your help
Thank you so much! Really rich content and very useful information.
Hello Kizi,
Thank you for taking the time and appreciating our efforts.
Keep learning with us. 🙂
Regards,
Team Excel Tip & Excel Forum
thank you so much sir