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

Get FREE Advanced Excel Exercises with Solutions!

The predictive AutoFill feature in Excel can save you time when you are entering lots of similar information in a column. Excel includes some handy time-saving features to autofill your data. In this article, we will discuss 6 methods of predictive autofill in Excel. So, let’s go through the entire article to understand the topic properly.


How to Perform Predictive AutoFill in Excel: 6 Easy Ways

For ease of understanding, we are going to use a List of Students. This dataset includes Serial No. and Name in columns B and C respectively.

excel predictive autofill

In this section, we will learn six methods to perform predictive autofill in Excel. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Enabling AutoComplete Option in Excel

Excel has a built-in feature called AutoComplete to predict your next data and autofill the cells. To activate this option, follow these steps.

📌 Steps:

  • At the very beginning, navigate to the File tab.

Enabling AutoComplete Option in Excel

  • Then, click on Options on the left task pane.

Immediately, the Excel Options window appears before us.

  • Here, go to the Advanced tab.
  • After that, check the box of Enable AutoComplete for cell values under the Editing options.
  • Later, click OK.

Working on Excel Options window to enable predictive autofill in Excel

This time, your Excel is capable of doing AutoComplete. There are several uses of this feature e.g. AutoComplete option from list, AutoComplete from another row, AutoComplete from another sheet. For your convenience, we are discussing the use of AutoComplete from a range here. 

♦ AutoComplete from Range

In this section, we’ll use a range of cells to AutoComplete the remaining cells. It’s so simple. Just follow along.

📌 Steps:

  • First of all, select cell C10.
  • Now, 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

Then, you can press ENTER to accept the suggestion.
Now 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

Read More: How to Repeat Formula Pattern in Excel


2. Using Keyboard Shortcut

Now, I know what you’re thinking. Are there any shortcut keys? Lucky you! There are shortcut keys to do the same task. In this method, we will apply both keyboard and mouse shortcuts to autofill data. So let’s follow.

📌 Steps:

  • Now, select cell D10 where we want Excel to predict our autofill data. Press ALT + 🔽 on your keyboard. A list of predictions is shown just under the cell. From there, you can choose your desired word.
  • Afterward, just 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 select 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 on your mouse and from the appeared options, select Pick From Drop-down List.

Picking from drop-down list in Excel

We can see a list of predictions just under the cell. Then, select the desired option from that list.

Thus, you can fill your cells with predictive autofill options.

Using Keyboard Shortcut to perform predictive autofill in Excel


3. Utilizing the Fill Handle Tool

In the following example, we have a data table containing columns with the headings Name, Serial No., Day, and Date.

Utilizing Fill Handle Tool

Here, we can notice that some of the columns are incomplete. The Fill Handle tool is an excellent way to autofill your data, text, etc. We will complete them using the Fill Handle tool.

📌 Steps:

  • At first, we will autofill the Serial No. column. To do this, fill at least two cells with numbers for Excel to predict the series. After that, select those two cells and then move the mouse cursor to the bottom right corner of the selected cell until you see this icon ➕.
  • Then, double-click on it.

Double-Clicking on the Fill handle tool

Instantly, it will autofill the numbers in the cells to the end of the data row.

In the case of the Day column, select the cell with data and move your mouse until the crossed icon is shown.

  • Following this, double-click on the icon.

Here, we got the predicted autofill data.

Utilizing Fill Handle Tool to perform predictive autofill

  • Next, do the same for the Date column.

using fill handle tool to perform predictive autofill in Excel

That’s how you can autofill your data using this method.

Read More: How to Autofill Dates in Excel Without Dragging


4. Implementing Fill Series Method

The Fill Series will give you a lot more control over your datasheet than the Fill Handle method. To perform this method, we will use the same data set that we used in the previous procedure. So let’s begin.

📌 Steps:

  • Firstly, in the datasheet, 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

Suddenly, the Series dialog box pops up.

  • In the box, select Columns under the Series in section, choose Linear under Type, and give the Step value as 1 and Stop value as 13.
  • Lastly, click OK.

using series dialog box in Excel

We have got our desired numbers.

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

Using Fill Series for Days

Our days are also auto-filled by this method.

  • And, for the Date column, in the Series wizard, select Date as Type, Day as Date Unit, and write the Step value as 1. Then click OK.

Using Fill Series for Date Column

Our Date column is now automatically filled as well.

Implementing Fill Series Method to perform predictive autofill in Excel

Read More: How to Autofill Days of Week Based on Date in Excel


5. Employing Flash Fill Feature

In the following example, we have a data table containing the Email Addresses of some candidates. We will autofill the Names of the candidates using the Flash Fill feature. So, without further delay, let’s dive in.

📌 Steps:

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

Employing Flash Fill Feature

  • After clicking on the Flash Fill, all the data are auto filled in a second.

Employing Flash Fill Feature to do predictive autofill in Excel

That’s how you can autofill your data using this method.


6. Applying VBA Code

In this method, we’ll learn how we can autofill the remaining cells based on the previously filled-up cells.

Applying VBA Code

But here lies the surprise. We will accomplish this with the VBA code. Let’s explore the method step by step.

📌 Steps:

  • First and forthwith, right-click on the sheet name. Then, select View Code from the context menu.

clicking on view code option

By this action, Excel will automatically add a code module for Sheet7 (VBA).

code module added for particular sheet

  • After that, 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

  • Finally, return to the VBA worksheet and you can see the blank cells got auto-filled. Thus, you can autofill cells using VBA code.

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 can see that we wrote down B in cell C10, but no suggestion is showing like in the previous scenarios. What could be the possible reason behind this incident? Any guesses?? Actually, there are two words Bob and Bash in cells C5 and C7 which start with the letter B. For this reason, AutoComplete is not working now.

Solution:

If this incident happens, you could use the keyboard shortcut stated in Method 2. Then, it will show all the suggestions in the drop-down list.

predictive autofill not working in excel

Therefore, you can select your desired one from the list.
Also, you can get the benefit of AutoComplete for the letters that have been used only once in the sheet.

Fix to the problem of autocomplete feature

Look, here, after writing “E“, Excel is suggesting the word, Erina.


Quick Notes

⏩  When you are using the Fill Handle or Fill Series method, make sure to change your number format to Date.

Flash Fill option is available from Excel 2013 to higher versions.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to perform predictive autofill in Excel in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


Further Readings


<< Go Back to Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo