With a new year comes a new budget and a chance to take control of your money. Whether you want to remodel your home or just want to keep on top of your personal or family budget, Money in Excel makes it easy to stay on track and reach your financial goals. About New Data Types in Excel With added AI capabilities, Excel can now recognize rich data types beyond numbers and text strings. For example, Excel recognizes that “France” is a country and automatically associates it with additional attributes such as population and gross domestic product. As you are no doubt aware, Microsoft added many new features with the release of Excel 2019. Further, the tech giant continues to update the venerable spreadsheet application through updates to Office 365.Of course, these new features do not pay dividends unless you are aware of.

Excel is a great tool for reporting, analyzing, organizing and automating data. The Excel Functions help a great deal with working on data. The functions like COUNTIFS, SUMIFS, VLOOKUP, etc are the most powerful and frequently used functions since there inception in Excel world.

Although the functions available in Excel 2016 and older are enough to work out any kind of calculation and automation, but sometimes the formulas get tricky. For example, if you won't find the maximum value with some conditions, you have to use some tricks in the 2016 older Excel version. These kinds of minor but important things are solved in Excel 2019 and 365.

There are 10+ new functions in Excel 2019 and 365 that reduce the human effort and complexity of the formulas.

1. The MAXIFS Function

In excel 2016 and older, if you want to get the maximum value in a range when one or more conditions match, you have to use MAX with IF with some tricks. That's not much difficult but time taking and confusing to some.

The Excel 2019 introduces a new function named MAXIFS function. This function returns the maximum value from an array when all the given conditions are matched.

The syntax of the function is:

Max_range1: It is the numeric range that contains the max value.
Criteria_range1: It is the criteria range that you want to filter before getting max value.
Criteria1: It is the criteria or the filter that you want to put on criteria_range before getting the Max value.

Suppose you need to get the maximum marks from class 3 then the formula will be

Here marks are the named range that contains the marks and class is the named range that contains the class.

Read about MAXIFS function in detail here.

2. The MINIFS Function

Same as MAXIFS function the MINIFS function is used to get the minimum value from the given range when all of the given conditions are satisfied.

The syntax of the function is:

Min_range1: It is the numeric range that contains the minimum value.
Criteria_range1: It is the criteria range that you want to filter before getting minimum value.
Criteria1: It is the criteria or the filter that you want to put on criteria_range before getting the minimum value.

Suppose you need to get the minimum marks from class 3 then the formula will be

Here 'marks' is the named range that contains the marks and 'class' is the named range that contains the class.

Read about MAXIFS function in detail here.

To find minimum value from in range with conditions in excel 2016 and older read this.

3. The IFS Function

As the nested Ifs has a special place in our daily work life, we like it very much. But to some new learners, it is complex. The nested ifs let us check multiple conditions and return a different value when any of the conditions is met. The formulas get complex with more and more IF in function.

The Excel 2019 and Excel 365 now use IFS function. It can check multiple conditions and return different values for each condition.

Syntax of IFS function:

Condition1: The first condition.

Value1_If_True: Value if the first condition is true.

[Condition2]: This is optional. The second condition, if you have any.

[Value1_If_True]: Value if the second condition is true.

You can have as many combinations of conditions and values you want. There's a limit but you will never need to reach that limit.

Let us say that you need to give the grades to the students by there marks. For marks more than 80, grade A, B for more than 60, C for more than 40 and F for less than or equal to 40.

A detailed explanation of the IFS function can be found here.

4. The SWITCH Function

The switch function returns different values depending on the one expression results. Sounds like IFS? It is kind of. Actually this function is for replacing another kind of nested IF formulas.

Unlike the IFS function that returns values based on TRUE, FALSE; the SWITCH function return values based on VALUES returned by the expression.

Expression: This can be any valid expression that returns some values. A cell reference, a formula or static value.

Value1,result1: The value and result are paired. If the value returned by the expression is value1 then the result1 will be returned.

[Default or value2,result2]: If you want to return some default value, define it here. Else define the value2 and result2. It is optional.

For example, if you have a formula that returns the names of animals. Now, depending on the returned name of the animal you want to return the signature sound of that animal.

I have explained the SWITCH function in detail here.

5. The FILTER Function

The FILTER function is used to filter data based on some criteria. We have used the filter option from the home tab in Excel. The FILTER function works the same as the filter option. It just returns the filtered data using a function. This filtered data can be used as a source of data to other formulas.

The syntax of the FILTER function is:

Array: This is the array that you want to filter. It can be one dimensional or two dimensional.

Include: It is the filter that you want to put on the Array. Like, colors='red'.

[if_empty]: This is optional. Define any text or expression if the filter returns nothing.

The below formula returns all the fruits whose color is red.

Here fruit and color are named ranges that contain names of the fruits and their colors, respectively.

You can read about the FILTER function in detail here.

6. The SORT Function

In excel 2016 and older, it was really tricky to get a sorted array using a formula. This process is simplified in Excel 2019 and 365.

The Excel 2019 introduces function SORT. The SORT function sorts the given array in ascending or descending order by the given column/row.

The syntax of the SORT function is:

Array: It is the reference of array or range that you want to sort.

[sort_index]: The column number in the two-dimensional array by which you want to sort the range. By default, it is 1.

[sort_order]: The order by which you want to sort the array. For ascending it is 1 and for descending it is -1. By default, it is 1.

New Excelsior Plates

[by_col]: Set it True(1) if you want to sort a horizontal array. By default it False(0) for Vertical data.

Let's say if you want to sort values in the range A2:A11 ascendingly. then the formula will be.

I have explained the SORT function in detail here.

7. The SORTBY Function

The SORTBY function is similar to the SORT function. The only difference is the sorting array does not need to be the part of the sorted array in the SORTBY function.

Array: This is the array that you want to sort.

Sorting_array1: This is the array by which you want to sort the array. The dimension of this array should be compatible with the array.

[order]: Optional. Set it to -1 if you want the order to be descending. By default, it is ascending(1).

Let's say if you want to sort the range A2:A11 by range B2:B11, in descending order. Then the formula in excel 2019 or 365 will be:

I have explained the SORTBY function here in detail.

8. The UNIQUE Function

In Excel 2016 and older, we used a number of functions in combination to get all the unique values from the given list. The formula used is quite complex and hard to understand.

The Excel 2019 and 365 introduces one simple UNIQUE function that returns all the unique values from a given array.

The syntax of the UNIQUE function is:

Array: The array from which you want to extract unique values:

[by_col]: Set it TRUE(1) if the array is horizontal. By default, it is FALSE for vertical data.

[exactly_once]: set it TRUE(1) if you want to extract values that occur only once in the array. By default, it is FALSE(0) to extract all unique values.

Let's say I want to get only one instance of each value from range A2:A11, then the formula will be:

To read about the UNIQUE function in detail you can click here.

9. The SEQUENCE Function

To get a sequence of numbers in excel 2016 and older we use a combination of functions. The solution does work but it is complex.

The Excel 2019 and 365 provides the solution in the form of the SEQUENCE function. The sequence function simply returns the series of the number.

The syntax of the SEQUENCE function is:

Rows: The number of rows to which you want to spill the sequence.

[column]: The number of columns to which you want to spill the sequence. The numbers will first fill in the columns and then rows. The column is optional. By default, it is 1.

[start]: Optional. The starting number of the sequence. By default, it is 1.

[step]: This the increment number for the next number. By default, it is 1.

The simple example is getting a series of 1 to 10. The formula will be:

To understand the SEQUENCE function in Excel 365 in detailed read this.

10. The RANDARRAY Function

This is another dynamic array formula that returns an array of random numbers. It is a combination of RAND and RANDBETWEEN function. You can get fractional random numbers or whole numbers. You can specify the number of random numbers you want. Even rows and columns in which you want to distribute these numbers.

The syntax of the RANDARRAY function is:

All the arguments in this function are optional. By default, it works as the RAND function.

[rows]: The number of numbers that you want vertically (number of rows you want to fill).

[columns]: The number of numbers that you want horizontally (number of columns you want to fill).

[min]: The starting number or the minimum value of the random number/s.

[max]: The maximum range of the number.

[integer]: Set it true, if you want the random numbers to be whole numbers. By default, it is false and returns fractional random numbers.

The below function will return five random fractional number row-wise:

Read about the RANDARRAY function in detail here.

11. The CONCAT Function

In Excel 2016 and older, it is not easy to concatenate more than one cell or range using one formula.

The excel 2019 and 365 the problem is solved with the function CONCAT. The function can take multiple cells, ranges as arguments.

The syntax of the CONCAT function is :

Text1 : The text1 can be any text or range that you want to concatenate.
[text2]: This is optional. This also can be any text or range.

Let's say if you want to concatenate each cell in range A2:A11, then the formula will be

To explore the CONCAT function in detail click here.

12. The TEXTJOIN Function

The above function does concatenate all the cells in a range but it does not concatenate the cells with any specified delimiter. Let's say if you are preparing a file for CSV formate than you will need to concatenate the cells with comma. In that case CONCATENATE and CONCAT function, both will fail.

Here the TEXTJOIN function works wonder and concatenates the given texts with the given delimiter.

Delimiter: This is the delimiter that you want to use a separator among individual texts. It can be a comma (,), semicolon (;) or anything, even nothing.

Ignore_empty_cells: This is a binary variable. Set it to TRUE if you want to ignore empty cells in ranges, else set to FALSE to include the empty cells.

Text1: This is the text that you want to join. It can be individual texts, cells or entire ranges.

Let's say I want to concatenate range A2:A11 with a comma, ignoring the blank cells.

To understand this function in detail, click here.

This article was only an introduction to the new function of Excel 365 and 2019. I have explained these functions in detail in separate articles. You may click on the links available on each function in the article to understand the function fully. There are other functions like XLOOKUP that are not released yet.

If you have any doubts regarding Excel or VBA topics, ask in the comments section below. Tell us how can we improve. We appreciate your suggestion and eager to hear from you.

Related Articles:

Create VBA Function to Return Array | To return an array from user defined function, we have to declare it when we name the UDF.

Arrays in Excel Formul|Learn what arrays are in excel.

How to Create User Defined Function through VBA | Learn how to create user-defined functions in Excel

Using a User Defined Function (UDF) from another workbook using VBA in Microsoft Excel | Use the user-defined function in another workbook of Excel

Return error values from user-defined functions using VBA in Microsoft Excel | Learn how you can return error values from a user-defined function

Popular Articles:

Split Excel Sheet Into Multiple Files Based On Column Using VBA | This VBA code split excel sheet base on unique values in a specified column. Download the working file.

Turn Off Warning Messages Using VBA In Microsoft Excel 2016 | To turn off warning messages that interrupt the running VBA code, we use the Application class.

Add And Save New Workbook Using VBA In Microsoft Excel 2016 | To add and save workbooks using VBA we use Workbooks class. Workbooks.Add adds new workbook easily, however...

How to open Excel workbooks in new windows (open multiple windows)?

In this tutorial, we will show you how to open Excel workbooks in different windows steps by steps.

Open Excel workbooks in new windows with holding Shift key and clicking in Taskbar

For the usage of lowest versions of Excel, with holding the Shift key and clicking the Excel icon in the Taskbar, you can open Excel workbooks in multiple windows.

1. Firstly you need to open a workbook in advance, and then hold the Shift key on the keyboard.

2. Click the Excel icon in the Taskbar. See screenshot:

Then a new blank workbook is opened. If you need to edit the new opened workbook, just edit and save it. Otherwise, you can open another workbook you have created before in this new workbook window.

3. Click File (Office button) > Open from your new created workbook just now, In the Open dialog box, find and select the workbook and then click the Open button.

Then you can see the workbooks are opened in two separate Excel windows. You can repeat the above steps to open more Excel windows.

Open Excel workbooks in new windows with Start menu

Besides the above method, you can open Excel workbooks in new windows with Start menu. Please do as follows.

1. Click the Start button to show the menu. Type Excel in the Search box, then click Microsoft Excel in the Programs section. See screenshot:

2. When a new blank workbook opened, please click File (Office button) > Open, In the Open dialog box, find and select the workbook you want to open and then click the Open button.

3. To open multiple workbooks you want, you just need to repeat the above steps one by one.

Open Excel workbooks in new windows with creating shortcut

The last method for opening Excel workbooks in new window is to create a shortcut. Please do as follows.

1. Open the Office folder in your computer with following paths.

In Excel 2010: C:Program FilesMicrosoft OfficeOffice 14

In Excel 2007: C:Program FilesMicrosoft OfficeOffice12

New Excel Functions 2020

2. In the opened Office folder, find the EXCEL icon, right click it and then select Create shortcut from the right-clicking menu. See screenshot:

3. Then a Shortcut prompt box pops up, click the Yes button.

4. Then an Excel shortcut is placed on the desktop. Double click it, and a new Excel window will be opened. Then you can click File (Office button) > Open to open the workbook that you need.

5. If you need to open multiple workbooks in new windows, you should repeat step 4 to open them one by one.

Note: For Excel 2013, the workbooks are opened in different windows by default.

Open Excel workbooks in new windows with Office Tab

In this section, I will show you a handy applications – Office Tab. With the Open In New Window feature of Office Tab, open workbooks in new windows is not a problem anymore.

Microsoft Excel 2016

1. After installing Office Tab, open the workbooks you want. Then you will see all workbooks are opened in an Excel window as tabbed browsing status.

2. If you want to open a specific workbook in a new window, please right click this workbook tab, and then click Open In New Window in the list. See screenshot:

Then the selected workbook is opened in a new window as below screenshot shown:

Tip.If you want to have a free trial of this utility, please go to Click for free trial of Office Tab first, and then go to apply the operation according above steps.

New Excel Sheet

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

New Excel Sheet

Open New Excel Spreadsheet

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

New Excelsior Plates

• To post as a guest, your comment is unpublished.
  if you do send to desktop you can avoid the pop-up asking you to put the shortcut on the desktop
• To post as a guest, your comment is unpublished.
  Thanks for your information guys, very very usefull