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.
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.
Pivot tables have been with us for a long time and allow us to easily analyze data by changing the perspective from which we look at them. Recently, there is also a PIVOTBY function that allows us to get a similar report, but in an Excel spreadsheet. By marking rows, columns, values and other parameters, a dynamic report is generated. Here’s a post about how to use this function…
If writing SQL queries is in your job description I am certain that you, in great extent, inside SELECT command, use a GROUP BY clause that allows you to group row values by a given column and then apply an aggregation function, e.g. SUM, COUNT, AVERAGE, MIN, MAX… I have good news for you: we can achieve almost the same thing within an Excel report by using the GROUPBY function…
This function, for given start and end date, calculates how much time has passed in years, and as a result returns a decimal number. It can be used to calculate annual interest, future value and in other cases when it is necessary to know how many years have passed. In the text that follows, through a simple example, you will get to know this function.