The Excel fill handle is a very quick way to copy text items or a series of values in a range. This feature is known as AutoFill.
With the help of the fill Handle (a small box at the lower right corner or the active cell), you can copy a cell or automatically complete a series.
Below are situations that call for the use of Excel Autofill.
Copy cell values using the fill handle Tool:
Here’s a scenario: assuming that a data table requires you to enter the same content in every cell in the second column. Instead of typing the same thing over and over to fill all the cells, you can use the AutoFill Handle instead.
Now here’s how to fill column in excel with same value using the Fill Handle:
- Fill in the first cell in a row or column to start off the entry.
- Activate or select the cell you entered, and then click and hold the Fill Handle (the small black square at the bottom-right corner of the active cell).
- Drag the mouse to the right (if you’re filling a row of items) or downwards (if you’re filling a column of items).
As you drag outside the selection, a tooltip appears, showing the text that Excel is filling for each cell. Dragging the fill handle inside will clear the values.
Excel fill handle shortcut
There are two other quick techniques to copy cell values. One still involves using the mouse and the other technique uses the keyboard.
Instead of dragging the fill handle to copy the cell values, just double click on it. Excel will copy the cell value to the rest of the cells below. The disadvantage of this technique is that when it spots an empty row, then it stops copying.
This shortcut involves using the keyboard as in the steps below:
- Fill the value to be copied in the first cell.
- With the cell being the active cell, press Shift plus the down arrow key (if you are filling a column) or Shift plus the right arrow key if you are filling a row) up to where you want to fill the content to.
- Then press Ctrl+D (to fill down) or Ctrl+R (to fill right).
See Video Below:
To fill down a series using the fill handle
Here’s a scenario: A table of data requires you to enter the 12 months names across the first row of the table. A quick way to perform this task is to use the AutoFill feature.
Note: In this case, the series could also be the 7 days in a week or just a series of numbers.
Below is how you fill down series of values using the Fill handle:
- Enter a couple of cells in a column or row to start off the series. You can fill in only one cell, though. But AutoFill works more accurately when it has a little more data to work with.
- Select the cells you filled, and then click and hold the fill handle. The pointer changes to a plus symbol (+) when the mouse is in the correct place.
- Now drag the mouse down (if you’re filling a column) or to the right (if you’re filling a row). As you drag, a tooltip appears, displaying the text that is being generated for each cell.
- On releasing the mouse button, Excel will fill the series with day/month names.
AutoFill Bonus Tip 1
While you’re dragging, you can press and hold the Ctrl key to change the way fills a list. For instance, after filling in at least two or three cells, holding down Ctrl while dragging tells Excel to just copy the list multiple times, rather than look for a pattern.
AutoFill Bonus Tip 2
Apart from the month names, Excel can generate many other built-in series including the following:
- Qtr 1 will continue to Qtr 2, Qtr 3, Qtr 4, Qtr 5 and so on.
- MON will continue to TUE, WED, THU and so on.
- JAN will continue to FEB, MAR, APR and so on.
- Day 10 will continue to Day 11, Day 12, Day 13 and so on.
- 1st year will continue to 2nd year, 3rd year, 4th year and so on.
AutoFill Bonus Tip 3
If you need to fill a series of odd numbers, enter 1 in the first cell and 3 in the next cell. Then select 1st Cell: 2nd Cell (e.g. B1:B2) and drag the fill handle.
AutoFill Bonus Tip 4
There are other fill options as well. One cool option is Fill Weekdays. To see how it works, follow the steps below:
- Enter a date in a cell and make it the active cell.
- Right-click, and drag the fill handle down several cells. A Tooltip shows that you are filling the series with daily dates.
- Release the mouse button for more other options.
- Choose Fill Weekdays to fill in only Monday through Friday dates.
Creating your own AutoFill Lists (Custom List)
There is a collection of AutoFill lists that Excel recognizes and refers to every time you use the feature. However, you can add your own lists to the collection, which extends the series Excel recognizes.
For example, you work at a company and you constantly need to type the name of colors every day. Here’s how to add these color names to Excel AutoFill:
- Go to File → The Excel Options window appears.
- Select the Advance Section
- Locate the General heading and at the bottom of that section, click Edit custom lists.
- In the “Custom lists” box select New List.
- In the “List entries” box type in your list separated with a comma as shown in the picture below:
- After typing your list, click Add to store your list.
- Click OK to close the Custom list window and OK again to close the Excel Option window.
Monday 31st of August 2020
when creating a custom list, I don't seem to be able to use the keyboard command for auto-fill. Any advice?
Tuesday 1st of September 2020
If you are filling the cells with the same values, just fill only the first cell and select it plus the remaining cells you wish to fill, then press Ctrl+D (to fill down) or Ctrl+R (to fill right).