Excel Fill Handle

What Is Fill Handle In Excel?

The fill handle is an Excel feature to enable us to bypass copying and pasting each value into cells and use patterns instead to fill out the information. This tiny cross is a versatile tool in the excel suite to perform data entry, data transformation, and many other applications. This is an inbuilt feature of excel and does not require any manual steps to enable it.

Fill Handle in Excel 1

Excel Fill Handle To Make Your Data Entry Easier

The basic idea of using the fill handle is just to select the initial value that needs to be pasted or referenced, dragging it down or across the excel sheet by holding the right mouse button and releasing the button at the end of our data.

As we drag the fill handle over the sheet, Excel displays the value that would be auto-filled in the cell we are hovering on. It looks like this:

How to Use fill handle in Excel

We can perform multiple operations on the filled data using the Excel fill handle, as shown below:

#1 – Quickly Copy And Paste Data Using Fill Handle

We can use the fill handle to quickly copy and paste data into adjacent cells by dragging the fill handle to the cells that we want to fill with the same data.

Quickly Copy and Paste data using the fill handle

#2 – Quickly Enter A Numbered List Into Excel

We can also use this feature to create a numbered list in excel by establishing a pattern for the first few cells and dragging the fill handle to the cell you want to be numbered. Remember that we have to select more than one cell for this feature to work:

Quickly enter a numbered list into excel

#3 – Enter Days Of The Month & Months of The Year Automatically

Let’s say we want to create a calendar in excel of activities for a certain number of days. We fill in the initial date in the first cell; we want to start the dates from and drag the fill handle down till the cell where we want the dates to end.

Enter days of the month automatically

This can also be used to enter months of the year by changing the date format or by entering the first few months and dragging the fill handle to the required cell.

enter months by using fill handle

#4 – Entering A Patterned List With Items Which Need To Be Differentiated

This feature can be used to draw up a variety of lists using patterns. To do this, we have to establish a pattern in the first few cells which need to be repeated and then drag the fill handle till the cell we want the pattern to end it.

Entering a patterned list by using fill handle

#5 – Copy A Formula Using Fill Handle

We can use this feature also to copy a formula to adjacent cells by following a similar process to the one mentioned in our other examples. We just have to create the excel formula in one cell and drag the fill handle till the required cell or cells as the case may be.

Copy same formula by using fill handle

#6 – Separating values Using Fill Handle

It can be used to separate values using different separators like commas, full stops, etc. Here’s how it works, we take a value and perform the separation for the first cell manually, and then we use the fill handle to flash fill the rest of the list:

Separating values by using fill handle

As we can see from the above examples, the fill handle works for both rows and columns and is a fast and powerful way to establish a data structure quickly.

How To Use Excel Fill Handle From The Keyboard?

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

fill handle using keyboard shortcut

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

fill handle using keyboard shortcut (Cntl+R)

Display or Hide Fill Handle in Excel 2007, 2010, 2013, 2016

The fill handle is sometimes hidden, and to display it, the following steps need to be completed in sequence:

Step 1 – On the File menu, click on Options

Excel File Menu

Fill handle 1

Step 2 – Select the Advanced or Alt+FTA option; under this option, select Allow to fill handle and cell drag-and-drop box

Excel options - Advanced

Formulas may not work with the fill handle when automatic workbook calculation is not enabled. Let’s see how to turn this on.

How to Turn on Automatic Workbook Calculation?

Step 1 – On the File menu, click on Options.

File Option 1


Step 2 – Select Formulas, under Calculation Options, select Automatic.

Excel Options

Things To Remember

  • It is very important to check the cell references when using the fill handle. We have two types of cell references, relative reference and absolute reference.
  • Relative references in excel are used when we want the references to change incrementally while using the fill handle. For example, if we use =sum(A1,B1) and the fill handle, the next cell below this would be =sum(A2,B2), and so on.
  • If we do not want the references to change, then we use absolute references by adding a dollar sign ($) in the first cell reference like this =sum($A$1,B1) so that the cell below that would return the value =sum(A1,B2) instead of =sum(A2,B2) as is the case with relative references. The dollar sign instructs excel to keep pointing to A1 as we keep filling subsequent cells.
  • It is impressive how fast we can work on data using this feature, saving a huge amount of time and effort in excel, making us more productive and efficient.

Recommended Articles

This has been a guide to Fill Handle in Excel. Here we discuss how to use the fill handle to make your data entry easier and also how to display or hide the fill handle along with excel examples and downloadable templates. You may learn more about Excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion