AutoFill in Excel

What is AutoFill in Excel?

Autofill is a function in excel where excel identifies the series and fills the data automatically for us when we drag down the data, such as if a cell value 1 and another cell below has value 2 when we select both the cells and drag the cells down the visual representation is like a solid cross and the series is automatically filled which is the autofill feature of excel.

Top 5 Ways of AutoFill in Excel

  1. Simple Excel AutoFill Option
  2. AutoFill using more than one starting value
  3. AutoFill dates & Times in Excel
  4. AutoFill Text Values in Excel
  5. Custom List in Excel
You can download this AutoFill Excel Template here – AutoFill Excel Template

Now let us discuss each of the methods in detail

#1 – Simple AutoFill Option

  • Enter any value in a cell.

AutoFill in Excel - Example 1

  • Select that cell. You will see that in the bottom right corner of the cell, there is a small square that’s called Excel Fill Handle.” Refer below screenshot.

AutoFill in Excel - Example 1-1

  • With the help of mouse drag, this Fill Handle across the cells to be filled.

AutoFill in Excel - Example 1-2

  • Excel will fill the selected cells by either repeating the value in the first cell or by inserting a sequence from the first cell and second cell. See the above screenshot.
  • At the end of the selected cells range in the bottom right corner, there is an AutoFill Options box available (shown in the below screenshot).

AutoFill in Excel - Example 1-3

  • Click on this box. It shows many Autofill options:

AutoFill in Excel - Example 1 (AutoFill options)

  • Copy Cells – Copy the starting cell value all over the selected range.

AutoFill in Excel - Example 1 (Copy Cells)

  • Fill Series – Fill the selected range with a series of values incrementing by 1.

AutoFill in Excel - Example 1 (Fill Series)

  • Fill formatting Only – Fill the selected range with the formatting but not the values of the starting cell.

AutoFill in Excel - Example 1 (Fill Formatting Only)

  • Fill Without Formatting – Fill the selected ranges with values, not with the formatting.

AutoFill in Excel - Example 1 (Fill without fornatting)

  • Flash Fill – Fill the selected range with the data after detecting the patterns.

AutoFill in Excel - Example 1 (Fill Flash option)

#2 – AutoFill Using More Than One Starting Value

For recognizing the patterns in data, first, type the two values of your series into the first and second cells. Select both the cells and drag the Fill Handle across the range to be filled.

Excel will automatically recognize the pattern from these two cell values and fill the range of cells in continuation. By using these steps, we can fill the range of cells by increment or decrements. See the below screenshot.

AutoFill in Excel - Example 2

Drag these two cells, and it will auto-fill these value pattern across the selected cell range, as shown below:

AutoFill in Excel - Example 2

Note: If you want to fill the cell range with alternative repeated values, fill the first two cells with the values.

AutoFill in Excel - Example 2-2

Select those cells, drag the Fill Handle across the selected range, and then click on the “AutoFill Options.” Then choose the option “Copy Cells” to repeat the starting cell values across the selected range, and the final result is shown below:

AutoFill in Excel - Example 2-3

#3 – AutoFill Dates & Times in Excel

We also can Autofill the dates and times in Excel. How to do this will understand with some example:

  • Type any date or time in a cell

AutoFill in Excel - Example 3

  • Drag the Fill Handle across the selected range.

AutoFill in Excel - Example 3-1

  • The selected range will fill in a series of dates by adding one day.

AutoFill in Excel - Example 3-2

  • You can change the different types of fill by clicking on the Autofill options box.

AutoFill in Excel - Example 3-3

Note: If you drag the time across the selected range of cells, it will fill with a series of times by adding one hour. Refer below screenshot.

AutoFill in Excel - Example 3-5

When we drag the time, the Autofill options box has 4 same options as we discussed under the “Same AutoFill.”

AutoFill in Excel - Example 3-6

For dates, Excel has additional options under Excel Auto Fill Options along with 4 options (Copy cells, Fill Series, Fill formatting only, Fill without formatting). These additional Excel Auto Fill Options are:

  • Fill days – While filling the selected cells, this observes the pattern in the day.

AutoFill in Excel - Example 3 (Fill Days)

  • Fill Weekdays – Look at the patterns in the day while filling the selected cells but exclude Saturdays or Sundays in the series.

AutoFill in Excel - Example 3 (Fill WeekDays)

  • Fill Months – Look for the pattern in the month while filling the selected cells.

Example 3 (Fill months)

  • Fill Years – Look for the pattern in the year while filling the selected cells.

Example 3 (Fill Years)

#4 – AutoFill Text Values in Excel

The excel fill the row or column with text values by repeating the values in the selected range of cells. There are some text values that Excel recognizes as a part of the series. They are:

  • Weekdays (shown in below screenshot)

Example (Weekdays)

  • Months (shown in the below screenshot)

Example (Months)

  • Rank (shown in the below screenshot)

Example (Rank)

  • Text & Number (shown in below screenshot)

Example (Text and Numbers)

#5 – Creating Custom Lists in Excel

For Auto Fill values, Excel provides a customs facility where we can create a list of items (called custom list). Below are the steps for creating a custom list:

  • Go to File.

Creating Custom list

  • Click on Options.

Creating Custom list 1

  • A dialog box will open for Excel Options. Click on Advanced in the left pane.

Creating Custom list (Excel Options Dialog box)

  • Some editing options will display in the right side box. Go to the General section and click on the Edit Custom Lists button, as shown below:

Creating Custom list (Edit Custom List)

  • A Custom List dialog box will open.

Creating Custom list (Custom List box)

Under the Custom Lists section in a left side box, as we can see, these are system-defined lists.

Here we can create our custom list and use it in the same way as the System defined custom lists.

Let’s create our list by typing some values one by one in the right side box under the List entries section and click on the Add button (shown in the below screenshot).

Creating Custom list (Custom List box) 1

Click on OK and exit from the Excel Options dialog box.

Now Enter the first value of our list in a cell manually and drag the Fill Handle into the selected range of cells, as shown below.

Creating Custom list (Custom List box) 2

It will fill with the list of items created by us.

Creating Custom list (Custom List box) 3

Recommended Articles

This has been a guide to AutoFill in Excel. Here we discuss the top 5 types of ways to use AutoFill Option n Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *