Two related sorting functions, SORT and SORTBY, replicate and extend Excel AutoFilter sorting functionality in formula form. The SORT function can be used to sort the contents of a range or array based on the values in that range or array, while the SORTBY function can be used to sort the contents of a range or array based on the values in the corresponding range or array, as illustrated here: By default, these functions will return multiple values, which will …
Tag: FILTER function
SEQUENCE function
The SEQUENCE function generates a list of sequential numbers in an array. We can generate a list of sequential numbers in rows, columns, or both. By default, the SEQUENCE function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here. The syntax of the SEQUENCE function is as follows: =SEQUENCE ( rows ; [columns] ; [start] ; [step] ) Rows specifies …
FILTER function
The FILTER function replicates and extends Excel AutoFilter filtering functionality in formula form. The FILTER function allows us to filter a range of data based on defined criteria. By default, the FILTER function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here. The syntax of the FILTER function is as follows: = FILTER ( array ; include ; [if_empty] ) …
Lookup the 2nd, the 3rd, or the nth value
Both the MATCH function and the XLOOKUP function look only for the first (last) available match in the array. This is perfectly reasonable in most cases, as we are expecting to deal with unique identifiers when looking up data. However, sometimes we will have to look up the second, third, fourth, or nth value. Consider the following example: Our table contains a list of contract numbers in column B and contact email addresses in column A. Contract numbers are …
Lookup with multiple criteria
If we have to look up items in tables where we can’t use unique identifiers (there are no names located in a table column that contains data in all of the rows, and that data is non-repeating), we will probably have to resort to matching multiple criteria in multiple columns. Consider the following example: Each year, multiple game tournaments are held in repeating cities, organized by various repeating organizers. Items in the column City are not unique, nor are …
Sort & Filter in Excel
How to insert or remove a filter Excel AutoFilter features enable us to quickly sort and filter, i.e., to quickly order, select from based on a criterion, or analyze tabular data. Feature is available under both Home and Data ribbons, as Sort & Filter: We can insert a filter by positioning ourselves in any cell that is a part of a table and using the Filter button: This is what a table with an inserted filter looks …
Top 10 lists in Excel
When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not …