Main Content
Microsoft shared details in October 2021 about the changes that were going to happen to their Office suite where they gave us a peek at what professionals and businesses could expect with the new features added to the suite.
The new Office 2021 included new and exciting features for all Office applications, including Excel, which provided more flexibility so users could stay connected and productive during challenging times.
The latest functions and features of Microsoft Excel are set to make collaboration with others, analyzing data and working with spreadsheets from day to day a lot easier.
So what key features of Excel are we talking about? Here they are:
Before dynamic arrays, you had to write a formula for every value you wanted to return to the grid. If you wanted another value, you had to write or copy another formula. That's how it had always been: one formula, one value.
But with dynamic arrays, all that changed. It's now one formula, many values.
That's because the spreadsheet editor now has an array of ready-made formulas. All you have to do is write a formula, hit Enter and Excel will evaluate all the individual values in an array, perform multiple calculations on one or several items and automatically spill an array of values to the neighboring cells.
You don't even have to use Ctrl + Shift + Enter.
To illustrate, here's a simple example:
Supposing you need to multiply two groups of numbers to calculate different percentages.
Before dynamic arrays, this formula would work only for the first cell:
=A3:A5*B2:D2
If you want to do the same with the other cells, you need to enter it in multiple cells and press Ctrl + Shift + Enter to make it an array formula.
But with dynamic arrays, you can just type it in one cell, press Enter and the whole range will be filled with results all at once.
This new dynamic array feature changes the entire calculation engine of Excel. It solves formatting issues created by complex business calculations that return multiple results.
The results are automatically organised in separate cells, too, which saves time and allows you to build spreadsheets faster with less chance of errors.
Dynamic arrays come with a whole bunch of other new functions: Sort, Filter, Unique, Sortby, Randarray and Sequence that can help you work more efficiently.
Instead of sifting through the entire spreadsheet, you can now search for data in a single spreadsheet, table or range by row and return matching results with XLOOKUP.
XLOOKUP is the resolution to the limitations of its siblings, VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup,) because it allows users to search for data in rows and columns, both to the right or left of your search value.
How do you use XLOOKUP? All you need to do is provide three basic parameters:
Once those have been provided, Excel does the rest. It's a useful function, especially for complex data searches.
Source: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
LET function is almost like variables in programming--you can store intermediate values and calculations and assign names (identifiers) to calculation results inside a formula through Excel's native formula syntax.
Let's put it this way: if you've been writing the same expression several times in a formula, that means Excel also calculated the result just as many times.
But with LET, you can assign a name to the expression you've written and Excel will have to calculate it only once and assign it to the given name.
Think of the names as "containers" where you can store and reuse calculations as many times as you want.
This function improves your spreadsheet's performance by eliminating redundant calculations and making complex formulas easier to write and remember.
How do you use the LET function? You have to assign names and values in pairs separated by commas. The assigned value can be constant or dynamic values that result from a calculation.
LET allows up to 126 name/value pairs.
Spreadsheets in project management, accounting and finance are viewed and edited by different people--that's just everyday business. However, despite collaboration features such as sharing and comments, things don't often work out as planned.
Either you find a file locked when you try to open it because someone else is using it, or you can't save your changes unless you keep a copy.
With the new co-authoring feature of Excel, you and your co-workers can work, save and edit the same workbook simultaneously and see each other's changes as it happens.
Depending on the Excel version you're using, you can even view the changes made by other people in different colors.
One of the key features of Microsoft Excel is the XMATCH function. It allows you to locate a specific item in an array or range of cells after which it will return the item's relative position.
Source: https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
How is it different from its predecessor MATCH?
XMATCH can perform a lookup both in horizontal and vertical ranges. It can support exact, partial and approximate matches and can even perform binary searches.
All these make XMATCH much more powerful and flexible than MATCH.
Microsoft gave Microsoft 365 and Office 2021 applications, including Excel, a new look. It's not a grand change--everything on the tabs and dialogues is still where they are. But the corners are now rounded, there's a new customizable ribbon and the user interface has a neutral color palette.
Users will also notice significant dark mode in Word documents, the Quick Access Toolbar is hidden by default and the user interface also has automatic theme-switching based on the Windows theme.
The visual refresh has been aligned with the design of Windows 11 for a coherent, intuitive and seamless experience.
Excel 2021 also boasts of improved performance, speed and stability.
Calculations from common Excel functions such as COUNTIF, SUMIF and AVERAGEIF are faster which is especially useful for larger spreadsheets.
These new Excel features will allow users to manipulate data more flexibly for a more efficient, streamlined workflow.
Discover how Vena’s budgeting and forecasting software can take your planning cycle to the next level.
