How to Create a Number Sequence in Excel Without Dragging

Get FREE Advanced Excel Exercises with Solutions!

Dragging is a quick and easy way to create a number sequence in Excel. But there are scenarios where you might want to create one without dragging the fill handle icon all the way. Or maybe it is your personal preference to not drag altogether. Either way, This article will focus on how to create a number sequence in Excel without dragging.


Watch Video – Create a Number Sequence Without Dragging


How to Create a Number Sequence in Excel Without Dragging: 3 Easy Ways

There are a total of three ways we can create a number sequence in Excel without dragging the fill handle icon. We can use the in-built Excel fill series option, which does the same thing as dragging. Also, there are dedicated functions that can both be used to create a sequence and utilized to create one. In addition, we are going to create two number sequences to better understand the processes. First, we are going to create a series of positive integers, then one that starts with 15 and has a step of 15.


1. Using Fill Series Feature

In the first method, we are going to demonstrate the fill series feature in Excel. We can use the fill series feature to create a number sequence in Excel without dragging. And you will be amazed at how many kinds of sequences you can create with this feature. But for the demonstration, we are going to create two as mentioned above.


Example 1: Generating Sequential Numbers

To create a divergent series, we have to select a linear series with a starting number written in a cell and the ending numbers and steps in the feature. Follow these steps to see how you can use the feature to create the divergent series.

Steps:

  • First, select cell B5 and enter the starting value. In this case, it is 1.

  • Now select the cell again.
  • Then go to the Home tab on your ribbon.
  • After that, click on Fill from the Editing group.
  • Next, select Series from the drop-down menu.

how to create a number sequence in excel without dragging

  • As a result, the Series box will appear. Now select the series orientation in the spreadsheet from the Series in We have selected Columns as we intend to write the series vertically.
  • Also, select the type of series you want from the Type As our series is a linear one, we have selected the Linear option here.
  • You also need to select step values and a stop value of the series below these options. Our divergent series has a step value of 1 and let’s say we want to stop at 10. So we have entered these values as shown in the figure.

how to create a number sequence in excel without dragging

  • Once you are done, click on OK. This will automatically create a number sequence from 1 to 10 in Excel without dragging.

how to create a number sequence in excel without dragging


Example 2: Generating Multiples of 15 up to 150

We can also create other types of sequences using the fill series feature without any form of dragging. For the demonstration, we are creating a sequence of multiple of 15 up to 150 here. Follow these steps to see how we can achieve that.

Steps:

  • First of all, select cell B5 and write down the starting number of the sequence.

  • Then select the cell again.
  • Now go to the Home tab in your ribbon.
  • After that, select Fill from the Editing group.
  • From the drop-down, select Series.

how to create a number sequence in excel without dragging

  • Thus, the series box will appear. Now select Columns in the Series in options.
  • Also, select Linear under the Type options.
  • For the Step value, write 15 and enter 150 as the Stop value.

how to create a number sequence in excel without dragging

  • After clicking on OK, this will automatically create the number sequence as intended in the Excel spreadsheet without any dragging.

how to create a number sequence in excel without dragging

Read More: How to Create a Number Sequence in Excel Based on Criteria


2. Applying SEQUENCE Function

There is a function in Microsoft Excel dedicated to creating number sequences, called the SEQUENCE function. This function takes four arguments- the number of rows, the number of columns, starting value, and the step value. It then returns the number sequence that suits those values.

Usually, this function creates an array in a cell. In case, you want to have each value in a different cell, you need to select a range of cells first and then create an array with the help of this function.


Example 1: Generating Sequential Numbers

To create the series of the whole numbers with the help of this function, follow these steps.

Steps:

  • First of all, select cell B5.
  • Then write down the following formula.

=SEQUENCE(10,1,1,1)

how to create a number sequence in excel without dragging

  • After that, press Enter.

This will automatically create a number sequence in Excel without dragging.


Example 2: Generating Multiples of 15 up to 150

To create the second series with the help of the SEQUENCE function, follow these steps.

Steps:

  • Firstly, select cell B5.
  • Secondly, write down the following formula in the cell.

=SEQUENCE(10,1,15,15)

how to create a number sequence in excel without dragging

  • Finally, press Enter.

This will create a number sequence consisting of multiples of 15 in the Excel spreadsheet without any form of dragging.


3. Utilizing ROW Function to Create a Number Sequence

Identically, there is another function in Excel called the ROW function which can also create a number sequence. Although the function doesn’t serve this particular purpose, we can use formulas with the function that can help us create a number sequence in Excel without dragging the fill handle icon bar. But unfortunately, this function is only limited to creating linear series.

The ROW function can take a reference cell as an argument and returns the row number of the cell. It can also work without any arguments. In that case, it returns the row number of the cell the function is in.

Although you don’t need to drag the fill handle icon bar with the help of this function, you need to select cells first. To create the two types of sequences, the formula needs to be approached in two different ways.


Example 1: Generating Sequential Numbers

For the first type of sequence, follow these steps.

Steps:

  • Firstly, select the range (B5:B14) where you want to put the sequence in.

  • Now write down the following formula while the range is still selected.

=ROW()-4

how to create a number sequence in excel without dragging

  • Then press Ctrl+Enter on your keyboard.

how to create a number sequence in excel without dragging

As we are starting from the fifth row, we have deducted 4 from each row position here.

This will create the same number sequence in Excel, but with the help of the ROW function.


Example 2: Generating Multiples of 15 up to 150

Generating the second type of series with the help of this function can be a bit tricky. Nevertheless, follow these steps for the formula and how to use the function to create the desired series in Excel without dragging.

Steps:

  • First, select the range where you want to put the sequence in.

  • Second, write down the following formula while the range is still selected.

=15+(ROW()-5)*15

Breakdown of the Formula

15+(ROW()-5)*15

👉 ROW() returns the current row number of the cell. In this case, it will be 5.

👉 The first 15 in the formula is the starting number and the second 15 is the step.

👉 The (ROW()-5) portion of the formula counts the step of the series.

👉 And finally, 15+(ROW()-5)*15 returns each number of the sequence based on its position.

  • Finally, press Ctrl+Enter on your keyboard.

how to create a number sequence in excel without dragging

This, too, will create the number sequence in Excel without dragging the fill handle icon.

Read More: How to Increment Row Number in Excel Formula


Download Practice Workbook

You can download the workbook with all the examples of different methods from the download link below.


Conclusion

These were all the methods you can try to create a number sequence in Excel without dragging the fill handle icon. Hopefully, you can create your desired sequence with ease now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo