If you have a lot of cells that need to be filled automatically in Excel You can utilize Excel’s Fill Series tool or Name box to fill in your data without having to drag the handle for fill.
Fill Handle Fill Handle can be described as an autofill feature of Excel which allows users to fill in with a set of numbers or copy formulas into the desired number of cells simply by moving it with the mouse. The fill handle in the lower left-hand corner of your chosen cell(s).
For example, if we input numbers, alphabets or dates into at least two cells in the range that have patterns, and then we choose those cells by dragging the handle for fill downwards and across cells, the sequence is filled automatically.
It is easy to automate the completion of a list as well as copy formulas to just a few dozen cells within one column or row by using an fill control. But what happens if you are able to autofilling thousands or tens of rows of data in the column? It’s a lot of work to drag and hold the fill handle over hundreds of cell.
This is the reason why in this article, we’ll try teach you how to fill quickly the formulas or values in cells without having to drag the handle of fill.
Table of Contents
AutoFill Cells in Excel Without Dragging the Fill Handle
Fill handle is an excellent tool to automate the process of completing data in Excel however, if you had to fill hundreds or hundreds of cell, it isn’t an easy task. Therefore, Excel comes with the Fill Series tool under the Fill command on the Excel Ribbon.
AutoFill Numbers without Dragging using the Series Dialog in Excel
To automate filling, a sequence of numbers simply type in the numeral (1) within the line (A1).
Click on the ‘Home’ tab, then click the Fill button on the Ribbon , and then select “Series” as an option.
In the dialog box for Series choose the location you wish to fill in the cells either in rows or columns In the section titled Type, choose Linear; and in the step value type in the value for the beginning (1) as well as in the the stop values, type in the final value (eg 500, for example).).
Click OK. The series will now be filled with the cell A1:A500 range with the numbers 1 through 500.
The Series dialog can also allow you to automatically fill odd numbers, even numbers, or any other pattern of the series.
To fill in odd numbers without drag the mouse, enter ‘1’ into cell A1. Then, enter 2 instead of 1 within the value for Step, meaning that the numbers will increase by two. Enter the number series you want to fill automatically within the stop value. In this case we’re entering 1000 as we’d like the numbers to auto-fill until 1000.
You can also opt to fill rows rather than columns. The irregular numbers will be filled into rows.
To fill even numbers with no drag, enter 2 instead of 1 into the cell A1 and then type “2” into the step value, meaning that the numbers will increase by 2 but this time we’ll receive even numbers. Enter the maximum number of series you want to fill within the stop value. In this case we’ve entered 1000 as we’d like the numbers to auto-fill until 1000.
The outcome:
AutoFill Dates Without Dragging Using Series Dialog in Excel
You can also automate the completion of Dates by dragging the fill handle by using the Series dialog box.
Then, enter the initial day (01-02-2010) into the cell that you first select (A1 in our example). Select the cells in the range that include the date of the initial in which you would like the dates to automatically fill in.
To choose a long-range simply select the first date, then click on the “Name box” above the cell A1. Enter the reference to the range (in the case of A1:A500) and hit enter.
This will pick the 100 cells that include the date of the first selection.
When you have selected the first date Click the ‘Fill’ option under the tab ‘Home’ and then select the option ‘Series’. Within the Series Dialog box choose columns or rows and then select the date unit you prefer and then input the ‘Step’ value according to the information you require. You don’t have to enter the stop value for the date series.
After that, click “OK” and the date sequence will fill with all the days of the month (500 day for each 500 cell).
Sometimes, you just want to include the days of the week (workdays) within the sequence with no weekends.
To make a list of workdays and weekdays, enter the date of the first day into the cell that is first (A3). Select the same range as before, and then go to the ‘Series’ dialogue beneath the Fill option in the Ribbon.
Within the Series dialog box, choose “Weekday” as the Date unit, and then enter the value for ‘Step’ according to the information you require. Then, click ‘OK’.
As it is evident, just workdays and weekdays are occupied, while weekends are not considered.
You can also add months or years using this tool.
If you need to add repeating values (same value) in all cells instead of a series of values, you could simply duplicate the values, choose the range by using the Name box or the mouse to copy it into all cells.
AutoFill Formula Without Dragging by using the name box
If you want to copy/autofill formulas without moving the fill handle, simply use the Name box. It is not necessary to use the Series dialog box to copy formulas.
Then, type the formula in one of the cells (C2) in the row or column and then copy the formula by clicking the Ctrl and C shortcut.
Choose the name box located just above column A. Type in the range you’d like to use for the formula to (C2:C800) and press the the Enter button to choose the cell.
Alternately, you could use Ctrl+Shift+ Arrow Down to select the entire column , or the combination of Ctrl+ Shift+ Arrow Left for the row to be selected.
After that you can use Ctrl+V to copy the formula into the chosen cells. Alternately, press Ctrl+D to fill in the lower part of the cell or press Ctrl +R for filling right. Both shortcuts produce the same results.
The formula is now copied across the entire column, without having to drag the handle to fill it.