A formula is a mathematical expression that computes a cell’s value. Functions are predefined formulas found in Excel. Based on the values specified as arguments or parameters, functions perform specific calculations in a specific order. For instance, =SUM (C1:C10). This function adds the values from cells C1 to C10. In the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories, several new functions are added. In addition, the Web category is introduced, along with a few Web service features. Some Basic functions in Excel were discovered earlier that can be used to perform basic operations.
We also have a Statistical function in Excel that can be used in industries and businesses for better and more effective results.
Categories of Functions
Excel functions are classified according to their functionality. If you know the category of the function you’re looking for, you can select it.
Step 1: Select the FORMULAS tab. The group Function Library appears. The function categories are contained within the group.
Step 2: Navigate to the More Functions tab. More function categories will be shown.
Step 3: Select a function category. All of the functions in that category will be shown. As you browse through the functions, the syntax and usage of the function will be displayed.
New Functions in Excel 2019
Excel 2019 has six new functions that will change the way you work with data. These functions may be visible in Excel 2016 if you have the most recent upgrades installed.
Concat Function
This function is superior to CONCATENATE. It enables you to merge words, strings, or ranges into a single string, saving you time and energy.
Syntax: CONCAT(text1,[text2],…)
- text1: The first text string or range of cells.
- text2: The second text string or range of cells (optional).
To utilize this function:
- Click the Insert function dialog box on the Formulas tab.
- Enter CONCAT in the “Search for a Function” dialog box, then click the GO button.
- The dialog box below will appear; select CONCAT and click OK.
- The first and second text strings or cell ranges will show; enter the text and click OK.
IFS Function
This function returns a value depending on multiple True or False conditions.
Syntax: IFS(logical_test1, value_if_true1 [,logical_test2, value_if_true2] [..])
- logical_test1: A condition that can be True or False.
- value_if_true1: Result if the logical value evaluates to True.
- logical_test2: A condition that can be True or False (Optional).
-
value_if_true2: Result if the logical value evaluates to True (Optional).
To utilize this function:
- Click the Insert function dialog box on the Formulas tab.
- Enter IFS in the “Search for a Function” dialog box, then click the GO button and select OK.
- The logical_test1 and value_if_true1 will appear; enter the text and click OK.
TEXTJOIN Function
This function returns the text string that is a concatenation of several strings (with delimiter).
Syntax: TEXTJOIN(delimiter, ignore_empty, text1 [,text2] [..])
- delimiter: One or more delimiter characters.
- ignore_empty: A logical value indicating if empty cells are ignored: True = Yes, False = No.
- text1: The first text item.
- text2: The second text item (Optional).
To Utilize this Function:
- Click the Insert function dialog box on the Formulas tab.
- Enter TEXTJOIN in the “Search for a Function” dialog box, then click the GO button and select OK.
- The delimiter, ignore_empty, and text1 dialog boxes will appear; enter the text and click OK.
MAXIFS Function
This function returns the largest value in a list or array of numbers that satisfy multiple conditions.
Syntax: MAXIFS(max_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])
- max_range: The actual cells to the max.
- criteria_range1: The range of cells you want to be evaluated.
- criteria1: The expression that contains the criteria.
- criteria_range2: The range of cells you want to be evaluated (Optional).
- criteria2: The expression that contains the criteria (Optional).
To Utilize this Function:
- Click the Insert function dialog box on the Formulas tab.
- Enter MAXIFS in the “Search for a Function” dialog box, then click the GO button and select OK.
- The delimiter, ignore_empty, and text1 dialog boxes will appear; enter the text and click OK.
MINIFS Function
This function returns the smallest value in a list or array of numbers that satisfy multiple conditions.
Syntax: MINIFS(min_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])
- min_range: The actual cells to min.
- criteria_range1: The range of cells you want to be evaluated.
- criteria1: The expression that contains the criteria.
- criteria_range2: (Optional) The range of cells you want to be evaluated.
- criteria2: (Optional) The expression that contains the criteria.
To Utilize this Function:
- Click the Insert function dialog box on the Formulas tab.
- Enter MINIFS in the “Search for a Function” dialog box, then click the GO button and select OK.
- The min_range and criteria_range1 dialog boxes will appear, enter the text and click OK.
SWITCH Function
This function returns the value based on a list of exact matches.
Syntax: SWITCH(expression, value1, result1 [,value2, result2] [,default])
- expression: The expression to be compared to the values.
- value1: The value to compare.
- result1: The result if the expression equals the value.
- value2: The value to compare (Optional).
- result2: The result if the expression equals the value (Optional).
- default: The value to return when there are no matches (Optional).
To Utilize this Function:
- Click the Insert function dialog box on the Formulas tab.
- Enter SWITCH in the “Search for a Function” dialog box, then click the GO button and select OK.
- The expression and value1 dialog box will appear; enter the text and click OK.
FAQs on New Functions in Excel
Q1: What are the Functions in Excel?
Answer:
Functions are a predefined formula that performs a specific calculation or return a specific value based on the data inserted. Function reduce the human work to perform all calculations manually.
Functions can be used to perform mathematical operations, manipulate text, analyze data, and many more.
Q2: What are the new functions that are present in Excel?
Answer:
The new functions in Excel are:
CONCAT Function
IFS Function
TEXTJOIN Function
MAXIFS Function
MINIFS Function
SWITCH Function
Q3: Can new functions be used in older versions of Excel?
Answer:
New Functions are introduced in recent versions of Excel and they are not available in older versions. You may not have access to the latest functions unless you upgrade to a newer version or use Excel online or Excel 365
Q4: What is the Syntax of the CONCAT Function?
Answer:
Syntax: CONCAT(text1,[text2],…)
- text1: The first text string or range of cells.
- text2: The second text string or range of cells (optional).