How to Perform Predictive AutoFill in Excel (6 Easy Ways)

The sample dataset includes Serial No. and Name in columns B and C, respectively. We’ll use these values to fill the columns via Predictive Fill.

excel predictive autofill

Method 1 – Enabling the AutoComplete Option in Excel

Steps:

Navigate to the File tab.

Enabling AutoComplete Option in Excel

Click on Options.

The Excel Options window appears.
Go to the Advanced tab.
Check the box Enable AutoComplete for cell values under the Editing options.
Click OK.

Working on Excel Options window to enable predictive autofill in Excel

AutoComplete contains several features e.g. AutoComplete option from list, AutoComplete from another row, AutoComplete from another sheet.

♦ AutoComplete from Range

Steps:

Select cell C10.
If you write B in this black cell of your Name column, Excel will automatically suggest Bob as it links the letter to the word.

AutoComplete from Range in Excel

Press Enter to accept the suggestion.
If you write A, Excel then predicts your word using the same method.

That’s how you can complete the list using the predictive AutoFill shortcut.

Completing Cells using predictive Autofill feature in excel

Method 2 – Using Keyboard Shortcuts

Steps:

Select cell D10, where we want Excel to predict our autofill data.
Press Alt + down arrow. A list of predictions is shown just under the cell.
Click on the word that you want to insert or move the Down Arrow Key on your keyboard to select it, then press Enter to insert it.

Using Keyboard Shortcut

You can perform this method using your mouse, too. Select the cell where you want to get your data, then right-click and select Pick From Drop-down List.

Picking from drop-down list in Excel

You’ll see a list of predictions just under the cell. Select the desired option from that list.

Here’s the result.

Using Keyboard Shortcut to perform predictive autofill in Excel

Method 3 – Utilizing the Fill Handle Tool

We have a data table containing columns with the headings Name, Serial No., Day, and Date. Some of the columns are incomplete.

Utilizing Fill Handle Tool

Steps:

We will autofill the Serial No. column.
Fill in at least two cells with numbers for Excel to predict the series.
Select those two cells and move the mouse cursor to the bottom-right corner of the selection until you see the plus icon called the Fill Handle.
Double-click on it.

Double-Clicking on the Fill handle tool

This will autofill the numbers in the cells to the end of the data column.

Select the cell with data in the column Day and hover over the corner to get the Fill Handle.
Double-click on the Fill Handle.

We got the predicted autofill data.

Utilizing Fill Handle Tool to perform predictive autofill

Repeat for the Date column.

using fill handle tool to perform predictive autofill in Excel

Method 4 – Implementing a Fill Series

Steps:

Select the column Serial No., then go to the Home tab, click on the Fill drop-down icon, and select Series from the options.

Implementing Fill Series Method

The Series dialog box pops up.
Select Columns under the Series in section, choose Linear under Type, and insert the Step value as 1 and Stop value as 13.
Click OK.

using series dialog box in Excel

We got our desired numbers.

Similarly, select the entire Day column, and bring the Series dialog box.
Select AutoFill and click OK.

Using Fill Series for Days

Our days are also autofilled by this method.

For the Date column, in the Series wizard, select Date as Type, Day as Date Unit, and write the Step value as 1.

Using Fill Series for Date Column

The Date column is now automatically filled as well.

Implementing Fill Series Method to perform predictive autofill in Excel

Method 5 – Using the Flash Fill Feature

We have a data table containing the Email Addresses of some candidates. We will autofill the Names of the candidates based on their emails.

Steps:

Write one name in cell C5 where you want to use the Flash Fill feature. This will help Excel to predict your next data.
Go to the Home tab, click on the Fill drop-down, and select Flash Fill from the list.

Employing Flash Fill Feature

All the data in the column will be autofilled.

Employing Flash Fill Feature to do predictive autofill in Excel

Method 6 – Applying VBA Code

We’ll use the starting dataset.

Applying VBA Code

Steps:

Right-click on the sheet name.
Select View Code from the context menu.

clicking on view code option

Excel will automatically add a code module for Sheet7 (VBA).

code module added for particular sheet

Paste the following code into the module.

Sub AutoFill_VBA()
Range("C5:C9").AutoFill Destination:=Range("C5:C14"), Type:=xlFillDefault
End Sub

VBA code to perform predictive autofill in Excel

Currently, execute the code by pressing the play-shaped Run icon.

Execute the code

Return to the VBA worksheet and you can see the blank cells got filled.

Applying VBA Code to perform predictive autofill in excel

Why Is AutoComplete Not Working in Excel?

Excel’s AutoComplete feature is a great feature. But sometimes it doesn’t work properly. See the image below.

Why Is AutoComplete Not Working in Excel?

In the above picture, we wrote down B in cell C10, but no suggestion is showing.

There are two words, Bob and Bash in cells C5 and C7, which start with the letter B. AutoComplete can’t determine which value to use.

Solution:

Use the keyboard shortcut from Method 2. It will show all the suggestions in the drop-down list.

predictive autofill not working in excel

You can select your desired one from the list.
You’ll still be able to use AutoComplete for the letters that have been used only once in the sheet.

Fix to the problem of autocomplete feature