What does Clicking and Dragging the Fill Handle in Excel do?

We may receive a commission for purchases made through the links on our site. This helps us keep everything up and running.
What does Clicking and Dragging the Fill Handle in Excel do?

The fill handle replicates the same formulas, values or fills in a sequence of texts, dates or numbers as well as other information to a specified amount of cell.

Fill Handle Fill Handle is a powerful autofill function in Excel that is a small green square that is located in the lower left corner of the cell in use (or the cell range). It can be used to quickly copying the same data across the column (or right across rows) or filling a sequence of data, like text, dates, numbers formulas, or a common sequence in a specified amount of cells.

If you hover over the fill handle using your mouse pointer the cursor of your mouse changes from an white cross to the black plus symbol. Hold the handle and click and drag it up, down, or across on other cell. When you release the click, the mouse automatically fills the contents of the cells you’ve dragged across.

The fill handle could save lots of time and also prevent human error (like mistakes). In this video we will teach you the steps to duplicate data, formulas, or fill series of texts, dates or numbers as well as other information using this fill handle.

Using AutoFill in Excel

The fill handle is among the several ways to use the autofill function in Excel. It’s a variation that uses copy-and-paste, however, it’s more than the latter. There are a few different ways to use the autofill feature in addition to the fill handle

  • Utilizing keyboard shortcuts Select the cells you want to copy, beginning with the cell that contains the information you wish to copy to other cells. After that, press Ctrl+D to copy the data down, as well as use Ctrl + R to fill the right.
  • Utilizing the Fill Button The user can use the fill option by clicking the button ‘Fill’ in the Editing section of the tab ‘Home. You will be able to find options to fill down Left, Right, Up left, across Worksheets series, Justify, as well as Flash Fill.
  • Double-clicking on the Fill handle Another method to fill a column automatically is by double-clicking the handle of the fill of the range you want to fill. If the cell that is adjacent to your selected cells contain data, double-click the handle for fill to swiftly fill the column to the point that there is data in the column adjacent to it. If your data set contains empty cells, they will fill up until it comes across an empty cell in the column adjacent to it.

Duplicate Data Using Fill Handle

One of the primary functions that the handle fill handles is to duplicate or copy contents from one cell(s) to several cells. It is easy to copy basic formulas, text, numbers or any other information using this handle.

All you need to choose the cell(s) you wish to copy and drag it using the handle for fill (at the lower left corner of the cell you want to copy) to any angle you like. It will fill in cells with data that have been selected to be dragged across cells.

If you double-click the fill handle in cell C2. It can fill up the column up to C9 since there is data up to B9 in the column that is adjacent.

Autofill Options

What happens to a fill handle when you drag your mouse is that it detects certain patterns within the information and then fills the data, and provides you with additional options that you can make use of.

When you are done moving the fill handle using your mouse (or double-click) and you have filled the list, you will see the “Auto Fill Options icon at the lower right-hand corner of the list.

If you click the icon shown, it’ll offer you the following options (depending on the information):

  • Copy cells Copy cells This will duplicate one cell to the cells you have selected
  • Fill Series – This option fills the cells selected with a series of numbers (usually incrementing the value by one) starting with the cell’s initial value.
  • Formatting for fill only –This fills the selected range using its formatting as per the first cell, but does not fill in the actual values.
  • Fill without formatting – This fills the range selected with values from the first cell however it does not alter the formatting.
  • Flash fill Flash fill This option identifies patterns in the information and then fills in the data based on it. For instance when we select this Flash fill option in the following example, it will recognize the 2000 value as 20 percent, considers 3000 as 30 percent, 6500 as 65 percent and so on and then fills the list.

Autofill Text Values using Fill Handle

Excel fill handle is able to automatically complete a list of texts by copying values(s) of the first cell(s). It can also identify the text values as part of a series, such as names of months, names of days or other text. It can be abbreviated, or the full names of weeks or months, etc.

The first step is to enter abbreviated or full names of weekdays or month in the first cell, and then utilize the fill handle to fill in other cells, as illustrated below.


Weekdays:


Month Names:

You can also make use of this handle for fill to autocomplete any other text that contains numbers. Enter the first sentence in the first cell, then let the fill handle to autofill the remaining cells.

Autofill Numbers using Fill Handle

Fill can also be used to handle to make an order of numbers. It can be any type of number, such as odd numbers or even numbers. the numbers increase by one, or more.

Choose at least two numbers to create patterns in the first two cells . Then, move the handle for fill across the cells in the number you’d like. If you only select one cell that has a number and drag it downwards, Excel just makes a duplicate of that number in all other cells since there isn’t a pattern within one number.

For instance, type the number ‘2’ in cell B1 and then enter the value 4 into cell B2. Select B1 and B2 using AutoFill. AutoFill handles to move them them downwards, Excel will create a sequence of even numbers.

If you click on the ‘Auto Fill Options’ icon you will be able to select certain options as we have explained in the past:

Instead to using your left-mouse button to drag, you can use your right-click button, and once you release of the right-click, additional options will pop up , as you can see in the image below.

We’ve already discussed what the four initial flash fill options can do Let’s look at what the other options have to offer:

  • Linear Trend option Excel creates an arithmetic series of values which can be charted using straight lines.
  • Option for Growth – Excel uses the initial values using the algorithm for exponential curves in order to create a growth series.
  • Series choice This option opens the Series dialog box with additional advanced options that you can choose to use.

Copying Formulas Using The Fill Handle

The process of copying formulas is like copying numbers down in a column, or filling up the values of a set.

Choose a cell with an equation then drag the handle for fill across different cells in order to transfer the formula into the cells. If you paste the formula into an additional cell references to cells to the formula be automatically adjusted in accordance with the cell’s address.

For instance, you can enter the formula in cell B1 and then use an eraser to duplicate the formula up to cell B10.

This formula is automatically adjusted corresponding to adjacent cells.

It will also give you the results for each row.

Autofill Dates using Fill Handle

To automatically fill dates within the range of cells, simply enter your dates into the cell in which you first enter them using any format for dates that Excel can recognize. Excel.

Use your fill tool to pull it to the cell you wish the date to expire.

For dates, you can choose to use AutoFill in addition to the options available once you click on the ‘Auto Fill Options’ icon at the end of the auto-filled selection.

We have four more advanced options for dates, in combination with the 5 choices we’ve previously seen:

  • Day FillersThe fills the calendar with days by increasing by 1.
  • fill weekdays This fills in the list with only weekdays by excluding Sundays and Saturdays.
  • Fill Months This option fills the calendar with months that are incrementing and the day stays identical across every cell.
  • Fill years This option will fill the list with years that increase by one, while the month and day remain the same.

Creating Custom List for Autofilling Data

Sometimes, you may not need to arrange your list in a conventional manner. In these situations, Excel lets you create your own lists that can be used to organize your data. Then, you can use your customized list to populate cells by fill handles.

To make custom lists To create custom lists, click the File tab, then select Options..

Choose ‘Advanced’ from the left pane and scroll down until you can see the Edit Custom Lists.. The button is located in the General section on the right side. Click that button and you will be able to access the Custom List dialog box.

Add your list to the window ‘List entries’ then click ‘Add’, and your list will be displayed in the Custom Lists area as illustrated below. After that, click OK and then to close the dialog box.

Select the cell in which you want to list your items and then type the first item from your list.

Drag the fill handle until you can auto fill the cells using the numbers from your personal list.

Enable or Disable the AutoFill Option in Excel

The Autofill feature of Excel is enabled by default. If the fill handle isn’t functioning, you can turn it off. You can enable it in Excel options:

Then, click on the File tab, then choose ‘Options’.

Within the Excel Options dialog box, choose ‘Advanced’, and then check the ‘Enable fill handle ‘ and cell drag-and-drop checkbox’ in the “Editing Options’ section. This will allow the fill handle to be used in Excel.

Click OK for the box to be closed.

That’s it.

Related