We all use the fill handle to drag out lists, and to quickly drag out a numbers or months.

A few things we need to know about the fill handle:

You can use the fill handle to drag series or lists on to your spreadsheet.

The fill handle: Create custom lists

Little known fact that we can edit these lists. Which is brilliant as in my day job we have work specific departments, machines all sort of custom lists that we can add here.
You need to go to File-Options-Advanced, then hit the button “Edit Custom List…”

You will be presented with this dialog box.

On the left the current lists, on the right is what you want to add.

-You can add a list manually on the right, one item per line.
-You can also add an existing list by using the arrow below.
This will let you select a range of cells from your workbook.
-You can also import a file like a txt file with one item per line.














Edit Custom List window in excel














Excel drag handle custom list setup.

Below I simply typed in Jan, then started to drag with the fill handle. Excel figured out that Jan was part of one of my series and started to fill in the rest.

Excel fill handle default fill














Using Excel dragging handle to drag series on to a worksheet

Use the fill handle to create series of numbers

The most common is when we need to go 1,2,3,4 and so on.
We can achieve this a couple of ways:
1. We can either drop 1 and 2 in the first two cells, then highlight both cells and drag.
This way excel will figure out that the series we want is incrementing by one.

Excel dragging handle














Using Excel drag to drag out numbers
2. We can just type 1 in to a single cell and hold the Ctrl down on our keyboard to modify the logic in the drag.

Below the left had side is without the Ctrl key modifier and the right with the Ctrl modifier

Excel fill handle control key














The fill handle used with Ctrl key modifier

Create a custom series of numbers with a custom series of number

Let’s say you want a custom step of between your numbers.
You can create it the same way.
Just set up your initial numbers, select both of them and drag:

Custom series of numbers in excel fill handle














Fill in a custom series of numbers

Fill in a custom list with right click drag

The fill handle has another neat feature.
A right click-drag will give you even more functions. When you drag this way it will give you a small dialog box.
I use this mostly used when I have to come up with series of months or even weekdays.

Excel fill handle right click drag








 You can experiment even more functions with excel drag handle right click drag. You can create custom lists, with names, machine numbers, car makes, or whatever your need! All you have to do is to create a custom list, with your own list entries, then next time this will be available for you ro drag. Just go and edit your custom list, then in the dialog box you can use your custom list.

Check out our article about merging cells in excel like a pro!

ExcelAngel has a facebook page! Come and visit us,

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]