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

AutoFill in Excel Intro
AutoFill in Excel Intro Example
Key Takeaways
  • The Autofill In Excel is a feature that fills subsequent data automatically when we drag the selected cell corners. This feature is an easy and less time-consuming feature.
  • The feature is used to autofill numbers, years, dates, months, and many more data types.
  • The feature is used as a Smart tag that helps fill the sequence, format, months, etc.
  • The feature is used to autofill formulas too. The shortcut to copy the formula in the below column-wise cell is “Ctrl+D”, and in the next row-wise cell is “Ctrl+R”.

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.
How to - 1

# 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.
Options

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

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

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

Fill Formatting Only - 1

We get the following output. [The formatting and the values are auto-filled to the cells, and the number series is replaced].

AutoFill in Excel Options - Fill Formatting Only 2

Select the “Fill Formatting Only” option, we get the output. The formatting of cell A1 is copied but not its value.

AutoFill in Excel Options - Fill Formatting Only 3
  • 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].

Fill Formatting Only - 1
AutoFill in Excel Options - Fill without Formatting 1

Select the “Fill without Formatting” option, we get the following output. [The formatting is only in cell A1].

Fill without Formatting
  • 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.

Flash Fill.1

Select cell C2, and combine the name “George-Bush”.

Flash Fill.2

Drag the fill handle from cell C2 to cell C5. The value in cell C2 is auto-filled, as shown below.

AutoFill in Excel Flash Fill.3

Click the “Flash Fill” option, we get the following output.

Flash Fill.4

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.
AutoFill in Excel Number 1

The steps to Autofill Numbers In Excel are as follows:

Step 1:  Select cells A2 and A3 to copy the pattern till cell A6.

AutoFill in Excel Number 2

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 Number - 3

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.
AutoFill in Excel Dates.1

The steps to Autofill Dates In Excel are as follows:

Step 1:  Select cell A2 and drag the fill handle to cell A6.

Dates.2

 The output is shown below, with the date’s series auto-filled following the sequence.

Dates.3

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,

AutoFill in Excel Shortcut.1

#The Autofill Excel Shortcut to copy the formula in the column is,

AutoFill in Excel Shortcut.2

Select cell D6 and press the excel shortcut keysCtrl+D”.

Shortcut.3

The formula in cell D5 gets copied to cell D6 [Next cell, same column].

#The Autofill Excel Shortcut to copy the formula in the row is,

Shortcut.4

Select cell E2 and press the Autofill Excel Shortcut keys “Ctrl+R”.

Shortcut.5

The formula in cell D2 gets copied to cell E2 [Next cell, same row].

Autofill Formula In Excel

We will now understand Autofill Formula in Excel.

In the table, the data is,

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

The steps to Autofill Formula in Excel are as follows:

Step 1: Enter the formula =SUM(A2,B2) in cell C2.

Formula.2

Step 2: Press the “Enter” key. We will get the following output.

Formula.3

Step 3: Drag the formula, using the fill handle, from cell C2 to cell C5.

Formula.4

The output is shown above. The formulas applied, and auto-filled are in column D, for our reference, in the image below. 

AutoFill in Excel Shortcut.1

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.

File Options

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

Turnoff

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.

File Options

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

AutoFill in Excel Not Working

Frequently Asked Questions

What is the use of Autofill In Excel?

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.

FAQ 1

How to do Autofill In Excel?

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.

FAQ 2

Where are Autofill options in Excel?

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.

How to - 1

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.

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 – 

Reader Interactions

Leave a Reply

Your email address will not be published.