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 …
Tag: ROWS function
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] ) …
ROW & COLUMN functions
In Excel, there are specific use cases where an exact row (column) number, or an exact number of rows (columns), are necessary for calculations. The ROW function returns the row number of a reference. The ROW function syntax is as follows: = ROW ( reference ) If reference is omitted, the number of the row in which the formula appears is returned. The COLUMN function returns the column number of a reference. The COLUMN function syntax is as …
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 …