In one of the previous “recipes” we talked about how to use the Excel GROUPBY function. On that occasion, you could find out how to use it in one of the standard scenarios of its application, and now you will learn how to use it in a slightly more advanced way. To begin with, how you can group by two or more dimensions, and then how to display percentages, determine the depth of the calculation, and so on.
Sometimes, when creating report, you need to display the values as a percentage. You can calculate percentages manually, and recently Excel also offers a PERCENTOF function to make this process easier. If you use functions such as GROUPBY or PIVOTBY, you can also use this function to calculate percentages when grouping values. Let’s see how we can use it!
Regular expressions are a programming term that refers to a sequence of characters used to define a search criterion within a given text. Recently, Excel has gained several functions for working with regular expressions, and we can also use them as an argument to the XMATCH function and the XLOOKUP function as an additional search criterion.
The TRIMRANGE function allows you to remove blank rows or columns before, after, and before and after a set of values within a specified range of cells. It makes it easier to work with cells that are not empty, but recently there is a much simpler syntax for clearing a given range, which is by specifying a dot before the “:” symbol that separates the beginning and end of the range.
When you search a range of data, for example, by using the VLOOKUP function, the search is always performed by using a unique criterion. How to perform multiple searches or quickly change criteria? In the following text, you will learn about the DGET function, which allows for greater flexibility and easier finding of the required values in a given range.