Finding a single match in Excel is easy with VLOOKUP or XLOOKUP. The challenge comes when you need to pull all matching values, not just the first one. To accomplish the task in pre-dynamic Excel, you had to rely on several nested functions working together, which was quite complex and far from beginner-friendly. Continue reading
Excel XLOOKUP and XMATCH are strong functions on their own, and regex match makes them even more versatile. Instead of searching for exact text, you can now use full-blown regular expressions to define what you are after. Continue reading
Calculating how much each value contributes to a total is something many Excel users do every day. In older versions, this usually meant creating your own formula that divided a part by the whole. The PERCENTOF function gives you a quick and direct way to return that percentage without building a formula manually. Continue reading
Among the latest additions to the Excel family of dynamic array functions are two aggregation tools: GROUPBY and PIVOTBY. While useful in their basic form, they truly shine when paired with custom Lambda functions, enabling you to summarize data in ways not possible with predefined Eta lambdas. Continue reading
Learn how to turn your data into clear and colorful summary reports using GROUPBY and PIVOTBY formulas, enhanced with conditional formatting. Continue reading
For decades, Excel users have relied on pivot tables to transform rows and columns of information into meaningful reports. Now, there's something even better: the new PIVOTBY function. This handy tool lets you summarize and analyze data right within a formula without the need for separate pivot tables. Continue reading
Microsoft Excel has always been great for sorting through lots of data and making sense of it. But now, there's a simple way to do it all with just a formula. Introducing the GROUPBY function – a handy and efficient tool to group and aggregate data without leaving the comfort of your formula bar. Continue reading
Imagine that you are working with a dataset of hundreds or thousands of columns. Obviously, some columns are more important than others, and naturally you may want to read their data first. Excel 365 offers a perfect function for the job, named CHOOSECOLS. Continue reading
Suppose you have an Excel worksheet with hundreds of rows from which you want to extract some specific ones. Do you already feel annoyed at the thought of copying and pasting the data manually or writing a VBA code to automate the task? Simply, use the CHOOSEROWS function. Continue reading
Want to increase an array to a specified number of rows or columns so that all similar arrays in your worksheet have the same size? In Excel 365, that can be done with one simple formula. Meet the brand-new EXPAND function that can grow an array to any dimensions you set. Continue reading
When analyzing huge datasets, you may sometimes need to pull only the data without a total row/column, headers or footers. In earlier Excel versions, you'd have to rely on the good old copy/pasting technique. In Excel 365, we have a special function to dynamically extract the desired part of the array by "dropping" irrelevant data. Continue reading
When working with large arrays of data, you may sometimes need to extract a smaller part for closer examination. With the new dynamic array function introduced in Excel 365, it will be a walk in the park. Continue reading
Since the earliest days of Excel, it has been very good at calculating and analyzing numbers. But manipulating arrays has traditionally been a challenge. With the new dynamic array functions, WRAPCOLS and WRAPROWS, you can transform a column or row into a 2D array in no time. Continue reading
Microsoft Excel 365 has introduced several new functions to perform various manipulations with arrays. With TOROW, you can perform range-to-row transformations in no time. Continue reading
The ability to transpose data from columns to rows and in reverse has been in Excel for quite a while. But converting a range of cells into a single column was a tricky task to crack. Now, that's finally changing. Continue reading
There may be various situations when you want to split cells in Excel. Now, we finally have a special function for this, TEXTSPLIT, that can separate a string into multiple cells across columns or/and rows based on the parameters that you specify. Continue reading
With the introduction of the LAMBDA function, recursive computations in Excel have become available to anyone, not just VBA programmers. To put it simply, you can now construct formulas that behave like a programming language and allow you to achieve so much, with so little, so fast :) Continue reading
Until recently, user-defined functions were the prerogative of programmers. The introduction of the LAMBDA function has made them available for everyone. With this wonderful addition to Excel's toolbox, we no longer need VBA skills to create our own custom functions. Continue reading
You have the feeling you know everything about your Excel workbooks, and then a @ character appears from nowhere in the beginning of your formulas. What does this mean? And how does it work precisely? Continue reading
If you work with lengthy formulas in Excel, then you are certainly familiar with the idea of named ranges that make complex formulas easier to read. And now, Microsoft is making a step further and allows assigning names to calculations and values directly in a formula. Continue reading
In Excel 365, the XMATCH function was added to supersede the MATCH function. But before you start upgrading your existing formulas, it would be wise to understand all advantages of the new function and how it differs from the old one. Continue reading
The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose. Continue reading
As you probably know, Microsoft Excel already has a couple of randomizing functions. What is the sense in introducing another one? In a nutshell, because it's far more powerful and can replace all of the older functions. Continue reading
Due to the revolutionary update in the Excel 365 calculation engine, array formulas become very straightforward and understandable for everyone, not just for super users. The tutorial explains what dynamic arrays are, and how they can make your worksheets more efficient. Continue reading
The Sort functionality has been around for a long time. But with the introduction of dynamic arrays in Excel 365, there appeared an amazingly simple way to sort in Excel with formulas. The beauty of this method is that the results update automatically when the source data changes. Continue reading
The introduction of the FILTER function in Excel 365 has become a long-awaited alternative to the conventional methods. Unlike the Filter feature that needs to be re-applied with each data change, Excel formulas recalculate automatically, so you need to set up your filter just once! Continue reading
In the previous versions of Excel, getting a list of unique values was a hard challenge. The introduction of the UNIQUE function in Excel 365 has changed everything! Now, you don't need to be a formula expert to get unique values from a range, based on one or multiple criteria, and sort the results in alphabetical order. Continue reading