How to Save Time Using the Microsoft Excel Fill Handle

Do you work with sequential datasets on a regular basis? These don’t have to be anything fancy – days of the week, numbered lists, and dates are all common types of cell values that frequently need to be repeated. Instead of having to type them all manually, you can save yourself hours of tedious data entry by using the Excel fill handle.

You can even choose different options to get Excel to fill in the exact type of pattern you need. Here’s how it works!

How the Excel Fill Handle Saves You Time

Let’s say you’re typing the numbers 1-10 in the rows in column A. That takes a while to do manually, but with the Excel fill handle, you can enter the first couple of numbers and then rely on it to take care of the rest. 

But, numbers are only one type of data. It’s important to keep in mind that the Excel fill handle will behave differently depending on the content of the selected cells.

• When formulas are present in the selected range, references are updated and the formulas are copied to new cells. 
• Values are copied when the specified range contains text or integers that lack a pattern that Excel recognizes. 
• When there is a pattern Excel recognizes in the text or numbers in the selected range (including custom lists), the pattern is carried on.

Even though the fill handle will autocomplete different types of information, the directions for using it are luckily still the same.

How to Use the Excel Fill Handle

The fill handle in Excel is one of the first time-saving tools many new Excel users learn, so luckily, it’s pretty easy to get the hang of it!

We can use the simple data set with numbers from 1-10 to practice.

• In cell A1, insert the number 1.
• In cell A2, insert the number 2.
• Select both cells, and then hover your mouse over the small square in the bottom-right corner of the selection (the fill handle). The mouse pointer will turn into a small plus (+).
• Drag the mouse down. The first two digits (in this case, 1 and 2) form a pattern that Excel utilizes to fill in the full series.

Another way to accomplish this, is rather than typing two values (1 and 2), you can type 1 and then drag down while holding down the Ctrl key. This is demonstrated in the short video below:

You can also double-click the fill handle if the adjacent column contains data as well. Based on the information in that column, it will automatically fill the list to the last cell.

One neat feature to keep in mind is that the fill handle functions in all four directions. You can fill up, down, left, and right.

Can the Excel Fill Handle Autocomplete All Types of Data?

The fill handle is pretty smart and can find patterns in all different types of datasets. As mentioned above, if no apparent pattern is present, the fill handle will complete the set using values.

Here are a few scenarios you can experiment with to get the hang of things!

Incremental Numbers

In the first example, we used numbers with an increment of one to establish the pattern in our dataset.

The same rule applies to increments of two, three, four, five, and so on.

For example, if you start a column with the numbers 5 and 10, the Excel fill handle will continue the pattern with 15, 20, 25, 30, etc.

Date Formats

The fill handle will recognize date formats available in Excel. Simply type dates in column A, like 1/1/2030 and 1/7/2030, and use the fill handle to autofill as many cells as you need.

Days of the Week

Making a calendar? Use the Excel fill handle to autocomplete your dates so you don’t have to type them in repeatedly!

Try creating a basic calendar with Monday in cell A1 and Tuesday in cell A2 – or, you can abbreviate with Mon and Tue, and the fill handle will know what you mean!

Drag the fill handle down to complete the rest of the days of the week. Note that if you drag it down further than seven rows, it will restart the list on Monday and continue from there. 

Note: if you have your own custom list you’d like the fill handle to recognize, add it to your custom lists (Excel Options > Advanced > Edit Custom Lists).

Excel Fill Handle Options

So far, you know the Excel fill handle can autocomplete lists based on patterns it finds in the first few data points. 

But, we have more options available. The Excel fill handle has a handy “options” button that pops up in the bottom right corner of the last cell after selecting the range of data that you’d like to autofill.

The options vary depending on the type of data you’ve entered – here’s what they mean:

• Fill Series – This is the default option that completes the recognized pattern.
• Copy Cells – This will copy and paste the cell values. For example, if you enter the values 1 and 2, instead of continuing with 3, 4, 5, etc., it will repeat 1 and 2 throughout the rest of the selected cells. 
• Fill Formatting Only – Extends the formatting of the selected cells, but not the values in them.
• Fill Without Formatting – This is the opposite of fill formatting only, so it extends the values but not the formatting.
• Flash Fill – Completes the list of cell values based on the values of adjacent cells. The command is also available at Data > Flash Fill.

Fill handle options for dates operate in the same way, but offer different abilities. 

• Fill Days – Like the fill series option for numerical values, this is the default option for dates and continues the recognized pattern.
• Fill Weekdays – Autofills dates excluding weekends.
• Fill Months – Keeps the day consistent, but increases the months in increments by one.
• Fill Years – Keeps the day and month consistent, but increases the years in increments of one.

The Excel fill handle is a powerful but easy-to-learn tool that saves Excel users a ton of time when filling out their spreadsheets. If you need an easy fix for repetitive data entry, it’s one of the best features available.

Do you have any time-saving tricks using the Excel fill handle? Let us know in the comments!