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 auto-fill your data. Today in this article, we will discuss some of the methods of Excel Predictive auto-fill.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Predictive Autofill in Excel (5 Methods)
In this section, we will learn five methods to perform predictive auto-fill in Excel.
1. Using the Autocomplete Option in Excel
Excel has a built-in feature to predict your next data and auto-fill the cells. To activate this option, follow these steps.
In the following example, we have some names and serial numbers in a data table. The “Name” column is incomplete. We will complete it by activating the auto-complete option.
To activate your autocomplete option, click on “File” then go to “Options”.
A new window appears. Here click on “Advanced”.
Now in the “Editing Options”, check on “Enable AutoComplete for Cell Values” then Click “OK”.
Now that we have turned on our autocomplete option, if you write “B” in the black cell of your Name column, Excel is suggesting “Bob” as it linked the letter to the word.
Press “Enter” to accept the suggestion. Now if you write “A” excel then predict your word using the same way.
That’s how you can complete the list using the predictive autofill feature.
2. Using the Keyboard Shortcut
In this method, we will apply both keyboard and mouse shortcuts to autofill data.
Let’s assume that we have a data table containing the columns “Serial No.”, “Name”, and “Location”. The Location column is partially complete. We will autofill the rest of the cell using a shortcut.
Now select cell D10 where we want the 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.
Just click on the word that you want to insert or move the Down Arrow Key on your keyboard to select and press “Enter” to select.
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”.
A list of predictions is shown just under the cell. Select the desired option from that list.
Thus you can fill your cells with predictive autofill options.
3. Using the FILL HANDLE Method
The FILL HANDLE method is an excellent way to autofill your data, text, etc. Let’s discuss this method.
In the following example, we have a data table containing columns “Name”, “Serial No.”, “Day”, “Date”. Some of the columns are incomplete. We will complete them using the Fill Handle Method.
First, we will autofill the “Serial No.” column. To do this, fill at least two cells with numbers for Excel to predict the series. Select those two cells and then move the mouse cursor to the bottom right corner of the selected cell until you see this icon
When you see the cross icon, double click on this icon and 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.
Now double click on the icon to get the predicted autofill data.
Do the same for the Date column. That’s how you can autofill your data using this method.
4. Using the 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.
Now in the datasheet, select the column Serial No. then go to Home, click on the Fill option, and select Series.
A new window appeared. Select Column as we want to autofill the columns, Step Value is 1, and Stop Value is 13 because we have 13 rows to fill. Click OK to continue.
We have got our desired numbers.
Similarly, for the “Day” column, click on Fill then Series. Then select “Autofill” and click “OK”.
Our days are also auto-filled by this method.
And for the Date Column in the series window, select Type as Date, Date Unit as Day, the Step value is 1. Then click Ok.
Our Day column is also now auto-filled.
5. Using the FLASH FILL Feature
In the following example, we have a data table containing the Email Address of some candidates. We will autofill the names of the candidates using the Flash Fill feature.
First, write one name in the column where you want to use the Flash Fill. This will help Excel to predict your next data. Now go to Data then select Flash Fill.
Click on the Flash Fill and all the data are auto-filled in a second. That’s how you can autofill your data using this method.
⏩ When you are using the FILL HANDLE or FILL SERIES method, make sure to change your number format as Date.
⏩ Flash Fill option is available from Excel 2013 to higher versions.
Today we discussed five different methods to autofill predictive data in excel. Hope this article proves useful to you. If you have any confusion, feel free to comment.