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
- Simple Excel AutoFill Option
- AutoFill using more than one starting value
- AutoFill dates & Times in Excel
- AutoFill Text Values in Excel
- Custom List in Excel
Now let us discuss each of the methods in detail
#1 – Simple AutoFill Option
- 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 AutoFill Options box available (shown in the below screenshot).
- Click on this box. It shows many 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 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.
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 “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 types of fill by clicking on the 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 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 at 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 that 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.
Let’s create our own 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).
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 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
