How to Use Fill Handle in Excel (4 Quick Tricks)

When we need to work with a large set of data with the same formula or pattern, it is tiring and boring to write formulas individually in every cell. At these times, the Fill Handle is very handy to use. We can easily fill the other cells by extending the formula or filling the series pattern automatically. In this article, I will show you 4 quick tricks to use the Fill Handle in Excel.


Download Practice Workbook

You can download and practice from our workbook here.


What Is Excel Fill Handle?

A Fill Handle is a feature in Microsoft Excel that allows you to fill multiple numbers, dates, or even text to adjacent cells by extending formulas or values. The Fill Handle is a little black box in the bottom-right corner of the active cell in the worksheet. It was first introduced in Excel 2010 version.

Fill Handle


How to Enable Fill Handle in Excel

If Fill Handle is not enabled in your Excel version, you can enable it from the advanced settings. For doing this, follow the steps below. 👇

Steps:

  • At first, click on the File tab.

  • Subsequently, choose More >> choose Options…

  • At this time, the Excel Options window will appear. Consequently, click on the Advanced tab. Put the Tick mark on Enable Fill Handle and cell drag-and-drop option. Next, click on the Ok button.

Enable the Fill Handle


4 Quick Tricks to Use Fill Handle in Excel

In the dataset, we have 2 columns ‘A’ and ‘B’ containing random numbers. Now, we want to multiply the values in the third column. So, for the first multiplication data, we have used =B5*C5 as the formula. Now, we want to extend the formula to all the cells below using the Fill Handle. Follow any of the 4 suitable ways described below to use the Fill Handle in Excel.

Use Fill Handle in Excel


1. Drag the Fill Handle Icon

You can use the Fill Handle and its features by simply dragging the Fill Handle toward the direction where you want to copy the cells. Follow the steps below to accomplish this. 👇

Steps:

  • At first, click on the cell which you want to copy or whose formula you want to copy. Subsequently, place your cursor on the bottom right corner of the active cell. Thus, the cursor will turn into a black cross sign. Now, drag the cursor down to all the cells you want the copied value or formula.

Use Fill Handle in Excel by Dragging

Thus, the active cell’s formula is copied to all the cells below dynamically. We can understand it by focusing on any other copied cell. For instance, if we click on the D6 cell, we can see its formula is just like the D5 cell. To sum up, the result sheet will look like this. 👇

Formula Copied through Fill Handle

Note:

You can copy your formulas or values to the right, left, or up also using this way. For this, drag the cursor to right, left, up, or to the direction in which you want to copy the cells.

Read More: How to Enable Drag Formula in Excel (With Quick Steps)


2. Use Keyboard Shortcut

Besides, you can use the Fill Handle and its features by using a keyboard shortcut. Follow the steps below to accomplish this. 👇

Steps:

  • At first, select all the cells where you want to copy your formula or value. When selecting, keep in mind that the first cell’s formula or value will be copied through. Now, press the Ctrl+D key.

Use Keyboard Shortcut to Use Fill Handle in Excel

Finally, all the cells will now have the same formula as the first selected cell. We can understand it by focusing on any other copied cell. For instance, if we click on the D6 cell, we can see its formula is just like the D5 cell as the D5 cell was the first cell in our selection. To sum up, the result sheet will look like this. 👇

Formula Copied through Keyboard Shortcut

Note:

To copy the formula rightwards, select the cells and press Ctrl+R.

Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)


3. Use the Fill Button

Moreover, you can use the Fill Handle feature by using the Fill button. It’s available in Excel since the 2013 version.

Follow the steps below to accomplish this. 👇

Steps:

  • At first, select all the cells where you want to copy your formula or value. When selecting, keep in mind, that the first cell’s formula or value will be copied through.
  • Subsequently, go to the Home tab >> click on the Editing group >> click on the Fill button >> select the Down option from the Fill dropdown list.

Use Fill Button to Use Fill Handle in Excel

Thus, all the cells will now have the same formula as the first selected cell. We can understand it by focusing on any other copied cell. For instance, if we click on the D6 cell, we can see its formula is just like the D5 cell as the D5 cell was the first cell in our selection. To sum up, the result sheet will look like this. 👇

Formula Copied by Fill Button

Read More: [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)


4. Double-Click on Fill Handle Icon

You can also use the Fill Handle’s features by double-clicking on the Fill Handle. Follow the steps below to do this. 👇

Steps:

  • At first, click on the cell which you want to copy or whose formula you want to copy. Subsequently, place your cursor on the bottom right corner of the active cell. Thus, the cursor will turn into a black cross sign. Now, double-click on the cursor.

Double-Click Fill Handle to Use the Fill Handle in Excel

Thus, the formula of the active cell will be copied to all the cells below. We can understand it by focusing on any other copied cell. For instance, if we click on the D6 cell, we can see its formula is just like the D5 cell as the D5 cell was the active cell. To sum up, the result sheet will look like this. 👇

Formula Copied Through Fill Handle

Note:

In this method, the formula will not be copied to the cell if there is no value in any column at that row.

Read More: How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)


4 Examples of Using Fill Handle in Excel

1. Autofill a Formula

You can autofill a formula to the cells you want using the Fill Handle. Say, you have two columns ‘A’ & ‘B’ to multiply at the ‘A*B’ column. For the first data, the formula should be B5*C5 at the D5 cell.

Now, if we use Fill Handle, we don’t need to write the formula individually anymore. We can just use the Fill Handle to copy the formula downward.

Use Fill Handle in Excel to Copy Formula

Read More: How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)


2. Autofill a Series

You can also autofill a series by using the Fill Handle. Say, we have two data of a series as 1 and 3.

Use Fill Handle in Excel to Autofill a Series

Now, we don’t need to write the other data of the series. We can use the Fill Handle to autofill the series.

Auto filled Series

Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)


3. Autofill Date/Month/Year

Now, you can also autofill a date or month, or year by using the Fill Handle. Say, we have a date as 29th May 2022 (05/29/2022).

Use Fill Handle in Excel to Auto Fill a Date

Now, you can use the Fill Handle to autofill the dates.

Auto Filled Dates


4. Autofill Weekdays

You can also use the Fill Handle to fill the weekdays. Say, we have data as Monday which is a weekday.

Use Fill Handle in Excel to Auto Fill Weekdays

Now, use the Fill Handle to autofill the weekdays downward.

Auto Filled Weekdays


Important Note

An important thing when copying the formula by Fill Handle is cell reference. If the cell reference is relative and Fill Handle is dragged to copy the formula, the formula will be copied dynamically. This means the formula will have the cell references changed. But, if you want to copy the value or don’t want to change the cell reference of a formula, make the cell reference absolute in the formula. Put a dollar sign ($) inside the cell reference, or press the F4 key to make the cell absolute.


Conclusion

So, in this article, I have shown you the 4 easiest ways to use the Fill Handle in Excel. Use any of these quick methods to accomplish the result in this regard. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo