The Autofill In Excel is a feature that automatically fills the values in the selected cells by dragging the ‘+’ cross sign or the fill handle seen at the bottom-right corner of the selected cell. We can use the Autofill in all directions (left, right, up, and down). We can use this feature for different values like dates, months, formulas, etc.
For example, enter ‘1’ in cell A1 and ‘2’ in cell A2, now drag the ‘+’ cross sign or the fill handle seen at the bottom-right corner of cell A2 till cell A4. The Autofill In Excel will automatically fill the series i.e., ‘3’ in cell A3 and ‘4’ in cell A4 [Only the values & not the formatting].
We can use Autofill In Excel in 2 ways,
#Access from the Excel ribbon.
# Use it manually in the worksheet.
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
When we use the Autofill In Excel, we get five options, as shown below.
We will consider the following data for all the five Autofill Options in Excel to understand in detail.
Drag cell A1 using the fill handle till cell A4. Select the “Copy Cells” option, we get the output as shown above. [The number series is replaced, and the value in cell A1 is auto-filled to other cells].
Drag cell A1 using the fill handle till cell A4, and select the “Fill Series” option. We get the following output. [The number sequence is auto-filled to the cells].
First, select cell A1 > go to the “Home” tab > go to the “Font” group > click on the “Text Highlight Color” drop-down > select any color, here Yellow. Then, drag cell A1 using the fill handle to cell A4, and select the “Fill Formatting Only” option.
[Note: We can use other formatting options like Fonts, Font Size, Font Color, etc., to a cell and then drag it to the other cells].
We get the following output. [The formatting and the values are auto-filled to the cells, and the number series is replaced].
Select the “Fill Formatting Only” option, we get the output. The formatting of cell A1 is copied but not its value.
If multiple cells are selected, it auto-fills the values of the range based on a pattern but not the formatting.
First, select cell A1 > go to the “Home” tab > go to the “Font” group > click on the “Text Highlight Color” drop-down > select any color, here Yellow. Then, drag cell A1 using the fill handle till cell A4, and we get the following output. [The formatting and the values get copied to the cell range].
Select the “Fill without Formatting” option, we get the following output. [The formatting is only in cell A1].
Consider the following data, with First Name and Last Name. We will combine the names using the “Flash Fill” option.
Select cell C2, and combine the name “George-Bush”.
Drag the fill handle from cell C2 to cell C5. The value in cell C2 is auto-filled, as shown below.
Click the “Flash Fill” option, we get the following output.
The output is shown above. The names are combined and auto-filled.
Hence, we learned about the five Autofill Options in Excel, along with examples.
Using the Autofill Feature In Excel, we can use Autofill Numbers in the cells in a sequence.
In the table, the data is,
The steps to Autofill Numbers In Excel are as follows:
Step 1: Select cells A2 and A3 to copy the pattern till cell A6.
Step 2: Drag the fill handle from cell A3 to cell A6. The output appears as the number series auto-filled following the sequence.
We can Autofill Dates In Excel in the cells in a sequence using the this feature.
In the table, the data is,
The steps to Autofill Dates In Excel are as follows:
Step 1: Select cell A2 and drag the fill handle to cell A6.
The output is shown below, with the date’s series auto-filled following the sequence.
Autofill Excel Shortcut is convenient when we have to copy a formula. It is because, the copy-paste method copies the result of the formulas and not the formula itself.
Consider the following table,
The Autofill Excel Shortcut to copy the formula in a column is,
Select cell D6 and press the Excel shortcut keys “Ctrl+D”.
The formula in cell D5 gets copied to cell D6 [Next cell, same column].
The Autofill Excel Shortcut to copy the formula in a row is,
Select cell E2 and press the Autofill Excel Shortcut keys “Ctrl+R”.
The formula in cell D2 gets copied to cell E2 [Next cell, same row].
We will now see how the Autofill Formula in Excel works.
We have the following data in the table.
The steps to Autofill Formula in Excel are as follows:
Step 1: Enter the formula =SUM(A2,B2) in cell C2.
Step 2: Press the “Enter” key. We will get the following output.
Step 3: Drag the formula, using the fill handle, from cell C2 to cell C5.
The output is shown above. The formulas applied, and auto-filled are in column D, for our reference, in the image below.
We can Turnoff Autofill In Excel as follows,
Step 1: Select the “File” tab > click on the “More…” option from the list > click on the “Options” from the drop-right list.
Step 2: The “Excel Options” window opens. Then, select the “Advanced” option on the left of the “Excel Options” window > deselect the “Enable fill handle and cell drag-and-drop” checkbox from the “Editing Option” group > click on “OK”.
Autofill In Excel may not work if it is disabled. We can enable the feature as follows,
Step 1: Select the “File” tab > click on the “More…” option from the list > click on the “Options” from the drop-right list.
Step 2: The “Excel Options” window opens. Then, select the “Advanced” option on the left of the “Excel Options” window > “check/tick” the “Enable fill handle and cell drag-and-drop” checkbox from the “Editing Option” group > click on “OK”.
We use the Autofill feature in Excel to enter the data automatically. It is an easy way to add data, reduces manual work, and saves time.
For example, enter the string “Spill the beans” in cell A1, and drag the fill handle from cell A1 to A2. The string is copied to cell A2, as shown in the adjoining image.
We can use the Autofill feature In Excel as follows,
1. Select the cell or cells.
2. Then, drag the fill handle of the cell throughout the cell range. [We can select more than one cell, which helps Excel to identify the pattern or sequence of the selected cells].
For example, we have entered ‘A’ in cell A1 and ‘B’ in cell A2, now drag the fill handle from cell A2 to cell A4. The Autofill feature In Excel will automatically fill ‘A’ in cell A3 and ‘B’ in cell A4, as per the sequence, as shown in the adjoining image.
We can find the Autofill in Excel on the Excel ribbon.
1. Select the cell or cells.
2. Select the “Home” tab > go to the “Editing” group > click on the “Fill” option drop-down, as shown below.
This article must help understand Autofill In Excel, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to Autofill In Excel. Here we will Use Autofill option with Dates/Numbers along with shortcuts and examples. You can learn more from the following articles –
