Using SEQUENCE function to create a sequence of numbers (office 365)

The new SEQUENCE function is now available for Excel 365 users only.  This new function is very convenient and powerful to create a sequence of numbers, for example when numbering the rows in a data set.  Using row numbers is very useful when you want to return the data set to its original sequence when you are performing various SORTING on the data set.  The former/older method involved many steps using Autofill and setting the Autofill options to series, then clicking and dragging manually through the rows. While this is functional and works for a small sequence, it is cumbersome and tedious if you are trying to create a large sequence of numbers, such as a numbering to a 100 or 500 or 1000 rows.  Clicking and dragging is not the most efficient method for larger sequences.

The SEQUENCE function in Office 365 generates a specified sequence of numbers and also allows you to increment the numbers.  The default is an increment of 1, so it does not need to be specified.  But, if you want to increase counting by 2’s or 5’s, then you can specify the STEP in the function argument (4^th argument). You can also start the sequence at a higher number such as 100 or 500 and still increment by the desired STEP value.

 Example 1: Use the SEQUENCE function to create a sequence of numbers to 10.

 Steps:

1. Click in a cell and begin typing the formula “=SEQ”
2. Click on the SEQUENCE function in the list
3. Type 10 and a Closing Parenthesis
   Note:  the 10 is for counting to 10.  The other 3 arguments will default to a value of 1, i.e
   1 column, Start at 1, and Increment/Step by 1 

Note:  No spaces in the formula but you may type in upper or lower case.

Example 2: Use the SEQUENCE function to create a sequence of 10 numbers, incrementing by 5.

Example 3: Use the SEQUENCE function to create a sequence of 10 numbers, incrementing by 5, but start the count from 100.

The new FILTER function is now available for Excel 365 users only.  This new function is very powerful and convenient to track down the records in a Data Table.  Sometimes a person or an entity may have several transactions in a table.  One often filters the data manually, but what happens when you have to change the filter criteria and see what the transactions?  Lookup functions are only good to look up a single item or data value so that will not meet our needs in this situation. Our L&OD Excel Level 2 course covers Filtering Data, and Excel Level 3 covers Data Validation (to create drop lists).

Scenario:

In our example (Figure 1), there are multiple sales transactions from sales reps.  The objective is to see all the transactions for a Rep using their unique RepIDs.

Objective:

Hint:  Use a drop list to enter a RepID (instead of manually typing the ID).

Use the new FILTER function to dynamically extract all the transactions for a particular Rep, and if you choose a different RepID selection, the FILTER function should automatically query the data table and display all the transactions for the RepID selected.

Steps:

1. Convert the data range into a table (this step is not required, but it is useful when adding new data rows to the transaction table)
2. Create a drop list for the RepIDs (Data Validation); Create the Headers for the FILTER output (Same as the headers in the Data Table)
3. Enter a formula with a FILTER function to display all the relevant sales transactions for the RepID selected

   Formula Syntax:   =FILTER(Table1,Table1[RepID]=Ref)     

   ** Ref is the cell reference where you enter the RepID
   In our example, the RepID is entered in Cell K4, using a drop list

   ** Table1 is the named data source with all the transactions
   In our example, the Table1 is the data table from A3:I23, with the headers in Row 3

   Note:  No spaces in the formula but you may type in upper or lower case.

** In this example, two transactions were extracted and displayed by choosing the RepID.