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.

 

image 27

 

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

Few more examples:

  1. COUNTIF in Microsoft Excel
  2. COUNT, COUNTA, COUNTIF and COUNTBLANK
  3. How to use COUNT functions :

 
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.

image 28

 

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.

 

image 29

 

Follow below steps:

  • 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

 

image 30

 

  • Click on OK

 

image 31

 

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

 

image 32

 

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.

 

image 33

 

Follow these steps:

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

 

image 34

 

  • Enter the formula in tab
  • Click on Format > Format cells dialog box will appear > Fill tab > Choose color > Click on OK
  • Follow the same procedure for the rest of the grades

 

image 35

 

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.

Follow these steps:

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

 

image 36

 

image 37

 

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

image 38

 

  • 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

 

image 39

 

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

 

image 40

 

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

 

image 41

In the above image, we can see very clearly that which manufacturer has contributed more than others and which manufacturer has contributed the least.

 

image 49

 

image 47
 image 48

 

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

 
 



4 thoughts on “Top 30 Microsoft Excel Interview Questions

  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….:)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>