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
Fill Handle in excel is used for filling up the data by creating a series of value which either follow a pattern or we can check if the values we entered are of series or not. It is very easy to implement. Fill Handle is available at the right bottom of any cell. Enter some values such as number or alphabets or dates in at least 3 cells to have a pattern. Select the cells with values and then either drag the Fill Handle or double click will also work here.
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 –
