If you want a sequence of numbers in Excel, the simplest option is usually the SEQUENCE worksheet function, not a macro: enter =SEQUENCE(5) in a blank cell to spill the numbers 1 through 5. If you specifically need VBA, a short macro can write a chosen starting value and increment down the sheet; the example below does that from the active cell.
SEQUENCE function or Excel macro?
The word “sequence” can refer to two different Excel tools. SEQUENCE is a built-in worksheet function that returns an array of consecutive numbers. A macro is a procedure written in Visual Basic for Applications (VBA), which can perform actions such as filling cells, formatting a range, or repeating a workflow.
For a simple list that should update or spill into nearby empty cells, the function is generally the better fit: it is quick to enter, does not require macro permissions, and recalculates when its inputs change. A macro is useful when a user needs to run a repeatable task, work with older Excel versions that lack the function, or combine number generation with other actions.
In Microsoft 365 Excel for Windows, SEQUENCE is available, as it is in Excel 2021 and Excel 2024. It is not available in older perpetual versions such as Excel 2019. The VBA example is intended for desktop Excel; Excel for the web can open macro-enabled workbooks but cannot create, edit, or run VBA macros.
How the SEQUENCE function works
The function has four arguments: rows, columns, start, and step. Rows is required; the other arguments are optional. If you leave out columns, start, or step, Excel uses 1 for each omitted value. The result is a dynamic array: Excel places the formula in one cell and spills its results into the neighboring cells.
With A1:A5 empty and the formula entered in A1, =SEQUENCE(5) returns 1, 2, 3, 4, and 5 down the column. With A1:E1 empty and the formula entered in A1, =SEQUENCE(1,5,2,2) returns 2, 4, 6, 8, and 10 across the row. These examples use a five-cell output area, so keep that area clear of values or merged cells.
For a custom count, start, and increment, put this in A1 while A1:A5 is blank: =SEQUENCE(5,1,10,3). It returns 10, 13, 16, 19, and 22. The first argument sets five rows, the second sets one column, the third starts at 10, and the fourth adds 3 each time.
Rows and columns need to be positive whole numbers. A negative step is allowed, so a descending list can be generated. For example, with A1:A5 clear, =SEQUENCE(5,1,10,-2) returns 10, 8, 6, 4, and 2. If a spill range is blocked, Excel displays a spill error instead of placing the full array.
When a VBA macro is useful
A macro makes sense when the sequence is one step in a larger routine. For example, staff might need to create numbered records in a template every week, or a workbook might need to fill a fixed range before printing. A macro can also write values directly into cells rather than leaving a dynamic-array formula behind.
Use the sample below when you want to enter a starting number, an increment, and a count, then write the results downward from the currently selected cell. It writes values to the active cell and the cells below it. It does not ask before overwriting existing contents, so select an empty starting cell and check the destination range first.
Sub FillNumberSequence()
Dim startValue As Double
Dim stepValue As Double
Dim itemCount As Long
Dim i As Long
Dim firstCell As Range
Dim answer As Variant
' Ask for the first number.
answer = Application.InputBox("Starting number:", "Create sequence", 1, Type:=1)
If VarType(answer) = vbBoolean Then Exit Sub
startValue = CDbl(answer)
' Ask for the difference between each number.
answer = Application.InputBox("Step between numbers:", "Create sequence", 1, Type:=1)
If VarType(answer) = vbBoolean Then Exit Sub
stepValue = CDbl(answer)
' Require a positive whole-number count.
answer = Application.InputBox("How many numbers?", "Create sequence", 10, Type:=1)
If VarType(answer) = vbBoolean Then Exit Sub
If answer < 1 Or answer <> Fix(answer) Then
MsgBox "Enter a positive whole number for the count."
Exit Sub
End If
itemCount = CLng(answer)
' Fill downward starting at the active cell.
Set firstCell = ActiveCell
For i = 1 To itemCount
firstCell.Offset(i - 1, 0).Value = startValue + (i - 1) * stepValue
Next i
End Sub
For example, enter 10 as the starting number, 3 as the step, and 5 as the count. Starting from A1, the macro writes 10, 13, 16, 19, and 22 in A1:A5. The count is the number of values, not the final row number. A negative step, such as -2, creates a descending sequence.
How to add and run the macro in Excel
- Save a copy of the workbook before running any macro. VBA changes cannot be reversed with Ctrl+Z.
- On Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > Module, then paste the code into the module window.
- Return to Excel, select the blank cell where the first number should go, and run the macro from Developer > Macros. Select
FillNumberSequenceand choose Run. - Enter the start, step, and count when prompted. Review the filled range before saving.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you want to keep the VBA code.
If the Developer tab is missing on Windows, use File > Options > Customize Ribbon, select the Developer check box, and choose OK. On Mac, the Visual Basic Editor and macro tools are available in desktop Excel, but keyboard shortcuts and ribbon details can differ; use the Developer tab or Excel’s Tools > Macro menu if a Windows shortcut does not apply. Excel for the web does not run VBA, so open the workbook in the desktop app to use this macro.
Choosing between a function, macro, and Fill Series
Choose based on how the list will be maintained, not just on how it is first created. A function keeps the sequence tied to its parameters, a macro automates actions, and Excel’s Fill Series command is a convenient one-time tool.
- Use SEQUENCE for a live, formula-based list, especially when the desired length or step may change. Change the formula arguments and the spilled result updates.
- Use VBA when generating the list belongs in a repeatable workflow or must be combined with other workbook actions. Consider who will need to enable or maintain the macro.
- Use Fill Series for a quick manual sequence without writing a formula or code. Enter an initial value, select the cells to fill, then use Home > Editing > Fill > Series and choose the direction and step settings.
- Use the fill handle for simple patterns. Enter the first values that establish the pattern, select them, and drag the small square at the selection’s lower-right corner. Excel may infer a simple number pattern, but review the result because a single starting value can be copied rather than incremented.
There is no requirement to use VBA just because a spreadsheet task is described as a “macro.” People sometimes mean any automated or repeated action by that word. If the requirement is only “show numbers 1 to 100,” a formula or Fill Series is usually more straightforward and easier to share.
Common problems and fixes
If SEQUENCE returns #SPILL!, inspect the intended output area for existing values, merged cells, or another obstruction. Clear the blockage or move the formula to an area with enough room. If Excel displays #NAME?, check that the function name is spelled correctly and that the installed Excel version supports dynamic-array functions.
If the macro does not appear in the Macros list, check that the code is in a standard module, that the procedure is named correctly, and that the workbook is saved in .xlsm format. If Excel blocks macros, do not enable them automatically: first confirm that the workbook came from a trusted source. Organization security settings may also prevent macros from running.
If the VBA code fills the wrong cells, the active cell was not the intended starting point. The macro uses ActiveCell, so click the correct blank cell before running it. If values already exist below that cell, the code overwrites them. Keep a backup and verify the selected range before execution, since a macro’s edits cannot be undone with Ctrl+Z.
Finally, avoid using a generated sequence as a substitute for meaningful identifiers when records need permanent IDs. A formula-based sequence can change when rows are inserted, sorted, or recalculated, and a macro that writes values can still create duplicates if it is run more than once in the same location. For identifiers that must remain unique, design a deliberate record-numbering process.
Macro safety and workbook compatibility
VBA macros can change workbook contents, so treat them as executable code rather than as ordinary formulas. Run only macros you understand or that come from a source you trust. Keep an untouched copy of important data, particularly before testing a macro against a large range.
The example is deliberately limited: it writes numeric values into one column and does not delete sheets or change workbook settings. It still replaces cells in its output range. If adapting it to work across multiple columns, add explicit range checks and clear rules about whether existing data may be replaced. Save in .xlsm format to retain code; a standard .xlsx workbook does not store VBA macros.
Frequently Asked Questions
Can Excel automatically generate a number sequence without a macro?
Yes. In Excel 2021, Excel 2024, and Microsoft 365, enter a formula such as =SEQUENCE(10) in a blank cell to spill 1 through 10 down the sheet. For a one-time list, use Home > Editing > Fill > Series.
How do I create a sequence that starts at a number other than 1?
Use the start argument. With A1:A4 empty, enter =SEQUENCE(4,1,20,1) in A1 to return 20, 21, 22, and 23. The third argument is the starting number and the fourth is the increment.
Does the SEQUENCE function work in older Excel versions?
It requires a version with dynamic-array support, including Excel 2021 or Microsoft 365. In older Excel versions, use Home > Editing > Fill > Series, or use a VBA macro if automation is required.
Why does my Excel sequence show a spill error?
A spill error usually means Excel cannot place every result in the required cells. Clear occupied cells or merged cells in the output area, or move the formula to a larger empty area.
Can I run an Excel VBA sequence macro in a browser?
No. Excel for the web can store and open a macro-enabled workbook, but it cannot run VBA. Open the workbook in desktop Excel for Windows or Mac to execute the macro.
Related
- Data Excel Skills: What They Are and How to Build Them
- Excel Data Tips and Tricks for Cleaner, Safer Workbooks
- Record Data in Excel Online: Setup, Sharing and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- VBA Function List: Built-In Functions, Groups, and UseA VBA function list is a reference to built-in functions you can call from Visual Basic for Applications,…
- VBA Double Loop: How Nested For Loops Work in ExcelA VBA double loop is a loop inside another loop: the outer loop runs once, and for each…
- Excel SUBSTITUTE in VBA: Replace or WorksheetFunctionTo substitute text in Excel VBA, use VBA’s built-in Replace function; it replaces every matching occurrence by default.…
- Excel VBA: Copy and Paste Filtered Cells Only SafelyYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to select cells that remain visible, then…
- VBA Excel Range Copy and Paste Into an Outlook EmailTo copy an Excel range into an Outlook email with VBA, copy the range, display a new message…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.