Tanjim Reza
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.
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.
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.
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.
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. 👇
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.
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. 👇
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.
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. 👇
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.
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. 👇
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.
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.
Now, we don’t need to write the other data of the series. We can use the Fill Handle to autofill the 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).
Now, you can use the Fill Handle to autofill the 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.
Now, use the Fill Handle to autofill the weekdays downward.
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
- [Fixed!] Excel VLOOKUP Drag Down Not Working (11 Possible Solutions)
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
- How to Fill Down to Last Row with Data in Excel (3 Quick Methods)
- Fill Down to Next Value in Excel (5 Easy Methods)
- How to Drag Formula Horizontally with Vertical Reference in Excel
- How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)
