The Fill Handle in Excel is the command to run the AutoFill feature. While AutoFill is a feature to fill multiple cells automatically.
With the AutoFill feature, you can “Automatically” fill a range in Excel with certain data patterns, such as sequential numbers (1, 2, 3,…), alphabetical order (A, B, C,…), consecutive dates (by day, weekday, month, or year), day/month name order, etc.
Of course, you can also copy-paste without changing the formula and copying the formatting.
There’s a lot more. Let’s see what you’ll get from this AutoFill feature…
Table of Contents:
How to Use Fill Handle in Excel ? (6+ Example)
As a first step, learn how to use the fill handle feature in Excel first. For example, please type 1 in cell A1. Then, follow these steps using the fill handle feature below:
- Hover the cursor to the lower right corner of cell A1.
- When the cursor changes to a plus sign, left click and drag down to cell A10 or any cell you want (for AutoFill multiple rows).
- If you want to AutoFill to the right (multiple columns), just use the fill handle and drag it to the right.
- Release the left click to get the following result:
As you can see, it will copy the values of the cell to another cell based on the fill handle you did.
However, for other purposes, the Fill Handle feature can also fill the series of data based on patterns for various types of data, as you can see in the following image:
Note: After using the fill handle, you will see the AutoFill Option on the last cell (see image). From this AutoFill option button, you can define the AutoFill action to apply. Let’s look at the following examples:
Copy Cells vs Series
By default, when you use the Fill Handle for a number in a cell, Excel will copy the values of that cell to another cell. That’s the result you get from the first example above.
If you want to create a sequence of numbers – like 1, 2, 3, …,10, etc. -, please click the AutoFill button after you do the fill handle. Then select Fill Series as shown below:
Note: This technique only applies if you want to create a sequence of numbers in multiples of one. If you want to make a serial number with a multiple of two, please follow these steps:
- Type the numbers in the order pattern you want into the two cells. For example 1 and 3 in cells C1 and C2.
- Select both cells.
- Hover over the last cell (C2) and do a Fill Handle like the first example above. Results:
Of course, you can also AutoFill the series on the right.
You only need to enter numbers into cells in two columns (according to the pattern/order), e.g. cells C2 and D2. Then, select both cells and apply the fill handle to the right. Only that…
AutoFill Excel for Text
By default, you can only AutoFill “Copy Cells” for text data. But, if you want to AutoFill series using the alphabet, like A, B, C, D, …, Z, you must create a custom list.
Because, by default, Excel only saved some data in the form of text in an AutoFill list, such as the name of the day and month.
Pro Tips: You can learn more about custom lists in the next guide. But, if you want to AutoFill by day or month name, please skip to the next section.
AutoFill Excel for Text + Numbers
If you have data with sequential numbers at the beginning or end of the text, you can also use AutoFill Copy Cells or Series.
For example, I have ten Warehouses. Each Warehouse is numbered from 1 to 10, such as Warehouse – 1, Warehouse – 2, to Warehouse – 10.
For this case, you can type the first data (Warehouse – 1). Then do AutoFill – Series as before. This is the result:
Isn’t that right?
Note: If your Excel software only copies the values of cell A1, please change the fill option to series.
AutoFill Excel for Date (Days, Weekdays, Months, and Years)
Microsoft Excel saved date data as serial numbers. So you can do AutoFill Copy Cells and Series.
Again, to create a sequential data series, please enter dates into the cell. Then do the Fill Handle as before.
For example, Type December 1, 2021, in cell A1. Then do a Fill Handle Series to cell A10. Here’re the results:
By default, like single number series, Excel also enforces multiples of one for date data series.
So, if you want to make a date series with multiples of two, please type two dates that represent these multiples in two cells.
Please repeat the steps for making AutoFill in multiples of two, above. You will get the same result as the following image:
In other cases, you can also create a date series based on the day, weekday, month, and year. Let’s look again at the first example (Date series with multiples of one).
After you do the Fill Handle, please click the AutoFill Options button. You will see four AutoFill options for the date as shown below:
Voila! What do you think?
There are four types of Fill Series for dates, namely:
- Days: for series by day.
- Weekdays: for weekday series (exclude Saturday and Sunday).
- Months: for series by month (Jan to Dec).
- Years: for series by years.
These four types of AutoFill dates can also use a series of multiples of two, three, and so on. Hopefully, the following image can explain it:
At first, I thought this guide would not be very long. As it turned out, it required quite an in-depth explanation.
Because there are other examples of AutoFill Series dates, which are based on the following day or month names:
#1 By Day Name
In the AutoFill example for the text above, I explained that “Excel only saved some data in the form of text in the AutoFill list, such as the name of the day and month”.
That’s right!
You can type one of the day’s names in cell A1 (for example, Wednesday). Then, apply a Fill Handle to cell A10. This is the result:
Note: When AutoFill for a date has reached the last day, Excel will repeat the day. In this example, Excel repeats Wednesday in cell A8. Also, of course, you can use multiples of two when AutoFill for day names.
#2 By Month Name
Likewise, you can also AutoFill month names such as day names. So, if you type February in cell B1, then, AutoFill up to cell B10, you’ll get the same result like the following image:
Note: In addition, you can also use the first three letters of the month’s name. For example Feb, Mar, Apr, etc. Likewise, if you AutoFill for day names, such as Mon, Tue, Wed, etc.
Next… Let’s take a look at some advanced examples below:
AutoFill Excel for Formula & Function
In Excel, formulas and functions are very important in processing data. And when using it, you have to type that function into the cell.
If you only use the formula in one cell (which isn’t always the case), that’s fine. But, what if you use the same formula for multiple cells?
Typing formulas one by one will be very tiring. Also often causes problems. This is where you can use AutoFill to copy formulas to other cells.
Let’s see what you will get.
For example, I have data1 in column A and data2 in column B, Then I use the formula for adding data1+data2 in cell C2 with the formula =A2+B2 as shown below:
After calculating the sum using the formula, please use the AutoFill feature to calculate the next data sum automatically. You will get the same result as the following image:
Note: If you want to sum each of the data from multiple rows/columns with the data contained in one cell, then Excel will give the wrong result. (See the results in column E).
It happened because I used a relative reference as a formula argument (i.e. =A2+A10). When you use AutoFill or Copy-Paste a cell contains a formula with a relative reference. Then Excel will adjust the cell reference automatically.
In this case, Excel will adjust the formula in cell B3 to =A3+A11.
That’s it… The cell reference A10 changes to A11. What do you see in cell A11? Is this the right cell? No. That’s the wrong cell!
Solution: Please use absolute reference for cell A10 in the formula. Just type a dollar symbol ($) between the column and row names. So the formula becomes =A2+A$10. Results:
Easy isn’t it?
In a different case, if you want to AutoFill to the right, then you must use an absolute reference before the column name (like =A2+$A10).
Pro Tips: There are several types of cell references in Excel. All of these types of cell references are useful for completing certain calculations. You can learn about it in the next guide.
AutoFill Excel for Formatting
In AutoFill Options, you can see two options, namely, Fill Formatting Only and Fill Without Formatting.
By default, Excel will use AutoFill Copy Cells. Meanwhile, you will get different results if you use the other two options. See the following example:
As you can see, Copy Cells will copy all the values of the cell including any other formatting (like cell color, font format, etc.). While the other options, each only copies the cell formatting or values.
In another case, if your cell contains a formula/function and applies some formatting. AutoFill will also give the same result. See the following example:
More than just understanding. I hope so…
If you want to become an expert in using Microsoft Excel, understand the guide above and apply it in various cases.
I’m sure… With more practice, you can become an expert – more than ever -. It’s not even just about the AutoFill feature in Excel.
But, if you want to discuss more the AutoFill feature using the Fill Handle in Excel, please leave a reply in the comment form.
Guide Shortcuts: Excel Data Ranges
Previous Chapter: Ribbon
- Range: Get to know columns, rows, cells, and ranges with examples.
- Autofill: You’re here!
Next Chapter: Format Cell