Tanjim Reza
When we need to work with a large set of data with the same formula or pattern; it is tiring, boring, and time-consuming 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. So, today, I will explain how to use this Fill Handle in Excel.
In this article, we will show you the tricks along with examples to use the Fill Handle in Excel. Here, we will use Microsoft 365 version.
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 plus/cross sign in the bottom-right corner of the active cell in the worksheet. It was first introduced in the 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.
- First, open a workbook >> from Top Ribbon >> click on the File tab >> choose Options.
At this time, the Excel Options window will appear.
- Then, click on the Advanced tab >> put a tick mark on Enable fill handle and cell drag-and-drop option >> click on the OK button.
How to Use Fill Handle in Excel: 7 Suitable Examples
Here, we will describe the 7 suitable examples to use Fill Handle in Excel. For your better understanding, we’re going to use a sample dataset to explain them.
Actually, there are several ways to apply this Fill Handle feature. See the examples and choose the appropriate one for your work.
1. Drag the Fill Handle Icon to Autofill a Series
You can use this Fill Handle and its features by simply dragging the Fill Handle icon toward the direction where you want to copy the cells. Now, we will autofill a series by dragging the Fill Handle icon.
Suppose you have a large dataset and you want to give a serial number to them, in this case, you can use the Fill Handle only for a numeric series. Again, if you want to make a series with both alphabetic and numeric terms then you can do that too.
1.1 Create Only Numeric Series
You don’t need to write a series manually. You can create a numeric series with the Fill Handle.
- First, write “1” and “2” respectively in B5, and B6 cells >> then drag this Fill Handle icon up to the required cell.
Below, you can see the result.
1.2 Make New IDs with Fill Handle
Here, we will make some IDs with this Fill Handle icon. Basically, we will create a series of not only numbers but also some alphabetic letters.
We have the product id in the C column. In the C5 cell, the id is PDCT104. We want more IDs like PDCT105 and PDCT106.
- So, select C5 cell >> drag the Fill Handle up to C12 cell >> get the series of IDs.
Read More: How to Enable Drag Formula in Excel (With Quick Steps)
2. Use Keyboard Shortcut to Copy-Paste a Formula Within Same Column
In the dataset, we have the price per unit and the total number of units. Now, we want to multiply their values to get the total price in the G column. So, for the first multiplication data, we have used =F5*E5 in the G5 cell as the formula. If we use Fill Handle, we no longer need to write the formula individually.
We will extend the formula to all the cells below using the Fill Handle.
- Select G5:G12 cells >> press CTRL+D >> you will get the total prices of all the products.
Below, you can see that the formula has been copied for all other cells.
3. Apply the Fill Button to Copy-Paste a Formula Within the Same Row
We have the daily sales of some products. Now, we want to find out the total sales. So, in the C14 cell, we have used the SUM function to get the total sale of the air conditioner. Similarly, we want to find the total sales of other products. Here, we’re going to use the Fill Handle feature. Moreover, you can use the Fill Handle feature by using the Fill button.
It’s available in Excel since the 2013 version.
- Select cells C14:G14 (the formula is in the C14 cell). When selecting, keep in mind that the first cell’s formula or value will be copied through.
- Then, from the Home tab >> go to Editing >> from Fill >> choose the Right 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 G14 cell, we can see its formula is just like the C14 cell as the C14 cell was the first cell in our selection. To sum up, the result sheet will look like below.
4. Dragging Fill Handle Icon to Autofill Weekdays
Say, we have data as Sunday which is a weekday. Now we need other weekdays.
- So, drag the Fill Handle icon to autofill the weekdays downward.
Below, you can see the output. If you drag the Fill Handle icon more, then you will get repeated weekdays.
Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)
5. Use the Fill Handle Icon to Autofill Date/Month/Year
Here, we have a date of 5th May 2023 (05-May-23). We want more dates afterward 05-May-23.
- Use the Fill Handle to autofill the dates.
See, you get all the dates one by one till the cell you dragged the Fill Handle icon.
Read More: [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)
6. Double-Click on Fill Handle Icon to Repeat the Same Text Data to Other Cells
Here, we want to copy a text value in all other cells. Manually writing the text value to all cells is time-consuming and boring work. Here comes the Excel Fill Handle feature to make your job easy. And you can use this Fill Handle’s features by double-clicking on the Fill Handle icon.
- First, click on the cell which you want to copy or whose formula you want to copy.
- 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.
Finally, the text of the active cell will be copied to all the cells at a moment.
Read More: How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)
7. Use Fill Handle Icon for Different Purposes with Multiple Columns
The most interesting part is you can use the Fill Handle icon with more than one column at a glance. The Fill Handle can instantly copy various types of data at once.
Like we have text data in the B5 cell which we want to simply just copy to the other cells. In the C5 cell, there is a date and we want to get more dates. Then, the D5 cell contains a weekday of the mentioned date from the C5 cell, we want the other weekdays too. In the E5 cell, we used a formula and want to use the same formula for the other products.
Here, we can use the Fill Handle icon to complete the entire dataset at once.
- Select the cells B5:E5 >> drag the Fill Handle icon at the corner of the rightmost cell (E5) from the selection.
And get all the target data.
Read More: How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)
Features of Auto Fill Options in Excel
There is a feature named Auto Fill Options in Excel. After using the Fill Handle icon, immediately you will get this feature beside the bottom-most item.
- After dragging the Fill Handle icon to get weekdays >> I clicked on the Auto Fill Options >> so you can see there are so many options under this Auto Fill Options.
- As I selected Fill Weekdays, the weekdays are organized excluding the weekends ( Saturday, Sunday).
In case of date, you will get some more options from the Auto Fill options. As I selected Fill Months, the date is filled with the month serial.
In the case of the numeric or formula-based data, you will get the following options from Auto Fill Options. The Copy Cells option is selected in the built-in process.
Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)
Use of Flash Fill Feature to Separate Data in Excel
There is a very useful feature called Flash Fill under the Auto Fill Options of Excel. Flash Fill will copy the pattern you used for the cell. For a clear concept see the following example.
There are product ids with the combination of brand, and product codes. Now, I want to separate the brand code and product code from the IDs.
- Copy the B5 cell value to the C5 cell >> keep only the brand code (letters only) >> drag the Fill Handle icon >> from Auto Fill Options >> click on Flash Fill >> get all the alphabetic codes.
- Write product code from product id in D5 cell >> double-click on Fill Handle >> then Auto Fill Options will be visible >> from Auto Fill Options >> check Flash Fill >> and get the product codes only.
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.
Frequently Asked Questions
1. Why is my fill handle not working in the Excel formula?
You must check if the Fill Handle is enabled in your Excel. So, from the File tab >> go to Options >> click on the Advanced tab >> put a tick mark on Enable fill handle and cell drag-and-drop option >> click on the OK button.
2. How do you use AutoFill to copy formatting in other cells?
After dragging the Fill Handle icon >> go to Auto Fill Options >> check Fill Formatting Only.
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
So, in this article, we have shown you 7 suitable examples of how to use the Fill Handle in Excel. Use the preferred methods to accomplish the result in this regard. We 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)
