How to Perform Predictive Autofill in Excel (5 Methods)

final result

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.

Step-1:

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.

creating table

Step-2:

To activate your autocomplete option, click on “File” then go to “Options”.

option

Step-3:

A new window appears. Here click on “Advanced”.

autocomplete activate

Step-4:

Now in the “Editing Options”, check on “Enable AutoComplete for Cell Values” then Click “OK”.

selecting options

Step-5:
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.

getting result

Press “Enter” to accept the suggestion. Now if you write “A” excel then predict your word using the same way.

result

That’s how you can complete the list using the predictive autofill feature.

final result

2. Using the Keyboard Shortcut

In this method, we will apply both keyboard and mouse shortcuts to autofill data.

Step-1:

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.

creating table

Step-2:

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.

using shortcut

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.

getting result

Step-3:

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”.

using mouse shortcut

A list of predictions is shown just under the cell. Select the desired option from that list.

getting result

Thus you can fill your cells with predictive autofill options.

final result

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.

Step-1:

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.

creating table

Step-2:

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 icon 1

creating table

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.

getting result

Step-3:

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

getting result

Now double click on the icon to get the predicted autofill data.

final result

Step-4:

Do the same for the Date column. That’s how you can autofill your data using this method.

final result

4. Using the FILL SERIES Method

The FILL SERIES will give you a lot more control over your datasheet than the FILL HANDLE method.

Step-1:

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.

creating table

Step-2:

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.

creating table

We have got our desired numbers.

getting result

Step-3:

Similarly, for the “Day” column, click on Fill then Series. Then select “Autofill” and click “OK”.

getting result

Our days are also auto-filled by this method.

final result

Step-4:

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.

getting result

Our Day column is also now auto-filled.

final result

5. Using the FLASH FILL Feature

Step-1:

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.

creating table

Step-2:

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.

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.

final result

Quick Notes

⏩  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.

Conclusion

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo