Dynamic Matrix Functions In Excel

Array formulas are potent formulas that allow you to perform complex calculations with standard calculation functions. Note that it is only possible on the version of Office 365. This article will find four handy functions to manage your databases very quickly.

The FILTER Function

The FILTER allows you to filter a range of data according to one or more criteria. Moreover, it is a dynamic filter, so even if your database is regularly fed, the filter will consider new information, unlike the classic filter or the advanced filter. As in the example below, we recommend you select the whole column so that if new data is inserted, it can be automatically taken into account in the filter. The syntax: =FILTER (array; include; [if_empty]). For this function you have 3 parameters:

1. Picture: The cells, or columns you want to display after the filter, in the example above, are first names and dates.
2. Include: The criterion, in our example, is the name, and we must then make a comparison
3. If_empty (It’s optional): This is filled in to avoid getting an error if there is no result

The SINGLE Function

It allows you to return a list of values ​​without duplicates and determine the values ​​present only once in an index. And even if the extraction concerns two columns side by side, the UNIQUE function also returns the uniqueness but on the association of the two columns. Depending on what you want to have. As a result, the syntax below must be modified: =UNIQUE(array;by_column;unique_values).

1. Picture: Matches the set of cells in which to perform the check/extract
2. by_column: This is filled in if your table is in line/horizontal
3. You can leave this parameter blank or put false
4. unique_values: To have the unique values, it will be necessary to insert true, otherwise false

If you want to have a list without duplicates, here is the syntax: =UNIQUE(array). If, on the contrary, you want to have the list of values ​​that are present only once in the array, it is: = UNIQUE(array; false; authentic)

The SORT Function

The SORT function will dynamically sort your data on a column as its name suggests. Here is the syntax: =SORT(array; index_column; order; by_column)

1. Picture: Matches the set of cells on which to sort
2. Index_sort: The column number of sorting by
3. Sort_Order: The order the array will be sorted, ascending (1) or descending (-1).
4. by_column: Should we sort by row (false) or column (actual).

If you want to sort the array by the first column and in ascending order, you can use this syntax: = SORT(array). However, if this is not the case, the other parameters must be entered using this syntax: =SORT(array; index_column; order; by_column)

The SORT.BY Function

The Excel SORT BY function sorts the contents of a set of cells or a table. This function allows more advanced sorting than the SORT function. Here is the syntax: =SORTBY(array; array_sort_1; order_1; array_sort_2; order_2, …)

1. Picture: Matches the set of cells on which to sort
2. Par_array1: The group of cells on which to base the sort
3. Sort_Order1: The order the array will be sorted, ascending (1) or descending (-1).
4. Par_table2: The set of cells on which to base the sort
5. Sort_Order2: The order the array will be sorted, ascending (1) or descending (-1).

For example, if you want to sort the table below according to the number of products sold, in descending order: Here is the syntax: =SORTBY(A13:B19;B13:B19;-1;A13:A19;1). With these new functions, you can easily manage your databases. If you want to deepen your knowledge of Excel, do not hesitate to contact us since all our office automation training is fully funded under the CPF. The process is simple.

Also Read: How WordPress Themes And Site Architecture Impacts SEO