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.
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.
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
- Next, do the same for the Date column.
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.
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.
We have got our desired numbers.
- Similarly, select the entire Day column, and bring the Series dialog box. Then select AutoFill and click OK.
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.
Our Date column is now automatically filled as well.
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.
- After clicking on the Flash Fill, all the data are auto filled in a second.
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.
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.
By this action, Excel will automatically add a code module for Sheet7 (VBA).
- After that, paste the following code into the module.
Sub AutoFill_VBA()
Range("C5:C9").AutoFill Destination:=Range("C5:C14"), Type:=xlFillDefault
End Sub
- Currently, execute the code by pressing the play-shaped Run icon.
- Finally, return to the VBA worksheet and you can see the blank cells got auto-filled. Thus, you can autofill cells using VBA code.
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.
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.
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.
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.
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.
