Wednesday, 17 Jul 2024
Technology

How to Create a Custom Autofill List in Excel

create a custom autofill list from the values in range a11 a14

The two things I love the most about Excel are its ability to automate tasks and the Flash Fill feature. When I first found out that I could create a list Excel would use to fill in my data automatically, I knew it would be my new favorite thing about Excel.

A custom fill is really handy in cases where you enter the same data frequently such as serial numbers and name lists. This will save you from the hassle of entering the same data each time you create a new spreadsheet.

While it is easy to create a custom autofill list in Excel, it is equally convenient to edit them to make changes, and even delete them. We’ve covered everything in this article, so keep reading!

Create a Custom Autofill List in Excel

Creating a custom autofill list falls under the advanced option in Excel. General lists such as numbers, names of the months, and names of the days are already listed in the feature. Remember, the list you enter must be texts or strings.

  1. Head to File > Options.
    Excel Options

  2. From the sidebar, select Advanced.

  3. Scroll down to the General section and click on the Edit Custom Lists button.
    Edit Custom Lists

  4. Under Custom lists, select NEW LIST.

Tham Khảo Thêm:  How to Connect your Roku Device to Wi-Fi without a Remote using the Roku App or Browser Extension

To enter your list:

  • Either, enter your list under the List entries section. Make sure you separate each entry with a comma.
    Add Custom List in Excel
  • Or, if you have your list in one of the active sheets, enter the range next to the Import list from cells section. Then, select the Import button.
    Create a Custom Autofill List from existing data
  1. Select Add > OK.

Use your Custom Autofill List

Using the list you created works exactly how you would use Excel’s autofill lists. For example, I just created a list with five elements: adj-890, asd-678, ajk-894, axc-674, akl-879. These are pretty random data Excel would otherwise never be able to autofill. After I added these under the Custom Lists section, I can finally autofill this list to my sheet.

Use Custom Autofill List

I entered the first list item, adj-890, on an empty cell on my spreadsheet. Then, I placed my cursor on the bottom-right edge of the cell and dragged it to the direction I wish to fill my cells. Voila! here’s the list I just created!

Edit an Existing Custom Autofill List

You may often find the need to update the list you created after some time. In such cases, you don’t have to entirely create a new list. Excel has the option to edit the list where you can change something or everything from the list.

  1. Navigate to File > Options.

  2. Click Advanced > Edit Custom Lists.

  3. Select the list you wish to edit under the Custom Lists section.
    Select Custom List in Excel

  4. Under List entries, update your values.

  5. Select the Add button on the right.
    Edit Custom List in Excel

  6. Click OK.

Tham Khảo Thêm:  Retail Demo Mode

Delete a Custom Autofill List

After you’re done with the list, it is best if you delete it. This will allow you to create similar lists in the future that begin with the same first element. Additionally, I suggest you get rid of data you don’t use so that they don’t clutter up and make your Excel run slow.

  1. From the menubar, select File.

  2. Head on to Options > Advanced.

  3. Under the General section, click Edit Custom lists.
    Edit Custom Lists

  4. Select the list you wish to remove under the Custom Lists section.

  5. Click the Delete button on the right.
    Delete a Custom List in Excel

  6. Select OK.

Why Can’t I Create a Custom Autofill List?

Cant create a custom list in Excel

If the list you created isn’t working, or if Excel isn’t importing the referenced cell from the spreadsheet, it’s almost always because you’re trying to create a list of numbers.

You cannot create a custom autofill list out of numbers. If you try to reference a cell with only numbers, you will receive a prompt that says “Cells without simple text were ignored”. Although you can enter numbers under the List entries section and even add it to your list, it simply won’t work when you try to use it on your spreadsheet.

Frequently Asked Questions

  • How do I create a custom autofill list in Excel?
  • Can I edit an existing custom autofill list in Excel?
  • How do I delete a custom autofill list in Excel?
  • Why can’t I create a custom autofill list in Excel?

Conclusion

Creating a custom autofill list in Excel is a powerful feature that saves time and effort when entering repetitive data. By following the steps outlined in this article, you will be able to create, edit, and delete custom autofill lists according to your specific needs. Enjoy the benefits of Excel’s automation capabilities and streamline your workflow with custom autofill lists!

Tham Khảo Thêm:  Dark Reading Cybersecurity Crossword Puzzle

For more information on Excel’s features and other helpful technology tips, visit Eireview.