I recently discovered that Excel has a new SEQUENCE function that will return a sequence of increasing numbers in a column, row, or 2D range. Use of the new function is shown in the screenshot below, with some old and new alternatives.
The formula used for each example is shown above the output, using the FORMULATEXT function.
The function arguments are:
- Number of rows
- Number of columns (default = 1)
- Start value (default = 1)
- Increment (default = 1)
The start value and increment may be integers or floats, and may be entered as values, cell references, or formulas. The example in Column I illustrates the use of the ROW function to calculate the required number of rows. This has the advantage that if rows are inserted within the output range, the range will expand to include the new number.
An alternative to the new function is using the range fill functionality, or entering a simple addition formula and copying down the required number of rows. Use of the Fill, Series command (Column L) is shown below:
- Enter the start value in the first cell, and with that cell selected, click the Fill icon on the Editing menu (rectangle with blue down arrow). Select Series to display the dialog as shown above and select “Series in Columns” and enter the step value and stop value.
Ranges can also be filled by entering the required values in the first two rows, selecting those two cells, then dragging down the bottom right hand corner of the selected range:
Finally, since Python has a range function that will generate a sequential list of integers, it was quite simple to call this from Excel, using pyxll:
@xl_func
@xl_arg('start', 'int')
@xl_arg('stop', 'int')
@xl_arg('step', 'int')
@xl_return('numpy_array<int>', ndim=1)
def py_Range(start, stop = '', step = 1):
if type(stop) == str:
return np.array(range(0, start))
else:
return np.array(range(start, stop, step))
The last two examples in the first screenshot show the Python function results. Note that:
- The function will always return a single column.
- Input is the start and stop values, and optional step, rather than number of values and start value.
- The default start value is zero, rather than 1.
- The ‘stop’ value is the lowest value not included in the output.
Newton Excel Bach, not (just) an Excel Blog 