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 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.
I am an employee at a Construction Company. I am in need of some important formulae for the excel files
with a large amount of data. I visited this site with useful information and knowledge.
Thank you for sharing the valuable info with all users.
Hats off to you guys..................
I m just a beginner in excel but these tips are really useful. Great work. Appreciate it.
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....:)
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.
Excellent! Thanks!