How to Make Number Series in Microsoft Excel

This article covers the comprehensive guide on how to make umber series in Microsoft Excel. Here we will explore multiple methods of creating the number series. These are;

1. Using the ROW Function
2. Using the SEQUENCE Function (Excel 2021 and later versions or Excel 365)
3. Using Fill Series Tool
4. Using the Fill Handle Drag and Drop

Using the ROW Function

The ROW function returns the row number of cell where the formula is used. It can be used for creating simple number series. Look at below example.

See in the formula bar, =ROW()-1 has generated the first serial number of 1. -1 in the formula is because the first row has the serial number. If you are starting the serial number from row number 5, you have to subtract the previous 4 rows in the formula i.e. =ROW()-4. To generate serial numbers of 2,3,4 and so on, use the fill handle and drag the formula as required.

This method makes the serial number dynamic. It means, if some row is deleted from the database, the serial number sequence will automatically update.

Using the SEQUENCE Function

The SEQUENCE function returns the series of numbers based on the parameters defined in the formula. It can generate number sequence in different ways like simple number series, even number series, odd number series, number series in a matrix form etc. If you are using the latest Excel version (2021 & later or Excel 365), this method works best across any type of data and requirement.

See in the below example, how SEQUENCE function generates number series in different way.

This method also makes the number series dynamic. It means when a row or column is deleted, the number sequence will auto update.

Using Fill Series Tool

If you are using old excel version and want to create a big series of number, this method might be useful.

Follow these steps to generate number series.

1. Write first number of the series i.e 1 in a cell.
2. Click on that cell, then click on series tool. (Home/Editing Group/Fill/Series)
3. Set the properties. To create series in column, click on column option. For row, click on row option.
4. Set the step and stop values. Step means what number to increase by in the series. Stop means the last number in the series.
5. Click on ok, and the number series will be generated.

Note: This method is not dynamic. It means if you delete the row or column, the serial number does not update.

Using Fill Handle Drag and Drop

This method is useful when you have to make a small and static number series like serial number of up to 100. In this method, you can write 1,2 in the first 2 cells, select both of them and use the fill handle drag and drop feature to generate serial numbers.

See in below animated image how it works.

So, these were the methods or formula for number series in Microsoft Excel. Based on the data size and requirement, you can choose any of the methods to generate the number series in Microsoft Excel.