5 Excel Features I’m Excited To Use in 2020

It’s surreal to reflect on my adventure. A vlookup formula was the first step in creating an instructional website that today sells custom Excel add-ins to corporations all around the globe. Excel 2019 has numerous new features from Microsoft. Moreover, Microsoft keeps updating Office 365 to include the venerable spreadsheet program. Of course, these new features are useless unless you know about them and how to utilize them.

Automate Data Analysis in Excel with Ideas

Ideas is an artificial intelligence feature accessible with Office 365 subscriptions. Excel Ideas can swiftly evaluate your data and provide unexpected insights. Ideas might help in the following situations:

Analyzing transactions to rank data and find outliers;

• Performing trend analysis to illustrate data patterns over time;
• Spotting data anomalies, such as possibly erroneous or fraudulent transactions.
• Highlighting cases when a single element accounts for a large proportion of overall value.

If you have an Office 365 subscription, Ideas is available from the Home tab on the Ribbon. This function requires an active internet connection.

Conditions with IFS, MAXIFS, and MINIFS

With IFS, MAXIFS, and MINIFS you can quickly design formulae with many tests. Before IFS, many Excel users “nested” many IF functions in a single calculation. This was usual practice when creating a computation based on many factors. With IFS, such formulae are substantially simplified. As shown in the formula below, just one IFS function is necessary to test the data in cell A2. Previously, numerous IF functions would have been necessary.

MAXIFS and MINIFS, like IFS, may run several tests on your data. After each test, Excel returns the biggest number using MAXIFS. When utilizing MINIFS, Excel returns the lowest number when all tests pass. Excel 2019 users may utilize these functionalities. They are also accessible to Office 365 subscribers who use Excel.

An Alternative to VLOOKUP: XLOOKUP

Beginning February 2020, Microsoft Excel Certification will bring XLOOKUP to Excel in Office 365. XLOOKUP outperforms VLOOKUP and comparable methods like HLOOKUP and INDEX. Whilst these historical capabilities will still be available in Excel, many users will prefer XLOOKUP. Most will find XLOOKUP more powerful. XLOOKUP is distinct from conventional lookup methods in that it:

When used as an exact match, XLOOKUP is preferred over VLOOKUP and HLOOKUP. Unlike VLOOKUP and HLOOKUP, XLOOKUP does not need a column or row index number.

With XLOOKUP, the columns and rows don’t matter. When used instead of VLOOKUP, the function may look left or right. When used instead of HLOOKUP, it may look above or below. Without using an IFERROR function, XLOOKUP lets you describe what occurs if your lookup value isn’t found.

XLOOKUP Example

Consider the example in Figure 1 to see the benefits of using XLOOKUP. In this example, XLOOKUP finds the value from cell H2 in B3 to B15. Don’t forget that XLOOKUP defaults to an exact match and VLOOKUP defaults to an approximate match. XLOOKUP then returns the matching value from D3 to D15. The formula returns “Item Not Found” if no match is found. Because XLOOKUP is simpler than VLOOKUP, HLOOKUP, and other related methods, it is likely to become the favored lookup function as more people use it and know more about excel in sprintzeal.

Auto-Arrays

Dynamic arrays are another new feature accessible exclusively with an Office 365 subscription. It is not necessary to duplicate the formula to all cells when using dynamic arrays. To input a standard array formula, you no longer need to employ the CTRL + SHIFT + ENTER keyboard sequence. If you have a version of Excel that supports dynamic arrays, you may take use of six additional functions. Included in this list are FILTER, SORTBY, RANDARRY and UNIQUE.

Power Query Data Quality Analysis

Query data into Excel from various external data sources, including most major accounting apps’ databases. You may also alter your data to make it more usable using Power Query. Among other things, these changes might involve eliminating redundant columns, combining columns, adding user-defined computations, and sorting and filtering as part of the query.

Power Query now automatically analyzes your data for quality problems like completeness and correctness. Erroneous data, missing information, and even duplicates may be rapidly identified with this capability. Check the Column quality, Column distribution, and Column profile boxes on the View tab of the Power Query Editor.