Following are 10 different methods to create a Number Series (1, 2, 3,… 49, 50) in Excel.

Table of Contents

### Excel Fill Handle to create Serial Numbers

To create a number series like 5, 6, 7,… up to the number14,

type 5 into a cell **>** 6 into the cell just below it **>** select these two cells **>** using the **Excel Fill Handle** drag down

release the mouse button after 9 cells and the cells up to that point will be filled with numbers from 5 to 14.

### Less known trick with Excel Fill handle

Type in 5 into a cell. Holding the **Ctrl **key, drag the **Excel Fill Handle** down.

release the mouse button after 9 cells and the cells up to that point will be filled with numbers from 5 to 14.

**Auto Fill** for creating Serial Numbers

Type in the first number of the series into a cell. Here the first number of the required series is 5.

Click on the Excel Fill Handle and drag down.

When you release the mouse button, the cells up to that point will be filled with the same number.

Click on the split button for **Auto Fill Options**

Select **Fill Series **and the highlighted cells will be filled with the corresponding Number Series.

### Adding one to the previous value

Type the starting number of the required series into a cell. In the adjacent cell down, create a formula which will add **1** to the above value.

Here I have the number 5 in the cell B2 and the formula in the cell below will be,

=B2+1

Copy the formula downwards up to the cell B11 and we will have the number series from 5 to 14.

### Fill Series

Type in the number 5 into a cell.

In the **Home **tab > **Fill **> **Series…**

In the dialog called **Series**,

click on the radio button for **Columns**, type in **1** in the input box for** Step value:** and **14** in the input box for **Stop value:**

Click on OK and we will have a Number Series from 5 to 14.

### SEQUENCE Function

The **SEQUENCE **function in Excel can be used to create a Single or Multi dimensional array of numbers in the required interval.

Following is the formula to generate a Number Series, starting from 5 to 14.

=SEQUENCE(10,1,5,1)

### ROWS Function

The ROWS function in Excel returns the number of rows in the **Range Reference** supplied into it.

The following formula is used to create the number 5 in the cell B2

=ROWS($A$1:A1)+4

Copy the above formula up to the cell B11 and we will have a number series from 5 to 14.

### ROW Function

The ROW function in Excel will return the **Row Index** of the cell reference used in it. If nothing is specified inside ROW functon, ROW function will return the row index of the cell in which the function is used.

When ROW function is used in the cell B2, it will return the value 2.

Subtract 2 from this formula to get **0** as result.

Add 5 to the formula to fix **5** as the starting number in the series.

Copy this formula up to the cell B11 and we will have the numbers from 5 to 14.

### Power Query

To create a Number Series using Power Query in Excel,

In the **Data **tab of the Excel Ribbon > **Get Data** > **From Other Sources **> **Blank Query**

The **Power Query Editor** of Excel is activated.

In the **formula bar** of the Power Query Editor, type in the following formula and press Enter.

= List.Numbers(5,10)

To load these serial numbers into an Excel Worksheet,

in the **Home **tab of the Power Query Editor > click on the split button for **Close & Load** > **Close & Load To…**

Using the **Import Data **dialog, specify the location where you want to place the table containing numbers

The table containing numbers from 5 to 14 is loaded into the Excel Worksheet.

### Macro to create Number Series

The following macro when executed will generate a series of numbers from 5 to 14.

Sub SerialNumbers() Dim i As Integer For i = 5 To 14 ActiveCell.Value = i ActiveCell.Offset(1, 0).Select Next i End Sub

Copy and paste the above code into the **VBA Editor** of Excel

Go to the **Developer **tab in the Excel ribbon > **Macros **> execute the Macro called **SerialNumbers **to generate a series of numbers from 5 to 14.