There are several ways to use the Data Validation option to generate lists to select values when filling out tables. One of the earlier “recipes” was about how to create dynamic lists. With the advent of the TRIMRANGE function, this can now be done in an even simpler way, without having to use a Name Manager and write complicated formulas.
One of the earlier “recipes” was about how to use the Go To Special option to remove empty rows from the table. Now it is possible to do this in a much easier way, by using the TRIMRANGE function in combination with some other functions which are returning dynamic arrays. The result is a dynamic table that will be automatically updated if you decide to remove any more rows.
The TRIM function removes extra blank characters, we all know this by now, and TRIMRANGE, as its name suggests, is used to remove empty values from a selected range of data. In addition, it is possible to specify whether their removal takes place in rows or columns and in which order. It is well combined with the SORT and UNIQUE functions, and in the following text you will see how to apply it in practice.
Power BI visualizations often have a Tooltip section with which is a feature that enables focus on specific attributes. For example, if you hover an item shown in the report, you can see information about the manufacturer, number of brands, average price, etc. Something like this does not exist in Excel, but if we can mark items in some way, it is possible, with a little improvisation, affect the display of attributes. To do this, we will use the Checkbox option…
Translation in Excel documents is not a new feature, and with the advent of artificial intelligence, it works incomparably better than before and can be said to be competitive with the Google Translate service. Recently, we also got a TRANSLATE function that translates text from one language to another, and allows you to create reports that are dynamically translated.