Excel Guides

Using AutoComplete with Disjointed Lists in Excel

When working with large data sets in Excel, it can be helpful to use the AutoComplete feature to fill in cells with data that is in a list. However, if the data is in a disjointed list, meaning it is not in one continuous column or row, then the AutoComplete feature will not work correctly. In order to use AutoComplete with disjointed lists, you must first convert the data into a table.

To convert data into a table, select any cell in the data set and then go to the Insert tab and click on Table. In the Create Table dialog box, make sure that your data has headers and that the My table has headers option is selected. Then click OK to create the table.

Once your data is in a table, you can then use the AutoComplete feature by selecting any cell in the table and then going to the Data tab and clicking on Data Validation. In the Data Validation dialog box, make sure that the Allow drop-down menu is set to Custom and then enter this formula into the Formula field:

=INDEX($A$2:$A$1048576,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$1048576),0))

This formula will allow you to use AutoComplete with a disjointed list of data. Simply start typing in the cell and Excel will automatically fill in the rest of the cell for you based on what is in the list.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.