Excel Fill Handle

The Fill handle is an Excel feature that is used to fill the data automatically with a specific pattern.
You can extend a series of numbers, dates and text combinations to the desired number of cells.

Fill handle is a versatile tool in the excel suite that autocompletes various entries, transforms the
data, and several other applications. It is an inbuilt excel feature that is enabled by default and
therefore does not involve any manual procedure to implement it.

To use the fill handle, move your mouse pointer to the right bottom corner of the cell; it will change
into a tiny + (cross) sign. Drag the cross sign, and if Excel recognizes the pattern, it will autofill the
cells with the updated data. Else it will copy the same data in all the dragged cells.

For example: Let's enter 2 in cell B2 and 4 in cell B3. Next, we will implement a fill handle by
selecting both the cells and dragging the cross (+) sign to the following cells. You will notice that Excel
will automatically update the cells by incrementing 2 in each value.

Advertisement

How to Use Fill Handles in Excel?

Moving on, let's understand the steps to use the fill handle feature to insert the first 25 numbers
quickly in your Excel spreadsheet.

o Enter the first two entries of your data. Unlike we have entered 1 and 2 in the cell C1 and C2.
o Select both the cells and hover the mouse over the bottom-right edge of the rectangle
window. You will notice the cursor will immediately change to a plus (+) icon.

o Click on the mouse and drag the cross (+) icon down to the below cells until it reaches the
25th value. As you can see in the below example, excel will automatically identify the pattern
that will fill the first 25 entries for you (increment by 1).

o If you want to fill data in the adjacent row, fill the first entry and hover the pointer over the
right edge of the selection window. It will change to a plus icon (+); double click on it. The
fill handle will automatically fill all the values to the last cell (based on the entries in the
adjacent column). As you see in the below example we have copied the values of Column C
 to Column D.

Examples of Using Fill Handle in Excel

In the above examples, we covered the steps to use the fill handle feature in excel to autofill the list
of the first 25 numbers. However, it is a basic operation that we have performed. There occurs many
situations where the fill handle is an advantage as it automatically recognizes the pattern and fills the
cells correctly.

Let's understand the Fill handle feature more briefly using the day-to-day operations.

1. Autofill Weekday Names

Entering the list of days in excel is one of the common operations performed by every user. You can
quickly fill the week names using the Fill Handle feature. Follow the below-given steps:
1. Open your excel spreadsheet and enter the full week name, i.e., Monday. You can also enter
the three alphabet nomenclature, i.e., Mon.

2. Select the cell and bring your mouse cursor over the bottom-right edge of the rectangle
window. You will note the cursor will change to plus (+) sign.
 3. Autocomplete the cells with the weekday names by dragging the cursor down.

Autofill Dates

Dates are also commonly used in our day-to-day excel life. Therefore using the Fill handle, you can
easily autofill dates in your spreadsheet.

Note: You can use the Fill Handle to autofill any date format that Excel recognizes.

Enter the Date in the first cell and drag the cross (+) sign to the following cells. The date values will be
auto-filled in the dragged cells. You will notice Excel will automatically increment the day field by 1.
Similarly, you can autofill Months, years, days, etc.

Autofill Options

After using the Fill Handle and dragging the cross (+) icon to autofill the cells, you will notice the Auto
Fill Options icon at the bottom right corner of the selection window (refer to the below image).

Click on the Fill Handle icon. You will have several options. These options are different for different
data. For example you will have the following option if you autofill a list of numbers using Fill Handle.
 1. Copy Cell: Selecting this option enables the copy-paste of selected data to the dragged cells.

2. Fill Series: It is the default option. It auto-fills the cells as soon as it identifies any pattern.

3. Fill Formatting Only: Selecting this option only replicates the formatting of the selected cells
to the dragged cells. It does not fill the values.

4. Fill Without Formatting: Selecting this option auto-fills the cells by identifying the pattern,
but it does not copy the cell's formatting.

Similarly when you are working with dates you will have the following fill handle options:

1. Copy Cell: Selecting this option enables the copy-paste of selected dates to the dragged cells.
 2. Fill Series: It is the default option. It auto-fills the dates based on their pattern.

3. Fill Formatting Only: Selecting this option only replicates the formatting of the selected cells
to the dragged cells. It does not fill the date values.

4. Fill Without Formatting: Selecting this option auto-fills the cells by identifying the pattern,
but it does not copy the cell's formatting.

5. Fill Days: This option auto-fills the cells with days.

6. Fill Weekdays: This option auto-fills the cells with weekdays only and eliminates the
weekends.

7. Fill Months: Selecting this option fills the cells with incrementing months. Therefore the day
number will be the same for all the cells, whereas only the month numbers change.

8. Fill Years: Selecting this option fills the cells with incrementing years. Therefore the day
number and month number will be the same for all the cells, whereas only the year numbers
change.

What if your Excel fill handle feature is not working?

Many times while working with Excel spreadsheets the Fill handle feature does not work. Don't
worry if it happens as Excel provides an option to enable it manually.

Follow the below steps to enable the fill handle feature manually in Excel:
1. Click on File -> Options.

2. The Excel Options dialog box will be prompted. From the left pane of the window, select the
"Advanced" option.

3. Look for the Editing Options section, and make sure to tick the checkbox that states "Enable
fill handle and cell drag-and-drop".
 4. Click OK.

Next TopicFill Handle with Text in Excel

Excel Fill Handle with Text and Numbers

Fill handle also works with cells containing both text and number. In this case, the text does not
change but the number increases by one for every cell you select. See the images:
Next TopicTitle Bar, Help Button, Z