How to Use Fill Handle in Excel (7 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Fill Handle icon


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.

Go to Excel 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 Enable Fill Handle in Excel


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.

How to Use Fill Handle in Excel

Below, you can see the result.

Create a Numeric Series with Fill Handle

Note: You need to write 2 numbers and then drag the Fill Handle icon. Basically, Excel needs a pattern to return other values based on that. Otherwise, you must use the Auto Fill Options after using the Fill Handle icon.

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.

Make New IDs with Fill Handle in Excel

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.

Use Keyboard Shortcut to Copy-Paste a Formula

Below, you can see that the formula has been copied for all other cells.

Copy a Formula from G5 cell to G12 cell

Note: If you want to copy the formula from left to right of a dataset, then after selecting the row-wise cells >> press CTRL+R.

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.

Apply the Fill Button to Copy-Paste a Formula Within the same Row

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.

Copied a Formula Row-wise


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.

Dragging Fill Handle Icon to Autofill Weekdays

Below, you can see the output. If you drag the Fill Handle icon more, then you will get repeated weekdays.

12 Inserting Weekdays with Fill Handle

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.

Use the Fill Handle Icon to Autofill Date/Month/Year

See, you get all the dates one by one till the cell you dragged the Fill Handle icon.

Inserting Date with Fill Handle icon

Note: You can’t get the series of dates or weekdays by using any methods like the Fill button or the keyboard shortcut. In these methods, the date will be only copied to the other cells.

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.

Double-Click on Fill Handle Icon to Repeat the Same Text in Excel

Finally, the text of the active cell will be copied to all the cells at a moment.

I have copied all text with Fill handle

Note: This method (double-clicking on the Fill handle icon) works only downwards. In a new sheet, if you have a single data also then this method will not work. Basically, for this method, you have to show Excel a pattern up to which cell you want to copy the cell value. To do so, make the data except for the cells where you want the repetition and then double-click on that cell.

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.

There are different types of data

  • Select the cells B5:E5 >> drag the Fill Handle icon at the corner of the rightmost cell (E5) from the selection.

Use Fill Handle Icon for Different Purposes with Multiple Columns

And get all the target data.

Copied Multiple Data with Fill Handle icon

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).

Auto Fill Options for Weekdays in Excel

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.

Auto Fill Options for Dates in Excel

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.

Auto Fill Options for Numeric data in Excel

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.

Using Flash Fill Feature in Excel

  • 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.

Using Flash Fill Feature to Get Product Code


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.

Use of Absolute cell reference in Excel


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.

Practice Section


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

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo