Last updated on July 26th, 2023 at 04:01 pm
Microsoft Excel 2019 is an updated version that updates its function and features to the user who can use their MS Office Excel. This Microsoft Office Excel changed functions such as CONCAT, IFS, MAXIFS, MINIFS, SWITCH, TEXTJOIN, etc. In this blog, we are learning about these new functions and how to use them.
CONCAT
CONCAT is replaced by the CONCATENATE Function, it also called CONCAT is a new version of CONCATENATE. This function is shorter and easier to type, It can also, support range locations in addition to cell locations.
Syntax
CONCAT (text1, [text2]…)
| Argument | Description |
| text1 (required) | Text item to be merged. A string, or array of strings, such as a range of cells. |
| [text2, …] (optional) | Other text items to be merged. There can be a maximum of 253 text opinions for the text items. Each can be a string, or array of strings, like a range of cells. |
For example, =CONCAT(“This”,” “,”is”,” “,”an”,” “,”Example.”) will return This is an Example.
Notes
If the resultant string exceeds 32767 characters (cell limit), CONCAT returns the #VALUE! error.
Example
Follow the example data in each of the following tables and put them in cell A1 of a new Excel workbook. The methods are to show results, select them, press F2, and then press Enter. You can also, adjust the column widths to understand all the data.
| =CONCAT (B: B, C: C) | D’s | E’s |
| d1 | e1 | |
| d2 | e2 | |
| d4 | e4 | |
| d5 | e5 | |
| d6 | e6 | |
| d7 | e7 |
This function can let full column and row references, it returns this result: D’sd1d2d4d5d6d7E’se1e2e4e5e6e7
MINIFS
MINIFS function is like MAXIFS, but it returns the smallest number in a series, that sees single or multiple values.
Syntax
MINIFS(min_range, standards_range1, standards1, [standards_range2, standards2], …)
| Argument | Description |
| min_range (required) | An actual range of cells in which the minimum value will be gritty. |
| standards_range1 (required) | A set of cells to assess with the standards. |
| Standards1 (required) | The standards in the form of a number, expression, & text that defines which cells will be assessed as a minimum. The equal set of standards works for the MAXIFS, AVERAGEIFS, and SUMIFS functions. |
| standards_range2, criteria2, …(optional) | Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs. |
Example
Follow this example data in each of the following tables and put them in cell A1 of a new Excel worksheet. This method is showing the results, first-rate them, press F2 and then press Enter. You can correct the column widths to see all the data.
Example
| Grade | Weight |
| 89 | 1 |
| 93 | 1 |
| 97 | 2 |
| 86 | 3 |
| 91 | 1 |
| 82 | 2 |
| Formula | Result |
| =MINIFS(A2:A7,B2:B7,1) | 93 In standards_range1 the cell B2, B6, and B7 match the standards of 1. Of the corresponding cells in min_range, A7 has the minimum value. The result is therefore 93. |
IFS
Weary of entering difficult, nested IF functions? Here is the IFS function is the answer. Using this function, conditions are verified in the command that you specify. If the function is passed, then the result is returned. Users can also specify an else if none of the conditions are seen.
Simple syntax
Usually, the syntax for the IFS function is:
=IFS([Somewhat is True1, Value if True1,Somewhat is True2,Value if True2,Somewhat is True3,Value if True3)
Note this IFS function permits you to trial up to 127 changed situations. Still, we don’t mention nesting too many situations with IF or IFS statements. The multiple conditions need to be out in the correct direction, and it can be very tough to size, test and update.
Technical Syntax
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
| Argument | Description |
| logical_test1 (required) | State that assesses to TRUE or FALSE. |
| value_if_true1(required) | Result to be returned if logical_test1 assesses to TRUE. Can be empty. |
| logical_test2…logical_test127(optional) | State that assesses to TRUE or FALSE. |
| value_if_true2…value_if_true127(optional) | Result to be returned if logical_N_test assesses to TRUE. Each value_if_N_true agrees with a condition logical_N_test. Can be empty. |
Example 1
The formula for cells A2:A6 is:
- =IFS(A2>89,”A”,A2>79,”B”,A2>69,”C”,A2>59,”D”,TRUE,”F”)
Which speaks IF(A2 is Greater Than 89, then return a “A”, IF A2 is Greater Than 79, then return a “B”, and other and for all other values less than 59, return an “F”).
Example 2
The formula in cell G7 is:
- =IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
Which speaks IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2 equals 2, then return the value in cell D3, and other, the ending with the value in cell D8 if none of the other conditions are seen).
MAXIFS
The MAXIFS function revenues the largest number in a series, that sees single or multiple principles.
Syntax
MAXIFS(max_range, standards_range1, standards1, [standards_range2, standards2], …)
| Argument | Description |
| max_range (required) | The real series of cells in which the maximum will be gritty. |
| values_range1 (required) | Is the set of cells to appraise with the principles |
| Standards1 (required) | The principles in the form of a number, appearance, or text that describes which cells will be assessed as maximum. The same set of principles works for the MINIFS, SUMIFS, and AVERAGEIFS functions. |
| standards_range2, standards2, …(optional) | Other ranges and their related principles. User can enter up to 126 range/principles sets. |
Example
Follow the example data in each of the following tables and put them it in cell A1 of a new Excel worksheet. This method shows results, first-rate them, press F2, and then press Enter. If you need to, you can change the column widths to see all the data.
Example
| Grade | Weight |
| 89 | 1 |
| 93 | 1 |
| 97 | 1 |
| 86 | 3 |
| 91 | 2 |
| 82 | 2 |
| Formula | Result |
| =MAXIFS(A2:A7,B2:B7,1) | 97 In standards_range1 the cells B2, B6, and B7 match the standards of 1. The consistent cells in max_range, A6 has the maximum value. The result is therefore 97. |
SWITCH
The SWITCH function appraises an appearance against a list of values in instruction and revenues for the first similar result. If no consequences match, the “else” is returned.
Syntax
SWITCH(appearance, value1, result1, [default or value2, result2],…[default or value3, result3])
| Argument | Description |
| appearance (required) | Appearance is the value (like a number, date or text) that will be equated against value1…value126. |
| value1…value126 | N_Value is a value that will be equated against appearance. |
| result1…result126 | N_Result is the value to be returned when the consistent N_value argument matches appearance. N_Result and must be provided for each consistent N_value argument. |
| default (optional) | Default is the value to return in case no matches are found in the N_value terms. A Default argument is known the having no consistent N_result expression. Default necessity to the final argument in the function. |
Here is the function partial to 254 opinions, you can use up to 126 couples of value and result in opinions.
Overview
In its humblest form, the SWITCH function says:
=SWITCH(Value to switch, Value to match1…[2-126], Value to return if there’s a match1…[2-126], Value to return if there’s no match)
You can appraise up to 126 matching values and results.
See the following formula:
Need to switch Worth? In this situation, WEEKDAY(A2) equals 2.
Do you want to match the worth? In this situation, it’s 1, 2 and 3.
If there is a match, what do you want to return as a result? In this situation, it would be Sunday for 1, Monday for 2, and Tuesday for 3.
The default value is returned if there’s no match found. In this situation, it’s the text “match not found”.
Note: If there are no matching values, and no default argument is provided, the SWITCH function returns the #N/A! error.
Examples
You can follow the example data in the following table and put them in cell A1 of a new Excel worksheet to understand the SWITCH function in the act. In case the formulas don’t show the results, then you can select them, and press F2 > Enter. You can change the column sizes to see all the data.
Example
| Value | Formula | Result |
| 2 | =SWITCH(WEEKDAY(A2),1,”Sunday”,2,”Monday”,3,”Tuesday”,”no-match”) | Because A2=2 and Monday is the result argument corresponding to the value 2, SWITCH returns Monday |
| 6 | =SWITCH(A3,1,”Sunday”,2,”Monday”,3,”Tuesday”) | Because there’s no match and no else argument, SWITCH returns #N/A! |
| 10 | =SWITCH(A4,1,”Sunday”,2,”Monday”,3,”Tuesday”,”match not found”) | No match |
| 2 | =SWITCH(A5,1,”Sunday”,7,”Saturday”,”weekday”) | weekday |
| 3 | =SWITCH(A6,1,”Sunday”,2,”Monday”,3,”Tuesday”,”match not found”) | Tuesday |
TEXTJOIN
The TEXTJOIN role is to syndicate text from several ranges, and each item is separated by a delimiter that you require.
Syntax
TEXTJOIN(delimiter, ignore empty, text1, [text2], …)
| argument | Description |
| delimiter (required) | The text string, also empty, or one or more fonts surrounded by double quotes or a situation to a valid text string. If a number is provided, it will be preserved as text. |
| ignore_empty (required) | Other text items are to be merged. The maximum of 252 text influences for the text items, with text1. Separately a text string, or array of strings, like a range of cells. |
| text1 (required) | The Text item is to be merged. A text string, or array of strings, is like a range of cells. |
| [text2, …] (optional) | Other text items to be merged. The maximum of 252 text influences for the text items, with text1. Separately a text string, or array of strings, like a range of cells. |
For example, =TEXTJOIN(” “, TRUE, “Hello”, “good”, “morning.”) will return Hello good morning.
Example
Follow the example data in each of the following tables and put them in cell A1 of a new Excel worksheet. The methods are to show results, first-rate them, press F2, and press Enter. You can adjust the column widths to understand all the data.
Example
| Currency | |
| US Dollar | |
| Australian Dollar | |
| Chinese Yuan | |
| Hong Kong Dollar | |
| Israeli Shekel | |
| South Korean Won | |
| Russian Ruble | |
| Formula: | =TEXTJOIN(“, “, TRUE, A2:A8) |
| Result: | US Dollar, Australian Dollar, Chinese Yuan, Hong Kong Dollar, Israeli Shekel, South Korean Won, Russian Ruble |
The secret thing about Microsoft Office Password Protection
In the above topic, we discuss and learn about the new functions of Microsoft Excel 2019. But MS Office Excel is not changed its security function after and before the updated Office version. This security function is the most important feature to protect Excel Workbook with the password.
Here is the one major problem in Microsoft Security Section, when you protect the file with the password then Microsoft Office has strictly worn the users and shows the message “If you lost or forgot Password it cannot be recovered”. But the person is a human being and they can lose and forget the password. Then you need a third-party tool that will help you to bring back the password.
So what should you do for a Lost Excel Password?
Here we suggest you, use Excel Password Recovery Software by eSoftTools and solve this type of problem without losing any data. Users can easily unlock, & remove passwords from Excel, Word, & Access files through the help of Dictionary Attack, Mask Attack, & Brute Force Attack techniques. It supports all MS Excel versions up to 2019 and Windows versions up to 10 with FREE DEMO VERSION.
Visit Here:- Excel Unlocker Tool
Here is the working process to show the steps for easily Recover, Remove, & Unlock Excel workbook Password protection. This program shows you to choose the right options and instantly Remove Excel (.xla, .xlam, .xls, .xlsm) file passwords.
