Excel AutoFill (16 Useful Examples)

In this article, we have discussed in detail how to use the Excel AutoFill feature. We have demonstrated 16 different examples to show the use of Excel AutoFill feature. We have explored the Growth Trend and Fill Series commands. We have also talked about the Flash Fill command. Lastly, we have discussed how to enable/disable or switch on/off the AutoFill feature.

The Excel AutoFill feature is very important for users as it enables efficient and accurate data entry by automatically populating a range of cells with values, formulas, and patterns.

Download Practice Workbook

You can download this practice workbook while going through the article.

Excel AutoFill.xlsm

How to Use Excel AutoFill Feature

1. Excel AutoFill with Fill Handle

Type 1 and 2 in cell B5 and cell B6 respectively.
Select range B5:B6 and use Fill Handle to AutoFill data in range B7:B14.

2. Use Formula to AutoFill

Go to cell B5 and put the formula based on the SEQUENCE function.

=SEQUENCE(10)

Serial numbers will generate automatically in range B5:B14.

Read More: How to Apply AutoFill Shortcut

3. AutoFill Cells Containing Both Text and Numerical Values

Type Week 1 and Week 2 in cell B5 and cell B6 respectively.
Select range B5:B6 and use Fill Handle to AutoFill data in range B7:B14.

4. AutoFill Date

Go to cell B5 and type 7/21/2023.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B14.

5. AutoFill Time

Go to cell B5 and type 11:55 AM.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B14.

6. AutoFill Series

6.1. Days of a Week

Go to cell B5 and type Monday.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B11.

6.2. Months of a Year

Go to cell B5 and type January.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B16.

6.3. AutoFill Rank

Go to cell B5 and type 1st.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B14.

7. AutoFill Formulas

Go to cell D5 and put the formula based on the CONCATENATE function.

=CONCATENATE(B5," ",C5)

Select cell D5 and use Fill Handle to AutoFill data in range D6:D14.

8. AutoFill from Custom List

Go to the File tab.

Select Options.

Excel Options dialog box will open.
Click on the Advanced tab.
Click on the Edit Custom Lists option under the General section.

Put the list values one by one in the box below the List entries header.
Click Add >> press OK.

Go to cell B5 and put the first entry of the custom list which is Morning Shift.
Select cell B5 and use Fill Handle to AutoFill data in range B6:B7.
You will see the entries of the custom list in range B5:B7.

Read More: Use AutoFill with Formula

9. AutoFill to Get Repeating Values

Type YES, NO, TRUE, FALSE– one by one in each cell of range B5:B8.
Select range B5:B8 and use Fill Handle to AutoFill data in range B9:B16.

Read More: Create a Custom AutoFill List

10. AutoFill Horizontally or Vertically

Go to cell C4 and type Saturday.
Select cell C4 and use Fill Handle to AutoFill data in range D4:I4.

Type Week 10 and Week 9 in cell B14 and cell B13 vertically in column B.
Select range B13:B14 and use Fill Handle to AutoFill data in range B12:B5.

Read More: AutoFill from List

11. AutoFill Multiple Rows or Columns

Write the week number and name of day in range B5:D6 as shown below.

Select range B5:D6 and use Fill Handle to AutoFill data in range B7:D11.

Select range C5:D11 and use Fill Handle to AutoFill data in range E5:I11.

12. AutoFill while Skipping Rows

Type 1 in cell B5 and keep cell B6 blank.
Select range B5:B6 and use Fill Handle to AutoFill data in range B7:B14.

13. AutoFill Geometric Pattern (Growth Series)

13.1. Use Growth Trend

Type 1, 2 in cell B5 and cell B6 respectively.
Select range B5:B6 >> right-click and drag down the Fill Handle from range B7:B14.

Select Growth Trend from the context menu.

You will have an exponential series in range B5:B14.

13.2. Use Series Option

Type 1, 2 in cell B5 and cell B6 respectively.
Select range B5:B6 >> Right-click and drag down the Fill Handle from range B7:B14.

Select Series from the context menu.

The Series dialog box will open.
Select Growth in the Type section.
Put 2 as the Step value and press OK.

You will have an exponential series in range B5:B14.

Read More: AutoFill Sequential Letters

14. Use Fill Series Command to AutoFill Data

Type 1 in cell B5.
Select cell B5 >> right-click and drag down the Fill Handle from range B6:B14.

Select Fill Series from the context menu.

You will see the output in range B5:B14.

15. Flash Fill in Excel

15.1. Use Flash Fill to Join Cell Content

We have a dataset of first and last names in different columns.

Go to cell D5 and write the full name of the person (Ethan Anderson) joining the first name and last name.
Select cell D5 >> go to the Data tab >> select the Flash Fill command from the Data Tools group.

You will have the full names in range D5:D14.

Read More: Fill Down Blanks

15.2. Use Flash Fill to Split Cell Content

Write the first name (Ethan) and last name (Anderson) in cell C5 and cell D5 respectively splitting the full name (Ethan Anderson) in cell B5.
Select cell C5 >> go to the Data tab >> select the Flash Fill command from the Data Tools group.

Select cell D5 >> use the Flash Fill command from the Data Tools group.

You will have the first and last names being split from the full names in range C5:D14.

Read More: Excel Repeat Formula Pattern

15.3. Use Flash Fill to Extract Data

We have a dataset that has a combination of section, ID, and name in a column. Now we will extract only ID from the dataset.

Write the ID in cell C5 of the corresponding cell B5.
Select cell C5 >> use the Flash Fill command from the Data Tools group.

You will have the IDs extracted from column B in range C5:C14.

Note:

You can use keyboard shortcut Ctrl+ E for Flash Fill.

16. Apply VBA to AutoFill Data

Type Week 1, Week 2 in cell B5 and cell B6 respectively.

Go to the Developer tab >> Visual Basic.

Select Insert >> Module.

Paste the following code in your VBA Macro Editor.
Press the Run button or F5 key to run the code.

Sub AutoFill_VBA_Code()
    'set active worksheet as WS
    Dim WS As Worksheet
    Set WS = ActiveSheet
    'AutoFill data in the specified range
    WS.Range("B5:B6").AutoFill Destination:=WS.Range("B5:B14"), Type:=xlFillDefault
End Sub

You will see the outcome in range B5:B14.

How to Enable/Disable AutoFill Feature in Excel

Go to the File tab.

Select Options.

Excel Options dialog box will open.
Go to the Advanced tab.
Look for the Editing options section on the right side.
Check/uncheck the Enable fill handle and cell drag-and-drop option to enable/disable the AutoFill feature.
Press OK to save changes.

Read More: Turn Off AutoFill

How to Turn On/Off AutoFill Options

Follow the steps in the previous method and go to the Advanced tab of the Excel Options dialog box.
Look for the Cut, copy and paste section.
Check/uncheck the Show Paste Options button when content is pasted option to turn on/off the AutoFill feature.
Press OK to save changes.

Things to Remember

There are a few things to remember while using the Excel AutoFill feature.

Flash Fill option is available in Excel 2013 and later versions.
Change the number format according to your preference while using the Fill Handle or Fill Series method.
Right-click and drag the Fill Handle to get Growth Trend, Fill Series and such commands.

Conclusion

In this article, we have discussed how to use the Excel AutoFill feature in detail. This article aims to enhance users’ efficiency and productivity when it comes to filling data with Excel AutoFill feature. If you have any questions regarding this essay, don’t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.

Frequently Asked Questions

1. How to AutoFill data without dragging the Fill Handle?

You can use the Fill Series command to AutoFill data without dragging the Fill Handle.

2. Can I AutoFill non-linear patterns?

Yes, you can AutoFill non-linear and other geometric patterns.

3. Can I use AutoFill to fill months and years?

Yes, you can use AutoFill to fill months and years. Just put the initial two or three values and then use Fill Handle to extend the series.

Excel AutoFill: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!