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 of the Excel experts believe that Pivot table is the most powerful tool.
Why do we use it?
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.
Follow below steps:
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:
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 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.
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:
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 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.