Vacationloading658

1. 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.
2. New and improved connectors. We have new connectors in Excel 2019. For example, there's the new SAP HANA connector. We also have improved many of our existing connectors so that you can import data from a variety of sources with efficiency and ease. For more information on our connectors, see Import data from external data sources.
3. About New Excel; Strong Distribution Network; MEDIA & SERVICES. Hong Kong Shopping Tour; Marketing & PR Services. China Media Relations; Light Box; LED TV; Brochure, Leaflet and Map Design & Distribution; Publications. Hong Kong Guide; HKG - Hong Kong Edition; HKG - Map Edition; HKG - MTR Map Edition; Hong Kong Map; VPM Seasons; VPM CRH; VPM.

A workbook is a file that contains one or more worksheets to help you organize data. You can create a new workbook from a blank workbook or a template. By default, a new workbook contains three worksheets, but you can change the number of worksheets that you want a new workbook to contain. You can also add and remove worksheets as needed. 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.

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.

New Excel Functions

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

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

Earlier this fall, Microsoft announced the release to the general public of its Office 2019 suite. The 2019 version of Excel contains a wide assortment of new and improved features and capabilities. Here's a look at 12 of those upgrades, all of which I have downloaded, installed, and tested:

1. Data import tool improvements: The Power Query data import tool in Excel 2013 was renamed Get & Transform in Excel 2016, but the name has reverted to Power Query in Excel 2019, bringing it in line with the name conventions used by the related Microsoft products Power Pivot, Power Map, and Power BI. The improved Power Query tool (pictured below) provides the ability to import data from external data sources (such as your accounting system) and then scrub and clean the data (referred to as transforming the data) before placing them in Excel. Some of the transforming capabilities include the ability to group data, rename column headings, transpose data, reverse rows, detect data types, replace values, split columns, merge columns, unpivot columns, invoke custom functions, create conditional columns, and parse data.

This tool makes it faster, easier, and more accurate to import data from external sources for manipulation and analysis in Excel.

2. Automated workflow: Excel 2019 now connects better to Microsoft Flow, which allows you to create automated workflows in which data are retrieved from other applications and then stored and/or manipulated in Excel. The dataflow can be further programmed to automatically send to or share those data with other people or third-party applications. For example, Flow might be used to collect favorable customer tweets about your goods or services on your Twitter account, and then store them in your Excel workbook (on your OneDrive or SharePoint platform). These Twitter data could then be pushed periodically (every Monday morning, for example) to selected recipients through email or text messages, or published directly to websites or other data destinations.

Flow can retrieve your data from practically anywhere — your accounting system, ERP system, CRM system, stock price databases, weather forecast databases, email messages, reminder notices, Salesforce.com, etc. As examples, you could use this technology to track your hours worked, travel locations, Facebook messages, Instagram posts, travel expenses, accounts receivable, etc. To create a new data flow, start at the My Flows webpage at login.microsoftonline.com, sign in to your account using your Microsoft ID, and click the Get started button.

Click Create a flow from blank, as pictured below in the bottom right corner, and then select one of more than 200 triggers and specify the event you want to launch your automatic flow process. For example, the receipt of an email with a specific attachment may trigger an automated flow process. Continue by specifying the action(s) you want to take, such as opening the email attachment in Excel and running a specific macro to produce a PivotTable or PivotChart, then specify a connector for sharing the results through other Microsoft and third-party apps such as Outlook or SharePoint. Learn more about Flow at click.linksynergy.com.

3. 3D images: Excel 2019 now includes new 3D imaging capability in which you can rotate a 3D model of an object. While it's difficult to convey the concept in a picture, the screenshot below contains three copies of the same butterfly image, which I have rotated to suggest the effect you might achieve.

Excel now includes hundreds of 3D images, which are available from the Inserttab's 3D Models option. Image categories include animals, electronics, gadgets, dinosaurs, geology, space, dioramas, letters, biology, and more. A few examples of 3D graphic images are displayed in the screenshot below.

You can view my brief video demonstrating this functionality at youtu.be/VLzglo1WBZ0.

New Excel Sheet

4. Custom functions: Excel 2019 now enables users to create their own custom functions using JavaScript. Previous editions of Excel already enabled users to create user-defined functions using Excel's built-in Visual Basic for Applications programming language; however, since JavaScript has emerged as a commonly used platform for such purposes, Microsoft has added this new capability to allow for greater compatibility with third-party solutions.

5. New functions: Excel includes six new or improved functions, as follows: IFS, SWITCH, ­TEXTJOIN, CONCAT, MAXIFS, and MINIFS. IFS and SWITCH are simplified versions of the nested IF function, which eliminates the need for more complicated nested functions. For example, the screenshot below depicts both the nested IF approach (in red text) and the IFS approach (in blue text) for creating the formula in cell C16 that returns the proper tax base for income calculation purposes. While both approaches work, the IFS approach requires only one function while the IF approach requires three IF functions. Note that in this example the IFS approach produces a slightly longer formula because the IFS approach requires you to spell out each criterion, while the IF approach does not require you to spell out the last criteria.

New Excel Formulas

The TEXTJOIN function is a simplified version of the CONCATENATE function in which inserted text need not be encapsulated by quotation marks. The CONCAT function allows you to combine text from multiple cells without specifying a delimiter. The MAXIFS and MINIFS functions work like the SUMIF or COUNTIF functions in that they calculate only the maximum or minimum for those data that meet the criteria you specify. For example, an auditor might use these functions to find the lowest and highest invoice amounts between $50 and $100, as part of statistical sampling procedures.

6. Custom visuals: To provide more visual ways to display and present your data, Microsoft has incorporated its Power BI suite of apps into Excel. To access these new custom visuals, from Excel's Add-ins tab select Get Add-ins to display the Office Add-ins dialog box pictured below.

Scroll through the apps, select the one you want to use, and then set up the app to link it to your desired data. For example, I selected the free Microsoft People Graph app to create the chart below as linked from the data displayed in cells A1:B5.

Other available visual add-in apps include Word Cloud charts, Bullet charts, Speedometer charts, the Prevedere Forecasting Tools, Lucidchart Diagrams for Excel, and Supermetrics, to name a few.

Note: I counted more than 350 available Excel add-in apps as of September 2018; more apps may be added. (Most of these add-in apps are available for free, while some are priced from $1.49 to $13.99, and others have subscription prices ranging from $1.49 to $5.99 per month.)

7. Full SVG graphics support: Excel 2019 now supports scalable vector graphics (SVG), which means your graphic images won't pixelate when you increase the image size, as illustrated by the small and larger hiker image in the screenshot below. (Note that this technology applies only to drawings and graphic images; it does not apply to photos, videos, or animations.)

8. 500+ new built-in icons: Excel offers 500+ new built-in SVG-compliant icons for creating dashboards and infographics; examples of the new hiker and mountain icons are displayed above.

9. New forms capabilities:Excel 2019 now works in unison with Microsoft Forms to produce better looking documents to solicit responses to questions such as those included in surveys, polls, and quizzes. For example, using this new technology, you can produce survey forms and share them via links within Excel. To get started creating a new Form, visit forms.office.com, sign in if necessary, and click the New form button (if this is your first visit, you may see an overlay screen with a ­Create a new form button).

As you collect automated responses, you can open the survey results in Excel for further analysis or charting of the collected data.

Excel New Version Download

10. Map chart improvements: New options allow you to project your maps in Albers, Mercator, or Miller layouts (which basically means you can display your map charts as flat or curved). There are also new controls for selecting the map area to be displayed. For example, you might display a world map, even though the data span only the United States.

11. Chart improvements: New options in Excel 2019 allow you to better control axis information on your funnel charts, such as tick marks and number formats, and you can now also produce color-coded heat maps.

New Excel Lookup Function

12. Co-authoring: New co-authoring capabilities enable you to share your workbooks with others so multiple users can edit the same workbook at the same time when your file is stored on either the SharePoint or OneDrive platform. The sharing tool also allows you to set permissions for collaborators, and workbooks can be shared with individuals or groups of users (see the screenshot below).

New Excel Sheet

When a workbook is shared in this manner, the names of all active collaborators are displayed in the upper right corner. Also displayed are the worksheet and cell addresses where collaborators are actively editing. All changes are shared almost immediately with other collaborators, so there is little lag time between user updates (previously all changes made between shared workbooks were updated for all collaborators every 15 minutes by default). Workbooks can be shared in this manner to other computers, tablets, and smartphones running Excel. If a collaborator doesn't have Excel loaded on his or her device, then Excel Online launches automatically in a browser environment to display the shared workbook.

New Excellent

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.