Excel Fill Handle

What Is Fill Handle In Excel?

Fill Handle in Excel is a feature that allows users to auto-complete or auto-fill the desired data into a row or a column by simple dragging. The selected data can be a cell value or multiple cells, dates, numbers, alphabets, text strings, formulas, etc.

The Excel Fill Handle saves time and makes the work more productive. It can be enabled/disabled from the “Excel Options” window from the “File” tab.”

What Is Fill Handle In Excel?

Excel Fill Handle is an efficient feature in Excel that helps us to copy and paste data automatically and accurately.
There are preset values like numbers, dates, months, years, weeks, etc., that can be auto-filled. For the rest of the data values, we can drag the cells with a pattern or a sequence and use the Fill Handle in Excel to fill the data series.
We can drag the formulas for the cells using this feature during calculations. When the dataset is modified or updated, the formulas get updated too, because of the auto-fill option.
We can use the Excel Fill Handle horizontally/vertically, in any direction, Up, Down, Left, or Right, row-wise/column-wise.

How To Use Excel Fill Handle Tool?

We can use the Excel Fill Handle in the following ways,

Drag it from the selected cell to the desired cell.
Double-click the fill handle on the selected cell in a series, and the series gets auto-filled.
Select a cell value along with empty cells [Columns/Rows], and press the shortcut keys Ctrl+D to fill the formula down a cell in a column, or Ctrl+R to fill the formula to the right in a row.

Examples

We will understand Fill Handle for 6 different scenarios.

Example #1 – Quickly Copy and Paste data Using Fill Handle

We will Copy and Paste Data using Excel Fill Handle for the following data where,

Column A contains the values.

Select cell A3, copy and paste the data by dragging the Fill Handle to the adjacent cell A4, as shown below.

Example #2 – Quickly Enter a Numbered List into Excel

We will Enter a Numbered List using Excel Fill Handle for the following data where,

Column A contains some numbers.

Select cell A1:A2, and drag the Fill Handle to the adjacent cells A3:A11 by establishing a pattern from the first few cells, as shown below.

Example #3 – Enter Days of the Month & Months of the Year Automatically

We will Enter Days of The Month & Months of The Year Automatically using Excel Fill Handle for the following data where,

Column A contains the Start Date.

Select cell A1, and drag the Fill Handle to the adjacent cells A2:A31, as shown below.

[Note: When creating a calendar of activities for a certain number of days, select the initial date in the first cell as the start date, and drag the Fill Handle down to the cell where we want the dates to end.]

Example #4 -Entering a Patterned List with Items which Need to be Differentiated

We will Enter A Patterned List with Items Which Need to Be Differentiated using Excel Fill Handle for the following data where,

Row 1 contains the value to be auto-filled.

Select cell A1, and drag the Fill Handle to the adjacent columns B1:F1, as shown below.

[Note: Select the pattern that has to be repeated in the first few cells, and drag the fill handle until the cell we want the pattern to end.]

We can leave it at this, or to further get only the values and not the formatting, select the “Fill without Formatting” option from the “Auto-Fill Options” box at the end of the selections, as shown below.

We will get the output shown below.

Example #5 – Copy a Formula Using Fill Handle

We will Copy a Formula to calculate the sum of the two values using Fill Handle for the following data where,

Column A shows the Value1.
Column B shows the Value2.
Column C shows the Total.

The steps to copy the formula using the Excel Fill Handle are as follows:

Step 1: Select cell C2, enter the formula =A2+B2, and press “Enter”.

The output is 146, as shown below.

Step 2: Drag the formula from cell C2 to C4 using the Fill Handle.

We will get the output shown above in column C. Column D is for our reference.

Example #6 – Separating Values Using Fill Handle

We will Separate the values, i.e., names of two people in one cell, using commas and try to copy patterns using Excel Fill Handle for the following data where,

Column A shows the Names.
Column B will display the Output.

The steps to separate the names with commas using Excel Fill Handle are as follows:

Step 1: Copy the cell value from A2 and paste it into cell B2 by adding separators like commas, full stops, etc., here comma, as shown below.

Step 2: Drag cell B2 to B5 using the Fill Handle. We will get the following result.

Step 3: Select the “Flash Fill” option from the “Auto-Fill Options” box at the end of the selections, as shown below.

We will get the output shown below.

How To Use Excel Fill Handle From The Keyboard?

There are two ways to use Fill Handle from Keyboard shortcut in excel, and they are;

We can use Ctrl+D to fill the cells below the selected cell.

We can use Ctrl+R for filling cells to the right.

Display Or Hide Fill Handle In Excel 2007, 2010, 2013, And 2016

To enable or disable the Fill Handle feature,

Select the “File” tab → click the “More…” option from the list → select the “Options” option from the drop-right list, as shown below.

Display or hide fill handle in excel - 1

The “Excel Options” window opens.

Select the “Advanced” option on the left of the “Excel Options” window → on the right, go to the “Editing options” group → check/tick the “Enable fill handle and cell drag-and-drop” checkbox → click “OK”, as shown below.

[Note: To disable or hide the “Fill Handle” feature, follow the same steps, and uncheck or deselect the “Enable fill handle and cell drag-and-drop” checkbox.]

Display or hide fill handle in excel - 2

How To Turn On Automatic Workbook Calculation?

To Turn On Automatic Workbook Calculation,

Select the “File” tab → click the “More…” option from the list → select the “Options” option from the drop-right list, as shown below.

The “Excel Options” window opens.

Select the “Formulas” option on the left of the “Excel Options” window → on the right, go to the “Calculation options” group → select the “Automatic” option radio button → click “OK”, as shown below.

Turn on automatic workbook calculation - 2

Excel Fill Handle Not Working

The Fill Handle may not work for the following reasons,

The feature is not enabled in the Excel Options window.
Not selecting all values – All the values to be dragged and dropped are not selected.
The “AutoFill Options” is not enabled in the Excel Options window.

Important Things To Remember

When we select a cell value along with empty cells [Columns/Rows], and press the shortcut keys Ctrl+D to fill the formula down a cell in a column, or Ctrl+R to fill the formula to the right in a row.
We can fill series of a cell value whether numeric or textual, by selecting and dragging the cell.
When we use the Excel Fill Handle on some vales, we get an Auto-Fill Option box at the end of the filled series that has some options namely, fill series, flash-fill, fill with formatting, fill without formatting, etc. We can select any option as per our requirement.

Frequently Asked Questions (FAQs)

1. What does Fill Handle do in Excel?

Excel Fill Handle is used to fill up the data by creating a series of values following a pattern or formulas we entered. It is effortless to implement, and once enabled, it is always available at the bottom right of any cell.

2. How do we use Fill Handle in Excel?

Let us understand working with the example.

The image below depicts the values, and we will try to copy the values using the Fill Handle.

In the table, the data is,
• Column A shows the values.

Excel Fill Handle - FAQ 2 - 1

Select cell A2:A3, copy and paste the data by dragging the Fill Handle to the adjacent cells A4:A5, as shown below.

Excel Fill Handle - FAQ 2 - 2

3. Where can we find Fill Handle in Excel?

Once enabled, the Excel Fill Handle option is always found at the bottom right of any active cell.

The option is also found in the “File” tab, as follows:

Select the “File” tab → click the “More…” option from the list → select the “Options” option from the drop-right list.

In the “Excel Options” window that appears, select the “Advanced” option on the left of the “Excel Options” window → on the right, go to the “Editing options” group → check/tick the “Enable fill handle and cell drag-and-drop” checkbox → click “OK”, as shown below.

Excel Fill Handle - FAQ 3

Download Template

This article must help understand the Excel File Handle formulas and examples. You can download the template here to use it instantly.

Excel Fill Handle Download