In: Finance
1. What are examples of instances when you might use conditional formatting in an Excel spreadsheet? Try to come up with an example that is not used in the chapter.
2.Provide an example of when you would use the COUNTIF function and one of when you would use the COUNTIFS function.
Which of the following functions identified in this or the previous chapter do you think will be the most useful? Why?
3.Why you might want to use tables in Excel?
Provide an example of when someone might want to perform calculations on parts of a spreadsheet.
1. Conditional formatting is a function available in MS excel which is used for applying different types of formatting to a range of cells as per some criteria or a condition like highlighting cells in the range whose value is above 50, highlighting the top 10 values in a range of cells, highlighting cells containing text such as Monday and many other options are also available.
Example: Suppose you are a senior HR manager of a company. You are required to monitor the performance of a large no. of employees working within the organization. Now, you have a long list of all the sales managers in your system, defining the number of products sold by them in the last 6 months. Now you need to find out the top 5 performers of the company for the last 6 months, simply using excel and applying conditional formatting of highlighting cell containing top 5 values, you can easily detect the top performers which would otherwise might be a hectic process for you to search in a long list for finding out the best performers.
2. Countif function is a function that is used for counting the number of cells in a range according to criteria: greater than, less than, or equal to particular value.
Use: Suppose you have a long list of your students taking coaching at your institution. You need to count the students who have secured marks above 90. simply by using countif function, you can in seconds find out the number of students who have above 90 marks.
Countifs function is also used for counting the number of cells in a range with more and multiple criteria, unlike countif function which counts only on the basis of one criteria.
Use: If in the same above example you want to find out the count of students whose name starts with "A" and have secured above 70% marks. You can easily find the count of such students in few seconds using this function in MS excel.
Countifs function is more beneficial function of Microsoft excel in comparison to countif function. The reason is that, in countif you can count the number of cells only on the basis of single criteria whereas in countifs functions you can count the cells on the basis of multiple criteria. Countifs function is one of the most useful function used in corporates nowdays for counting values as per desired criteria among a large set of data.
3. Table function is a function that helps you to arrange your set of data into proper tabulated format in excel. It arranges your data into rows, columns and headers as per your needs. Data converted into the table provides numerous benefits: First of all it looks better in a proper arrange format, it makes it easy for analyzing and comparison of data when available in a table format, provides various filters options to quickly sort and filter data, effortless data entry, quick data formatting, fast calculations, dynamic charts and many other options.
Use: Suppose you have a large set of unorganized data, using the table function in excel you can just in seconds arrange in proper formats and apply different types of formatting. In absence of table function, it becomes a quite hectic process to arrange you set of data.