fbpx

Top 10 Most Useful Set of Excel Functions

Brief

            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.

Case study in the use of key excel functions

1) INDEX-MATCH functions

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.

  • Formula: =INDEX(The column or area where the result is expected to be, MATCH(What you want to lookup, Where you are looking for, How similar is the lookup to the data you are looking for [0=identical] & [-1 or 1 = approximate]), discretionary1, discretionary2).
    Discretionary1 = [column_num]
    Discretionary2 = [area_num]
  • Case Study: A company has a pricing database that lists all of its products. The manager requests that you confirm the price of Zippers from the database.
    Answer: By using INDEX to define the positioning of the returning variable ‘Price’ and MATCH to connect the searched item to the database, we are quickly able to extract the requested information.
Index match function in excel

2) VLOOKUP & XLOOKUP functions

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.

  • Formula: =VLOOKUP(What you want to lookup, Where you are looking for, The column number where the result is expected to be, Approximate match [0=identical] & [-1 or 1 = approximate])
  • Case Study: A company has two databases, consumer purchases and product information. The first contains data on consumer’s name, their country and product sold. The second database contains merchandise related information such as the Product Type and Price. The request of your manager is to reconcile the price in database 2 into database 1.
    Answer: By setting up a new column in database 1 and building a VLOOKUP formula that looks up the product type in the same row to the database 2, it is possible to quickly determine purchase price for all items in database 1.
    Note: To ensure that the search in database 2 goes smoothly, it is important to use the $ sign for absolute referencing.
Learning to use vlookup in excel functions

3) SUM & SUMIFS functions

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.

  • Formulas: =SUM(number1, number2,…)
          =SUMIF(sum_range, criteria_range1,criteria1, criteria_range2,criteria2,…)
  • Case Study: Using the database in the previous example and assuming the purchase price was the amount spent. A manager requests that you check to see how much money was made selling Zippers in Mexico.

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

Learning to use sumifs in excel functions

4) AVERAGE, MEDIAN & STDEV.S functions

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.

  • Formulas: =AVERAGE(number1,number2,…)
                        =MEAN(number1,number2,…)
                        =STDEV.S(number1, number2, …)
  • Case Study: A manager is requesting that you provide the average, median and standard deviation for the purchase prices in database 1 (the same database used in previous examples).

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.

Avg, mean and stdev functions in excel

5) COUNT & COUNTIFS functions

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

  • Formulas: =COUNT(value1, value2, value3,…)
        =COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)
  • Case Study: Following previous patterns, your manager requests that you confirm the number of items in the database that have a purchase price. She also asks that you provide the number of sales made to French customers.
Countifs function in excel

6) CONCATENATE (CONCAT) functions

What does concatenate do?  The Concatenate or CONCAT function helps users combine two or more strings (cells) into one.

  • Formula: =CONCATENATE([text1], [text2], [text3]…)
  • Case Study: A company needs to ship 100 products to a variety of clients, unfortunately the data is divided into 3 segments: First Name, Last Name, and Address.
    Answer: If someone were to create the shipping sticker manually, they would need to copy and paste each segment. By using concatenate, the company is able to add all 3 segments into one. It could even add its name at the end! The imagine below can shed some light into the solution. Please note that “ “ provides a much needed space, “ @ “ serves as a clear denomination of where it will go and “ – Sent by GPetrium” clarifies the sender.
Concatenate function in excel

7) AND & OR functions

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.

  • Formulas: =AND(logical1, logical2, logical3,…)
        =OR(logical1, logical2, logical3,…)
  • Case Study: Your manager would like to see the purchases that were made in the United States with a price of $5 or higher. On a different request, she would also like to see all the entries in the database that happened in Germany or under product type ‘Bottle’.
And or functions in excel

Specify the criteria to be identified within the quotation marks (“ “)and input ‘>=’ to identify prices that are greater than or equal to.

8) SEARCH function

What is the SEARCH function? Search is used to find characters within a cell and where it is situated.

  • Formulas: =SEARCH(find_text, within_text, [Start_Num])
  • Case Study: In the database provided, the Lewis family is seen as a major buyer of the company’s products. Your manager wants to see all the purchases that have been made under the name Lewis.

    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.

Making use of search function in excel

9) MAX & MIN functions

   

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.

  • Formulas: =MAX(number1, number2, number3,…)
        =MIN(number1, number2, number3,…)
  • Case Study: Utilizing the previous database, your manager wants to know the highest and lowest purchase price.
Making use of search function in excel

10) IF & IFS functions

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.

  • Formulas: =IF(logical_test, [value_if_true], [value_if_false])
        =IFS(logical_test1, [value_if_true1], logical_test2, [value_if_true2],…)

  • Case Study: Based on the database, your manager wants to know whether the organization has surpassed its high expectations (e.g. $2,000). If not, the manager wants to know whether it managed to achieve the base expectation (e.g. $1,000).
    Answer: To properly establish the logical test, it is important to set the sum of purchase price [sum(##:##)] to be (=>) greater than or equal to $2,000, if that is true, it outputs ‘Goal Achieved’. For the multiple IFS statement, it has been determined that if ‘Major Goal is not achieved’, it then checks to see if the base goal is achieved. If the answer is no, it will output ‘Goal Failed’.
Ifs function in excel

Our Latest Posts

RASCI Template

RASCI Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                The RASCI matrix is a visual business tool used to define the connection between members and their

Read More »

Communication Plan Template

Communication Plan Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                Communication helps instill important information between different parties, helping to build relations, drive collaboration and much

Read More »

Team Charter Template

Team Charter Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                Team charters can help teams, projects and organizations build the rules of engagement and roadmap needed

Read More »

Check Register Template

Check Register Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                Financial transactions have existed throughout history, helping buyers and sellers exchange goods based on their needs.

Read More »

Employee Training Tracker

Employee Training Tracker Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                Training is a great way to learn or hone the skills relevant to one’s professional career

Read More »

Bi-Weekly Budget Tool

Bi-Weekly Budget Tool Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp                There are many cases where employees or contractors are paid bi-weekly for their effort. To some,

Read More »
Receive a monthly newsletter with updates, insights and solutions from GPetrium!