How to AutoFill in Excel without Dragging

The Fill Handle is an Autofill feature in Excel that enables you to fill up a series of values or copy formulas to a desired number of cells by simply dragging it using the mouse. You can find the fill handle at the lower right-hand corner of the selected cell(s).

For instance, if we enter numbers or alphabets, or dates in at least 2 cells of a range with a pattern, and when we select those cells and drag the fill handle down or across the cells, the series is automatically filled.

It easy to auto-complete a list or copy a formula for a few dozen cells in a row/column using the fill handle. However, what if you have autofill 5000 or 10,000 rows of data in a column? It will be very difficult to hold and drag fill handle overs thousands of cells.

That is why in this post, we’ll going to show you how to quickly fill a series of values or formulas in cells without dragging the fill handle.

AutoFill Cells in Excel Without Dragging the Fill Handle

Fill handle is a great tool for autocompleting data in Excel but if you were to populate hundreds or thousands of cells, that is not an easy job. Hence, Excel includes a Fill Series tool under the Fill command in Excel Ribbon.

AutoFill Numbers Without Dragging using Series Dialog in Excel

To autofill, a series of numbers, first, just enter a number (1) in the first cell (A1).

Go to the ‘Home’ tab, click the ‘Fill’ command on the Ribbon and select ‘Series’ option.

In the Series dialog box, select where you want to fill the cells, ‘Columns’ or ‘Rows’; in the Type section, select ‘Linear’; and in the Step value, enter the start value (1) and in stop value, enter the end value (eg, 500).

Click the ‘OK’ button. Now the series is filled in the cell range A1:A500 with the numbers 1 to 500.

The Series dialog also allows you to autofill odd numbers or even numbers or any other series pattern.

To fill odd numbers without dragging, type ‘1’ in cell A1, then enter ‘2’ instead of 1 in the Step value, which means that numbers would increase by 2. Enter, up to what number series to be auto-filled in the Stop value. In our case, we’re entering ‘1000’ because we want the numbers to be auto-filled up to 1000.

You could also choose to fill rows instead of columns. Now, the odd numbers are filled in a row.

To fill even numbers without dragging, type ‘2’ instead of 1 in cell A1, then enter ‘2’ in the Step value, which means that numbers would increase by 2, but now we would get even numbers. Enter up to what number series to be auto-filled in the Stop value. In our case, we’re entering ‘1000’ because we want the numbers to be auto-filled up to 1000.

The result:

AutoFill Dates Without Dragging Using Series Dialog in Excel

You can also auto-complete Dates without dragging the fill handle using the Series dialog box.

First, type the initial date (01-02-2010) in the first cell (A1 in our case). Then, select the range of cells, including the initial date, where you want the dates to be auto-filled.

To select a long-range, just select the initial date and click the ‘Name box’ right above cell A1. Then, type the range reference (in our case A1:A500) and press Enter.

This will select the 100 cells including the initial date.

With the initial date selected, click the ‘Fill’ command under the ‘Home’ tab and select ‘Series’. In the Series dialog box, select ‘columns’ or ‘rows’; select the ‘Date unit’ of your choice, and enter the ‘Step’ value as you need. And you don’t need to specify the Stop value for the date series.

Then, click ‘OK’ and the dates series will be filled with all days of the months (500 days for 500 cells).

Sometimes you only want to include the weekdays (workdays) in the series without weekends.

To create a list of weekdays/workdays only, type the initial date in the first cell (A3). Then, select the range as we did before and go to the ‘Series’ dialog under the Fill command in the Ribbon.

In the Series dialog box, select ‘Weekday’ as the Date unit, and enter the ‘Step’ value as you need. Then, click ‘OK’.

Now as you can see, only weekdays/workdays are filled and weekends are ignored.

You can also fill only months or years with this tool.

If you only want to fill repeating value (same value) to all cells instead of series of values, you can just copy the value, select the range using the Name box or using the mouse, and paste it to all cells.

AutoFill Formula Without Dragging using the Name Box

If you intend to copy/autofill a formula without dragging the fill handle, you can just use the Name box. You don’t need to use the Series dialog box to copy formulas.

First, type the formula into the first cell (C2) of the column or row and copy the formula by pressing Ctrl + C shortcut.

Select the ‘Name box’, right above column A and type the range reference you want to apply the formula (C2:C800), and hit Enter key to select the cells.

Alternatively, you can also press Ctrl+ Shift+ Arrow down to select the entire column or Ctrl + Shift + Arrow Right to select the entire row.

Then, press Ctrl + V to paste the formula to the selected cells. Alternatively, press hit Ctrl + D to fill down or Ctrl + R to fill right. Both shortcuts give the same result.