Autofill In Excel

What Is Autofill In Excel?

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].

How To Use Excel Autofill?

We can use Autofill In Excel in 2 ways,

• Access from the Excel ribbon.
• Use it manually in the worksheet.

#Access from the Excel ribbon.

• Select the cell or cells.
• Select the “Home” tab > go to the “Editing” group > click on the “Fill” option drop-down, as shown below.

# Use it manually in the worksheet.

1. Select the cell or cells.
2. Click the ‘+’ cross sign or the fill handle at the bottom-right corner of the cell and drag it in any direction as required.

Autofill Options In Excel

When we use the Autofill In Excel, we get five options, as shown below.

1. Copy Cells.
2. Fill Series.
3. Fill Formatting Only.
4. Fill Without Formatting.
5. Flash Fill.

We will consider the following data for all the five Autofill Options in Excel to understand in detail.

• Copy Cells – It copies the selected cell’s value to the selected cell range.

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].

• Fill Series – Excel identifies a sequence for selecting multiple cells. The Fill Series fills the range with values based on the sequence detected.

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].

• Fill Formatting Only – It applies the formatting of the initially selected cell to the selected cell range.

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.

• Fill Without Formatting – It copies the selected cell’s value to the selected cell range.

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].

• Flash Fill – It fills the selected range automatically w.r.t the pattern detected. We can use it to extract values from a data cell or combine the values of different data cells.

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.

Autofill Numbers In Excel

Using the Autofill Feature In Excel, we can use Autofill Numbers in the cells in a sequence.

In the table, the data is,

• Column A shows the Numbers.

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.

AutoFill Dates In Excel

We can Autofill Dates In Excel in the cells in a sequence using the this feature.

In the table, the data is,

• Column A shows the Dates.

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

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].

Autofill Formula In Excel

We will now see how the Autofill Formula in Excel works.

We have the following data in the table.

• Column A shows the value, V1.
• Column B contains the value V2.
• Column C contains the Sum of V1 & V2.

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. 

Turnoff Autofill In Excel

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 Not Working

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”.

Frequently Asked Questions

Download Template

This article must help understand Autofill In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

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 – 

• Strikethrough In Excel
• Name Range In Excel
• Group in Excel