Top 30 Microsoft Excel Interview Questions

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.

• Enter the formula in cell I2
• =COUNTIF(\$A\$2:\$A\$17,H2)
• Copy the same formula for the all manufacturer

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 of the Excel experts believe that Pivot table is the most powerful tool.

Why do we use it?

• Pivot table gives us flexibility and analytical power
• It is a time saver source in Excel
• Listing unique values in any column of a table
• Making a dynamic pivot chart
• Linking data sources outside excel and be able to make pivot reports out of such data

Q24. How to use advanced filter?

Answer:  We use Advanced filter 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 want to filter only unique list.

• Select the data range
• Go to Data tab > Click on Advanced
• Advanced dialog box will open
• Click on copy to another location
• Select the destination

• Click on OK

Q25. How we can 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:

• Press Ctrl+1 shortcut key to open Format cells dialog box
• In the number category, click on Percentage option
• Click on OK

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.

• Select the Cell A2
• Go to Home Tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format

• Enter the formula in tab
• Click on Format > Format cells dialog box will appear > Fill tab > Choose color > Click on OK

Q27. How to make 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.

Make the weekday’s list in column A.

Select the cell in which we want to create the drop down list.

• Go to Data tab > Data validation > Data Validation dialog box will open
• In Settings tab > List (Allow) > Source (Select the range A1:A8) > Click on ok
• In Cell C1, drop down list will be created

Q28. How to make dynamic drop down list?

Answer: To add item in the list, always create the dynamic list. This list picks the added value automatically and no editing is required within the list. To create dynamic drop down list, we use offset function along with Countif function.

Steps to create the dynamic list:

• Select the cell C1
• Go to the Data tab>Data Validation > Data Validation dialog box will appear

• In the Settings tab >List (Allow)
• Enter the formula in formula box
• =OFFSET(A:A,1,0,COUNTA(A:A)-1,1)
• Click on OK

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:

• Enter the formula in Cell B1
• =WEEKDAY(A1,1) press Enter
• Formula will return 3, it means today is 3rd day of the week

Q30. What is 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.

• Select the data range
• Go to Insert tab > Charts > Select 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.

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

## Users are saying about us...

1. You can also return the day of the week by formatting the cell as a day of the week.

If the dates are in Column A, and you want the days of the week in Column B, just make the formula in B1 “=A1″, and copy it down the list.

Then select all of Column B and go to Format Cells… Under Category, choose Custom. In the “Type” field, enter ddd or dddd (ddd for shortened day names, dddd for the full day names).

Now you will see the actual day of the week, instead of a number, which means nothing. “3″ is not a day. “Tuesday” is a day.
The other advantage is that now you can apply more functions to this data. If you want to know the day of the week 5 days before, you can’t do it with your method, because 3-5 = (-2). My method will return the correct day.

2. I liked few questions from this list as well….thanks Excel Tip….for sharing such a priceless information with your users as free of cost….:)

3. I m just a beginner in excel but these tips are really useful. Great work. Appreciate it.

Terms and Conditions of use

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.