AutoFill in Excel

AutoFill in Excel (Table of Contents)

• Definition of AutoFill in Excel
• How to Use AutoFill in Excel?

What is AutoFill in Excel?

Sometimes we work on the data in Excel which is repetitive. It can be a sequence of dates, a specific series of numbers or patterns. Instead of entering this type of data manually, Excel provides a very useful feature called AutoFill Feature. This saves a lot of time while working on a spreadsheet.

Excel AutoFill Option allows you to create an entire column or rows of data which are based on the values from other cells. In other words, this feature fills cells with data that follows a pattern or that are based on data in other cells.

With this facility, we can work on spreadsheets more efficiently and can quickly fill cells with a series of data.

In this article, we will cover the 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. Creating Custom Lists in Excel

How to Use AutoFill in Excel?

Here we will understand the working of Excel AutoFill Option with some examples.

#1 – Simple AutoFill Option in Excel

• Enter any value in a cell.

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

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

• 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 Excel AutoFill Options box available (shown in the below screenshot).

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

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

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

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

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

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

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

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

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

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

#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

• Drag the Fill Handle across the selected range.

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

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

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.

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

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.

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

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

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

#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 which Excel recognizes as a part of the series. They are:

• Weekdays (shown in below screenshot)

• Months (shown in below screenshot)

• Rank (shown in below screenshot)

• Text & Number (shown in below screenshot)

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

• Click on Options.

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

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

• A Custom List dialog box will open.

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

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

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

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

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

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

Recommended Articles

This has been a guide to AutoFill in Excel. Here we discuss 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 –

• Excel VBA Weekday
• Excel Sum by Color
• Excel RANK Function
• Flash Fill in Excel
• Solver in Excel
• How to Column Sort in Excel?
• How to Sort by Color in Excel?
• Types of Formula Errors in Excel