Excel Fill Handle

Excel Fill Handle (Table of Contents)

• Fill Handle in Excel
• Enable fill handle if you cannot find the fill handle in Excel
• How to Use Fill Handle in Excel?

Fill Handle in Excel

Often in excel, we need to copy the content of one cell to several other cells in your worksheet. Generally, we use copy and paste method to do that, but this will take some of your productive time. Instead, we can use excel fill handle to quickly copy and paste content to adjacent cells in the same row or column.

Excel fill handle is one of those features that, once you find out about it, you wish you had been using it all along. Use it with numbers, dates, or text to create a series of data — from basic to complex. The less you use copy and paste, the more productive you will be.

In the active cell of the worksheet, the fill handle is a small black box at the bottom right corner, as shown in the below image.

A basic level of knowledge about fill handle excel could save you tons of your productive time at your workplace.

It is a tool, which can be used to autocomplete a series of lists in excel. Generally, in excel we enter serial numbers. Let us say serial numbers from 1 to 10 from cells A1:A10. We usually type 1, 2, 3, 4… Like that, isn’t it?

Instead, of manual typing, we can use excel fill handle to auto-fill the series of numbers, dates, days, etc…

Enable fill handle if you cannot find the fill handle in Excel

If you do not find fill handle in excel you need to do some settings here. We can enable the fill handle option (although it is enabled by default, it may happen that you disable it by mistake).

Step 1: Go to File.

Step 2: Go to Options.

Step 3: Select Advanced.

Step 4: Make sure that you have to Enable fill handle and cell drag-and-drop option checked.

How to Use Fill Handle in Excel?

Fill handle in excel is very simple and easy to use. Let understand the working of Excel Fill handle by some example.

Example #1

Assume you want to fill the numbers from 1 to 20 from cell A1 to A20. Here are the steps to auto-fill your series of numbers.

Step 1: Enter numbers 1 and 2 from A1:A2 and select those two cells.

Step 2: Hover the mouse over the bottom right edge of the selection; you will see a plus icon appear – and drag until you find the cell A20.

Example #2

Pro tip: In the above case, you need to drag until you find your desired cell. However, if there is a value in the adjacent column, you can hover the mouse over the right edge of the selection and double-click. It will automatically fill the list to the last cell based on the data in the adjacent column.

Look at the below image where I have a list of cities from B1 to B20. Now I need to insert serial numbers from A1 to A20.

I just need to type 1 and 2, select the two numbers, and double-click. It will fill until the cell A20.

Example #3

Autofill Numbers that Decrement by 1

As we seen in the previous example we can auto-fill numbers incremented by one. Similarly, we can insert numbers decremented by 1.

Step 1: Type 10 and 9 in the cells A1:A2.

Step 2: Since we do not have value in the adjacent column we cannot double-click on the fill handle. Instead, drag until the cell A10.

Step 3: Now it will insert the numbers for you.

Example #4

Autofill Weekday Names (Both Short Name & Full Name)

We can use fill handle in Excel to autocomplete weekday names. It could be either the three alphabets nomenclature (Mon, Tue…) or the full name ( Monday, Tuesday )

Step 1: Type Mon & Tue from cell A1:A2. Now type Monday & Tuesday from B1:B2.

Step 2: Now keep dragging the fill handle to auto-fill the weekday names.

Note: Once all the week names end it will start over again from the day where you have started. In the above case, I started with Monday and ends on Sunday. Once I cross all the days in a week it will again start from Monday only.

Example #5

Autofill Dates

We can auto fill dates too. Any date format recognized by the excel it will auto fill. We can increment the date by 1, by 2, like that we can auto fill.

If we drag and drop the above dates, the first column will increment the date by 2 i.e. it will insert alternative days. The second column will increment by 1.

Example #6

Autofill Weekdays Dates

We can auto fill only weekdays dates. Click on that Auto Fill Options.

These are the below options, you will get. Now select Fill Weekdays.

It will insert only weekdays.

Similarly, you can exercise all the options listed in the above image.

Things to Remember about Excel Fill Handle

When we are inserting numbers, we get so many fill options. Look at all the options one by one.

• Copy Cell: If you select this option, it will simply copy paste the cells from the above.
• Fill Series: It fills the cells with numbers incrementing by 1.
• Fill Formatting Only: It only fills the formatting and not the values.
• Fill Without Formatting: This options fill the cells based on the recognized pattern but do not copy the formatting.

Recommended Articles

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

1. MS Excel: FLOOR Function
2. Guide To TRUE Function in Excel
3. How To Use Excel SUBSTITUTE Function?
4. OFFSET Excel Function with Examples