Excel has been around for a while, in fact its first release came to the public in 1985. Since then, it has seen a continuous rise in popularity and today, the software benefits from 750 million users around the world. Many other products have come and gone, but Excel has managed to remain a centerpiece of the technology stack used at organizations of all sizes due to its flexibility, accessibility and ease-of-use. The tool has proved to withstand the test of time and, over the years, new functionalities have been added that allow individuals and organizations to gather better insights and even partially automate processes. To ensure that you are ready to use excel to support your organization, our team has set-up a list of the 10 most useful set of excel functions.
Throughout every single excel function showcased, a case study will be offered to help you get a feel for how the function works. For those interested in having the Case Study file, please download it here.
What is the index function? It returns a value or reference from a given table, range or array.
What is the match function? The match function searches for an item that has been specified by the user and provides the position that the result is situated at.
When combining both functions, it allows users to find an item and return a reference that is associated with that item. It serves as a powerful tool for many businesses and analysts.
What is VLOOKUP? VLOOKUP or Vertical lookup searches for a requested item in a column and outputs the results tied to that cell. It is considered to be the slower and less efficient option in comparison to INDEX-MATCH.
What is XLOOKUP? XLOOKUP is the upcoming solution (July 2020 estimated release) available only on Microsoft 365 and onwards. It aims to upend both VLOOKUP and INDEX-MATCH by doing both functionalities.
What is SUM? The SUM function adds up all the numerical cells that are defined in the formula.
What is SUMIF? SUMIF only summarizes a set of values defined in the formula when the values meet a certain criteria or condition specified by the user.
Answer: The formula is defining that all cells under purchase price (E4:E103) should be summed up if their respective product type (D4:D103) matches Zipper ($N$4) AND their respective countries (C4:C103) matches Mexico ($N$3).
What is Excel Average? The AVERAGE function returns the arithmetic mean in a defined dataset. It works by adding together all the numbers and dividing by the quantity of numbers added. For those interested in creating averages with criteria, there is a function called AVERAGEIFS that works in a similar way to SUMIFS.
What is Excel MEDIUM function? It provides the middle number out of the defined dataset.
What is Excel STDEV.S? It is the standard deviation function (Use STDEV.P for population) to a defined dataset. It works by gaging the amount of variation happening in a dataset.
Answer: this is a simple formula inputting answer. However, it is important to note the power of these formulas to analyze numerical data and understand variations in data.
What is the COUNT function? The COUNT function is used to determine the number of cells within a range or table that are numbers based.
What does COUNTIFS do? Counts the number of cells that follows a certain defined criteria(s).
What does concatenate do? The Concatenate or CONCAT function helps users combine two or more strings (cells) into one.
What is the AND function? AND function is a logical argument that determines whether the conditions defined in the formula are TRUE or FALSE. Under the AND function, all conditions need to be TRUE to achieve a TRUE state.
What does OR do? OR function is another logical argument that works in similar fashion to AND, however, it will turn TRUE as long as at least one of the logical arguments are TRUE.
Specify the criteria to be identified within the quotation marks (“ “)and input ‘>=’ to identify prices that are greater than or equal to.
What is the SEARCH function? Search is used to find characters within a cell and where it is situated.
Answer: Input the criteria within the quotation marks (“ “), search filtered out the #VALUE! To change the #VALUE!, it is possible to use an IFERROR formula as shown in the downloadable case study.
What is the MAX function? The MAX function determines the highest value in a defined range.
What does MIN do? It searches for the lowest value in a defined range.
What is the IF function? The IF function does a user defined logical test and outputs a result if it falls under TRUE and another for FALSE.
What does IFS do? The IFS function allows users to do multiple consecutive logical tests followed by the expected response if a logical test is TRUE. Note that logical tests are processed from left to right, so if Excel discovers that the second logical test is TRUE, it output the result for that and not move forwards with the rest.